PostGIS Day 2022 Videos are out and some more highlights

Sunday, December 04. 2022

How Tos that cover several extensions

• Brian Timoney: Skating to Where The Puck Is Going To Be: Why SQL is the Future of Full Stack Analytics (video)

  Brian gave a master presentation. It's rare that any topic as uninteresting to me as hockey would keep me on the edge of my seat. It was like a magic show. Now I have this urge to start analyzing hockey data now that I know NHL tracks coordinates of players on the field. He had cleverly planted jokes interspersed with SQL tricks. Elizabeth built on one of his jokes and pushed it into his corner. I'm still amazed how well he live-demoed postgis, postgis_raster, http extension, and PostgreSQL's built-in json goodness in under 30 minutes and without speed talking.

• Regina Obe: A Tour of the PostGIS Extended Family of Extensions (video)

  I ran out of time as usual presenting, so didn't cover all I wanted. But you can pick up the code if you are so interested to follow the rest. I think maybe next time, I'll just skip the background presentation and get to the demo. I'll also try to start with things I haven't covered before and maybe intersperse the extensions into a well-stewed soup similar to what Brian Timoney did.

pgRouting How To's

• Vicky Vergara: How to prepare a graph for pgRouting (video)

  One of the presentations, that got cut a little short because of network issues was Vicky Vergara's pgRouting presentation. Luckily Elizabeth was able to catch up with Vicky later and rerecord. Graph building is one of the most important steps in any pgRouting work, check out the video here. Much of your pgRouting performance and correctness of your analysis starts with a good graph. One of the things Vicky has been hard at work on in her pgRouting maintenance activities is improving build time of graphs and the pgrouting docs. The functions she shows in this video are relatively new and more performant than the pgr_CreateTopology/pgr_CreateVertices most people are used to using. One big take-away is that most of pgRouting work has little to do with PostGIS. pgRouting uses PostGIS as a tool for deriving graphs from location data. All the algorithms in pgRouting are based on graph theory and as such pgRouting is a big bag of graph theory tricks with an SQL api. It's still my hope to see more non-GIS applications of pgRouting.

• Ryan Lambert: Route the Interesting Things Not Just Roads! with OpenStreetMap

  Ryan of Rustproof Labs talked about using pgRouting and OpenStreetMap data for indoor routing, which I thought was pretty cool. I also didn't know OpenStreetMap had indoor content. Got to check that out. He also used ST_Points, which was news to me that that was a thing. It's not even a new function. Clearly I got to study up more on this PostGIS documentation thing. Although he did cover mostly pgRouting, he show-cased some postgis_sfcgal functions which many assumed were part of the postgis extension. If you see ST_StraightSkeleton or ST_ApproximateMedialAxis, and are wondering why you don't have those, try to do: CREATE EXTENSION postgis_sfcgal;. I know it's not available with all PostGIS packages, but it is for most .

How PostGIS is made

• Aliaksandr Kalenik (Kontur): GiST Index Building in PostgreSQL 15 (video)

  Aliaksandr, who works at Kontur, is one of the newest members of the PostGIS development team. This is one of those talks Paul Ramsey likes to call "How sausage is made". If you are a deep PostgreSQL developer, you'll find this talk interesting. For others be warned it's a bit technical. If you are an OpenStreetMap data user who works with large volumes of OpenStreetMap data, you should be very grateful of this work even if you don't understand it.

• Martin Davis (Cruncy Data): Triangles, Hulls, and Coverages: New and Planned Functions for Processing Polygons in PostGIS

  Martin goes into the technical details, narrated with gorgeous pictures of the new ST_ConcaveHull (to replace my PostGIS crappy implementation of ST_ConcaveHull I wrote a really long time ago). It's much faster than the older version and achieves better results for most things (polygons and points), but does require you are building with GEOS 3.11+. Sadly it seems most distros have a GEOS that is too old (median around 3.9/3.10), so you might need to wait a year or so for this, upgrade your OS if you are getting from packages, or just build your own PostGIS and GEOS. If you are on Windows, I ship the latest GEOS with the PostGIS 3.3 Windows Bundle, so you're covered.

Case Studies

There were lots of case studies which were all pretty good. Many even had How To moments in them. One stood out for me mostly because of sentimental value. It's from my birthland of Nigeria and my father was a land surveyor in his youth, a statistician and professor in later life, and dealt with data much like the topic of this talk. Granted my dad did not have as good of technology to make sense of it back then.

• Victor Idakwo (De-Vicks): Property Data Management with PostGIS, PostgreSQL, QGIS and QField (video)

  This is not just a case study, but has many how-to moments in it, with structuring data, defining views, using triggers to drive updates done in the field.

What is this?

• Paul Ramsey: Web Sockets and Real-Time Updates for Postgres with pg_eventserv

  Paul's talk was a talk that didn't really seem to fit anywhere and yet it could fit everywhere. The discussion on PostGIS was a little underwhelming, but use of PostgreSQL listen/notify api in conjunction with geofences was mind-blowing.