A journey in geospatial timeseries
summary
An application ingests AIS from the Norwegian Coastal Administration and navigational maps from the Norwegian Mapping Authority into PostgreSQL using jsonb for varying metadata, PostGIS with geography SRID 4326 for positions, and TimescaleDB hypertables chunked by one-day intervals. Continuous aggregates roll older data and retention drops raw points after seven days, keeping recent high-resolution trails while reducing storage. The result is a working vessel-tracking app that replaces a multi-database stack with plain SQL and simpler architecture and queries.
AI-written summary · about
chapters (8)
- 0:00Introduction to talk
- 1:04Postgres for geospatial timeseries
- 3:31AIS data and JSONB storage
- 7:03Geospatial distances with PostGIS
- 9:12Querying ship trails with PostGIS
- 11:23Handling endless data streams
- 12:47TimescaleDB for time series
- 15:20Summary and benefits
related talks
11mA journey in geospatial timeseries
30mBuilding geospatial apps on Postgres, PostGIS, & Citus at large scale
02h 44mBuilding Geographical APIs with Open Data, PostgreSQL & PostGIS
46mGetting Down To Earth: Geospatial Analysis With Rails
51mPostgreSQL, The Time-Series Database You Want
21mActual trees, not b-trees: or how I found PostgreSQL through PostGIS
50mUnearthing the Past with PostgreSQL...
39mTimescaleDB: Re-engineering PostgreSQL as a time-series database