PostGIS Spatial SQL Guide & Performance Cheat Sheet

1. What is PostGIS?

PostGIS is an open-source spatial database extender for PostgreSQL. It adds support for geographic objects allowing location queries to be run in SQL. Conforming to the OGC Simple Features for SQL specification, PostGIS provides hundreds of spatial functions for analysis, measurement, transformation, and indexing.

2. Geometry vs Geography Data Types

PostGIS offers two distinct spatial column types:

Feature	geometry	geography
Model	Euclidean planar surface	Spheroidal / geodetic surface (WGS 84)
Distance Units	Units of the projection (degrees or meters)	Always physical meters
Computation Speed	Blazing fast (simple Cartesian math)	Slower (complex spherical trigonometry)
Antimeridian Wrap	Coordinates do not wrap at ±180°	Naturally handles crossing ±180° longitude
Recommendation	Best for regional, city, or projected data	Best for global flight paths, maritime tracks, worldwide points

3. Creating Tables & Inserting Geometries

-- Enable PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create spatial table with explicit SRID and Geometry Type
CREATE TABLE delivery_zones (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    geom GEOMETRY(POLYGON, 4326) NOT NULL
);

-- Insert from WKT using ST_GeomFromText
INSERT INTO delivery_zones (name, geom)
VALUES (
    'Downtown Core',
    ST_GeomFromText('POLYGON((-74.01 40.71, -73.99 40.71, -73.99 40.73, -74.01 40.73, -74.01 40.71))', 4326)
);

4. Spatial Indexing: GiST vs SP-GiST vs BRIN

A spatial query without an index forces PostgreSQL to read every single row in the table (sequential scan). For spatial tables larger than a few thousand rows, a spatial index is critical:

-- Standard 2D GiST Index (Recommended for 95% of use cases)
CREATE INDEX idx_delivery_zones_geom ON delivery_zones USING GIST (geom);

-- Cluster table by spatial order for maximum read cache locality
CLUSTER delivery_zones USING idx_delivery_zones_geom;

5. Essential Spatial Predicates & Operators

1. ST_Intersects

Returns true if two geometries share any portion of space (boundary or interior):

SELECT z.name 
FROM delivery_zones z
WHERE ST_Intersects(z.geom, ST_SetSRID(ST_MakePoint(-74.00, 40.72), 4326));

2. ST_Contains & ST_Within

ST_Contains(A, B) is true if no points of B lie in the exterior of A and at least one point of B lies in the interior of A:

-- Find all stores contained within a specific zone
SELECT s.store_name
FROM stores s, delivery_zones z
WHERE z.id = 1 AND ST_Contains(z.geom, s.geom);

3. ST_DWithin (Proximity / Radius Search)

Tests whether two geometries are within a specified distance of each other. Always use ST_DWithin instead of ST_Distance < X because ST_DWithin uses the spatial index!

-- Find all points within 5,000 meters of a GPS coordinate using geography
SELECT id, title
FROM venues
WHERE ST_DWithin(
    geom::geography,
    ST_MakePoint(-73.985, 40.748)::geography,
    5000 -- meters
);

6. Distance, Area, Buffering & Intersections

1. Calculating Area in Square Meters

-- Cast geometry to geography to calculate geodesic area in square meters:
SELECT name, ST_Area(geom::geography) AS area_sq_meters
FROM delivery_zones;

2. Creating a Buffer

-- Expand a route linestring by 100 meters
SELECT ST_Buffer(geom::geography, 100)::geometry AS corridor_geom
FROM bus_routes
WHERE route_id = 42;

3. Geometric Difference & Union

-- Merge all polygons in a city into a single multipolygon
SELECT ST_Union(geom) AS merged_boundary
FROM delivery_zones;

7. Outputting WKT, GeoJSON & Vector Tiles

-- Output as WKT
SELECT ST_AsText(geom) FROM delivery_zones LIMIT 1;

-- Output as standard GeoJSON feature
SELECT json_build_object(
    'type', 'Feature',
    'id', id,
    'geometry', ST_AsGeoJSON(geom)::json,
    'properties', json_build_object('name', name)
) AS feature
FROM delivery_zones;

8. Spatial Query Optimization Checklist

1. Always create a GiST index: Run CREATE INDEX ... USING GIST(geom);.
2. Run VACUUM ANALYZE: PostgreSQL's query planner needs accurate spatial statistics to estimate selectivity: VACUUM ANALYZE my_spatial_table;.
3. Use ST_DWithin, never ST_Distance in WHERE: ST_Distance(a, b) < 100 does not leverage indexes, whereas ST_DWithin(a, b, 100) is index-accelerated.
4. Simplify complex geometries for display: For web rendering at low zoom levels, use ST_SimplifyPreserveTopology(geom, 0.001) to reduce coordinate payload sizes by 90%.
5. Validate geometries before inserting: Filter or clean invalid geometries using ST_MakeValid(geom).