Working with PostgreSQL and PostGIS: How To Become A GIS Expert

PostgreSQLMastering Geographical Information Systems, better known simply as GIS, can be considered in some ways as a rite of passage. The complexities and challenges involved learning, what are ostensibly non-IT concepts, is steep. However, as they say, “there’s more than one way to skin a cat“. I’d like to share with you one way to tackle this challenge.

Let me introduce you to PostGIS.

PostGIS is a PostgreSQL extension that adds GIS capabilities to this RDBMS. Its popularity stems from not only being “free” but because it’s considered to be among the leading GIS implementations in the world today. Virtually every major front-end application provides the hooks for a PostGIS, PostgreSQL enabled back-end.

The PostGIS project, which is BSD licensed, began back in 2001. It turns our vanilla flavored postgres into a spatial database and includes spatial datatypes (geometry, geography), spatial indexes (r-tree, quad-tree, kd-tree), and spatial functions.

Working with GIS normally requires several layers of technology of Geo-Spatial Software, as for example:

Boundless Server (formerly the OpenGeo Suite)

  • PostGIS – Spatially enabled object-relational database.
  • GeoServer – Software server for loading and sharing geospatial data
  • GeoWebCache – Tile cache server that accelerates the serving of maps
  • Composer – Web-based map configuration and styling utility
  • WPS Builder – Web-based graphical constructor for generating server-side processes
  • QuickView – Web application for composing and styling web maps

Note: Boundless Server GitHub repository

Querying The Database

It’s amazing the kinds of answers one can get by a single, well-composed, and yet standard, query by asking such simple questions as:

  • How far is it from here to there?
  • What’s the closest point between two meandering streets?
  • Is a street found in a certain zip-code?
  • How many homes are susceptible to flooding?

Here are a few example queries using a PostGIS powered database. Refer here for more community-related shapefiles for Kamloops British Columbia:

Installation

Basic

The following installation instructions assume one is using PostgreSQL version 12 on Linux/CENTOS-7, although any major version of postgres and OS can be used.

Note: Although it is on the road-map, the Percona Distribution for PostgreSQL does not currently include PostGIS. But you should be able to install the community PostGIS packages to it with the pertinent YUM/RPM package switches.

Complete Installation

The following provides a more complete installation of all PostGIS capabilities. Refer to the PostGIS documentation for more information.

Working With Shapefiles

There are literally hundreds of terabytes available online. Ironically, the most precious data one can get is free to download because it’s been generated by governments from all over the world for the public good.

Now it’s time to get some data. The most common format is “shapefile“.

PostGIS includes these two command-line utilities:

Working With ZipCodes From The Us Census Data: Tiger Files

TIGER/Line files are a digital database of geographic features, such as roads, railroads, rivers, lakes, legal boundaries, census statistical boundaries, etc. covering the entire United States and are freely available here.

Once you’ve navigated to this website you can download a multitude of fascinating pieces of data.

Example:

Step 1: Execute The Following As A Bash Script

Step 2: Download The 2019 ZipCode Shapefile

The shapefile is stored in a zipfile. Unzip the entire archive before attempting an upload.

URL: https://www.census.gov/cgi-bin/geo/shapefiles/index.php

Once the upload into the postgres database is complete, this is what you’ll get:

Step 3: An Example Query

On The Road To Mastering GIS

There are a couple of things to keep in mind when querying PostGIS powered databases:

  • Follow the PostGIS reference documentation closely: it’s easy to read, explains a lot of the ideas behind the function call, and provides many examples.
  • The GIS standard requires that functions be documented using mixed case.
  • GIS function calls, implemented in postgres, are written using lowercase.
  • For the adventurous, here’s a comprehensive and freely available dataset of shapefiles for the city of Kamloops, British Columbia.

Because so much of our modern big data insights depend upon raw GIS data, directly querying a GIS database empowers one to create even more powerful and precise insights. PostGIS is an excellent way for the DEV, DBA, and SRA to learn all things GIS.

Have Fun!


Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.

Download PDF

Share this post

Leave a Reply