has come up a few times recently. This intersects with a common type of query used in PostGIS: nested geometries. An example of nested geometries is the state of Colorado exists within the United States. The PgOSM Flex project nested polygon data from OpenStreetMap places into a handful of array ( ) columns. I decided to explore to see if it would be a suitable option for PgOSM Flex nested places. is not suitable for OpenStreetMap data in the way I would want to use it. The following is what the "Colorado is in the U.S" would look like using a Postgres ..read more

Of course, when my proposal was accepted then I realized I had to figure out how I was actually going to deliver! This post explains how I deployed demo databases to the participants of my PASS 2023 pre-con session. . My chosen topic was focused on PostGIS, titled . A key part of my submission was that the session would be an interactive, follow-along type design. Julie and I believe that is key to learning so we wanted to enforce that as much as possible. The plan was to use real data and queries to teach a nuanced, technical topic to an audience of unknown size or background. I that PASS is ..read more

I avoid UUIDs. The only places I have used UUIDs in production are the places where a 3rd party system is involved. for the full challenge text. The topic is described as a debate between the Database People and Developers. I'm not sure there's such a clean divide on people's opinions on the topic, as I know plenty of Database People that have settled on using UUIDs as their default. Similarly, I know even more developer types that have followed the arguably more conventional choice of using an auto-incrementing ID ..read more

Before I started writing this post I had assumed that all of the code would be contained in the body of this post. It turned out that to get to the quality I wanted, I had to create a new project creates a few tables, a view, and a stored procedure in the project (MIT licensed) to share the code. This is already a long post without including a few hundred more lines of code! The schema. The stored procedure is responsible for cleaning an importing the data and weighs in at nearly 500 lines of code itself ..read more

It just so happens, I finally got to meet Pavlo in person at the summit in Seattle, Washington! ? for the focus for this month's topic. See for the full details. As always, I can't wait to read the rest of the contributions this month. This post addresses roughly three of his questions ..read more

The data, permissions script, and example SQL queries used through this session are available below. PostGIS and PostgreSQL: GIS Data, Queries, and Performance at PASS Data Community Summit 2023 on November 13 ..read more

The following download is the PDF version of the slide deck. 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 ..read more

in Seattle is only four weeks away! I am honored that I was selected to provide a full day on PostGIS, as well as a 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. 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 S ..read more

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. 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 boo ..read more

Creating the extension for PostGIS usage now involves installing two (2) extensions. Some components have been split out into the Using Uber's H3 hex grid in PostGIS H3 indexes for performance with PostGIS data . Naturally, things have changed over the past 12 months, specifically version 4 of the H3 Postgres extension was released. The portion since that also requires which I do not have installed by default ..read more