A field guide to geospatial lookup
Answering "which areas contain this point — and which points fall inside this area?" with plain PostgreSQL and PostGIS, fast enough to sit on the request path.
The moment polygons appear on a roadmap, most teams assume they have outgrown their database.
The feature sounds simple — which delivery zone is this address in, which service territory covers this meter, which risk areas does this parcel touch — and the polygons are anything but: thousands of vertices, holes, slivers, boundaries that follow rivers. The naive query works beautifully in the demo, then falls over at production volume. And that is usually the moment someone proposes a specialty geospatial engine, a second database, and a synchronization problem the team keeps forever.
In our experience building lookup services that sit on the request path, the specialty engine is rarely the answer. Plain PostgreSQL with PostGIS resolves point-in-polygon questions in milliseconds at production volume — but only if the design respects two things. The first is how a spatial lookup actually executes: an index phase that prunes candidates by bounding box, and an exact phase that verifies real containment. The second is quieter and bites later: boundary data does not behave like reference data. It arrives from upstream publishers on their own schedule, in sets that must go live together, and the version that answered yesterday's question still matters after today's update. The fix is to ship boundaries the way you ship software — in versioned releases, activated atomically, archived rather than deleted. Everything in this brief follows from those two disciplines.
Prune with the index. Verify with the geometry.
A spatial query is never one operation. The index can only say "possibly inside" — a bounding box is a rectangle and your polygon is not. The exact check says "actually inside," and its cost is proportional to vertex count. Fast lookups come from making the first phase prune hard and the second phase verify little.
Boundaries ship like releases
Shapes arrive in sets that go live together. Publish into a versioned release, activate it by moving a pointer, roll back by moving it back. A deploy should never ship a shape.
Two shapes of one question
"Which areas contain this point?" and "which points fall inside this area?" are the same relationship queried from both ends. Model and index for both from day one.
The write path subsidizes the read path
Areas change rarely; lookups happen constantly. Every unit of validation, simplification, and subdivision spent at write time is repaid on millions of reads.
Four moves that keep the hot path fast.
None of these are exotic. All of them are the difference between a lookup that holds its latency at production volume and one that degrades the first time a real boundary file arrives.
Pick geometry or geography once, on purpose.
PostGIS offers two column types: geometry (planar math, fast) and geography (spheroid math, correct over long distances, slower). The mistake is not choosing wrong — it is not choosing, and paying for casts on every query. For containment lookups over city- and region-sized areas, geometry in a single well-chosen SRID is almost always right; geography earns its cost when distances span continents. Decide once, encode it in the schema, and reject writes in any other reference system — a silent SRID mismatch does not error, it just returns wrong answers.
Store every shape three ways.
One logical boundary, three physical representations, each with one job. The raw shape exactly as published — the audit copy, never touched by the hot path. The prepared shape — repaired with ST_MakeValid, snapped to a consistent precision grid, and cut with ST_Subdivide into pieces with tight bounding boxes and bounded vertex counts, which is what the two-phase lookup actually queries. And the cell set — the H3-style grid cells the shape covers, which powers caching and cheap "possibly relevant?" checks without touching geometry at all. Subdividing alone is routinely the difference between tens of milliseconds and single digits; the triple is what keeps audit, lookup, and caching from compromising each other.
Scope by namespace before you scope by geometry.
In any real system the shapes belong to something — a catalog, a customer, a program — and most lookups only care about one branch of that hierarchy. Model the hierarchy as a path (ltree is the Postgres-native tool) and filter by path prefix before the spatial predicate runs: the cheapest candidate to prune is the one a b-tree already excluded. The same paths then do double duty as the permission model — read, write, and administer rights attach to prefixes of the hierarchy rather than to rows — and as the audit key, so every publish and activation logs who did what, where. From there, the spatial discipline is the familiar one: ST_Contains and ST_Intersects against the indexed column, nothing wrapped in functions, KNN via <-> when the question is "nearest," and query plans reviewed like code.
Ship boundaries like software releases.
Boundary sets must go live together — half an update is worse than none. So publishing never edits live data: each set lands in a versioned release alongside the current one, prepared and verified while the old version keeps answering. Activation is a pointer move — "current" now names the new release — which makes cutover atomic and rollback the same operation in reverse. Two refinements earn their keep at volume: on republish, shapes that have not changed are detected and only their properties updated, so versions are not churned for nothing; and superseded releases are archived rather than deleted, because "which boundary was live when this decision was made?" is a question someone eventually asks with money on the line.
Two paths, one pointer.
The four moves compose into a deliberately boring topology. On the write path, a publisher's boundary set is prepared and staged as a new release while the current one keeps answering; activation moves the pointer. On the read path, every lookup runs the same query against whatever "current" names: prune by namespace prefix, prune by bounding box, verify exact containment. The consumer never knows a cutover happened — which is the point.
The architecture is deliberately boring: one database, one stateless service, one query shape. The sophistication lives in the prepared data — which is exactly where you want it, because prepared data is testable, versionable, and cheap to get wrong in staging instead of production.
Where the database quietly betrays you.
Nothing above is hard to build. What is hard is that spatial workloads fail differently from relational ones — silently, with plausible-looking wrong answers instead of errors. Three failure classes to design against:
Invalid geometry is normal, not exceptional.
Real boundary files arrive with self-intersecting rings, duplicate vertices, and slivers thinner than a coordinate rounding error — and PostGIS functions applied to invalid geometry return undefined results, not exceptions. The discipline is a hard gate at ingest: ST_IsValid on everything, ST_MakeValid with a review step for what it changed, and a rejection path for shapes repair cannot save. A polygon that was never valid produces months of "the lookup is wrong for this one address" tickets that no amount of query tuning will fix.
The planner changes its mind at scale.
The query that used the index in staging can flip to a sequential scan in production — statistics drift, a parameter shape changes, a table crosses a size threshold, and latency multiplies by a thousand with no code change anywhere. Treat plans as observable state: capture EXPLAIN ANALYZE for the hot queries continuously, alert on the index-to-seq-scan flip and on rows-removed-by-recheck growing faster than results, and keep table statistics fresh after every bulk boundary load. The recheck ratio in particular is your early warning — it is the direct measure of move 02 eroding.
The ceiling is real — just farther than the vendors say.
A prepared, subdivided, GiST-indexed lookup on commodity Postgres carries a very large read volume, and read replicas carry it further. But a database on the request path has a connection budget, and a viral endpoint can spend it in an afternoon. Know your sequence in advance: pooling first, then replicas, then the H3-keyed cache in front of the hottest cells — and only when a measured recheck-and-latency profile says the data has outgrown the model does a specialty engine enter the conversation. Adopting one earlier buys no speed and costs you a permanent synchronization problem between two sources of spatial truth.
The judgment work around the lookup.
The lookup itself should stay deterministic — a containment check is not a place for a model's opinion. The payoff is in the work around it, where the inputs are messy and the questions arrive in prose:
Ingest triage that speaks geometry
When a boundary file fails validation, the interesting question is why — a genuinely broken ring, a precision artifact, a datum shift from the publisher. An agent that inspects the failing shape, classifies the defect, and proposes the specific repair (with a rendered before/after) turns a GIS-specialist bottleneck into a reviewable pull request.
Query plans, read continuously
The planner-flip failure mode from the ledger is a pattern-recognition job: watch the captured plans, notice the index scan that became sequential or the recheck ratio trending up, and file the diagnosis with the fix attached — refresh statistics, rewrite the predicate, re-subdivide the offending boundary. The database explains itself; the agent does the reading.
Pathological fixtures on demand
Spatial code needs test data that real files only produce occasionally: polygons with holes, slivers a millimeter wide, boundaries crossing the antimeridian, points exactly on an edge. Agents generate these edge-case fixtures exhaustively and keep the suite growing as production surfaces new defect classes — coverage a hand-built fixture set never reaches.
Spatial questions in plain language
Analysts ask spatial questions conversationally — "which territories gained addresses last quarter?" — and the schema is exactly the structured, well-described kind that natural-language-to-SQL handles well. We have shipped NL-to-SQL over governed schemas before; a spatial schema with clean area and point tables is a natural fit, with the agent writing the index-shaped predicate so the analyst does not have to know what one is.
The honest closing note: if your workload is routing, map-tile rendering, or continuous tracking of very large numbers of moving objects, this brief undersells your problem — those are genuinely different workloads with their own tools. For the far more common case — "we have areas, we have points, and the product needs to know which is in which, right now" — the boring architecture above is the one that ships, and the one still standing two years later.
Get the next one by email.
A short note from Robin when a new brief publishes — the point, and the link. No cadence promises, no funnel, one-click unsubscribe.
You'll get one confirmation email first — nothing sends until you click it.
If location features are about to get real —
If polygons just appeared in your product plans, or an existing lookup is getting slower as the boundary data gets real, the decisions in this brief are cheapest before the first production incident. An hour is usually enough to know whether your workload fits the model — and to save you a second database.