This post supports my session titled PostgreSQL: Extensions Shape the Future at PASS Data Community Summit 2023 on November 15. Thank you to everyone who joined this session during PASS. I believe the audio recording with slides should be made available at some point a few months in the future.

Slides

The following download is the PDF version of the slide deck.

• Extensions Shape the Future slides (PDF)

Continue Reading

This post supports our full day pre-conference session, PostGIS and PostgreSQL: GIS Data, Queries, and Performance at PASS Data Community Summit 2023 on November 13.

Thank you everyone who participated! This page has been updated with the slide decks used during the session.

Downloads for session

The data, permissions script, and example SQL queries used through this session are available below.

Continue Reading

This year's PASS summit in Seattle is only four weeks away! I am honored that I was selected to provide a full day pre-conference training on PostGIS, as well as a general session talk on extensions. Both of my topics are focused on the Postgres ecosystem. Of course, that is not a surprise to my regular readers! It may be a surprise to those who have been aware of PASS in the past.

What is PASS?

This year's PASS conference is called "PASS Data Community Summit 2023." In the past, PASS was an acronym for Professional Association for SQL Server, and the conference was very much a Microsoft conference. When I attended in 2018 it was because I wanted to learn more about MS SQL Server and PowerBI. This year, that focus is expanding to include Postgres!

Continue Reading

My previous post, Setup Geocoder with PostGIS and Tiger/LINE, prepared a PostGIS geocoder with TIGER/Line data for Colorado. This post uses that setup to bulk geocode addresses from OpenStreetMap buildings to try to determine the accuracy of the geometry data derived from the input addresses.

Quality Expectations

Let's get this out of the way: No geocoding process is going to be perfectly accurate.

There are a variety of contributing factors to the data quality. The geocoder data source is pretty decent, coming from the U.S. Census Bureau's TIGER/Line data set. The vintage is 2022, and this is as recent as I can load today using this data source. We should to understand that this will not contain any new development or changes from the past year (or so).

The OpenStreetMap data is also a source of error. It is certain that there are typos, outdated addresses, and other inaccuracies from the OpenStreetMap data. Some degree of that has to be expected with nearly any data source. The region of OpenStreetMap used will also be an influence, some regions just don't have many boots-on-the-ground editing and validating OpenStreetMap data.

Continue Reading

Geocoding addresses is the process of taking a street address and converting it to its location on a map. This post shows how to create a PostGIS geocoder using the U.S. Census Bureau's TIGER/Line data set. This is part one of a series of posts exploring geocoding addresses. The next post illustrates how to geocode in bulk with a focus on evaluating the accuracy of the resulting geometry data.

Before diving in, let's look at an example of geocoding. The address for Union Station (see on OpenStreetMap) is 1701 Wynkoop Street, Denver, CO, 80202. This address was the input to geocode. The blue point shown in the following screenshot is the resulting point from the PostGIS geocode() function. The pop-up dialog shows the address, a rating of 0, and the calculated distance away from the OpenStreetMap polygon representing that address (13 meters), shown in red under the pop-up dialog.

Continue Reading