Location, Location, Location! PostGIS and Heroku Postgres

Extending Postgres to Support Geospatial Data Queries

I’ve lost too much time fiddling around with configurations and services just to spin up a compute instance in AWS. Sometimes, I just need a production-ready environment to play around with to test out applications and ideas. With Heroku, I can get that with just a few simple commands at the CLI.

Recently, I learned that Heroku also includes support for PostGIS. I’ve personally never used PostGIS before. I know of several proprietary competitors to the product, so I figured this would be a great time to try out the open-source option.

In this article, I’ll show you how to get a PostGIS-enabled Postgres instance running on Heroku. Then, I’ll run some sample queries on the database, just to give you a feel for how it works. And the best part? You can follow along and do your own exploring as we go!

What Is PostGIS?

Even if you’ve used Postgres for a while, you might not be familiar with PostGIS. The GIS stands for Geographic Information System. There are many solutions in the space. But the thing that makes PostGIS nice is that it’s based on the well-loved PostgreSQL database. In addition to all of the performance you’d expect from Postgres, we get a full-featured tool for storing geospatial data.

Not only does PostGIS provide a good storage solution for this type of data, but it can be seamlessly integrated with several applications that can consume this data (such as ArcGIS and Tableau).

TL; DR—If you need to process, store, or query location data, PostGIS is a great option for doing that. Fortunately, it’s as simple as adding an addon to a Heroku app to get a new Postgres instance up and running. So, let’s do that now.

How Can We Use PostGIS?

To get started, you’ll need an app of any size. Then, you add an instance of Heroku Postgres to your app.

Create a Heroku App

For my demo, I’m going to create an empty app.

Attach a Heroku Postgres add-on

Once the app is created, I can create the Heroku Postgres add-on. Because my sample data set is too big for a Mini plan instance, I need to use the Basic plan instead. I can do this from the command line:

$ heroku login

$ heroku addons:create heroku-postgresql:basic -a postgis-demo
Creating heroku-postgresql:basic on ⬢ postgis-demo... ~$0.013/hour (max $9/month)
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pg:copy
Created postgresql-fitted-78461 as DATABASE_URL

Once I’ve created my Postgres database, I only have a few more steps to set up PostGIS.

Create the PostGIS Extension

Heroku Postgres has many possible extensions we could install. To list them, we can ask our instance:

$ heroku pg:psql -a postgis-demo
--> Connecting to postgresql-fitted-78461
…
postgis-demo::DATABASE=> \x on;
Expanded display is on.

postgis-demo::DATABASE=> show extwlist.extensions;
…
address_standardizer,address_standardizer_data_us,amcheck,autoinc,bloom,btree_gin,btree_gist,citext,cube,dict_int,earthdistance,fuzzystrmatch,hstore,insert_username,intarray,isn,lo,ltree,moddatetime,pg_partman,pg_stat_statements,pg_trgm,pgcrypto,pgrowlocks,postgis,postgis_raster,postgis_topology,refint,seg,sslinfo,tablefunc,tcn,tsm_system_rows,tsm_system_time,unaccent,uuid-ossp

We see postgis in the list of available extensions. From there, we can create the extension.

postgis-demo::DATABASE=> CREATE EXTENSION postgis;
CREATE EXTENSION

We can confirm the extension is installed and check the version:

postgis-demo::DATABASE=> SELECT postgis_version();
-[ RECORD 1 ]---+--------------------------------------
postgis_version | 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Alright! It looks like we’re up and running with PostGIS 3.4.

Load Initial Dataset

Now that I’ve got the PostGIS extension enabled, I need to load in a dataset to play around with. I’m using the dataset provided by the Introduction to PostGIS book. The downloaded data bundle is a 21.5 MB zip file. In the data subfolder of the extracted archive, there’s a 9.5 MB file called nyc_data.backup.

This is a file with all of the census data from the 2000 census of New York City, along with all of the streets, neighborhoods, and subway stations in the city.

We can restore the data backup directly to our Heroku Postgres instance by using the heroku pg:backups:restore command. This is incredibly convenient. However, keep in mind the following caveats:

• The backup file that you can restore from cannot be uploaded from your local machine. It must be available online. Fortunately, I found a GitHub repo that makes nyc_data.backup available.

• Performing database restore starts by completely resetting your Heroku Postgres instance, including your installation of the postgis extension. So, even though we showed above how to install the extension manually, we’ll need to add a flag when we restore our database to pre-install the extension before loading the data.

Here’s the command we would use to restore the database backup:

$ heroku pg:backups:restore \
  https://github.com/Giorgi/PostgresSamples/raw/main/nyc_data.backup \
  -e postgis \
  -a postgis-demo

Our backup file is specified through a publicly accessible URL. You can always download the dataset from the PostGIS tutorial, extract the nyc_data.backup file, and post it online to a location of your own choosing.

The -e postgis flag specifies that we want to install the postgis extension prior to loading the backup’s schema and data.

That was it! Not bad for a few simple commands. We have our database and data.

Why Heroku?

If you already know how to set up Postgres on a local machine, you might be wondering why I went with Heroku. For me, the biggest reason is simplicity. Besides choosing a large enough Heroku Postgres plan for the analysis I plan to do and installing the PostGIS extension, there’s nothing else I need to do to get up and running.

Also, collaborating on any analysis I do is easy. I can grant other people access to my database as collaborators, or I can quickly build an application on top of the database and share access through a normal web interface, rather than the Postgres client.

Finally, when I’m done working on a project and I don’t need it any longer, I can just delete the app on Heroku and it’s all gone. No data files on my computer to worry about. No extra software installed locally. I’m able to enjoy a quick excursion into a new technology and then move on when I’m done.

Working With PostGIS

Now, let’s take a look at how PostGIS works.

Work Just As You Would With Postgres

The first thing to remember is that PostGIS is an extension within Postgres. That means that you can also perform any standard Postgres query.

Let’s say I wanted to find out how many streets in New York start with B. A simple SQL query will tell me:

postgis-demo::DATABASE=> SELECT count(*)
postgis-demo::DATABASE->   FROM nyc_streets
postgis-demo::DATABASE->   WHERE name LIKE 'B%';
count

1282
(1 row)

How about the number of neighborhoods in each borough? Again, a simple SQL query:

postgis-demo::DATABASE=> SELECT boroname, count(*)
postgis-demo::DATABASE->   FROM nyc_neighborhoods
postgis-demo::DATABASE->   GROUP BY boroname;
boroname       | count
---------------+-------
Queens         |    30
Brooklyn       |    23
Staten Island  |    24
The Bronx      |    24
Manhattan      |    28
(5 rows)

So far, we’ve just done standard PostgreSQL. Now, let’s take a look at how to use PostGIS features.

Examples of Working With Geospatial Geometries

Because our dataset includes all New York streets, we can ask how many kilometers of streets there are in the city with this query:

postgis-demo::DATABASE=> SELECT Sum(ST_Length(geom))
                           / 1000 as street_length
                           FROM nyc_streets;
   street_length
--------------------
 10418.904717199996
(1 row)

We can also calculate areas, such as the acreage of the entirety of Manhattan:

postgis-demo::DATABASE=> SELECT Sum(ST_Area(geom)) / 4047 as acreage
                           FROM nyc_neighborhoods
                           WHERE boroname = 'Manhattan';
      acreage
-------------------
 13965.32012239119
(1 row)

Note that these calculations come from the geospatial data, not from columns related to aggregated data of this sort. Not only that, but these queries execute extremely quickly.

One final query that I’m really amazed by involves the use of spatial joins. Much like standard database joins, spatial joins can unite multiple tables, but on the basis of spatial relationships. For example, we can query for which neighborhood a specific subway station is in, using the spatial data. To do this, we can use ST_Contains from PostGIS to determine if the geometry of the neighborhood completely contains the geometry of the subway station.

Based on the name of the subway (in nyc_subway_stations), we query for the neighborhood (in nyc_neighborhoods) for which ST_Contains is true. Our query looks like this:

postgis-demo::DATABASE=> SELECT
                           subways.name AS subway_name,
                           neighborhoods.name AS neighborhood_name,
                           neighborhoods.boroname AS borough
                         FROM nyc_neighborhoods AS neighborhoods
                         JOIN nyc_subway_stations AS subways
                         ON ST_Contains(neighborhoods.geom, subways.geom)
                         WHERE subways.name = 'Broad St';
 subway_name | neighborhood_name  |  borough
-------------+--------------------+-----------
 Broad St    | Financial District | Manhattan
(1 row)

PostGIS provides even more advanced location querying functionality with geometries, but that’s outside the scope of our simple demo here.

Conclusion

Having never used PostGIS before, I’m really impressed by what it can do. There’s a lot more I could do with this database too, since I’ve only made it about halfway through the official Introduction to PostGIS book. Not only that, I can build and deploy applications on top of PostGIS by using any number of languages supported by Heroku.

In particular, I’m thinking I might want to find a use case for building a Rails app on top of PostGIS. I already found some documentation on how I can get started.

But for now, I don’t need this instance anymore, so I’m going to clean it up and delete my app. From the CLI, this is what I need to do:

$ heroku apps:destroy postgis-demo
 ▸    WARNING: This will delete ⬢ postgis-demo including all add-ons.
 ▸    To proceed, type postgis-demo or re-run this command with --confirm postgis-demo

> postgis-demo
Destroying ⬢ postgis-demo (including all add-ons)... done

Wait, that’s all? Yeah, that’s all. With a single command and confirmation, everything is torn down, and I don’t need to worry about it anymore.

$ heroku apps
You have no apps.

$ heroku addons
No add-ons.

Now that I’ve deleted my app, you have an incredible opportunity: The unique app name postgis-demo is available for the first reader who wants to grab it on Heroku! Are you ready to build your next great PostGIS app? Today is the day!