Getting Started with PostGIS on Postgres App

If you've worked with data before, you've probably heard of Postgres, but if you haven't, Postgres is a leading open source relational database started over 30 years ago. Amazon Redshift was famously built on it and now Google just released AlloyDB built on it. On version 14 with the open source version, it's incredibly easy to get projects set up on your Mac by downloading the Postgres App. You don't have to get the app and you can download only what you want, but if you want a quick way to get started with Postgis for geospatial analysis, go ahead and get the app, which comes with the files necessary for postgis.

What's PostGIS? It's is a spatial extension to Postgres, which allows you to store data types like point and polygon rather than trying to store coordinates as decimals. You can then do geospatial analysis right from your database. For example, if you have a geographical area and you want to find the center, you can run a query with ST_Centroid() with an individual geometry or an entire column.

You can even develop a geocoder in your database. I'll dive into that in my follow up on this post.

So, once you have the Postgres App installed, how do you set up Postgis? First, create a new database (do not do this in the Postgres database).

CREATE DATABASE geocoder;

After you've granted all access and privileges to your user, all you need to do to make it into a Postgis-enabled database is to go into the database and type:

CREATE EXTENSION postgis;

If you want to different capabilities (including what we'll need for the geocoder), you'll need to add other extensions (also packaged in the Postgres App), but we can focus on that next time.

Now, if you have a .shp or .gdb file and want to put it in your database, you can easily do that with ogr2ogr, which again, ships with the Postgres App. ogr2ogr will create a table in your database that mirrors the attributes of the file you're loading. You may have to adjust some of the parameters, but the basic pattern is:

ogr2ogr Pg:'dbname=DBNAME host=HOST user=USER port=5432' YOUR_FILE.shp  

Next, I'll try to do a dive into how to convert this into a geocoder.