DEV Community: Joseph Caudle

PostGIS and Heroku Postgres… Location, Location, Location!

Joseph Caudle — Wed, 17 Apr 2024 13:00:00 +0000

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!

Secure your Heroku Apps with SSL

Joseph Caudle — Sat, 16 Mar 2024 18:55:39 +0000

How to secure your apps with SSL certificates for custom domains

If you’re building an application that needs to go toward production, you’ll undoubtedly need to serve it up securely with SSL. What that entails varies from provider to provider, and you’ll encounter differing levels of complexity (and cost) in getting it all set up.

Fortunately, if you’re using Heroku to run your application, this is pretty straightforward. Because I’ve been giving Heroku another spin recently, I’m going to spend this article looking at what you’ll need to get going with SSL on the platform and why you might choose some features over others.

What does Heroku provide for SSL right out of the box?

Heroku focuses on ease of use, and I love that. Whether it’s the one-click deploy straight from my GitHub repo or the plugin ecosystem that lets me drop all kinds of great things into my app from the command line, I always appreciate the out-of-the-box options I have at my fingertips. Likewise, SSL on Heroku provides some great default options.

Heroku offers two main ways of working with SSL certificates on their platform:

For most apps, ACM will give you a really simple, automated certificate management experience—that is the name, after all. But there may be times when you’ll need the more robust Heroku SSL option. We’ll take a look at ACM first. Then, we’ll investigate why you might need Heroku SSL and how you’d use it.

Automated Certificate Management and how it works

ACM is built on top of Let’s Encrypt—a free, automated certificate authority, run by a nonprofit organization committed to enhancing the security of the web in general. While organizations can sponsor the Let’s Encrypt project, there is no cost for using Let’s Encrypt-issued SSL certificates. Because the certificates are free, Heroku provides Automated Certificate Management with any of their basic plans at no additional charge.

While Let’s Encrypt automates the process of requesting and issuing certificates, ACM automates the process of installing those certificates on the relevant apps for the domains they’re issued for. Other methods exist for automated management of certificates, but they’re paid add-ons, so I won’t be looking at them here. Instead, let’s show you how to add ACM to a demo app I’ve already deployed to Heroku.

To be clear, there isn’t a whole lot you need to configure for ACM. In fact, if you haven’t added a custom domain to your app, the Heroku-generated URL (on the herokuapp.com domain) for your app already has SSL.

Step 1: Enable ACM

Your first step is to turn on ACM. The easiest way to do this for any deployed app is with the Heroku CLI. Assuming you’re logged into Heroku via the CLI, all you need to do is run a single command to turn on ACM:

$ heroku certs:auto:enable
Enabling Automatic Certificate Management... starting.
See status with heroku certs:auto or wait until active with
heroku certs:auto --wait
=== Your certificate will now be managed by Heroku.
Check the status by running heroku certs:auto.

If you enable ACM before setting up a custom domain on your app, you’ll get a message like this:

$ heroku certs:auto
=== Automatic Certificate Management is enabled on pure-brushlands-82324

=== Add a custom domain to your app by running: heroku domains:add <yourdomain.com>

Step 2: Add custom domain

At this point, you simply need to do what the Heroku CLI says and add a domain to your app. You can do this in the CLI too.

$ heroku domains:add really-cool-stuff.app
Configure your app's DNS provider to point to the DNS Target lively-basin-0v9xkh99iaz1yc0xldmakla5.herokudns.com.
    For help, see https://devcenter.heroku.com/articles/custom-domains

The domain really-cool-stuff.app has been enqueued for addition
Run heroku domains:wait 'really-cool-stuff.app' to wait for completion
Adding really-cool-stuff.app to ⬢ pure-brushlands-82324... done

Step 3: Configure DNS

At this point, you need to update your DNS records on your domain to point to the DNS target provided by Heroku. The instructions in the help documentation it links to are detailed and clear. Typically, this means adding a CNAME record that directs to the new location provided by Heroku.

Once you have your DNS configured properly, you can check on the status of your certificates. You should see output like this:

$ heroku certs:auto
=== Automatic Certificate Management is enabled on pure-brushlands-82324

Certificate details:
Common Name(s): really-cool-stuff.app
Domain(s):      fe6ab605-91f9-4261-974c-ee85c043dbf7
Expires At:     2024-05-27 17:08 UTC
Issuer:         /CN=R3/O=Let's Encrypt/C=US
Starts At:      2024-02-27 17:09 UTC
Subject:        /CN=really-cool-stuff.app
SSL certificate is verified by a root authority.

Domain                       Status       Last Updated
───────────────────────────  ───────────  ────────────
really-cool-stuff.app        Cert issued  4 minutes

If you’re like me and you made a mistake when setting up the DNS records 🤦🏻, then you might see some warnings on the app dashboard in the web interface like this:

Or this one:

Fortunately, ACM repeats its validation attempts regularly for an entire hour. So, I was able to fix my issues quickly. I got to a green status pretty easily.

If you’ve ever had to set up SSL on another platform before, you’re probably painfully aware that it usually isn’t this simple (and that’s putting it mildly). That said, Let’s Encrypt has some limitations that may require you to use the more robust offering: Heroku SSL.

Heroku SSL and why you might want it

While ACM gives you a really simple experience, with that simplicity also comes somewhat limited functionality. Let’s consider two examples where ACM might not meet your unique needs.

Example 1: When you need wildcard certificates

Let’s Encrypt is restricted to providing common name certificates on only one single domain name zone at a time. Sometimes, you might actually want to have a wildcard certificate; this is the type of functionality you might need if you have a multi-tenant application that switches between tenants at the subdomain level. ACM doesn’t support this. But Heroku SSL does.

Example 2: When you need Organizational or Extended Validation (OV/EV)

Let’s Encrypt also doesn’t really guarantee that you are who you say you are. What I mean is that the type of certificate issued by Let’s Encrypt only attests that the entity requesting the certificate also controls the domain name that it’s securing. There are some scenarios when you need to ensure more than just that your domain traffic is encrypted in transit. You might need to verify that your application is actually associated with your company. In this case, you’d want to purchase an SSL certificate with either Organizational Validation (OV) or Extended Validation (EV). Those usually require checks that can’t be simply automated by a product like Let’s Encrypt.

Using Heroku SSL

In the above cases, you’ll need to manually upload your own certificate. As usual, this process has been beautifully simplified by Heroku (but it’s still more work than just telling the platform to start issuing certificates with ACM).

To add or update the certificate for your Heroku app, and to use a certificate you provide, you need to run a single command:

$ heroku certs:add server.crt server.key
Adding SSL to example... done
exampleapp now served by exemplary-sushi-4gr7rb6h8djkvo9j5zf16mfp.herokudns.com.
Certificate details:
Expires At: 2022-08-18 21:53:18 GMT
Issuer: C=US; ST=CA; L=SF; O=Heroku; CN=www.example.com
Starts At: 2021-08-18 21:53:18 GMT
...

Ok, so it’s not that much more work than using ACM.

Note that you can’t add multiple intermediate certificates to make a valid chain toward whatever root certificate has signed your new certificate. However, you can merge your intermediate certificates into one file so they can be uploaded as a single file.

After uploading your certificate through Heroku SSL, you’ll be able to see your application protected by the SSL certificate you provided on your own. While Heroku SSL still keeps things pretty simple, keep in mind that you’re responsible for keeping your certificates up to date. You’ll also need knowledge of how to work with certificates. While these might not be deal-breakers for you, they’re important points to remember—especially when you consider that ACM takes care of automatic renewals of SSL certificates. With ACM, the simple commands to set it up really are “set it and forget it.” That said, Heroku designed their entire certificate management system in such a way that even if you need the most advanced options, it’s still quite simple and manageable, even if you don’t have extensive certificate experience.

Conclusion

While there is more you can do with SSL on Heroku, both the ACM and Heroku SSL options can provide the functionality to fit most use cases. Heroku’s documentation in this space also provides enough detail to point you in the right direction. If you haven’t secured your app with SSL (shame on you!), then the simplicity of ACM should remove any excuse you have to secure your site properly. If your use case is more complicated, then Heroku SSL should give you what you need.