An agent with both a spatial database and an in-process geometry library has two ways to do almost everything, and the choice is not a matter of taste. Sending a million-row overlay into a Python process is a memory incident; round-tripping a four-feature buffer through the database is three network hops for work that takes microseconds. This topic is about routing each operation to the side that should do it.
It belongs to geospatial prompt engineering and tool routing and depends on the query construction described in prompt-to-spatial-SQL generation for everything that lands on the database side.
Foundational Principles
Move the computation to the data, not the data to the computation. A filter, a join or an aggregate over many rows belongs where the rows live. Fetching a million geometries to count them is the canonical mistake, and it is made by code that reads perfectly reasonably.
Small data in memory stays in memory. Once a few hundred features have been fetched, doing three more operations on them in process is faster and simpler than three more round trips, even when the database would be faster per operation.
The routing decision is per operation, not per pipeline. A chain that filters in the database, iterates in the process and aggregates back in the database is normal and correct, and forcing the whole chain to one side is what produces both of the failures above.
Step-by-Step Implementation Pipeline
1. Estimate the row count before deciding anything
Every routing decision starts from how many rows the operation touches, and that number is available cheaply from statistics or from a bounded count.
import logging
from dataclasses import dataclass
from typing import Literal, Optional
log = logging.getLogger("spatial_routing")
Side = Literal["database", "process"]
FETCH_CEILING = 50_000 # rows above which fetching is a memory risk
ROUND_TRIP_FLOOR = 200 # rows below which a round trip is not worth it
@dataclass(frozen=True)
class Estimate:
rows: int
exact: bool
basis: str
def estimate_rows(conn, table: str, region_wkb: bytes, cap: int = 100_000) -> Estimate:
"""A bounded count: exact when small, capped when not, never a full scan."""
sql = ("SELECT count(*) FROM (SELECT 1 FROM {table} "
"WHERE geom && ST_GeomFromEWKB(%s) LIMIT %s) t").format(table=table)
try:
with conn.cursor() as cur:
cur.execute(sql, (region_wkb, cap))
rows = int(cur.fetchone()[0])
except Exception as exc: # a failed estimate routes conservatively
log.warning("row estimate failed for %s: %s", table, exc)
return Estimate(cap, False, f"estimate failed: {exc}")
return Estimate(rows, rows < cap, "bounded count over the region")Capping the count is what keeps the estimate from becoming the expensive operation. A count that stops at a hundred thousand tells you everything the routing decision needs, and an uncapped one over a national table is exactly the query the routing was supposed to prevent.
2. Route on volume first
Volume dominates every other consideration, and the two thresholds are the whole of the common case.
def route(est: Estimate, operation: str, already_in_memory: bool,
index_available: bool) -> tuple[Side, str]:
"""Choose a side and say why. Volume decides most cases."""
if already_in_memory and est.rows <= FETCH_CEILING:
return "process", "the data is already here"
if est.rows > FETCH_CEILING:
return "database", f"{est.rows}+ rows is too many to fetch"
if est.rows < ROUND_TRIP_FLOOR and already_in_memory:
return "process", "too few rows to justify a round trip"
if not index_available and est.rows > ROUND_TRIP_FLOOR:
log.info("no spatial index on this table; the database has no advantage")
return "process", "no index available to exploit"
return "database", "the database can filter before moving data"The index check earns its place. A database’s advantage on a filtered operation comes almost entirely from the index, and against an unindexed table it is doing the same linear scan the process would do — with serialisation on top. The full decision surface is laid out in the GeoPandas and PostGIS decision matrix.
3. Push filters down, always
Whatever side the operation runs on, the filter runs in the database. This is the single highest-value rule in the topic, because it changes the volume that every subsequent decision is made against.
-- The filter is index-aware and runs before anything is fetched.
SELECT id, ST_AsBinary(geom) AS geom, use_class
FROM parcels
WHERE geom && ST_GeomFromEWKB(:region)
AND ST_Intersects(geom, ST_GeomFromEWKB(:region))
AND use_class = ANY(:classes)
LIMIT :cap;Fetching a region’s worth of rows and filtering by attribute in the process is a common and expensive shape: the attribute filter is free in the database and costs a full transfer in the application.
4. Choose the transfer format deliberately
When rows do move, the encoding matters. Binary geometry is compact and fast to parse; text is human-readable and roughly twice the size; a structured object form is larger still and easier to inspect.
def fetch_geometries(conn, sql: str, params: dict, cap: int = FETCH_CEILING):
"""Fetch as binary, stop at the cap, and report truncation rather than hiding it."""
with conn.cursor() as cur:
cur.execute(sql, {**params, "cap": cap + 1})
rows = cur.fetchall()
truncated = len(rows) > cap
if truncated:
log.info("fetch truncated at %d rows; consider routing this to the database", cap)
rows = rows[:cap]
return rows, truncatedFetching one row beyond the cap is what makes truncation detectable. Without it, a result of exactly the cap size is ambiguous between “that is all there was” and “there was more”, and the two mean completely different things for the answer.
5. Keep results where the next step needs them
An operation’s output should land on the side that will consume it. A database-side overlay whose result is immediately fetched for one small transformation has paid the transfer anyway; a process-side computation whose result is written back for a join has paid it twice.
def plan_chain(steps: list[dict], est: Estimate) -> list[tuple[str, Side, str]]:
"""Assign a side per step, preferring to stay put rather than alternate."""
plan, current: Optional[Side] = [], None
for step in steps:
side, why = route(est, step["op"], already_in_memory=(current == "process"),
index_available=step.get("indexed", True))
if current is not None and side != current:
why += "; crossing sides costs a transfer"
plan.append((step["op"], side, why))
current = side
return planCounting the crossings is a useful discipline. A five-step chain that alternates sides four times has paid four transfers, and reordering the steps so that all the database work happens together is frequently possible and always cheaper.
6. Use the process for what the database cannot express
Custom logic — a scoring function, an iterative snap, a rule that depends on external state — belongs in the process regardless of volume, and the correct response to large volume is to reduce the rows first rather than to move the logic.
def reduce_then_iterate(conn, sql: str, params: dict, predicate, cap: int = FETCH_CEILING):
"""Filter and aggregate in the database; iterate over what survives."""
rows, truncated = fetch_geometries(conn, sql, params, cap)
if truncated:
raise TooManyRows(
f"more than {cap} rows survive the filter; narrow the region or "
"express the rule as a query")
return [r for r in rows if predicate(r)]Raising rather than processing a truncated set is the important behaviour. A custom rule applied to the first fifty thousand of two hundred thousand rows produces an answer that is silently about a quarter of the data.
7. Keep both sides consistent about geometry
The two sides can disagree — about validity handling, about precision, about what an empty geometry means — and a chain that crosses sides can produce a result neither side would have produced alone.
def normalise_for_process(geom):
"""Apply the same validity and precision policy the database side uses."""
if geom is None or geom.is_empty:
return None
if not geom.is_valid:
geom = make_valid(geom)
return round_geometry(geom, PRECISION_PLACES)8. Record which side served each operation
The routing distribution is the signal that tells you whether the thresholds are right. A system routing everything to one side has thresholds that are not discriminating, which is worth knowing before someone concludes the other side is unnecessary.
def observe(op: str, side: Side, rows: int, elapsed_s: float, metrics) -> None:
metrics.increment("spatial.route", tags={"op": op, "side": side})
metrics.timing("spatial.route.seconds", elapsed_s, tags={"op": op, "side": side})
metrics.gauge("spatial.route.rows", rows, tags={"op": op, "side": side})9. Give the agent one tool, not two
The routing decision belongs in the application, not in the model. An agent offered separate database and in-process tools will choose between them on grounds that have nothing to do with row counts — phrasing, recency, whichever appeared first in the schema — and the choice will be wrong in exactly the cases where it matters.
def spatial_operation(op: str, params: dict, conn) -> dict:
"""One tool. The agent asks for an operation; the router decides where it runs."""
est = estimate_rows(conn, params["table"], params["region"])
side, why = route(est, op, already_in_memory=params.get("in_memory", False),
index_available=has_spatial_index(conn, params["table"]))
started = time.monotonic()
result = (run_in_database(op, params, conn) if side == "database"
else run_in_process(op, params, conn))
observe(op, side, est.rows, time.monotonic() - started, metrics)
return {"result": result, "side": side, "rows_considered": est.rows, "routing": why}Returning the side and the row count with the result is what keeps the routing debuggable without exposing it as a choice. The agent can mention that an answer considered forty thousand features; it cannot decide to consider them in the wrong place.
This also removes an entire category of prompt engineering. A tool schema describing two backends invites the model to reason about backends, which it cannot do usefully and which consumes context that should describe the operation.
10. Handle the case where neither side can
Some operations are too large for the process and too expensive for the database within the turn’s budget, and the honest response is neither a fetch nor a query but a job — or a narrower question.
def route_or_defer(est: Estimate, op: str, budget_s: float,
db_rate: float, enqueue) -> tuple[str, str]:
"""When both sides exceed the budget, defer rather than starting either."""
projected_s = est.rows * db_rate
if projected_s <= budget_s:
return "database", ""
if est.rows > FETCH_CEILING:
job = enqueue(op=op, rows=est.rows)
return "queued", f"{est.rows} rows projected at {projected_s:.0f}s; started job {job}"
return "process", "over the database budget but small enough to fetch"The middle branch is the one worth having explicitly. Without it a large operation runs on whichever side was chosen by the volume rule, consumes the whole budget, and fails — where deferring produces a handle and a wait the user can decide about.
Operating This Stage Over Time
Thresholds drift with the data. A fetch ceiling that was comfortable against a corpus of two million rows is a memory risk against twenty million, and nothing in the code notices — the same operation simply fetches more. Deriving the ceiling from available memory rather than from a constant, and reviewing it when the corpus grows, is what keeps the routing honest.
Index availability changes too, and usually silently. An index dropped during a migration and not recreated turns every database-side operation on that table into a scan, and the symptom is a latency regression that looks like a database problem rather than a missing index. Asserting index presence for the tables the router assumes are indexed is a cheap check that catches it immediately.
The third drift is in the operations themselves. New capabilities add steps to chains, and a chain that once ran entirely in the database acquires a process-side step in the middle, doubling its transfers. Counting side crossings per chain and reviewing the ones that grew is a more useful signal than per-operation latency, because the cost is in the pattern rather than in any one step.
Finally, watch for a routing decision that has become folklore. “We always do overlays in the database” starts as a measured conclusion and survives long after the data that justified it has changed shape. Re-measuring the crossover once a year takes an afternoon and occasionally moves it by an order of magnitude.
Failure Modes & Root Causes
The full fetch. A million geometries are pulled into memory to be counted or filtered. Root cause: an operation expressed in the application that the database could have done. Mitigation: estimate rows first; push filters down unconditionally.
The chatty chain. Six operations on a hundred features produce six round trips. Root cause: routing per operation without considering where the data already is. Mitigation: the in-memory check, and counting side crossings.
The silent truncation. A fetch caps at fifty thousand rows and the answer describes a quarter of the data. Root cause: a cap with no truncation signal. Mitigation: fetch one extra row and report.
The disagreeing sides. A geometry repaired differently in the process than in the database produces a result that neither side would have produced. Root cause: two validity policies. Mitigation: one policy, applied on both sides.
Production Validation Protocols
- Filter push-down assertion. Assert that no code path fetches rows and then applies an attribute filter that the query could have expressed.
- Truncation detection test. Assert a fetch at exactly the cap is reported as truncated, using a fixture sized to the boundary.
- Index presence check. Assert every table the router treats as indexed has a spatial index, on every deploy.
- Crossing count. Publish the number of side crossings per chain and alert when a chain’s count increases.
- Consistency test. Run a representative operation on both sides against the same input and assert the results agree within the precision policy.
- Routing distribution. Publish the split by operation and alert on a collapse to one side, which usually means a threshold has stopped discriminating.
Frequently Asked Questions
Where should the fetch ceiling sit?
Derived from available memory and typical geometry size rather than chosen as a round number. A ceiling of fifty thousand is reasonable for small parcels and far too high for detailed boundaries with thousands of vertices each. Computing it from a measured average geometry size and a memory allowance makes it adapt to the data, and makes the constant meaningful rather than traditional.
Should routing consider which side has spare capacity?
In principle yes, in practice rarely worth it. Load-aware routing is appealing and adds a dependency on metrics that may themselves be stale, and the volume-based decision is right often enough that overriding it on load mostly moves work to the side that is worse at it. Where the database is genuinely saturated, the answer is usually a queue rather than a route change.
What about operations the database supports but implements differently?
Prefer the database and verify the difference once. Buffer, simplify and overlay have subtly different implementations in different engines — different join styles, different precision models — and the differences are usually irrelevant and occasionally not. A consistency test against a representative fixture tells you which case you are in, and it needs running once per library upgrade rather than continuously.
How does this interact with the async decision?
They are independent and compose. Routing decides which side does the work; the async decision decides whether the user waits for it. A large database-side overlay can still be a queued job, and a small process-side operation is always inline. Keeping the two decisions separate avoids the tangle where a routing rule secretly encodes a latency assumption.
Is it worth supporting both sides at all?
For anything beyond a small system, yes. A database-only pipeline cannot express custom logic without pushing it into SQL where it is hard to test, and a process-only pipeline cannot handle volume. The cost of supporting both is the consistency work described above, which is real and bounded; the cost of supporting neither properly is a system that hits a wall in one direction or the other.
Related
- Up to the section overview: Geospatial Prompt Engineering and Tool Routing
- Technique: Routing LLM Calls to GeoPandas vs PostGIS Backends
- Comparison: GeoPandas vs PostGIS Decision Matrix
- Peer topic: Prompt-to-Spatial-SQL Generation
- Peer topic: LLM-Assisted Geoprocessing Pipelines
- Related topic: Cost and Latency Budgets for Spatial Agents