Notes From PostGIS Day 2022

Thanks to the excessive length of this year’s PostGIS-Day schedule, I could catch a couple of hours on Friday morning. Here’s a quick summary of some of the talks I saw.

Ryan Lambert of Rust Proof Labs took a deep dive into some fantastic PostGIS wizardry for routing outside of roads, such as waterways, indoors or on access-restricted private roads. A considerable part of the solution to these complex problems comes down to deep knowledge of PostGIS’ functionality but doing basic things like understanding your data, cleaning data, understanding and documenting edge cases and making decisions on the problems you don’t want to solve. Ryan also recently published a book on PostGIS and OpenStreetMap.

Brian Timoney showed how to create goal-scoring heat maps for NHL games, inspired shot-efficiency maps Kirk Goldberry did for the NBA. He used the public NHL API as the data source data, downloaded data using pgsql-http, and created the visualisation all in PostGIS. Also, what a refreshing way to present; I had a great laugh watching Brian.

Martin Davis talked us through some new features introduced in PostGIS 3.3), such as

Martin also gave a sneak peek to features landing in future versions of PostGIS, such as validating polygon coverage, simplify boundaries on coverage polygons, and simplifying inner boundaries while keeping outer boundaries unchanged.

Brendan Farrell presented db2vector, which creates bespoke vector-tile APIs from data in PostGIS. Db2vector allows you to specify a specific SQL query for each API endpoint, so you can quickly create different web maps from a single data source with great flexibility. (Unfortunately, I couldn’t find any page to link to detailed information about the service.)

And finally, Paul Ramsey talked about Moving Objects, a proof of concept he has built to demonstrate how updates to records in a Postgres database can be propagated in near real-time to clients. It uses a mix of triggers, Postgres notifications, and pg_eventserv to push notifications to web clients via WebSockets.