A Tour of the PostGIS Extended Family of Extensions

Regina Obe

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

Latest books

SQL In a Nutshell

48% hardcopy
25% off e-book

pgRouting a Practical Guide

Use: promo
for 25% off

Books Coming
The Book of PostgreSQL (No Starch Press)
In Progress
pgRouting (a practical guide) 2nd Ed (Locate Press) in Progress.

Use: POSTGISDAY22 at locatepress.com checkout and get 25% off on all e-books

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)

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

Need to geocode US data?

extension: postgis_tiger_geocoder

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;
 692 E Executive Ave NW, Washington, DC 20006


extension: postgis_sfcgal

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


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


extension: 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.


extension: 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.


extension: pointcloud, postgis_pointcloud data types pcpatch, pcpoint

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)

extension: ogr_fdw

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)


Buy our books https://postgis.us