An Introduction to PostGIS (with a use case in QGIS)

This post veers off my usual takes on Tableau and focuses on a little more technical topic. Today I would like to introduce you to PostGIS — an open source, freely available spatial database extender for PostgreSQL.

Why should I care about PostGIS?

For those of you that love geographic data (like me) and SQL (like me) this brings both of these worlds together. It gives you a way to store your shapefile data without storing all those pesky shapefiles on your hard drive.

The other powerful feature is all the spatial SQL functions it comes packaged with. For example, lets say you have a table of point locations with their coordinates, but you want to find which county they fall into. You could launch QGIS (or ArcMap) and run a spatial join, but if you are doing this on a regular basis, it could get pretty annoying.

Instead, you can just write one line of SQL:

SELECT *
FROM points, counties
WHERE ST_Contains(points.geom, counties.geom);

(assuming your geometries are stored in the respective geom fields).

Finally, Tableau is working on supporting spatial queries and geometry types according to tweets by Kent Marten:

postgis_1

I won’t go into that beta (or is it alpha) version, but it’s pretty exciting.

Without further comment, lets get started.

Getting Started

There are a ton of tutorials out there on how to download and install PostGIS, so I’ll try not to repeat too much.

1.) Download and install PostgreSQL and pick the appropriate version based on your operating system. I’m on a Windows machine, so I picked version 11.1 for Windows x86-64.

2.) Click on the installer and follow the instructions. Select all the default components. If it’s not already checked at the end of the install, click on the checkbox to launch StackBuilder.

3.) After picking the server on your local machine, you’ll see a menu like this:

postgis_2

Click on Spatial Extensions, and click on PostGIS 2.5 bundle and click Next.

4.) Click through the instructions, and you’ll come to a menu like this:

postgis_3

On my installs, I typically check the box to “Create spatial database”.

5.) Name the database what you like, I just named mine “postgis”. Click through the menus to finish the installation.

Loading some data

Now we get into the fun part loading and querying some data.

1.) You now have a new program/application on your computer called pgAdmin 4. Pull that up. It should launch a new browser tab. You should see “Servers” on the left hand side with the ability to expand it. If you expand it, you should see something like this:

postgis_4

But we have no data.

2.) How do we get some spatial data into our PostgreSQL database? Pretty easily actually.  With your PostGIS installation, it comes with a program called “PostGIS 2.0 Shapefile and DBF Loader Exporter.”

If you start this program, you’ll see this:

postgis_5

Under “View Connection Details…” type in the credentials for your PostgreSQL database and the database you want to import your shapefile into. Mine is postgis.

3.) Next, we are going to add a file. Let’s add a shapefile of the United States. To keep it simple, let’s just download and import the Tiger 2018 shapefile. To download it, go to the US Census Tiger/Line – Geography site; click on download > web interface and select “States (and equivalent)”. It will download a zip file.

4.) Unzip the files. Go back to your Shapefile Importer Exporter and click “Add file” and navigate where you unzipped the file you just downloaded. Once you select it, you will see this:

postgis_6

Click Import. It will take a second, and if all works, it should say “Shapefile import completed.”

5.) Go back to pgAdmin. Under your database tree, right click on postgis (or what you named your spatial database) and click refresh. Navigate to > Schemas > tables and you should see your shapefile you just loaded!

postgis_7

If you right click on it, and select View/Edit data, you can see all the fields it imported, as well as a geometry field (which is the most important part).

Let’s visualize it in QGIS!

1.) Open up QGIS. (If you don’t have it, download it here.)

2.) There’s a couple different ways of bringing it into QGIS. The easiest is to click on Layer > Add Layer > Add PostGIS layers…

3.) Click on New Connection, type in your credentials and click Connect. Navigate to your state table and click add.

postgis_8

4.) It will then prompt you to define a CRS. Click OK, and your map will appear!

postgis_9

Yes, I know that’s not very exciting, but hey it works!

That’s it for now!

-Paul

 

 

 

 

 

2 thoughts on “An Introduction to PostGIS (with a use case in QGIS)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s