PostGIS Day 2023 Summary

We hosted our annual PostGIS day a couple weeks ago with some great talks on a big variety of topics within open-source GIS. Here is a summary of the themes I saw take shape across the day’s events that will point you towards the recordings, depending on your interests. A full playlist of PostGIS Day 2023 is available on our YouTube channel.

The Ideal GIS Stack

If you’ve spent time with developers this year you know that folks love to tell you the details and reasoning behind their tech stack and the GIS community is no different. PostGIS is really the engine behind the modern day open-source GIS installations and several presenters came to talk about their GIS architecture and preferred toolchains, backed by PostGIS.

The PRAM stack

Paul asked Rhys if the PRAM stack name is flattery or if he’s being trolled. I think you’ll see that Rhys’ talk is almost all flattery. He digs into using PostGIS with the ogr_fdw, pgRouting, PostgREST, Sqitch, and pgTAP for projects in the utilities industry. Rhys’ talk had some of the best screengrabs including this gem.

Modern GIS Analytics

Matt Forrest from Carto also had a great talk on the analytics workflows and his take on a modern GIS analytics stack. He had details on using Geoparquet, DuckDB, dbt, and H3. What connects everything, in Matt’s opinion is, SQL. He had some great slides, including this one (bonus points for putting PostGIS at the center of everything):

PostgREST & making PostGIS your modern REST API

PostgREST turns your Postgres database, and PostGIS, into a REST API and it is pretty cool. Krishna has a great technical overview of PostgREST and how to get started with this, including some of the tricks in working with the Swagger API Platform.

Movement, Mobility, and Emergency Services

We had several really good talks from people working in the field to solve issues with getting people and things where they need to go. We had two speakers in the emergency services sector.

Laure-Hélène Bruneton from CamptoCamp talked about her work at NexSIS emergency operations management in France with her talk “Custom Road Network Contraction for Routing”. She digs into working with a large routing dataset and some tips for reducing size and making it more performant.

Randal Hale came to talk about his work with 911 in rural Tennessee, comparing using just the Geopackage files for 911 to using PostGIS. Without even storing data in a database, Randal is able to work with data, QGIS, and SQL all through a Geopackage file.

Vicky Vergara, the primary developer behind pgRouting came to talk about a special pgRouting project she has been developing for a UN initiative on how close people live to hospitals. She demonstrated how this data is accessible with open source tools and open data and you can see how important something like this would be for a developing nation.

Ford Motor Company’s research and development team presented on using PostGIS and pgRouting in their BlueCruise Hands-Free Highway Driving technology. I love that PostGIS is literally behind the wheel! Brendan Farrell came to talk about “Mapping Where the Data is Not”. This is when you’re dealing with missing pieces of data, denoting that, and using complementary geometry.

Initially released in early 2022, MobilityDB, has been getting more and more attention and hands-on love in 2023 and we ended up with two talks digging into some details. MobilityDB is an extension that is built on top of PostGIS and Postgres and expands the capabilities even more for moving geospatial objects. The main project leader, Prof Esteban Zimanyi, gave a great overview of how this fits in. Following Zimanyi’s talk was Wendell Turner with “Air Traffic Analysis with PostGIS and MobilityDB”. He used airplane, airport, and weather data to do demo research on everyone’s favorite topic, flight delays.

Leveling up your SQL & PostGIS skills

We’re always blessed to have some of the best and brightest come to share their expertise. One great thing about PostGIS day is that it’s kind of a mix of hearing stories, learning about projects and tools with demos of technical skills outside of your day to day life.

I presented a talk on being a “Spatial DBA in a Pinch” as I’ve found lots of folks end up taking care of a database even though they didn’t really set out to do that. This is a basic talk, but if you’re new to PostGIS, there are handy tips about creating roles, looking at queries, and crating basic indexes. Paul added some really great tips on PostGIS performance too, which expand on some of the basics I presented.

Regina Obe always brings the coolest PostGIS examples and this year she did not disappoint her fans. She showed off a bunch of Star Wars graphics with her talk on “PostGIS surprise extensions”. If you want to show your kid some PostgreSQL over the holidays, Regina has all the code ready to go so you can run this yourself.

We had a couple other really great technical deep dives. Benjamin Trigona-Harany talked about processing airplane flight data in “Trajectory Analysis Using PostGIS” (with some tips on getting free airline and flight path data). Crunchy Data’s own Martin Davis discussed some new PostGIS features for handling “Simple polygonal coverages”, including validation, union and simplification (which he calls the “killer app” for coverages).

The role as GIS professionals

We got some nice breaks from technical talks to spend time thinking about our role as GIS professionals in this big wide world and what all of it means. Bonnie McClain came to talk to us on how “We are part of the infrastructure, not above it.” Bonnie has been doing amazing things in the GIS world by telling stories, looking at urban data through the lens of both GIS and human geography. Her talk digs into some of the things she uncovered on a recent project where she uses GIS data to build a Global and Healthy Sustainable Cities Indicator.

Brian Timoney talked about “Refactoring the Way We Talk About SQL”. This talk reviews open source software, value, and how we talk about our roles. He also gives an incredible demo of getting data straight from PostGIS through the pg_svg SVG extension and pg_featureserv and then into a spreadsheet.

Brian is a uniquely talented speaker with a love for this line of work that’s rarely communicated into words. His recent talk from FOSS4G NA on “You Can’t Get There From Here Alone” is also worth a mention here; it’s excellent (I had to hold back tears when I saw this in person).

Thank you!

Thanks to everyone who participated this year by speaking, coming to the event, chatting with us, or waiting until the videos are up on YouTube to catch on this year’s PostGIS Day. We had event attendees from more than 54 countries! We’ll be posting a call for papers next September so keep an eye out for that.