PostGIS Basics

PostGIS adds geometry and geography types, spatial functions, and indexes to PostgreSQL. Keep transactional geospatial data in Postgres; push map rendering and heavy tile math to tile servers or client libraries.

Recipe

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA extensions VERSION '3.5.2';
 
CREATE TABLE stores (
  id        serial PRIMARY KEY,
  name      text NOT NULL,
  geom      extensions.geometry(Point, 4326) NOT NULL
);
 
INSERT INTO stores (name, geom) VALUES
  ('NYC Flagship', extensions.ST_SetSRID(extensions.ST_MakePoint(-73.9857, 40.7484), 4326)),
  ('LA Branch',    extensions.ST_SetSRID(extensions.ST_MakePoint(-118.2437, 34.0522), 4326));
 
-- Nearby stores within 5 km of a point (geography for meter distances)
SELECT name,
       extensions.ST_Distance(
         geom::extensions.geography,
         extensions.ST_SetSRID(extensions.ST_MakePoint(-73.99, 40.75), 4326)::extensions.geography
       ) AS meters
FROM stores
ORDER BY meters
LIMIT 10;

When to reach for this: Delivery zones, asset tracking, geofencing, and spatial joins where data already lives in your OLTP database.

Working Example

CREATE INDEX stores_geom_gist ON stores USING gist (geom);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM stores
WHERE extensions.ST_DWithin(
  geom::extensions.geography,
  extensions.ST_SetSRID(extensions.ST_MakePoint(-73.99, 40.75), 4326)::extensions.geography,
  5000
);
 
SELECT extensions.PostGIS_Version();
SELECT extensions.ST_AsGeoJSON(geom) FROM stores WHERE id = 1;

What this demonstrates:

• geometry(Point, 4326) stores WGS84 lat/lon
• Cast to geography for geodesic meter distances
• GiST index supports ST_DWithin bounding-box prefilter
• ST_AsGeoJSON feeds web maps without a separate export pipeline

Deep Dive

Postgres vs Tile Server

Pattern: PostGIS is the system of record. Tile server generates vector tiles for basemaps and overlays. App queries Postgres for search, routing inputs, and business logic.

Core Types

• geometry - Planar math in a projected coordinate system; fast for local areas with correct SRID.
• geography - Spheroid calculations; distances in meters on WGS84.
• SRID 4326 - GPS lat/lon default for web maps.

Install Ordering

CREATE EXTENSION postgis;
-- optional add-ons after core
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_raster;  -- if raster workloads approved

Pin versions in allowlist ADR. Upgrade with ALTER EXTENSION postgis UPDATE and postgis_extensions_upgrade() when release notes require.

API Output

-- GeoJSON for APIs
SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(extensions.ST_AsGeoJSON(s.*)::json)
) FROM stores s;

Gotchas

• Lat/lon reversed - ST_MakePoint(lon, lat) not (lat, lon). Fix: Code review convention; ST_Y/ST_X sanity tests.
• Missing SRID - SRID 0 geometries break distance semantics. Fix: NOT NULL constraint with geometry(Point, 4326) and ST_SetSRID on insert.
• Geometry for global distance - Wrong units on 4326 geometry. Fix: Cast to geography for meters or project to local UTM.
• Serving tiles from raw SQL - Heavy ST_AsMVT on unindexed tables melts CPU. Fix: Dedicated tile layer with simplified geom and GiST.
• Unvalidated WKT - Invalid polygons from user uploads. Fix: ST_IsValid check constraint or ST_MakeValid in ingest with audit.
• PostGIS in public schema - Clutters search_path. Fix: WITH SCHEMA extensions on greenfield installs.

Alternatives

FAQs

Related

• Geometry vs Geography - types and distance
• Spatial Indexes - GiST patterns
• PostGIS Performance - simplify and prefilter
• Extensions Basics - install and pin
• PostGIS Best Practices - SRID and validation

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.