PostGIS Day 2022 is tomorrow, was November 17th. I am excited to be giving a talk at this year's PostGIS Day virtual event hosted by Crunchy Data!

The recording to this talk is on the Crunchy Data's YouTube page! Downloads are below.

My talk, Route the Interesting things (not just roads!) with OpenStreetMap, is scheduled for 2:00 PM Mountain Time. The full schedule is available on Crunchy Data's website. The original purpose of this post is to provide resources I will reference in my talk today. If you're here for those resources, feel free to skip to the Downloads and Links sections below.

The other purpose for this post is to provide a last minute announcement for this year's PostGIS Day talks and speakers. There are a lot of great talks scheduled! Register now, there's still time, it's virtual, and free!

What I'll be watching

PostGIS Day is an all-day, 12 hour, 24 session marathon of PostGIS related talks. I plan to attend PostGIS sessions from start to finish this year, with small breaks in there for a bit of real life. As I read through the schedule I find myself saying "That'll be interesting" and "I wonder how..." It's a seriously great lineup!

It was tough to choose the top-5 list of talks I don't want to miss. I had to firmly apply a "How will I use this today?" filter to narrow it down.

• Vicky Vergara: How to Prepare a Graph for pgRouting
• Krishna Lodha: Creating APIs from PostGIS with Django and GeoAlchemy
• Aliaksandr Kalenik: GiST Index Building in PostgreSQL 15
• Bonny Mcclain: Once upon a database... Telling stories with PostGIS and QGIS
• Greg Smith: Speedrunning the Open Street Map osm2pgsql Loader

Continue Reading

This blog post is for PGSQL Phriday #002. Read Ryan Booz' introduction from September for more details on PGSQL Phriday. Andreas Scherbaum is this month's host and chose the topic: Postgres backups!

The topic reposted here:

Which tool(s) are you using, where do you store backups, how often do you do backups? Are there any recommendations you can give the reader how to improve their backups? Any lesser known features in your favorite backup tool? Any new and cool features in a recently released version?

Bonus question: Is pg_dump a backup tool?

What is a backup?

I address most of ads' questions in this post, but before we dive in we need to define "backup." Merriam Webster has a definition for backup in the context of computers:

backup (3): "a copy of computer data (such as a file or the contents of a hard drive)"

I'm running with this simple defintion of backup for today's post. To frame the definition of "backup" in a non-Postgres context: Suppose I have a "business document." I want to make some major changes but am afraid of accidentally losing something important. What do I do? I copy / paste the file, change the name to include today's date, and edit away. Did I create a backup of the original document? Sure. In a way. Is it the same thing as when the IT department backs up the network drive the documents where both the original and newly modified files are saved? Nope. Do both approaches serve their purpose? Yes!

Database backups are similar. There isn't a one-size-fits-all solution.

Continue Reading

One of my favorite layers to route with pgRouting is the water layer. I am interested in where water comes from, where it goes, where runoff happens, and how urban development interacts with this powerful force of nature. The OpenStreetMap water layer, however, presents a challenge when routing with PostGIS and pgRouting: Polygons.

Why are polygons a challenge? A routing network using pgRouting is built from lines (edges). Now, to state the obvious: polygons are not lines.

Real world waterway networks are made up of both lines and polygons. Rivers, streams, and drainage routes are predominately (but not exclusively!) mapped using lines. These lines feed into and out of ponds, lakes, and reservoirs. The following animation shows how much impact the water polygons can have on a waterway network... some very important paths simply disappear when they are excluded.

To make the full water network route-able we need to create a combined line layer. The combined line layer will include:

• Initial osm.water_line inputs
• Medial axis lines from osm.water_polygon
• Lines to connect initial inputs to medial axis

Continue Reading

A few years ago around the time PostgreSQL 12 was released, I created a tool to help identify the changes to postgresql.conf. The pgConfig tool has helped me become (and stay) aware of important changes to Postgres configuration as I work with various major version upgrades. Now that Postgres 15 is available, pgConfig is updated with the latest configuration. This post provides a quick look at changes in the Postgres 15 version of the postgresql.conf options.

Summary of changes

The postgresql.conf for Postgres 15 has 6 new items, 3 changed items and 1 removed item. Visit the pgConfig site to see the full list of changes.

Continue Reading

In late 2020 when osm2pgsql released the flex output I eagerly jumped on that bandwagon. The osm2pgsql flex output enabled the type of data structure and cleanup abilities I had always wanted from osm2pgsql. By January 2021 the PgOSM Flex project was up and running and I was phasing out my legacy OpenStreetMap processes. Since then, I have written more than a dozen posts exploring different improvements and use cases for the OpenStreetMap data loaded via PgOSM Flex. This post looks at a few notable improvements to version 0.6.0 over prior versions. The two areas of focus are:

• Data quality
• Usability

Data quality improvements

The set of improvements that gave me the idea for this post were made in PgOSM Flex versions 0.5.1 and 0.6.0. Version 0.5.1 took advantage of the long awaited addition of multilinestring support to osm2pgsql. Adding that feature in osm2pgsql allowed relations of lines to be added in the same manner that relations of polygons had used. Without the multilinestring support, relations such as 13642053, shown in the following screenshot, were being skipped by the PgOSM Flex import. This improvement targeted roads, waterways, and public transport layers.

Continue Reading