What can you do with PostGIS?¶

Regina Obe

My name is Regina Obe and I'm a member of the PostGIS project steering committee. I'm also a co-author of numerous books on the topic of PostGIS, pgRouting, and PostgreSQL as well as upcoming books to be announced. I'll discuss in this presentation what you can do with PostGIS and highlights of upcoming release PostGIS 3.2.

Today I'm going to talk about what you can do with PostGIS, but before that I'll go over some house-keeping items.

PostGIS In Action 3rd Edition available for Purchase¶

Get a copy of 2nd edition with purchase of 3rd edition
Covers PostGIS 3, 3.1, and highlights of upcoming 3.2
https://www.manning.com/books/postgis-in-action-third-edition
All chapters completed and with planned release of hard-copy in 1 to 2 months
https://www.postgis.us find code and data here
Live Book https://livebook.manning.com/book/postgis-in-action-third-edition

We just completed PostGIS in Action, 3rd edition. It will be in print in the next 2-3 months. You can buy an Early release copy now from our publisher Manning. A purchase of the book includes an electronic copy of the 2nd edition of the book. The 3rd edition covers PostGIS 3-upcoming 3.2 and PostgreSQL 11 - PostgreSQL 13

Correct pronounciation of PostGIS¶

Post-jihs¶

PostGIS is 20 years old and still charming¶

http://blog.cleverelephant.ca/2021/05/postgis-20-years.html

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 makes sense of data with location information¶

GPS data
Satellite data
Survey data about boundaries of land
Raster data (aerial imagery, pictures, thematic matrices like temperature, soil acidity, elevation)

PostGIS derives information using the key of space¶

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 is an extension to PostgreSQL and follows the SQL/MM standard¶

It is similar to below but unlike these, is an extension rather than part of Db proper

Oracle Spatial/Locator
SQL Server
MySQL / MariaDB

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/v21.1/spatial-data.html
Snowflake - https://docs.snowflake.com/en/sql-reference/functions-geospatial.html

PostGIS has become a de facto standard with many upcoming relational databases adopting the PostGIS function names (regardless of if they are defined in SQL/MM or not), well because, many GIS people know how to use PostGIS already and almost all spatial tools know how to work with PostGIS.

Where to get PostGIS¶

PostGIS Docker - https://registry.hub.docker.com/r/postgis/postgis/
Debian / Ubuntu - https://apt.postgresql.org (currently at 3.1.2 and PostgreSQL 14beta PostGIS 3.1.2)
Yum (CentOS, Redhat EL, Scientific Linux): https://yum.postgresql.org (has 3.1.2 for PostgreSQL 12 and 13)
Windows ( part of application stackbuilder): https://postgis.net/windows_downloads/ -- (has fresh updates for PostgreSQL 11,12,13 with GEOS 3.9, GDAL 3.2, Proj 7.1.1, PostGIS 3.2)(Experimental Builds section builds on each commit)
The manual - https://postgis.net/documentation

If you want to know what's coming in PostGIS 3.2, best place is to check our development manual which is updated whenever there are new features added to our development code base.

If you'd like to try out new features, our windows buildbot builds packages for PostgreSQL EDB Windows for PostgreSQL 11-13. You can copy these binaries into a development install to test.

LIVE DEMOS¶

Download the postgres_vision_2021.sql file

How you install extensions in 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();

screen
POSTGIS="3.1.2 3.1.2" [EXTENSION] 
PGSQL="130" GEOS="3.9.0-CAPI-1.14.0" SFCGAL="1.3.8" 
PROJ="7.1.1" 
GDAL="GDAL 3.2.0, released 2020/10/26" 
LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

PostGIS 3.0 enhanced GeoJSON support to now accept full features¶

very very old way of creating a feature collection - https://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html (painful)

New way

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