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:
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:
I won’t go into that beta (or is it alpha) version, but it’s pretty exciting.
Without further comment, lets get 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:
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:
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:
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:
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:
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!
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.
4.) It will then prompt you to define a CRS. Click OK, and your map will appear!
Yes, I know that’s not very exciting, but hey it works!
That’s it for now!