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

0

0

Mastering 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:

--
 
-- EXAMPLE 1: What is the total length of all roads in British Columbia?
 
--
 
SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;
 

 
km_roads
 
------------------
 
70842.1243039643

--
 
-- EXAMPLE 2: How large is the city of Prince George, British Columbia?
 
--
 
SELECT ST_Area(the_geom)/10000 AS hectares
 
FROM bc_municipality
 
WHERE name = 'PRINCE GEORGE';
 

 
hectares
 
------------------
 
32657.9103824927

--
 
-- EXAMPLE 3: What is the largest municipality in the province of British Columbia?
 
--
 
SELECT name,
 
       ST_Area(the_geom)/10000 AS hectares
 
FROM bc_municipality
 
ORDER BY hectares DESC
 
LIMIT 1;
 

 
          name | hectares
 
---------------+-----------------
 
 TUMBLER RIDGE | 155020.025561

--
 
-- EXAMPLE 4: What is the length of roads fully contained within each municipality?
 
--
 
SELECT m.name,
 
       sum(ST_Length(r.the_geom))/1000 as roads_km
 
FROM   bc_roads AS r,
 
       bc_municipality AS m
 
WHERE ST_Contains(m.the_geom,r.the_geom)
 
GROUP BY m.name
 
ORDER BY roads_km;
 

 
                       name | roads_km
 
----------------------------+------------------
 
                     SURREY | 1539.47553551242
 
                  VANCOUVER | 1450.33093486576
 
           LANGLEY DISTRICT | 833.793392535662
 
                    BURNABY | 773.769091404338
 
              PRINCE GEORGE | 694.375543691

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.

#
 
# REDHAT/CENTOS DERIVATIVES, USING COMMUNITY POSTGRES
 
#
 
yum update -y
 
yum install -y postgis30_12 postgis30_12-client

#
 
# CREATE THE DATACLUSTER
 
#
 
/usr/pgsql-12/bin/postgresql-12-setup initdb

#
 
# SERVICE STARTUP
 
#
 
systemctl start postgresql-12

#
 
# INSTALLING THE POSTGIS EXTENSION
 
#   Execute as UNIX account postgres
 
#
 
createdb mydatabase;
 
psql -c 'create extension postgis' mydatabase

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.

--
 
-- if you want to install raster support
 
--
 
CREATE EXTENSION postgis_raster;

--
 
-- if you want to install topology support
 
--
 
CREATE EXTENSION postgis_topology;

--
 
-- if you want to install sfcgal support
 
--
 
CREATE EXTENSION postgis_sfcgal;

--
 
-- if you want to install tiger geocoder
 
--
 
CREATE EXTENSION fuzzystrmatch
 
CREATE EXTENSION postgis_tiger_geocoder;

--
 
-- if you want to install pcre, add the address standardizer extension
 
--
 
CREATE EXTENSION address_standardizer;

--
 
-- list of all installed extensions
 
--
 
mydatabase=# select * from pg_available_extensions where name ~ '^postgis';
 
                     name | default_version | installed_version | comment
 
--------------------------+-----------------+-------------------+---------------------------------------------------------------------
 
                postgis-3 | 3.0.1           |                   | PostGIS geometry, geography, and raster spatial types and functions
 
         postgis_raster-3 | 3.0.1           |                   | PostGIS raster types and functions
 
         postgis_sfcgal-3 | 3.0.1           |                   | PostGIS SFCGAL functions
 
postgis_tiger_geocoder-3  | 3.0.1           |                   | PostGIS tiger geocoder and reverse geocoder
 
       postgis_topology-3 | 3.0.1           |                   | PostGIS topology spatial types and functions
 
                  postgis | 3.0.1           | 3.0.1             | PostGIS geometry, geography, and raster spatial types and functions
 
           postgis_raster | 3.0.1           | 3.0.1             | PostGIS raster types and functions
 
           postgis_sfcgal | 3.0.1           | 3.0.1             | PostGIS SFCGAL functions
 
   postgis_tiger_geocoder | 3.0.1           | 3.0.1             | PostGIS tiger geocoder and reverse geocoder
 
         postgis_topology | 3.0.1           |                   | PostGIS topology spatial types and functions

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:

• pgsql2shp
• shp2pgsql

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

psql -U postgres <<_eof_
 
\set ON_ERROR_STOP on
 

 
drop database if exists gis_demo;
 
create database gis_demo;
 

 
\c gis_demo
 

 
create extension if not exists postgis;
 
create extension if not exists postgis_topology;
 

 
create schema gis;
 

 
create or replace function gis.findzipcode(
 
inout longitude double precision,
 
inout latitude double precision,
 
out zipcode varchar
 
) as
 
\$\$
 
begin
 
select zcta5ce10
 
into zipcode
 
from zipcodes, (values (st_makepoint(longitude,latitude)))t(p)
 
where st_contains(geom, p);
 
end;
 
\$\$
 
language plpgsql
 
security definer
 
set search_path=gis,public,topology;
 
_eof_

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

#
 
# Execute the following, the actual arguments will vary
 
#
 
shp2pgsql -c -D -I tl_2019_us_zcta510.shp gis.zipcodes | psql -U postgres gis_demo

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

gis_demo=# \d
 
List of relations
 
   Schema | Name              | Type     | Owner
 
----------+-------------------+----------+----------
 
   public | geography_columns | view     | postgres
 
   public | geometry_columns  | view     | postgres
 
   public | spatial_ref_sys   | table    | postgres
 
 topology | layer             | table    | postgres
 
 topology | topology          | table    | postgres
 
 topology | topology_id_seq   | sequence | postgres
 
(6 rows)

gis_demo=# \dn
 
List of schemas
 
     Name | Owner
 
----------+----------
 
      gis | postgres
 
   public | postgres
 
 topology | postgres
 
(3 rows)

gis_demo=# \d zipcodes
 
Table "gis.zipcodes"
 
     Column | Type                   | Collation | Nullable | Default
 
------------+------------------------+-----------+----------+---------------------------------------
 
        gid | integer                |           | not null | nextval('zipcodes_gid_seq'::regclass)
 
  zcta5ce10 | character varying(5)   |           |          |
 
    geoid10 | character varying(5)   |           |          |
 
  classfp10 | character varying(2)   |           |          |
 
    mtfcc10 | character varying(5)   |           |          |
 
 funcstat10 | character varying(1)   |           |          |
 
    aland10 | double precision       |           |          |
 
   awater10 | double precision       |           |          |
 
 intptlat10 | character varying(11)  |           |          |
 
 intptlon10 | character varying(12)  |           |          |
 
       geom | geometry(MultiPolygon) |           |          |
 
Indexes:
 
"zipcodes_pkey" PRIMARY KEY, btree (gid)
 
"zipcodes_geom_idx" gist (geom)

Step 3: An Example Query

--
 
-- getting a zipcode based upon the LAT/LON
 
--
 
gis_demo=# select * from gis.findzipcode(-87.798615,30.53711);
 
  longitude | latitude | zipcode
 
------------+----------+---------
 
 -87.798615 | 30.53711 | 36576

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!

This information was first published on https://www.percona.com/blog/2020/04/15/working-with-postgresql-and-postgis-how-to-become-a-gis-expert/

John Jason Fallows

See author's posts