Getting started with PostGIS

For many years I had been hearing about spatial databases. I knew that some of the frustrating issues I was dealing with as the GIS specialist for small environmental consulting companies could be addressed with the technology.  But consulting is all about billable hours and its really hard to convince your boss to give you the unbillable time needed to figure it all out. And its really hard and probably unethical to expect a client to let you figure it all out on their dime. So until a few years ago I kept chugging away with single-user file-based data storage and wasted an enormous amount of time managing data and people to prevent conflicts that could have easily been prevented with a spatial database. 

Then a few years ago I was finally assigned a project that allowed me to take the plunge into spatial database technology. I was very impressed and I have not looked back.  I think that there are many GIS professionals in the same position as I was a few years ago. Wanting to learn, wanting to expand their toolset and personal marketability, wanting to solve the problems they are facing the correct way instead of hacking together “solutions” that are unnecessarily complex and prone to failure. Yet somehow not having the time or resources to do it, or maybe not even knowing how to get started, especially with commercial software products that charge large amounts of money to access the technology.

A few months ago I wrote a blog post titled What can a spatial database do for you? In which I attempted to summarize the benefits of the technology for storing and analyzing geospatial data. I also wrote a Quick start to spatial databases with QGIS and SpatiaLite to provide an entry point to explore the technology for free, that didn’t involve the complexities of setting up a true client-server database system. And I developed a course called QGIS 3.0 for GIS Professionals, which is relevant because QGIS was originally developed as a viewer for spatial data stored in PostGIS and it has spatial database technology embedded deep in its DNA.

The feedback from that work led me to begin development of a new course on spatial databases, specifically focused on PostGIS that will provide detailed instructions on installing PostGIS locally (You’ll want to talk to your IT department about installing it on your companies server), loading data, working with it from various clients, using spatial SQL to request data, analyze data, create and edit data, control user access to your companies data, and automate your organizations business logic.

I wanted to release a few lectures to allow those who are or who have been in the position I was in a few years ago to get started with PostGIS on their own I also wanted to make the technology available to current students of my QGIS course, because I believe that the combination of QGIS 3.0 and PostGIS provides an amazingly powerful set of tools for working with geospatial data. They are free so there is no barrier to learning other than your time. My hope is that this guide will save you from having to figure everything out on their own as I did and convince you to dip your toes in the waters of multi-user enterprise level spatial database technology.

The first video demonstrates how to install PostgreSQL and PostGIS localy on your machine. I demonstrate on a Windows 10 machine only as I don’t have access to OS X or Linux computers but all the software (except ArcGIS) is available on those platforms as well. You’ll have to fill in the blanks yourself though.

The second video demonstrates how to load spatial data into a PostGIS database using both a standalone GUI client and QGIS.

The third video demonstrates how to load non-spatial tables into a PostGIS database because I believe one of the really powerful benefits of using a spatial database is the ease with which it integrates non-spatial and spatial data.

The fourth video demonstrates how to interact with your database and write SQL queries to manage and analyze your data fro a variety of clients including the psql comand line, the pgAdmin GUI, QGIS, and even ArcGIS.  If you just want to watch the part demonstrating QGIS and ArcGIS feel free to skip to 10:22.