PostGIS Day 2024 Summary

In late November, on the day after GIS Day, we hosted the annual PostGIS day online event. 22 speakers from around the world, in an agenda that ran from mid-afternoon in Europe to mid-afternoon on the Pacific coast.

We had an amazing collection of speakers, exploring all aspects of PostGIS, from highly technical specifics, to big picture culture and history. A full playlist of PostGIS Day 2024 is available on the Crunchy Data YouTube channel. Here’s a highlight reel of the talks and themes throughout the day.

The Old and the New

My contribution to the day is a historical look back at the history of databases and spatial databases. The roots of PostGIS are the roots of PostgreSQL, and the roots of PostgreSQL in turn go back to the dawn of databases. The history of software involves a lot of coincidences, and turns on particular characters sometimes, but it’s never (too) dull!

Joshua Carlson delivered one of the stand-out talks of the day, exploring how he built a very old-style cartographic product–a street with a grid-based index to find street names–using a very new-style approach–spatial SQL to generate the grid and find the grid numbers for each street to fill in the index. Put Making a Dynamic Street Map Index with ST_SquareGrid at the top of your video play list.

For the past ten years, Brian Timoney has been warning geospatial practitioners about the complexity of the systems they are delivering to end users. In Simplify, simplify, simplify, Timoney both walks the walk and talks the talk, delivering denunciations of GIS dashboard mania, while building out a minimalist mapping solution using just PostGIS, SVG and (yes!) Excel. It turns out that SVG is an excellent medium for delivering cartographic products, and you can generate them entirely in PostgreSQL/PostGIS.

And then, for example, work with them directly in MS Word! (This is, as Brian says, what customers are looking for, not a dashboard.)

Steve Pousty brought the mandatory AI-centric talk, but avoided the hype and stuck to the practicalities of the new era: what do the terms mean, what are the models for, what tools are there in PostgreSQL to make use of them, and in particular what makes sense for spatial practitioners.

Parquet and PostGIS

Our own Rekha Khandhadia showed off the power of our latest product, Crunchy Data Warehouse, when combined with the massive map data available from Overture, and the analytical tools of PostGIS.

In Geospatial Analytics with GeoParquet, using only SQL, she addressed the 300GB of Overture data, and ran a spatial analysis on the fly over the state of Michigan.

GeoParquet is the new kid on the block, with lots of folks in the researching phase.

Brian Loomis of Nikola Motor shared how he is using PostGIS/PostgreSQL to quantify how much time their trucks are spending in various impacted communities, for reporting to the California Air Resources Board (CARB). Loomis also shares his use case for Crunchy Data Warehouse. In working with 4 billion points a day, they're using s3 to store partitioned data in Parquet. Loomis has some useful notes on Parquet file sizes and structure optimization if you're new to that topic.

The Larger World

PostGIS doesn’t exist in a vacuum, it’s part of a larger open ecosystem of data and other software and organizations trying to solve problems. Bonny McClain returned to PostGIS day with an update on her work on urban climate issues and using SQL as an engine for public policy analysis.

At Overture Maps, a collaboration of industry members is synthesizing a public world base map from multiple sources, and Dana Bauer and Jake Wasserman got us Started With Overture Maps, how PostGIS can make use of the data and what is being built. At the other end of the spectrum, Felt is building end-user facing tools for spatial collaboration, and Michal Migurski walked us through a demo of pulling climate data from a PostGIS service, visualizing and story telling with the data.

Meanwhile, in the daily grind of GIS operations, Kurt Menke is seeing a wave of open source adoption in Danish municipalities, as QGIS and PostGIS take over and old MapInfo installations are phased out. The pattern of adoption across the nation is very interesting and Kurt provides lots of maps.

This poll from the webinar shows a lot of QGIS use in our PostGIS Day audience! Not surprising, really, QGIS is the easiest desktop GIS to integrate with PostGIS.

Finally, we got to hear from Pekka Sarkola on How to Connect PostGIS to ArcGIS and the answer is “it depends”. There’s a lot of complexity in the Esri environment, lots of products, and lots of history, so the precise way you want to connect will depend on your needs. But you can do it, just remember to read the docs carefully.

Regina with a pure SQL exploration of PostGIS-related extensions, shared PostGIS Surprise, the Sequel;

The Nitty Gritty

Using PostGIS often means accessing and using from another language, and Tom Payne provided a great deep dive into using PostGIS from within the Go language. Tom’s work on 3D geospatial is built into flight devices to warn aviators of hazards in the Swiss alps. Also in the world of 3D, Loïc Bartoletti explained SFCGAL and PostGIS, bringing new algorithms into PostGIS – in particular algorithms working with volumetric types and 3D data.

Finally, Maxime Schoemans introduced us to the power of Multi-entry Generalized Search Trees – imagine the current PostGIS spatial indexes, but with each spatial object potentially represented with multiple index keys. The potential for performance improvements, as Maxime demonstrated, is very high, particularly for data involving large and complex shapes.

All these speakers crossed the threshold of true nitty – they talked about C and core code bindings!

Routing and Driving

Route finding and fleet management continue to be ever-green topics in the world of geospatial, as the world keeps spinning faster on more and more wheels. While it is tempting to reach for pgRouting to solve any routing problem, both Ibrahim Saricicek and Dennis Boachie Boateng counseled making sure your routing solutions matches your routing problem.

Everyone has a favourite cost for routing, and this poll shows the PostGIS day audience pretty divided on the right one.

Ibrahim provided a good comparison of different open source routing options, in a Survey of pgRouting and Other Open Source Routing Tools.

And Dennis went all-in on the bespoke routing path, describing the core principles of routing, and demonstrating his own Custom Routing Solutions with PostGIS, in particular a live example of his own mobile way-finding application.

You get an API, you get an API, you all get APIs!

Web APIs to PostGIS are always a rich topic, because there’s a lot of them, and everyone has a favorite specification or implementation language. Michael Keller shared his incredibly well fleshed out FastCollection API, a Python state-of-the-art implementation of the Open Geospatial Consortium standards, with a few extra API end points for easier web application building. We are looking forward to seeing Michael in future years, as he builds out a complete example application on top of this API.

Elizabeth Christensen showed off our favourite API tools, the lightweight services we use for building Web maps from PostGIS – pg_featureserv and pg_tileserv. Simplicity of deployment and interface are what distinguish these Go language services, just download and run, no dependencies, no fuss.

Martin Davis also showed off our microservices, but in the context of the Uber global hexagonal grid system. He built a live dashboard specifically to show Summarizing Data in H3 with PostGIS and pg_tileserv. All the summary maps were generated on-the-fly, which is particularly impressive given the data on the backend.

Topological Data Models

Two approaches to managing data with shared boundaries were demonstrated at PostGIS day this year. The “traditional” approach was explained by Felipe Matas in Simplify Space Relations like Country/State Divisions with Postgis Topology. PostGIS comes with a built-in topology model, but understanding the moving parts can be hard, and Felipe provided a great talk with (importantly) a lot of pictures about how a topological model represents something like administrative boundaries.

Yao Cui from the British Columbia Geological Survey showed off the data model he developed 20 years ago to handle the difficult problem of keeping geological data clean while still supporting a robust data update cycle. Cui’s approach uses PostGIS to Facilitate Polygonal Map Integration Without Edge Matching. He keeps the topology implicit, and just manages the boundaries between areas, with a little careful work in identifying the boundaries of edit areas to allow long term data checkout, and clean data check-in.

The curtain closes

It was an honor to once again host PostGIS day, and we are in debt to all the great speakers who gave their time to participate. Thanks to everyone who participated in the chat and Q&A sessions, it was a lively experience, all 11 hours of it!