FOSS4G 2023

PostGIS supports geometries with a Z dimension and geometries with M (measure) values, but there are not a lot of examples of both of these being used together. One use case is the analysis of airplane tracks which requires both - that is to say every vertex has an altitude and a timestamp.

This talk will show how live positional data transmitted from aircraft can be accessed in a PostGIS database. I will then show how a sequence of these positions can be represented effectively as LINESTRINGZM geometries which can be analyzed as trajectories using native PostGIS functions.

With spatial SQL, we can do things such as determine anomalous changes in an aircraft's velocity or altitude and find the exact point in time at which two aircraft came closest to one another. The focus on the talk will be showing how future work on large datasets of ADS-B data can be done using PostGIS and other open-source geospatial tools.

I will cover how to use Python and PostgreSQL's PL/Python language extension to import the data and QGIS to render the data, but the analysis will be be done in SQL.