Getting Started with Geoalchemy2 and PostgreSQL

PostgreSQL is my favorite relational database solution, and PostGIS is one of the larger reasons why I opt to use it over other great choices like MariaDB for projects of advanced complexity. Being able to work with spatial logic at the database level in your application opens up a world of possibilities.

When I first started trying to work with spatial logic in PostgreSQL using Geoalchemy, I found that the documentation for features was abundant, but there wasn’t much help in the way of getting off the ground, which for many people is the most important part. Here’s my attempt at filling that void.

This tutorial assumes you’ll be using the excellent Geoalchemy2 Python package, which is an extension for the indispensable SQLAlchemy Object Relational Mapper. It also assumes that you’ll be using PostgreSQL (aka Postgres).

Postgres

The standard Postgres distribution does not come with the spatial capabilities preinstalled. For these you need to install PostGIS. Fortunately if you’re on a Redhat-type distro it’s as easy as:

Those last two digits should match the version of your PostgreSQL installation.

At some point after creating the database for your application, you need to connect to your database and run the following command in psql  to enable PostGIS capabilities:

That’s all for now. Next you need to write your models.

Geoalchemy2

For your application which uses SQLAlchemy, you need to install the Geoalchemy2 plugin. You are using pip , right?:

The Model

I think a convenient way to introduce the topic with data which are readily available is to make a table to store US Zip Code data.

Here’s a CSV file with all United States Zip Codes and latitude/longitude coordinates for each one.

Take a moment to carefully look through the model code. The latitude and longitude are stored as Floats since precision is critical, even down to six or more decimal places in some cases.

Now, the location field is what I had the most trouble figuring out. If you don’t already know about the concept of an SRID, that’s basically the identifier for the coordinate system being used. Use 4326 for now. If you want to learn more, here and here are great places to start.

The update_location  method makes it easy to update the location field (which will be used for the mathematical work in your spatial queries). It’s simply putting the latitude/longitude data in the proper format.

Putting It All Together

OK, now that we have our model in place, update your database schema accordingly.

NOTE: if you’re using Alembic to manage your database migrations, it doesn’t handle the postgis tables properly. Check your migration scripts (which you should be doing anyway!) and make sure you’re removing the lines where it’s trying to drop these necessary spatial information tables!

Now, let’s populate that Zip Code table:

It will take some time–usually around two minutes for me. Use tqdm if you care about monitoring progress!

Now, we have everything we need to do some sandbox work. If you’re using Flask and you don’t already have a script for manual monkeying around, make one that looks like this. I call mine shell.py:

With this script you’re pulling in your database and models and giving yourself a prompt.

OK, now try some test queries. Let’s find all the zipcodes within 10 kilometers of California’s most recognizable ZIP, 90210:

And here’s what you should get:

Pretty neat!

ST_Within  is a PostGIS function to which Geoalchemy2 provides a hook, and is one of the most useful. Here are more. You can use them in your filter methods just like I’ve shown in my example, as a method on the func  class.

Leave a Reply

Your email address will not be published. Required fields are marked *