DIPAAL: DIstributed PostgreSQL-based AIS Analytics and Loading

Mikkelsen, Mikael Vind ; Josefsen, Lau Ernebjerg ; Klitgaard, Alex Skov

4. term

Software, Master

2023

2023-06-15

25 pages

AIS data show promise for analytical purposes, but as the data are not intended for analysis, the data need to be cleaned, processed, and stored before being usable. This paper presents an extension of DIPAAL, a system consisting of an efficient and modular ETL process for loading AIS data, as well as a distributed data warehouse storing the trajectories of ships. A spatially distributed data warehouse, with granularized cell and heatmap representations, is designed, developed, and evaluated. At the time of writing, DIPAAL stores 414 million kilometres of ship trajectories and more than 10 billion rows in the largest relation. It is found that the introduced granularized cell representation resolved out-of-memory errors of previous work, while improving the runtime of up to 324% compared to a trajectory-based query. It is also found that the spatially divided shards enable a consistently good scale up for both cell and heatmap analytics in large areas, ranging between 354% to 1164% with a 5x increase in workers. Lastly, it is found that the spatial divisions become slightly skewed over time, as traffic patterns evolve.

Spatio-temporal ; AIS ; trajectory ; distributed ; ETL ; RDBMS ; Moving Object ; Cell representation ; Heatmaps ; Spatial partitioning ; PostgreSQL ; PostGIS ; MobilityDB ; Citus

Download
View record in AAU Student Projects