Now we are ready to construct ship trajectories out of their individual observations:
CREATE TABLE Ships(MMSI, Trip, SOG, COG) AS SELECT MMSI, tgeompoint_seq(array_agg(tgeompoint_inst( ST_Transform(Geom, 25832), T) ORDER BY T)), tfloat_seq(array_agg(tfloat_inst(SOG, T) ORDER BY T) FILTER (WHERE SOG IS NOT NULL)), tfloat_seq(array_agg(tfloat_inst(COG, T) ORDER BY T) FILTER (WHERE COG IS NOT NULL)) FROM AISInputFiltered GROUP BY MMSI; -- Query returned successfully: 2995 rows affected, 01:16 minutes execution time.
This query constructs, per ship, its spatiotemporal trajectory
Trip, and two temporal attributes
Trip is a temporal geometry point, and both
COG are temporal floats. MobilityDB builds on the coordinate transformation feature of PostGIS. Here the SRID 25832 (European Terrestrial Reference System 1989) is used, because it is the one advised by Danish Maritime Authority in the download page of this dataset. Now, let's visualize the constructed trajectories in QGIS.
ALTER TABLE Ships ADD COLUMN Traj geometry; UPDATE Ships SET Traj= trajectory(Trip); -- Query returned successfully: 2995 rows affected, 3.8 secs execution time.