What can you do with PostGIS?

Regina Obe

Buy our books! https://postgis.us/page_buy_book

Latest books

Twitter: @reginaobe

Books Coming

SQL in a Nutshell 4th Edition

Out in hard-copy in 2 months, covers SQL:2016.

The Book of PostgreSQL (No Starch Press)
In Progress

pgRouting (a practical guide) 2nd Ed (Locate Press) in Progress.

PostGIS related extensions

CREATE EXTENSION ...;

Why I started using PostGIS

I have land and projects. PostGIS ticked all the boxes.

Which gets us to the point of what can you do with PostGIS. I started using PostGIS 20 years ago because I was a data manager and I needed to manage an inventory of city owned land which would be given out to create projects like open spaces, shopping malls, affordable housing.
We even had slivers of land inside buildings that companies didn't know they didn't own until we told them.
That deserves a story for a later time.

These parcels of land, people needed to know where they were, how big they were, what they were near by, and what was their selling value given their proximity to other things like transportation, parks, hospitals, and schools.

In addition they needed this information needed to sit alongside things that weren't so spatial. It needed to speak to data that lived in relational databases. Boring stuff like project timelines,
who is working on construction, demographic data (before and after project completion) to assess the impact the city was making.

The standard way others did it was unpleasant to my taste-buds. The popular solutions involved some gyrations with desktop mapping software. This was something I neither cared to learn or wanted to spend my time doing.

In short I was in a perfect storm and PostGIS came to the rescue.

And so my journey began with PostGIS and it's mothership Postgres.

PostGIS understands data about location

GPS data
Satellite data
Survey data about boundaries of land
Raster data (Thematic matrices like population density, instrument data like temperature, soil acidity, elevation, aerial, pictures)

PostGIS can use the key of space

With the key of space it can derive all sorts of information

Average sale price of an arbitrary area
Regions with highest traffic accidents
Income levels in your area
Distances from your area to closest high school, mall, elementary school, hospital
Approximate location of this spot based on postal address (using road network data)
Population impacted by outage

PostGIS helps you visualize data

Outputs data in numerous web-friendly and spatial standard ways

SQL/MM: ST_AsText, ST_AsWKB
Lossless, performance focused binary: ST_AsFlatGeoBuf, ST_AsTWKB
Geospatial Standard Formats: ST_AsKML, ST_AsGML ST_AsGeoJSON, ST_AsMVT
Web Consortium Standards: ST_AsSVG, ST_AsX3D
Can output 100s of standard raster formats using ST_AsGDALRaster, ST_AsPNG, ST_AsJPEG

PostGIS helps you injest data

Input functions help consume spatial data in numerous formats

SQL/MM: ST_GeomFromText, ST_GeomFromWKB
Performance focused lossless: ST_GeomFromTWKB, ST_GeomFromFlatGeoBuf
Geospatial Standard Formats: ST_GeomFromGeoJSON, ST_GeomFromKML, ST_GeomFromGML
Can input 100s of raster types using ST_FromGDALRaster

PostGIS is standards compliant

PostGIS is an extension to PostgreSQL, but also follows the SQL/MM (Multimedia) Part 3 standard. It is similar to below but unlike these, is an extension rather than part of Db proper

Oracle Spatial/Locator
SQL Server
MariaDB / MySQL

Most common relational databases systems have a spatial component in them that follows a standard called OpenGeospatial Simple Features for SQL which morphed into an SQL standard called SQL/MM (The MM is for multi-media).

What sets PostGIS apart from these others is that PostGIS is not a part of PostgreSQL proper where as the other databases the spatial is baked in. This is both a good and a bad thing. It's a bad thing because it's hard to explain to other database folks who have spatial readily available when they install their relational database.

"PostgreSQL follows the SQL/MM standard if you have PostGIS installed"

Luckily there aren't too many of those, so most discussions end up being, let me show you how to use the spatial in "Your relational database" and why it's not as good as PostGIS and you should switch to PostgreSQL.

Where your is "Oracle, SQL Server, or MySQL".

PostGIS doesn't need to be a part of PostgreSQL because PostgreSQL is easy to extend without touching the core and most distributions of PostgreSQL make it easy to install PostGIS. It allows PostGIS to move at its own pace which is not completely aligned with PostgreSQL pace.

PostGIS is a de facto standard

Google BigQuery GIS https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions
CockroachDB - Emulates both PostgreSQL and PostGIS - https://www.cockroachlabs.com/docs/v22.1/spatial-tutorial.html
Snowflake - https://docs.snowflake.com/en/sql-reference/functions-geospatial.html

PostGIS models space in many ways

Flat, Round, Matrix, Declarative

PostGIS geometry type (Flat)

extension: postgis

The flat space model. Space is a cartesian grid. Supports drawing of linestrings, polygons, 3D polygons, points, 3d points, collections of polygons, points, linestrings, Polyhedral Surfaces, and TINS

Basic geometric types

Polyhedral Surface

Triangulated Irregular Network (TIN)

PostGIS geography type (round)

extension: postgis

Model of space as spheroid. Takes into consideration the earth or any given planet whose spatial reference is defined in spatial_ref_sys table.

Geodetic (Geography) 4326 (WGS 84 Lon Lat) in geography

PostGIS raster type (matrix)

extension: postgis_raster

Model of space as a flat numeric matrix (with cells (called pixels) that have values (on) or don't have values (off))

Elevation
Soil
Weather
Fire
Aerial
Population

PostGIS topogeometry type (declarative)

extension: postgis_topology

Defined in SQL/MM Topology-Geometry specs. Spatialite has it too. Topology partitions space into edges, nodes, and faces. Then it says this thing called topogeometry is space composed of these edges, nodes, faces and other topogeometries (which are again just a bunch of edges, nodes,and faces). If two topogeometries have the same set of (edges,nodes,faces) then they are the same.

(1,1,2,3) -> topology_id, layer_id, id, type

SELECT topo::geometry AS geom, (topo).* 
FROM  some_topo_table;

PostGIS packaged command-line tools

These are part of PostGIS, but often shipped by packagers in a separate package from PostGIS extensions.

shp2pgsql - Loads data from ESRI Shapefile format
pgsql2shp - Exports data from PostGIS format to ESRI shapefile format (for other formats use ogr2ogr (part of GDAL suite of tools) or ogr_fdw
raster2pgsql - Loads data into postgis raster from hundreds of different raster formats (thru the power of GDAL)
pgtopo_export - New in upcoming PostGIS 3.3.0, export topologies
pgtopo_import - Import topologies (New in PostGIS 3.3.0)

Where do you get PostGIS?

PostGIS Docker - https://registry.hub.docker.com/r/postgis/postgis/
Debian / Ubuntu - https://apt.postgresql.org (postgis PostgreSQL 10-14), latest PostGIS 3.2.1 on newer PostgreSQL. PostGIS 3.3.0alpha1 available for PostgreSQL 15beta1
Yum (CentOS, Redhat EL, Scientific Linux): https://yum.postgresql.org (has 3.2.1 (and other versions) for PostgreSQL 10 - 14)
Windows ( part of application stackbuilder): https://postgis.net/windows_downloads/ -- (PostGIS 3.2.1 for PostgreSQL 10 -14)(Experimental Builds section builds on each commit, PostGIS 3.3.0dev for PostgreSQL 11-14)
The manual - https://postgis.net/documentation

LIVE DEMOS

Download the postgres_vision_2022.sql file

Enable extensions PostGIS 3+

This is running in psql. If in pgAdmin just manually reconnect to your gisdb. Example is gisdb but do for any spatial databases you have.

                  CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,tiger,contrib;
\c gisdb
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS contrib;
CREATE EXTENSION postgis SCHEMA postgis; -- core includes geometry / geography
CREATE EXTENSION postgis_raster SCHEMA postgis; -- raster type and functions
CREATE EXTENSION postgis_sfcgal SCHEMA postgis; -- extended support for 3D geometries
CREATE EXTENSION postgis_topology; -- topogeometry relational view of spatial data
CREATE EXTENSION fuzzystrmatch SCHEMA contrib; -- needed by tiger geocoder
CREATE EXTENSION address_standardizer SCHEMA contrib; -- for standardizing addresses
CREATE EXTENSION postgis_tiger_geocoder; -- geocoding and reverse geocoding using US TIGER data

How you upgrade¶

SELECT postgis_extensions_upgrade();

Check your installation

SELECT postgis_full_version();

POSTGIS="3.3.0alpha1 3.3.0alpha1" [EXTENSION] PGSQL="150" GEOS="3.11.0dev-CAPI-1.16.0" 
SFCGAL="SFCGAL 1.4.1, CGAL 5.3, BOOST 1.78.0" 
PROJ="7.2.1" GDAL="GDAL 3.4.2, released 2022/03/08" L
IBXML="2.9.9" LIBJSON="0.12" 
LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER TOPOLOGY

Output full rows

SELECT json_build_object('type',
	'FeatureCollection', 
	  'features', 
             json_agg( ST_AsGeoJSON(r.*)::json ) )
FROM recent_building_permits AS r;