A Tour of the PostGIS Extended Family of Extensions

Regina Obe

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

Latest books

48% hardcopy 25% off e-book

Use: promo POSTGISDAY22 for 25% off

PostGIS solves spatial problems

PostGIS helps you analyze, manage, mold, and visualize space.

PostGIS related extensions

PostGIS models space in many ways

Flat, Round, Matrix, Declarative

PostGIS geometry type (Flat)

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)

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)

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)

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 PostGIS 3.3 export topologies
• pgtopo_import - Import topologies (New in PostGIS 3.3.0)

Need to geocode US data?

Geocoding and Reverse Geocoding using US Census Tiger data.

Companion extension: address_standardizer

Loading the data

SELECT ST_AsText(g.geomout), pprint_addy(g.addy)
FROM geocode('1600 Pennsylvania Avenue NW, Washington, DC 20500', 1) AS g;

                 st_astext                  |                  pprint_addy
---------------------------------------------+------------------------------------------------
 POINT(-77.03511478601034 38.89867093615472) | 1600 Pennsylvania Ave NW, Washington, DC 20500

SELECT pprint_addy(rg.addy[1])
FROM reverse_geocode(ST_Point(-77.03511, 38.89867)) AS rg;

                pprint_addy
----------------------------------------------
 692 E Executive Ave NW, Washington, DC 20006

PostGIS + CGAL

Power of CGAL in PostGIS via the SFCGAL library. Used for advanced 3-D and 2-D processing.

• Many functions specifically targeted for Polyhedral surfaces and TINS
• New in PostGIS 3.3.0

  ST_3DConvexHull

  ST_OptimalAlphaShape ST_AlphaShape

Installing

CREATE SCHEMA postgis;
ALTER DATABASE gisdb set search_path=public,postgis;
set search_path=public,postgis;
-- included with postgis
CREATE EXTENSION postgis SCHEMA postgis; --only vector in 3.0
CREATE EXTENSION postgis_raster SCHEMA postgis; -- raster separate in 3.0
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch; -- needed by geocoder
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us; -- lex and rules

pgRouting

Uses graph algorithms: edges have costs, nodes connect edges.

• Find shortest path consider costs (road network, one way, toll price etc).
• Maximum service area
• Decision trees - compute most favorable path given where you are in the tree.

MobilityDb

Analysis and managing spatial movement in a compact way. Follows the OGC Moving Features spec. Extends postgis geometry and geography types with time elements. Raw data often comes in General Transit Feed Specification (GTFS) format and GPX (that stuff from your GPS device). Extends postgis geometry and geography types to form temporal spatial types: tgeompoint, tgeogpoint, and many others.

• Store massive amounts of movement data in a compact way.
• Do short and fast computations on travel.
• What's the average speed a cat travels when on the hunt for prey.
• Integration with QGIS for viewing movement data.

pgpointcloud

Load data in using another open source tool PDAL: Point Data Abstraction Library

What are point clouds? a bag of fat points collected by satellites, drones, planes, 3d scanners, and used to develop other forms of data: spatial data formats (particularly 3D models).

Foreign Data Wrappers

Foreign data wrappers allow you to query data not in your database as if it is a table in your database.

• oracle_fdw: Oracle sdo_geometry -> postgis::geometry
• ogr_fdw: spatial anything -> postgis::geometry

ogr_fdw (mega foreign data wrapper)

Links external (sometimes spatial) data in PostGIS/PostgreSQL format. It is an abstraction: this data over there is a table in my database.

• If you have spatial data it can link it.
• If you have non-spatial data, it can link it.
• If you have no clue what this random file someone gave you is, it can probably link it.

H3 hierarchical geospatial indexing system

extensions: h3-pg
h3, h3_postgis

Exposes Uber's Hexagonal tiling scheme as a PostgreSQL SQL API. Also adds a new type called h3index.

Live demo

• postgis
• postgis_tiger_geocoder
• h3, h3_postgis
• postgis_sfcgal
• postgis_topology
• postgis_raster
• ogr_fdw
• mobilitydb
• pgrouting
• pointcloud, pointcloud_postgis

PostGIS 3.3.1 latest stable

Works with PostgreSQL 11-15. Available in a package near you.

Can't enable in database with CREATE EXTENSION unless have binaries

• Available from packagers: (apt.postgresql.org, yum.postgresql.org, Windows Stackbuilder, bsd ports, homebrew) postgis packaged extensions, pointcloud/pointcloud_postgis, pgrouting, ogr_fdw, oracle_fdw

  Many available via pgxn

• Support via Database As a Service (DBaaS): postgis extensions (except sometimes not postgis_sfcgal), pgrouting (a lot of the time, but popular ask when not), oracle_fdw (sometimes), ogr_fdw (rarely)

• Need to compile yourself or use provided container setups: - mobilitydb, h3, h3_postgis (available via pgxn also)

FIN

Buy our books https://postgis.us