UUID in Postgres: PgSQLPhriday #015
This month's PgSQLPhriday #015 topic is about UUIDs. Lætitia Avrot is this month's host, see her post 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.
TLDR;
I avoid UUIDs. The only places I have used UUIDs in production are the places where a 3rd party system is involved.
Working with GPS data in PostGIS
One of the key elements to using PostGIS is having spatial data to work with! Lucky for us, one big difference today compared to the not-so-distant past is that essentially everyone is carrying a GPS unit with them nearly everywhere. This makes it easy to create your own GPS data that you can then load into PostGIS! This post explores some basics of loading GPS data to PostGIS and cleaning it for use. It turns out, GPS data fr om nearly any GPS-enabled device comes with some... character. Getting from the raw input to usable spatial data takes a bit of effort.
This post starts with using ogr2ogr
to load the .gpx data to PostGIS. Once the data is in PostGIS then
we actually want to do something with it. Before the data is completely
usable, we should spend some time cleaning the data first.
Technically you can start querying the data right away, however, I have found there
is always data cleanup and processing involved first to make the data truly useful.
This is especially true when using data collected over longer periods of time with a variety of users and data sources.
Travel database project
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
travel database project (MIT licensed)
to share the code. This is already a long post without including a few hundred
more lines of code! The travel-db project creates a few tables, a view, and a stored procedure
in the travel schema. The stored procedure travel.load_bad_elf_points is
responsible for cleaning an importing the data and weighs in at nearly 500 lines
of code itself.
Postgres Events: PgSQLPhriday #014
It is PgSQLPhriday time again! This month's event is PgSQLPhriday (#014) and is hosted by Pavlo Golub. I'm barely making the deadline, but didn't want to miss this one! Pavlo chose PostgreSQL Events for the focus for this month's topic. See his post 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.
Networking
It just so happens, I finally got to meet Pavlo in person at the PASS 2023 summit in Seattle, Washington! 👋
Q: "Discuss the importance of networking. Have you formed valuable connections or partnerships as a result of these events?"
PASS Session: Postgres Extensions Shape the Future
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.
Pre-conference Session Materials: GIS Data, Queries, and Performance
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.
Mastodon