MarineCadastre.gov also offer a Track Builder script that creates lines out of the broadcast points. (It can also join additional information from the vessel and voyage layers.) We could reproduce the line creation step using tools such as Processing’s Point to path but this post will show how to create PostGIS trajectories instead.
First, we have to import the points into PostGIS using either DB Manager or Processing’s Import into PostGIS tool:
Then we can create the trajectories. I’ve opted to create a materialized view:
The first part of the query creates a temporary table called ptm (short for PointM). This step adds time stamp information to each point. The second part of the query then aggregates these PointMs into trajectories of type LineStringM.
CREATE MATERIALIZED VIEW ais.trajectory AS WITH ptm AS ( SELECT b.mmsi, st_makepointm( st_x(b.geom), st_y(b.geom), date_part('epoch', b.basedatetime) ) AS pt, b.basedatetime t FROM ais.broadcast b ORDER BY mmsi, basedatetime ) SELECT row_number() OVER () AS id, st_makeline(ptm.pt) AS st_makeline, ptm.mmsi, min(ptm.t) AS min_t, max(ptm.t) AS max_t FROM ptm GROUP BY ptm.mmsi WITH DATA;
The trajectory start and end times (min_t and max_t) are optional but they can help speed up future queries.
One of the advantages of creating trajectory lines is that they render many times faster than the original points.
Of course, we end up with some artifacts at the border of the dataset extent. (Files are split by UTM zone.) Trajectories connect the last known position before the vessel left the observed area with the position of reentry. This results, for example, in vertical lines which you can see in the bottom left corner of the above screenshot.
We can also perform trajectory analysis, such as trajectory generalization:
This is a first proof of concept. It would be great to have a script that automatically fetches the datasets for a specified time frame and list of UTM zones and loads them into PostGIS for further processing. In addition, it would be great to also make use of the information in the vessel and voyage tables, thus splitting up trajectories into individual voyages.
- Movement data in GIS: issues & ideas
- Movement data in GIS #2: visualizing individual trajectories
- Movement data in GIS #3: visualizing massive trajectory datasets
- Movement data in GIS #4: variations over time
- Movement data in GIS #5: current research topics
- Movement data in GIS #6: updates from AGILE2017
- Movement data in GIS #7: animated trajectories with TimeManager
- Movement data in GIS #8: edge bundling for flow maps
- Movement data in GIS extra: trajectory generalization code and sample data
- Movement data in GIS #9: trajectory data models