PostGIS related extensions

Regina Obe

Buy our books!

Our latest books

(50% discount
pgRouting a Practical Guide
(35% all books
from Locate Press)
Books Coming
SQL in a Nutshell 4th EditionSQL In a Nutshell
Due out mid next year, available now in early release
The Book of PostgreSQL (No Starch Press)
Due out late next year
pgRouting (a practical guide) 2nd Ed (Locate Press) target late next year.

PostGIS related extensions



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, stbox, geodstbox, periodset and several others.

  • Store massive amounts of movement data in a compact way.
  • What's the average speed for X part of the path of a trip.
  • Integration with QGIS for viewing movement data.


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.

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

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;


ALTER DATABASE gisdb 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

-- packaged separately
CREATE EXTENSION pgrouting SCHEMA postgis;
CREATE EXTENSION mobilitydb SCHEMA postgis;
CREATE EXTENSION pointcloud SCHEMA postgis;
CREATE EXTENSION pointcloud_postgis SCHEMA postgis;

Live demo in words

Word Play with Spatial SQL
  • postgis_topology
  • pgrouting
  • postgis_raster
  • postgis
ALTER DATABASE gisdb SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
DROP TABLE IF EXISTS happy_raster;

WITH the_words AS
        (SELECT pos, ST_Translate(
			ST_LettersAsGeometry(word, 'kankin'), 0, 
				-pos*100) As geom
          FROM  (VALUES 
			(2,'DAY')  ) AS my(pos, word)
 , canvas
    AS (SELECT ST_MakeEmptyRaster((ST_XMAX(ext) - ST_XMin(ext))::integer, 
        (ST_YMAx(ext) - ST_YMin(ext))::integer,0, 0, 1, -1, 0, 0, 0) As rast
        FROM (SELECT ST_Extent(geom) As ext FROM the_words) As foo
SELECT 1 AS id, ST_Union(ST_AsRaster(geom
     , canvas.rast
     , ARRAY['8BUI','8BUI', '8BUI']::text[]
     , ARRAY[pos*75,6,0], ARRAY[0,0,0]) 
        ) AS rast
    INTO happy_raster
     FROM  the_words CROSS JOIN canvas;

Copy and paste in address bar of browser to see the image

SELECT 'data:image/png;base64,' || encode(ST_AsPNG(
    ST_Resize(rast, 0.3,0.3)),'base64')
     FROM  happy_raster;


Buy our books