What can you do with PostGIS?

Regina Obe

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

Latest books

pgRouting a Practical Guide

Twitter: @reginaobe

Books Coming
SQL in a Nutshell 4th EditionSQL In a Nutshell
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


Why I started using PostGIS

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

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

PostGIS helps you injest data

Input functions help consume spatial data in numerous formats

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

PostGIS is a de facto standard

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?


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 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" 
Output full rows
SELECT json_build_object('type',
             json_agg( ST_AsGeoJSON(r.*)::json ) )
FROM recent_building_permits AS r;