PostGIS

Geospatial superpowers for PostgreSQL — maps, routing, geofencing.

Database #sql#relational#gis#spatial#postgresql

PostGIS is the gold-standard geospatial extension for PostgreSQL. It adds geometry and geography types, 1000+ spatial functions, R-tree indexes, projection support, and topology — turning Postgres into a fully-featured GIS server used by national mapping agencies, ride-sharing platforms, and field-service apps worldwide.

Deploy with Pier

1 Open the Pier dashboard and click Add service.
2 Pick PostGIS from the template list.
3 Choose the version, set a service name, and Pier provisions the container, storage, and ports automatically.
4 Attach a domain if you want HTTPS. Traefik auto-provisions the Let's Encrypt certificate.

What is PostGIS?

PostGIS is the canonical geospatial extension for PostgreSQL. Created in 2001 by Refractions Research, it has grown into the foundation of the open-source GIS stack: OpenStreetMap stores its data in PostGIS, Mapbox uses it as a backend, every national mapping agency that touches OSS has a PostGIS server somewhere in production.

Technically it’s an extension you install into a Postgres database, but in practice you almost always use a Postgres distribution where it’s already compiled in (the official postgis/postgis Docker image). Once enabled, your Postgres database gains spatial types, 1000+ spatial functions, GIST indexes for fast geometric queries, and tooling for raster, topology, and tile work.

How Pier deploys it

Pier uses the official postgis/postgis Docker image, mounting /var/lib/postgresql as the data volume. The default version is latest (PostGIS 3.5 on PostgreSQL 18); 18-master, 17-master, 16-master, and 15-master are available for version pinning.

When creating a database through Pier’s UI, you get a checklist of PostGIS extensions to install (postgis, postgis_topology, postgis_raster, fuzzystrmatch, postgis_tiger_geocoder). Pier installs them in dependency order via CREATE EXTENSION … CASCADE.

All Postgres tooling works — pg_dump, psql, ORMs, pgAdmin, DBeaver. Pier’s backup and replication features apply the same way as the vanilla postgresql template.

When NOT to use PostGIS

If you don’t need spatial functions, use regular PostgreSQL — PostGIS adds disk and memory overhead. If your only geo needs are “store lat/lon and find items within radius”, Postgres with a btree on (lat, lon) and a Haversine function might suffice. Move to PostGIS the moment you need polygons, projections, or raster.

Key features

Spatial types

GEOMETRY (planar) and GEOGRAPHY (spherical) types with points, lines, polygons, multipolygons, collections. SRID-aware — store data in any of 5000+ coordinate reference systems.

1000+ spatial functions

ST_Distance, ST_Within, ST_Intersects, ST_Buffer, ST_Union, ST_ConvexHull, ST_Centroid — every standard OGC spatial function plus PostGIS-specific extras.

R-tree GIST indexes

GIST indexes on geometry columns make spatial queries (ST_DWithin, ST_Intersects) sub-millisecond even on tables with hundreds of millions of features.

Raster support

PostGIS Raster handles satellite imagery, elevation data, weather grids. Co-query rasters and vectors in one SQL statement.

Topology

postgis_topology models topological relationships (shared edges, faces) — critical for cadastral, transportation network, and political-boundary work where consistency matters.

100% PostgreSQL

It's still PostgreSQL — same drivers, same transactions, same backups. Combine spatial queries with relational joins, JSONB, full-text, and stored procedures in one query.

Use cases

Maps & cartography

Vector tile servers (pg_tileserv, Martin) serve millions of map tiles from PostGIS in real time. Mapbox Vector Tiles via ST_AsMVT().

Geofencing & alerts

Notify when a vehicle enters or exits a polygon — ST_Within against geofence polygons. Sub-millisecond with proper indexing.

Routing networks

pgRouting adds Dijkstra, A*, K-shortest-paths on PostGIS road networks. OpenStreetMap data + pgRouting = your own routing engine.

Fleet & asset tracking

Store GPS pings as POINT(longitude, latitude). Query "all assets within 5 km of HQ in the last hour" with one SQL statement.

Risk & insurance modelling

Spatial joins of customer addresses against flood zones, crime maps, fault lines. Returns risk scores per policy in batch.

Code examples

Create spatial table + GIST index sql

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE places (
  id     BIGSERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  geom   GEOGRAPHY(POINT, 4326) NOT NULL
);

CREATE INDEX places_geom_idx ON places USING GIST (geom);

INSERT INTO places (name, geom) VALUES
  ('Moscow', ST_GeogFromText('POINT(37.6173 55.7558)')),
  ('London', ST_GeogFromText('POINT(-0.1276 51.5074)'));

Nearest neighbour (KNN) sql

SELECT name,
       ST_Distance(geom, ST_MakePoint(37.6, 55.7)::geography) AS meters
FROM places
ORDER BY geom <-> ST_MakePoint(37.6, 55.7)::geography
LIMIT 10;

Points-in-polygon (geofencing) sql

SELECT v.id, v.callsign
FROM vehicles v
JOIN geofences g ON ST_Within(v.last_position::geometry, g.area)
WHERE g.alert_zone = true;

Build a Mapbox Vector Tile sql

SELECT ST_AsMVT(tile, 'places')
FROM (
  SELECT id, name,
         ST_AsMVTGeom(
           geom::geometry,
           ST_TileEnvelope(z, x, y),
           4096, 64, true
         ) AS geom
  FROM places
  WHERE geom && ST_TileEnvelope(z, x, y)
) AS tile;

How it compares

vs Vanilla PostgreSQL	PostgreSQL doesn't ship spatial types — PostGIS is the de-facto extension. Use the PostGIS template if you know you'll need spatial; the regular postgresql template can install postgis later via CREATE EXTENSION.
vs Esri ArcGIS / Oracle Spatial	PostGIS is the OSS equivalent — competitive feature set, used by US Census Bureau, IGN France, UK Ordnance Survey, Berlin Senate, and many more national mapping agencies.
vs MongoDB GeoJSON	Mongo's 2dsphere covers basic geo queries; PostGIS goes deeper (raster, topology, projections, network analysis). Pick PostGIS for serious GIS work.
vs Specialist tile servers (Tegola, Mapnik)	PostGIS pairs with tile servers — they read from PostGIS. PostGIS is your geo source of truth, not a tile renderer itself.

Frequently asked questions

Is PostGIS just an extension I install in regular PostgreSQL?

Yes — technically. Pier ships a dedicated PostGIS template because the postgis/postgis Docker image bundles the extension pre-built. You could install it manually on the postgresql template via apt + CREATE EXTENSION, but the dedicated image is faster and cleaner.

Which extensions does Pier auto-install?

When you create a new database through Pier's UI on a PostGIS service, Pier offers a checklist — `postgis`, `postgis_topology`, `postgis_raster`, `fuzzystrmatch`, `postgis_tiger_geocoder`. Tick the ones you need; Pier runs CREATE EXTENSION ... CASCADE in order.

What SRID should I use?

4326 (WGS 84) for most GPS data. 3857 (Web Mercator) for web map tiles. ST_Transform converts between them.

GEOGRAPHY vs GEOMETRY?

GEOGRAPHY treats earth as a sphere — distances in metres, no projection. GEOMETRY is planar — distances in SRID units. For "distance between two GPS points" use GEOGRAPHY; for cartographic work in a projected CRS, use GEOMETRY.

Which PostgreSQL version does the PostGIS template use?

Default is `latest` (currently PostGIS 3.5 on PostgreSQL 18). 18-master, 17-master, 16-master, 15-master variants pin the Postgres major version.

Backups work the same as Postgres?

Yes — pg_dump captures the spatial data. Backup format is .dump (pg_custom). Restore preserves geometry types and indexes.

Can I use pgRouting?

pgRouting is a separate extension. Install it manually inside the container — the postgis/postgis image includes pgRouting in some tags. Check tag descriptions on Docker Hub.

Related services

Deploy on your VPS

Deploy this service →