The Open Source Geospatial Community, PostGIS, & Postgres

Regina Obe

Buy our books!

Latest books

SQL In a Nutshell pgRouting a Practical Guide


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

What is PostGIS?

PostGIS is a set of PostgreSQL extensions to manage and query spatial data in a PostgreSQL database. Spatial data is any data involving space and what inhabits space.

What's in a name?

  • Post-Jis
  • Post G I S
  • Post Gees

Why PostGIS?

To make spatial data not special

Spatial Standards and SQL

2001 PostGIS is born

To make spatial data not special

The intersection of two worlds

  • Relational
  • Spatial (Geo)

Open Source Geospatial, PostGIS, and Postgres

Spatial data is not special?

What? Not Only Special

You can use PostGIS anywhere you can use SQL and Postgres. If you know SQL, you know 90% of what you need to know about PostGIS.

.NET, C, Go, Java, NodeJS, PHP, Python, R, Rust, SQL, and more

But you can also use fancy tools to look at it in its full glory

Example PostGIS query 1:

What are the 3 closest roads to me?

SELECT road_name, s.geog <-> ST_Point(:param_longitude, :param_latitude)::geography AS dist_m
FROM roads

Example Query 2

Summarize Crime stats for NYC Boroughs

SELECT b.boro_name, count(*), c.type
	FROM nyc_boros AS b 
		INNER JOIN crimes AS c ON ST_Intersects(b.geom, c.geom)
		WHERE c.year = 2020
GROUP BY b.boro_name, c.type
ORDER BY b.boro_name, c.type;

PostGIS raises the bar for RDBMS

PostGIS models space in many ways

Flat, Round, Matrix, Relational (Declarative)

Flat: PostGIS geometry type

extension: postgis

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)

Round: PostGIS geography type

extension: postgis

The Round space model. Space is the surface of a spheroid where you can draw linestrings, polygons, points, and collections of polygons, points, linestrings on the surface.

Basic types

On the surface of a sphere

Matrix: PostGIS raster

extension: postgis_raster

Model of space as a numeric matrix (with cells (called pixels) that have values (on) or don't have values (off)). Within that are things called bands that define the number of matrices you have stacked.

  • Elevation
  • Soil
  • Weather
  • Fire
  • Aerial

Relational: PostGIS topogeometry type

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 helper extensions

  • postgis_sfcgal - functions for volumetric and 3D surface
  • postgis_tiger_geocoder - geocoding and reverse geocoding using US Census TIGER data
  • address_standardizer, address_standardizer_data_us Rule-based address standardizer (useful as precursor for geocoding)

PostGIS extended family of extensions


Thank you

You can reach me at:

  • Email:
  • Mastodon:
  • LinkedIn:
  • Twitter: @reginaobe