Geospatial Queries in SQL: Using PostGIS and Spatial Functions to Filter, Measure, and Join Location-Based Data

Introduction

Picture this: you're a data analyst at a logistics company, and your operations team wants to know which of your 50,000 delivery addresses fall within 10 kilometers of each warehouse. Or maybe you work at a retail chain and need to find all competitor locations within a given radius of your stores. Perhaps you're building a feature that surfaces nearby restaurants to a mobile user in real time. In every one of these cases, the underlying problem is the same — you have coordinates in a database, and you need to do something spatially meaningful with them.

The naive approach — converting lat/lon pairs to Cartesian distance using a haversine formula crammed into a WHERE clause — technically works, but it scales horribly, ignores the curvature of the earth inconsistently, and gives you no easy path to more sophisticated operations like polygon containment tests or spatial joins. PostGIS, the spatial extension for PostgreSQL, solves all of this at the database layer. It gives you first-class geometry types, spatial indexing, a library of hundreds of functions, and the ability to express location logic as naturally as you'd write any other SQL query.

By the end of this lesson, you'll have the practical competence to model location data correctly, write efficient radius queries, perform point-in-polygon tests, execute spatial joins between two geometry datasets, and measure distances and areas in real-world units. This is the stuff that would take a day to figure out by trial and error — let's compress it.

What you'll learn:

• How PostGIS geometry and geography types differ, and when to use each
• How to load, store, and index spatial data in PostgreSQL
• How to filter rows using spatial predicates (ST_Within, ST_DWithin, ST_Intersects)
• How to measure distances, areas, and lengths in real-world units
• How to perform spatial joins between two location datasets
• How to diagnose and optimize slow spatial queries

---

Prerequisites

You should be comfortable writing multi-table SQL joins, using aggregate functions, and understanding basic index concepts. Familiarity with Common Table Expressions will help you structure the more complex queries in this lesson. You don't need a deep GIS background — we'll cover the spatial concepts you need as they arise.

You'll need PostgreSQL 14+ with the PostGIS extension installed. If you're using a local environment, CREATE EXTENSION postgis; is all it takes after installation. Cloud databases like Amazon RDS, Google Cloud SQL, and Supabase all support PostGIS natively.

---

Understanding PostGIS Data Types: Geometry vs. Geography

Before you write a single spatial query, you need to make a fundamental decision: should your location columns use the geometry type or the geography type? This isn't just a naming convention — it determines how PostGIS interprets coordinates, how it performs calculations, and what performance characteristics you'll get.

Geometry treats the world as a flat, two-dimensional plane. Coordinates are abstract numbers — they can represent latitude and longitude, but they can also represent pixels on a screen, inches on a map, or arbitrary units. When PostGIS calculates distance with geometry, it's using Euclidean geometry: a straight line between two points in 2D space. This is fast and works well when your data is confined to a small area (like a city block) where the Earth's curvature is negligible, or when you're working in a local projected coordinate system designed to minimize distortion.

Geography treats the world as a sphere (technically a spheroid). Coordinates are always longitude/latitude in degrees, and distance calculations account for the curvature of the Earth. This means ST_Distance on geography columns returns meters — accurate meters — regardless of whether you're computing distance in New Zealand or Norway. The trade-off is that geography operations are somewhat slower and support a smaller subset of PostGIS functions.

The practical rule: use geography when your data spans large areas (multiple cities, countries, or the whole globe) and you care about physically accurate distances. Use geometry with an appropriate SRID when your data is localized and you want maximum function support or are working with local projected coordinate systems.

Key insight: Both types store coordinates, but geography answers "how far is it to walk?" while geometry answers "how far is it on this map?" For most business applications dealing with addresses, stores, and delivery zones, geography is the safer default.

A Spatial Reference Identifier (SRID) tells PostGIS which coordinate system your data lives in. For geography columns, it's almost always 4326 — the WGS 84 datum used by GPS. For geometry columns, you might use 4326 (degrees), or a local projected system like 32618 (UTM Zone 18N, covering the US East Coast) that expresses coordinates in meters.

Let's create a realistic schema to work with throughout this lesson:

-- Enable the extension (run once per database)
CREATE EXTENSION IF NOT EXISTS postgis;

-- Warehouses table: a modest number of fixed locations
CREATE TABLE warehouses (
    warehouse_id   SERIAL PRIMARY KEY,
    name           TEXT NOT NULL,
    city           TEXT NOT NULL,
    location       GEOGRAPHY(Point, 4326)  -- lon/lat, accurate distances
);

-- Delivery addresses: potentially millions of rows
CREATE TABLE delivery_addresses (
    address_id     SERIAL PRIMARY KEY,
    customer_id    INTEGER NOT NULL,
    street         TEXT NOT NULL,
    city           TEXT NOT NULL,
    location       GEOGRAPHY(Point, 4326)
);

-- Delivery zones: polygon areas assigned to each warehouse
CREATE TABLE delivery_zones (
    zone_id        SERIAL PRIMARY KEY,
    warehouse_id   INTEGER REFERENCES warehouses(warehouse_id),
    zone_name      TEXT NOT NULL,
    boundary       GEOGRAPHY(Polygon, 4326)
);

---

Loading Spatial Data

Spatial data doesn't arrive pre-packaged as PostGIS geometry. In the real world, you'll receive it as latitude/longitude columns, WKT strings, GeoJSON blobs, or shapefiles. Here's how to handle each.

From Latitude/Longitude Columns

The most common scenario: your source data has separate lat and lon columns, and you need to construct a geometry point.

-- Suppose you've loaded raw data into a staging table
CREATE TABLE staging_warehouses (
    name    TEXT,
    city    TEXT,
    lat     DOUBLE PRECISION,
    lon     DOUBLE PRECISION
);

-- Insert some realistic data
INSERT INTO staging_warehouses VALUES
    ('Midwest Distribution Center', 'Chicago',       41.8781,  -87.6298),
    ('Southeast Hub',               'Atlanta',        33.7490,  -84.3880),
    ('Pacific Northwest Facility',  'Seattle',        47.6062, -122.3321),
    ('Texas Fulfillment Center',    'Dallas',         32.7767,  -96.7970),
    ('Northeast Logistics',         'Philadelphia',   39.9526,  -75.1652);

-- Convert and insert into the warehouses table
INSERT INTO warehouses (name, city, location)
SELECT
    name,
    city,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography
FROM staging_warehouses;

Warning: Notice the argument order in ST_MakePoint(lon, lat) — it's longitude first, then latitude. This is the correct OGC standard order, but it's the single most common mistake beginners make. If your map shows everything in the ocean, this is why.

From WKT and GeoJSON

Well-Known Text (WKT) is a human-readable format for geometries. GeoJSON is the format you'll most often get from APIs and front-end mapping tools.

-- From WKT string
INSERT INTO delivery_zones (warehouse_id, zone_name, boundary)
VALUES (
    1,
    'Chicago North Zone',
    ST_GeogFromText('POLYGON((-87.8 42.1, -87.5 42.1, -87.5 41.7, -87.8 41.7, -87.8 42.1))')
);

-- From GeoJSON (common when receiving data from a mapping API)
INSERT INTO delivery_zones (warehouse_id, zone_name, boundary)
VALUES (
    2,
    'Atlanta Metro Zone',
    ST_GeogFromGeoJSON('{
        "type": "Polygon",
        "coordinates": [[
            [-84.6, 34.0], [-84.0, 34.0], [-84.0, 33.5],
            [-84.6, 33.5], [-84.6, 34.0]
        ]]
    }')
);

Creating a Spatial Index

Before you run any real queries, create a spatial index. PostGIS uses a GiST (Generalized Search Tree) index that indexes the bounding box of each geometry. Without it, every spatial query is a full table scan.

CREATE INDEX idx_warehouses_location
    ON warehouses USING GIST (location);

CREATE INDEX idx_delivery_addresses_location
    ON delivery_addresses USING GIST (location);

CREATE INDEX idx_delivery_zones_boundary
    ON delivery_zones USING GIST (boundary);

Think of the GiST index as a spatial filing system. When you ask "which addresses are within 10 km of this warehouse?", PostgreSQL first consults the index to quickly eliminate all addresses whose bounding boxes don't overlap the search area, then applies the precise spatial function to the remaining candidates. For large tables, this two-phase approach is the difference between a millisecond and a minute.

Tip: If you're using geometry instead of geography, you can also consider an SP-GiST index for point-only datasets — it can outperform GiST for pure point lookups. But GiST is the safe universal default. For a deeper dive on index mechanics, see Indexing Fundamentals for Query Performance.

---

Filtering with Spatial Predicates

Spatial predicates are the PostGIS equivalent of =, >, and LIKE — they answer yes/no questions about geometric relationships. Let's build up from the simplest case.

Radius Search with ST_DWithin

ST_DWithin(a, b, distance) returns true if geometries a and b are within distance of each other. For geography types, distance is in meters. This is your go-to function for "find everything within X km of this point."

-- Find all delivery addresses within 25 km of the Chicago warehouse
SELECT
    da.address_id,
    da.customer_id,
    da.street,
    da.city,
    ROUND(
        ST_Distance(da.location, w.location)::NUMERIC / 1000,
        2
    ) AS distance_km
FROM delivery_addresses da
CROSS JOIN warehouses w
WHERE w.name = 'Midwest Distribution Center'
  AND ST_DWithin(da.location, w.location, 25000)  -- 25,000 meters = 25 km
ORDER BY ST_Distance(da.location, w.location);

Key insight: ST_DWithin is index-aware — it uses the GiST index to quickly shortlist candidates before computing exact distances. ST_Distance alone in a WHERE clause does not use the index. Always filter with ST_DWithin first, and then use ST_Distance in SELECT for the display value.

Point-in-Polygon with ST_Within and ST_Contains

ST_Within(point, polygon) returns true if the point lies inside the polygon. ST_Contains(polygon, point) is logically equivalent but reverses the argument order. The choice between them is semantic — pick whichever reads more naturally in your query.

-- Find all delivery addresses that fall within any delivery zone
SELECT
    da.address_id,
    da.city AS customer_city,
    dz.zone_name,
    w.name AS assigned_warehouse
FROM delivery_addresses da
JOIN delivery_zones dz
    ON ST_Within(da.location::geometry, dz.boundary::geometry)
JOIN warehouses w
    ON dz.warehouse_id = w.warehouse_id;

Notice the ::geometry cast. When both sides of a spatial join are geography, PostGIS supports only a subset of predicates. Casting to geometry (they remain in SRID 4326, so results are still valid for reasonable distances) unlocks the full function library. For continental-scale data this introduces small inaccuracies; for city-scale polygons, it's fine.

Intersection Tests with ST_Intersects

ST_Intersects returns true if two geometries share any space — they can overlap, one can contain the other, or they can touch at a boundary. It's useful for detecting whether delivery zones overlap (which you'd generally want to prevent):

-- Find pairs of delivery zones that overlap (potential territory conflicts)
SELECT
    a.zone_name AS zone_a,
    b.zone_name AS zone_b,
    wa.name AS warehouse_a,
    wb.name AS warehouse_b
FROM delivery_zones a
JOIN delivery_zones b
    ON a.zone_id < b.zone_id  -- avoid duplicate pairs and self-joins
    AND ST_Intersects(a.boundary::geometry, b.boundary::geometry)
JOIN warehouses wa ON a.warehouse_id = wa.warehouse_id
JOIN warehouses wb ON b.warehouse_id = wb.warehouse_id;

The a.zone_id < b.zone_id trick is a pattern worth memorizing for self-join scenarios — it ensures each conflicting pair appears exactly once. This is similar to the advanced JOIN patterns you'd use for non-spatial self-joins.

---

Measuring Distance, Area, and Length

PostGIS measurement functions return values in meters (for geography) or in the units of your coordinate system (for geometry in SRID 4326, those would be degrees — which is rarely what you want).

Distance Between Points

-- Distance matrix: every warehouse to every other warehouse
SELECT
    a.name AS from_warehouse,
    b.name AS to_warehouse,
    ROUND(ST_Distance(a.location, b.location)::NUMERIC / 1000, 1) AS distance_km
FROM warehouses a
CROSS JOIN warehouses b
WHERE a.warehouse_id != b.warehouse_id
ORDER BY a.name, distance_km;

Area of Polygons

-- Calculate the area of each delivery zone in square kilometers
SELECT
    dz.zone_name,
    w.name AS warehouse,
    ROUND(
        (ST_Area(dz.boundary) / 1e6)::NUMERIC,   -- convert m² to km²
        2
    ) AS area_km2
FROM delivery_zones dz
JOIN warehouses w ON dz.warehouse_id = w.warehouse_id
ORDER BY area_km2 DESC;

Nearest Warehouse for Each Address

This is a classic analytical question. Without PostGIS, you'd compute distances from every address to every warehouse, then pick the minimum — an O(n×m) operation that doesn't scale. With PostGIS's ORDER BY ... LIMIT 1 pattern combined with a lateral join, you can express this cleanly:

-- Find the nearest warehouse for each delivery address
SELECT
    da.address_id,
    da.street,
    da.city,
    nearest.name AS nearest_warehouse,
    ROUND(nearest.distance_m::NUMERIC / 1000, 2) AS distance_km
FROM delivery_addresses da
CROSS JOIN LATERAL (
    SELECT
        w.name,
        ST_Distance(da.location, w.location) AS distance_m
    FROM warehouses w
    ORDER BY da.location <-> w.location
    LIMIT 1
) AS nearest;

The <-> operator is PostGIS's distance operator for index-assisted nearest-neighbor search. When used with ORDER BY ... LIMIT 1, it enables a KNN (K-Nearest Neighbor) index scan on the GiST index — enormously faster than computing all distances and sorting. The CROSS JOIN LATERAL pattern executes the subquery once per row in the outer table, giving you per-row nearest-neighbor results. This pairs naturally with the lateral joins pattern.

---

Spatial Joins: Combining Two Geometry Datasets

A spatial join is exactly like a regular SQL join, except the join condition is a spatial predicate instead of an equality check. The mechanics are the same; the planning considerations are different.

Assignment Join: Addresses to Zones

Let's solve the original problem — assigning each delivery address to its correct delivery zone:

-- Assign each delivery address to a delivery zone
-- and flag addresses that fall outside all defined zones
SELECT
    da.address_id,
    da.customer_id,
    da.street,
    da.city,
    COALESCE(dz.zone_name, 'Unassigned') AS delivery_zone,
    COALESCE(w.name, 'No Warehouse') AS assigned_warehouse
FROM delivery_addresses da
LEFT JOIN delivery_zones dz
    ON ST_Within(da.location::geometry, dz.boundary::geometry)
LEFT JOIN warehouses w
    ON dz.warehouse_id = w.warehouse_id
ORDER BY da.address_id;

Using LEFT JOIN instead of INNER JOIN ensures you see addresses that don't fall in any zone — which is operationally important. You want to know about coverage gaps, not silently drop those rows.

Enrichment Join: Attaching Zone Attributes

A common pattern is enriching a point dataset with attributes from a polygon dataset — attaching census tract data, sales territories, risk zones, or zoning classifications:

-- Suppose we have a competitor_locations table and want to know
-- which of our delivery zones each competitor operates in
WITH competitor_zone_membership AS (
    SELECT
        cl.competitor_id,
        cl.brand_name,
        cl.address,
        dz.zone_name,
        dz.warehouse_id,
        ST_Distance(cl.location, w.location) AS distance_to_our_warehouse_m
    FROM competitor_locations cl
    JOIN delivery_zones dz
        ON ST_Within(cl.location::geometry, dz.boundary::geometry)
    JOIN warehouses w ON dz.warehouse_id = w.warehouse_id
)
SELECT
    zone_name,
    COUNT(*) AS competitor_count,
    ROUND(AVG(distance_to_our_warehouse_m / 1000)::NUMERIC, 2) AS avg_distance_to_warehouse_km,
    STRING_AGG(DISTINCT brand_name, ', ') AS brands_present
FROM competitor_zone_membership
GROUP BY zone_name
ORDER BY competitor_count DESC;

This query illustrates how spatial joins compose naturally with regular aggregation. The CTE structure keeps the spatial logic separate from the aggregation, making the query easier to test and maintain.

Warning: Spatial joins on large tables without proper indexing are catastrophically slow. PostGIS needs a GiST index on both sides of a spatial join predicate to plan the query efficiently. Always verify with EXPLAIN ANALYZE that you're seeing "Index Scan using idx_..." in the plan, not "Seq Scan."

---

Working with Geometry Constructors and Transformations

Real-world spatial work often requires constructing geometries on the fly, transforming between coordinate systems, and manipulating shapes.

Constructing a Buffer (Radius Zone)

A buffer converts a point into a circle (technically a polygon approximating a circle). This is useful for creating on-the-fly service areas:

-- Create a 10 km buffer around each warehouse and count addresses within it
SELECT
    w.name AS warehouse,
    COUNT(da.address_id) AS addresses_in_range
FROM warehouses w
JOIN delivery_addresses da
    ON ST_DWithin(da.location, w.location, 10000)
GROUP BY w.name
ORDER BY addresses_in_range DESC;

If you actually need the buffer as a polygon geometry (to store it or display it):

-- Generate buffer polygons for visualization or storage
SELECT
    warehouse_id,
    name,
    ST_Buffer(location::geometry, 0.09)  -- ~10km in degrees at mid-latitudes
        AS buffer_10km_geom
FROM warehouses;

Warning: ST_Buffer on a geometry column in SRID 4326 works in degrees, not meters. A degree of longitude varies in physical length by latitude. For accurate buffers, either cast to geography first (ST_Buffer(location::geography, 10000)) or transform to a projected coordinate system with ST_Transform.

Transforming Between Coordinate Systems

-- Transform Chicago warehouse to UTM Zone 16N (SRID 32616) for meter-accurate geometry operations
SELECT
    name,
    ST_Transform(location::geometry, 32616) AS location_utm,
    ST_X(ST_Transform(location::geometry, 32616)) AS easting_m,
    ST_Y(ST_Transform(location::geometry, 32616)) AS northing_m
FROM warehouses
WHERE city = 'Chicago';

Computing the Centroid and Bounding Box of a Zone

-- Get centroid and bounding box of each delivery zone
SELECT
    zone_name,
    ST_AsText(ST_Centroid(boundary::geometry)) AS centroid_wkt,
    ST_XMin(boundary::geometry) AS min_lon,
    ST_YMin(boundary::geometry) AS min_lat,
    ST_XMax(boundary::geometry) AS max_lon,
    ST_YMax(boundary::geometry) AS max_lat
FROM delivery_zones;

---

Hands-On Exercise: Warehouse Coverage Analysis

You're a data analyst at a regional logistics company. The operations team wants a complete coverage report: for each warehouse, how many delivery addresses fall inside its assigned delivery zone, how many are within 25 km but outside the zone, and what's the average distance to its three nearest unzoned addresses (potential expansion candidates).

Build this analysis from scratch using the schema we've defined.

Step 1: Count addresses in each zone

WITH zoned_addresses AS (
    SELECT
        da.address_id,
        da.location,
        dz.zone_id,
        dz.warehouse_id
    FROM delivery_addresses da
    JOIN delivery_zones dz
        ON ST_Within(da.location::geometry, dz.boundary::geometry)
)
SELECT
    w.name AS warehouse,
    COUNT(za.address_id) AS addresses_in_zone
FROM warehouses w
LEFT JOIN zoned_addresses za ON w.warehouse_id = za.warehouse_id
GROUP BY w.name;

Step 2: Count nearby unzoned addresses

WITH zoned_address_ids AS (
    SELECT da.address_id
    FROM delivery_addresses da
    JOIN delivery_zones dz
        ON ST_Within(da.location::geometry, dz.boundary::geometry)
),
unzoned_near_warehouse AS (
    SELECT
        da.address_id,
        da.location,
        w.warehouse_id,
        w.name AS warehouse_name,
        ST_Distance(da.location, w.location) AS distance_m
    FROM delivery_addresses da
    CROSS JOIN warehouses w
    WHERE da.address_id NOT IN (SELECT address_id FROM zoned_address_ids)
      AND ST_DWithin(da.location, w.location, 25000)
)
SELECT
    warehouse_name,
    COUNT(*) AS unzoned_addresses_within_25km,
    ROUND(AVG(distance_m / 1000)::NUMERIC, 2) AS avg_distance_km
FROM unzoned_near_warehouse
GROUP BY warehouse_name
ORDER BY unzoned_addresses_within_25km DESC;

Step 3: Combine into a single report

WITH zoned AS (
    SELECT dz.warehouse_id, COUNT(*) AS in_zone_count
    FROM delivery_addresses da
    JOIN delivery_zones dz
        ON ST_Within(da.location::geometry, dz.boundary::geometry)
    GROUP BY dz.warehouse_id
),
unzoned_nearby AS (
    SELECT
        w.warehouse_id,
        COUNT(*) AS unzoned_nearby_count,
        ROUND(AVG(ST_Distance(da.location, w.location))::NUMERIC / 1000, 2) AS avg_unzoned_distance_km
    FROM delivery_addresses da
    CROSS JOIN warehouses w
    WHERE NOT EXISTS (
        SELECT 1 FROM delivery_zones dz
        WHERE ST_Within(da.location::geometry, dz.boundary::geometry)
    )
    AND ST_DWithin(da.location, w.location, 25000)
    GROUP BY w.warehouse_id
)
SELECT
    w.name AS warehouse,
    COALESCE(z.in_zone_count, 0) AS addresses_in_zone,
    COALESCE(un.unzoned_nearby_count, 0) AS unzoned_within_25km,
    COALESCE(un.avg_unzoned_distance_km, 0) AS avg_unzoned_distance_km,
    ROUND(
        100.0 * COALESCE(z.in_zone_count, 0)
        / NULLIF(COALESCE(z.in_zone_count, 0) + COALESCE(un.unzoned_nearby_count, 0), 0),
        1
    ) AS pct_coverage
FROM warehouses w
LEFT JOIN zoned z ON w.warehouse_id = z.warehouse_id
LEFT JOIN unzoned_nearby un ON w.warehouse_id = un.warehouse_id
ORDER BY pct_coverage DESC;

This final query gives the operations team a single table showing which warehouses have strong zone coverage versus which ones have a large pool of nearby addresses not yet captured in a delivery zone — exactly the information they need to decide where to expand zone boundaries.

---

Common Mistakes & Troubleshooting

Mistake 1: Swapping Latitude and Longitude

ST_MakePoint(lon, lat) — longitude is X, latitude is Y. This matches the mathematical convention of (x, y) where x is the horizontal axis. Data sources that provide lat, lon columns (most of them) require you to flip the order explicitly.

Diagnosis: Your points appear in the ocean, in Antarctica, or wildly wrong locations when visualized.

Fix: ST_MakePoint(lon_column, lat_column) — always double-check by visualizing a sample with ST_AsText.

Mistake 2: Mixing SRIDs

PostGIS won't silently let you compare geometries in different coordinate systems — it will either error or return nonsense.

-- This will fail or return wrong results:
-- ST_DWithin(point_in_4326, point_in_32616, 1000)

-- Always ensure both sides share the same SRID:
SELECT ST_DWithin(
    ST_Transform(point_a, 4326),
    ST_Transform(point_b, 4326),
    10000
);

Diagnosis: "Operation on mixed SRID geometries" errors, or distance values that are implausibly large or small.

Fix: Use ST_SRID(geom) to check the SRID of stored geometries. Use ST_Transform(geom, target_srid) to reproject.

Mistake 3: Forgetting the Spatial Index

The single biggest performance mistake. Without a GiST index, a spatial join on a million-row table will full-scan both sides.

Diagnosis: Run EXPLAIN ANALYZE on your query. If you see Seq Scan on your geometry columns, you're missing the index. For guidance on reading query plans, see Query Profiling and Statistics in SQL.

Fix:

CREATE INDEX IF NOT EXISTS idx_table_location ON table_name USING GIST (location);

Then run ANALYZE table_name; to update the planner statistics.

Mistake 4: Using ST_Distance in WHERE Instead of ST_DWithin

-- Slow: computes distance for every row, can't use the index
WHERE ST_Distance(da.location, w.location) < 10000

-- Fast: uses the GiST index via bounding box filtering
WHERE ST_DWithin(da.location, w.location, 10000)

ST_DWithin is specifically designed to be index-aware. ST_Distance in a WHERE clause is not. This is one of those SQL anti-patterns that's easy to write and painful to debug at scale.

Mistake 5: Performing Spatial Operations on Unindexed Subqueries

-- Problematic: the subquery materializes all addresses before spatial filter
SELECT * FROM (
    SELECT *, 'extra_column' AS tag FROM delivery_addresses
) subq
WHERE ST_DWithin(subq.location, $1::geography, 10000);

If the planner can't push the spatial predicate through to the base table's index, it materializes the full subquery first. Use CTEs carefully — in older PostgreSQL versions, CTEs are always optimization fences. Prefer inline subqueries or rewrite with direct table references when possible.

Mistake 6: Calling ST_Buffer in Degrees on Geography Data

As covered earlier, ST_Buffer on geometry in SRID 4326 operates in degrees. One degree of latitude ≈ 111 km, but one degree of longitude varies by latitude (from 111 km at the equator to 0 at the poles).

Fix: Cast to geography before buffering, or use ST_Transform to project into a meter-based system first.

-- Correct: 5000 meter buffer on geography
ST_Buffer(location::geography, 5000)::geometry

-- Correct: transform to UTM, buffer in meters, transform back
ST_Transform(
    ST_Buffer(ST_Transform(location::geometry, 32618), 5000),
    4326
)

---

Performance Considerations at Scale

When your spatial tables grow into the tens or hundreds of millions of rows, a few additional strategies become important.

Partition by geography. If your data is global but your queries are almost always regional, consider table partitioning by country or state code. PostgreSQL can then prune irrelevant partitions before even consulting spatial indexes.

Cluster the table on the spatial index. CLUSTER delivery_addresses USING idx_delivery_addresses_location; physically reorders table rows to match the GiST index. Spatially nearby rows end up on the same or adjacent disk pages, which dramatically improves performance for region-based queries that touch many rows in the same area.

Materialize expensive spatial joins. If you're repeatedly joining delivery addresses to zones and the zone boundaries change infrequently, consider materializing the assignment as a regular column. A nightly job computes the spatial join and writes zone_id back to the addresses table. Subsequent queries filter on zone_id directly — no spatial computation at query time. This trades storage and maintenance complexity for dramatic read performance. The materialized view approach works well here.

Use ST_Simplify on complex polygon geometries. If your delivery zone boundaries have thousands of vertices (common with shapefile imports), spatial operations against them are slower than necessary. Simplifying the geometry reduces vertex count while preserving the overall shape:

UPDATE delivery_zones
SET boundary = ST_Simplify(boundary::geometry, 0.001)::geography
WHERE ST_NPoints(boundary::geometry) > 500;

The 0.001 tolerance is in coordinate units (degrees for SRID 4326) — approximately 100 meters. Adjust based on acceptable precision.

Tip: You can also keep both a high-resolution geometry for precise operations and a simplified geometry for fast display and coarse filtering. Store them as separate columns and choose which to use based on the operation's accuracy requirements.

---

Summary & Next Steps

You now have a solid working foundation in geospatial SQL with PostGIS. Let's recap what we covered:

• Data types matter: geography for global, accurate distances; geometry for local or projected coordinate work.
• Spatial indexes are non-negotiable: always create a GiST index before running production queries. Verify with EXPLAIN ANALYZE.
• Spatial predicates are your filter tools: ST_DWithin for radius, ST_Within/ST_Contains for point-in-polygon, ST_Intersects for overlap detection.
• Measurement functions return meters on geography: use ST_Distance, ST_Area, and ST_Length directly; convert to km by dividing by 1000.
• Spatial joins compose naturally with regular SQL: use LEFT JOIN to surface coverage gaps, CTEs to separate spatial logic from aggregation, and lateral joins for per-row nearest-neighbor queries.
• Common pitfalls: swapped coordinates, SRID mismatches, ST_Distance in WHERE clauses, and ST_Buffer in degrees — all avoidable once you know to look for them.

Where to go from here:

• Explore ST_Union and ST_Difference for combining and subtracting polygon geometries — essential for territory management problems.
• Look into ST_ClusterDBSCAN for density-based spatial clustering of points directly in SQL.
• If your spatial data has a time dimension (event locations with timestamps, vehicle tracking, real-time delivery positions), combining PostGIS with the temporal query patterns in time-series analysis unlocks the full power of spatiotemporal analytics.
• For testing your spatial query logic in CI/CD pipelines, the principles in Writing Effective SQL Unit Tests apply directly — you can test spatial predicates by constructing known geometries and asserting expected containment or distance results.
• If you're building spatial logic into reusable functions, Stored Procedures and User-Defined Functions covers how to wrap PostGIS calls into composable, parameterized units.

Geospatial SQL is one of those capabilities that, once you have it, you'll wonder how you ever worked without it. The ability to ask "what's near what?" directly in the database — where the data already lives, with the indexes already built — is genuinely powerful. Go build something with it.