Database field guide · 32

Spatial Data and PostGIS

Coordinate systems, predicates, indexes, and spatial retrieval

Build correct and measurable spatial queries with PostGIS types, reference systems, bounding filters, exact predicates, and nearest-neighbor access.

Franck Pachot7 chaptersPostGIS · spatial SQL · GiST · geospatial retrieval

Build the mental model before choosing the mechanism.

This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.

Geometry carries a reference system

A coordinate pair has no useful distance meaning without its spatial reference system. PostGIS stores an SRID with geometry and geography values so transformations and operators can be interpreted explicitly.

Validate incoming SRIDs rather than assigning one to unknown coordinates.
Use ST_Transform to change coordinates, not ST_SetSRID.
Choose geometry or geography from the required model and scale.

Predicates express spatial relationships

Containment, intersection, overlap, and distance are different business questions. Exact topological predicates should encode the invariant instead of approximating it with latitude and longitude ranges.

State boundary behavior explicitly.
Keep invalid geometries out of trusted query paths.
Test points on edges and polygons crossing coordinate-system seams.

A spatial index narrows candidates

GiST indexes organize bounding boxes so PostgreSQL can reject most objects before evaluating an exact spatial predicate. The recheck is part of correctness because overlapping boxes do not prove that geometries intersect.

Keep an indexable operator in the predicate.
Read candidate and recheck counts in the plan.
Large or complex shapes can weaken bounding-box selectivity.

Distance depends on units and projection

Planar geometry distance uses coordinate-system units; geography models positions on the earth. A convenient global type and a suitable local projection have different accuracy and cost profiles.

Name the expected unit in APIs and tests.
Measure distortion across the application extent.
Do not compare a degree threshold with a meter requirement.

Nearest-neighbor search needs ordered access

PostGIS distance operators can drive a GiST K-nearest-neighbor scan, allowing the executor to stop after the closest candidates rather than sorting every row. Exact distance can still be retained for final display or filtering.

Use a deterministic tie-breaker.
Inspect whether ORDER BY is index-backed.
Combine spatial and semantic candidates deliberately.

Spatial retrieval can join other signals

Location may filter, rank, or contextualize lexical and vector results. Keep each score and candidate source observable before combining them so relevance failures can be attributed.

Separate mandatory geographic eligibility from ranking boosts.
Normalize or rank-fuse incomparable scores.
Evaluate quality by distance and query cohort.

Field manual

Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.

Declare and transform coordinates

Set an SRID only when it describes the existing coordinates; transform when converting them. A transformation requires suitable definitions and produces coordinates in the target system.

SELECT ST_Transform(ST_SetSRID(ST_Point($1, $2), 4326), 3857);

Build an index-assisted intersection

GiST first compares bounding boxes and the exact predicate rechecks geometry. EXPLAIN shows whether candidate reduction is effective.

CREATE INDEX region_geom_gix ON region USING gist (geom);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM region WHERE ST_Intersects(geom, $1);

Run nearest-neighbor access

The distance-ordering operator can drive a GiST KNN scan and stop early. Return exact distance separately when its units and model are part of the API.

SELECT id, ST_Distance(geom, $1) AS distance
FROM place ORDER BY geom <-> $1, id LIMIT 20;

Validate spatial input

Invalid polygons can make topology results surprising or fail operations. Reject, repair deliberately, or quarantine them rather than silently changing trusted data.

SELECT id, ST_IsValid(geom), ST_IsValidReason(geom)
FROM region WHERE NOT ST_IsValid(geom);

Source articles

Optional deep dives with the complete experiments and product-version context behind this guide.

PostGIS and AI Retrieval in Azure HorizonDBmicrosoft-techcommunity · 2026-09-29 PostGIS on YugabyteDB Alma8 (workarounds)dev.to · 2023-10-03