PostGIS

What PostGIS does

PostGIS extends PostgreSQL with two core data types — geometry and geography — along with spatial indexing via GiST and over 300 functions for spatial analysis. The geometry type operates on a flat Cartesian plane, suitable for projected coordinate systems. The geography type operates on a spheroid, giving accurate real-earth measurements in meters.

With PostGIS installed, you can store spatial data directly in PostgreSQL columns, create GiST indexes that accelerate spatial lookups to sub-millisecond speeds, and perform operations like distance calculations, containment tests, intersections, buffering, and coordinate transformations — all in SQL.

PostGIS follows the OGC Simple Features specification and implements the SQL/MM Spatial standard. It supports 2D, 3D, and 4D geometries, raster data, topology, and geocoding. The extension ships with the spatial_ref_sys table containing over 8,000 coordinate reference system definitions, including the ubiquitous SRID 4326 (WGS 84) used by GPS and most web mapping services.

When to use PostGIS

PostGIS is the right tool whenever your data has a spatial dimension:

• Location-based search — finding restaurants, stores, or drivers near a given point
• Geofencing — testing whether a coordinate falls inside a delivery zone, city boundary, or restricted area
• Route and distance calculations — measuring real-world distances between locations
• Spatial analytics — aggregating data by region, computing coverage areas, detecting overlaps between zones
• Map data storage — storing and serving polygons, linestrings, and point layers for web mapping applications
• GIS data pipelines — importing shapefiles, GeoJSON, and raster data for processing alongside business data in the same database

Installation and setup

PostGIS is a third-party extension — it is not included in the core PostgreSQL distribution and must be installed separately. On most Linux distributions, the package is named postgresql-17-postgis-3 (adjust the version numbers to match your PostgreSQL and PostGIS versions). On macOS with Homebrew, brew install postgis handles everything.

-- Install the system package first (Debian/Ubuntu)
-- sudo apt install postgresql-17-postgis-3

-- Then enable PostGIS in your database
CREATE EXTENSION postgis;

-- Verify the installation
SELECT PostGIS_Full_Version();

Unlike pg_stat_statements, PostGIS does not require shared_preload_libraries or a server restart. A single CREATE EXTENSION and the entire spatial toolkit is at your disposal — types, functions, and the spatial_ref_sys reference table with over 8,000 coordinate systems. No restarts, no downtime, no fuss.

On managed cloud platforms, PostGIS is typically pre-installed — you only need to run CREATE EXTENSION postgis to enable it.

Core types: geometry and geography

Understanding the difference between geometry and geography is the most important concept in PostGIS. Get this wrong, and you will spend an uncomfortable afternoon wondering why your "meters" are in degrees.

The geometry type stores coordinates on a flat plane. Calculations are fast because they use simple Cartesian math, but distances and areas are only accurate when using an appropriate projected coordinate system. Storing WGS 84 lon/lat data as geometry means distances will be in degrees, not meters — a common source of confusion.

The geography type stores coordinates on a spheroid. Distances are always returned in meters, and calculations account for Earth's curvature. This is more accurate for real-world measurements but supports a smaller subset of PostGIS functions and is slower for complex operations.

-- Geography type: uses real-earth spheroidal calculations
CREATE TABLE airports (
  code char(3) PRIMARY KEY,
  location geography(Point, 4326)
);

-- Insert a point (geography accepts lon/lat directly)
INSERT INTO airports (code, location)
VALUES ('LHR', ST_MakePoint(-0.4543, 51.4700)::geography);

-- Distance is in meters — no cast needed
SELECT a.code, b.code,
  ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM airports a, airports b
WHERE a.code = 'LHR' AND b.code = 'JFK';

A practical approach: store data as geometry(Point, 4326) and cast to geography when you need accurate distance calculations. This gives you access to the full function library while still getting correct metric results when it matters.

Spatial indexing with GiST

Spatial queries on large tables are expensive without an index. PostGIS uses GiST (Generalized Search Tree) indexes to accelerate spatial predicates. A GiST index stores bounding boxes for each geometry, allowing PostgreSQL to quickly eliminate rows that cannot possibly match before computing exact spatial relationships.

-- Create a table with a geometry column (planar, SRID 4326)
CREATE TABLE restaurants (
  id serial PRIMARY KEY,
  name text NOT NULL,
  location geometry(Point, 4326)
);

-- Insert a point using longitude, latitude
INSERT INTO restaurants (name, location)
VALUES ('The Ivy', ST_SetSRID(ST_MakePoint(-0.1339, 51.5114), 4326));

-- Create a GiST index for spatial queries
CREATE INDEX idx_restaurants_location
  ON restaurants USING GiST (location);

Most PostGIS functions that accept two geometries — ST_DWithin, ST_Contains, ST_Intersects, ST_Within — automatically use the GiST index when one is available. The index narrows candidates using bounding box overlap, then the function refines results with exact geometry calculations.

Key functions

ST_DWithin — find features within a distance

The most important function for "find nearby" queries. ST_DWithin returns true if two geometries are within a specified distance, and it uses the spatial index. Always prefer ST_DWithin over filtering on ST_Distance — the latter cannot use the index and forces a sequential scan, which is the spatial equivalent of searching the entire estate when you know the item is in the west wing.

-- Find all restaurants within 500 meters of a point
-- ST_DWithin uses the spatial index automatically
SELECT name, ST_Distance(
  location::geography,
  ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326)::geography
) AS distance_m
FROM restaurants
WHERE ST_DWithin(
  location::geography,
  ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326)::geography,
  500  -- meters, because geography type uses meters
)
ORDER BY distance_m;

ST_Contains — test containment

Returns true if the first geometry completely contains the second. Useful for point-in-polygon queries like "which delivery zone is this address in?" or "which country contains this coordinate?"

-- Find all parks that contain a given point
SELECT name
FROM parks
WHERE ST_Contains(
  boundary,
  ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326)
);

ST_Transform — reproject coordinates

Converts geometries between coordinate reference systems. Use this when you need to compute areas or distances in a projected system (meters) or when integrating data from different sources that use different SRIDs.

-- Transform coordinates from WGS 84 (4326) to Web Mercator (3857)
SELECT ST_Transform(location, 3857) AS location_mercator
FROM restaurants;

-- Calculate area in square meters using a projected CRS
SELECT name,
  ST_Area(ST_Transform(boundary, 3857)) AS area_sq_m
FROM parks;

Cloud availability

How Gold Lapel relates

Spatial queries are among the most expensive operations I encounter in production systems. A single ST_DWithin against a table with millions of geometries will tax even well-indexed hardware — and applications that run these queries on every request tend to discover this under load, which is never the ideal time for discovery.

Gold Lapel attends to this in two ways. First, I identify expensive spatial query patterns and recommend materialized views that pre-compute the results. A query that joins live orders against delivery zones on every request can be replaced with a materialized view that refreshes on a schedule — turning a multi-second spatial join into a simple lookup. The spatial computation still happens; it simply happens once, on your terms, rather than thousands of times at your users' expense.

Second, I detect spatial queries that would benefit from a GiST index but do not have one. This is more common than you might expect. Developers create tables with geometry columns, populate them, write queries — and forget the corresponding CREATE INDEX ... USING GiST statement. An understandable oversight that produces decidedly unpleasant sequential scans.