Window Functions for Geospatial

Geospatial workloads routinely demand sequential, context-aware computation across coordinate streams, sensor trajectories, and proximity networks — ranking the nearest neighbours per anchor, summing distance along a GPS path, or assigning each feature to a density-ranked grid cell. When the question depends on a row’s position relative to its neighbours, a plain GROUP BY collapses the detail you need and a self-join explodes the row count, so the window function becomes the primary execution primitive. This page, part of Modern Spatial SQL Query Patterns, covers how DuckDB executes spatial windows, how to keep the sort phase from spilling, and how to read the plan so a regression surfaces before it reaches production. It builds directly on the set-based, vectorized model described in the parent reference, but adds the memory and frame-specification constraints unique to windowing over geometry.

Runtime Configuration & Memory Guardrails #

A window function executes strictly after the sort phase: the planner materializes each partition, orders it by the spatial or temporal key, applies the frame boundary (ROWS, RANGE, or GROUPS), and only then evaluates the spatial expression. That ordering makes spatial windowing memory-bound and acutely sensitive to sort spills, because every spilled chunk re-serializes GEOMETRY values to and from disk. The minimal reproducible session below is the starting point for every pattern on this page.

INSTALL spatial; LOAD spatial;

-- Cap threads at physical cores: spatial windowing is memory-bound, and extra
-- threads only add contention on the geometry cache without speeding the sort.
SET threads = 8;

-- Reserve headroom for OS + Python; spilled GEOMETRY re-serialization is the
-- dominant cost, so under-committing memory is cheaper than thrashing.
SET memory_limit = '16GB';

-- Keep spill files on fast local storage; never a network mount.
SET temp_directory = '/mnt/nvme/duckdb_spill';

-- Window output rarely needs source order; releasing it frees the sort to
-- parallelize across partitions.
SET preserve_insertion_order = false;

Trade-off: Raising memory_limit keeps partitions resident and avoids spill, but a single oversized partition can still exhaust the budget regardless of the limit. If a feed has one dominant key (a fleet where one vehicle reports 90% of rows), the guardrail to reach for is partition cardinality, not more RAM — see the anti-patterns below. For the broader picture of when DuckDB chooses to spill and how the spill directory is sized, the in-memory versus on-disk storage model is the authoritative reference.

A second guardrail is what you carry through the sort. Sorting and buffering full GEOMETRY objects is far more expensive than sorting raw lon/lat doubles. Where the window only needs ordering and the geometry is reconstructed afterward, project coordinates through the sort and rebuild ST_Point in the final projection.

Primary Execution Patterns #

Three patterns cover the overwhelming majority of spatial windowing: sequential trajectory metrics (distance and speed along an ordered path), proximity ranking (top-N neighbours per anchor without a full cross-join), and density-ranked partitioning (grid-cell grouping ranked by feature count). All three share the same scan → sort → window pipeline.

Sequential trajectory metrics #

LAG over a partition ordered by timestamp gives each point its predecessor, so a single pass yields per-segment distance, cumulative path length, and a rolling average — no self-join, no procedural loop.

WITH trajectory AS (
  SELECT
    vehicle_id,
    ts,
    lon, lat,                                   -- carry raw coords through the sort
    ROW_NUMBER() OVER w_seq AS seq,
    ST_Distance(
      ST_Point(lon, lat),
      ST_Point(LAG(lon) OVER w_seq, LAG(lat) OVER w_seq)
    ) AS segment_dist_m
  FROM gps_events
  WINDOW w_seq AS (PARTITION BY vehicle_id ORDER BY ts)
)
SELECT
  vehicle_id,
  ts,
  ST_Point(lon, lat) AS geom,                   -- rebuild geometry after the sort
  segment_dist_m,
  SUM(segment_dist_m) OVER (
    PARTITION BY vehicle_id ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- running total
  ) AS cumulative_dist_m,
  AVG(segment_dist_m) OVER (
    PARTITION BY vehicle_id ORDER BY ts
    ROWS 10 PRECEDING                                   -- 11-row smoothing window
  ) AS rolling_avg_dist_m
FROM trajectory;

Note that LAG is applied to the scalar lon/lat columns rather than to an assembled GEOMETRY. This keeps the window buffer holding 8-byte doubles instead of variable-length WKB, which is the single largest lever on sort throughput for long trajectories.

Proximity ranking without a full cross-join #

Window ranking returns the k nearest features per anchor without materializing the full distance matrix. The CROSS JOIN is only safe because the ST_DWithin proximity filter bounds the candidate set before the rank is computed.

WITH ranked_proximity AS (
  SELECT
    a.id AS anchor_id,
    t.id AS target_id,
    ST_Distance(a.geom, t.geom) AS dist_m,
    RANK() OVER (
      PARTITION BY a.id ORDER BY ST_Distance(a.geom, t.geom)
    ) AS proximity_rank
  FROM anchor_points a
  CROSS JOIN target_features t
  WHERE ST_DWithin(a.geom, t.geom, 5000)   -- bound cardinality BEFORE ranking
)
SELECT * FROM ranked_proximity WHERE proximity_rank <= 3;

Without the ST_DWithin predicate the intermediate result scales as $O(N \times M)$ and exhausts memory immediately. Choose the ranking function deliberately: RANK() keeps ties (two targets at identical distance both receive rank 1), while ROW_NUMBER() forces an arbitrary tie-break. For full top-N strategies and index-aware predicate pushdown, the spatial joins and proximity filters reference covers when a join out-performs a windowed rank.

Density-ranked spatial partitioning #

Window primitives also drive lightweight spatial grouping. DuckDB has no built-in DBSCAN, so deriving a grid-cell key with integer arithmetic on the coordinates gives a fast, SQL-native partition you can rank and aggregate like any other window result.

WITH clustered_points AS (
  SELECT
    id,
    geom,
    floor(ST_X(geom) / 0.001) AS cell_x,   -- ~100 m cells in a projected CRS
    floor(ST_Y(geom) / 0.001) AS cell_y
  FROM urban_features
)
SELECT
  cell_x, cell_y,
  COUNT(*) AS feature_count,
  ST_Centroid(ST_Collect(geom)) AS group_center,
  ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS size_rank
FROM clustered_points
GROUP BY cell_x, cell_y
HAVING COUNT(*) >= 5;                       -- minimum density per cell

Because the cell size assumes the geometry is in a metric coordinate system, an unprojected lon/lat input silently produces cells that vary in real-world size with latitude — confirm the coordinate reference system before tuning thresholds. For parameter tuning and a true distance-based grouping, the DBSCAN-style spatial grouping walkthrough extends this pattern. Anchor downstream logic to the derived metrics (centroid, count) rather than the raw cell ids.

Execution Plan Validation #

Run EXPLAIN ANALYZE on the trajectory query and read the plan bottom-up. A healthy spatial window shows three nodes in order — scan, order, window — with no spill node between them.

┌─────────────────────────────┐
│      PROJECTION (vectorized) │  cumulative_dist_m, rolling_avg_dist_m
├─────────────────────────────┤
│           WINDOW            │  SUM, AVG · PARTITION vehicle_id · frame ROWS
├─────────────────────────────┤
│            ORDER            │  vehicle_id ASC, ts ASC
├─────────────────────────────┤
│         SEQ_SCAN            │  gps_events  (est: 4.0M, actual: 4.0M)
└─────────────────────────────┘

Diagnostic Boundary: ORDER and WINDOW combined should stay under 60% of total execution time. If ORDER alone exceeds 40% of estimated cost, or a Spill/temporary file node appears, the partition cardinality is too high for the memory budget. The two reliable mitigations are reducing what flows through the sort (raw coordinates, not GEOMETRY) and lowering partition cardinality (pre-aggregate timestamps, or coarsen the partition key).

Estimated-versus-actual row drift is the second signal to watch. When the scan’s estimate diverges from the actual count by more than ~30%, the optimizer is sizing the sort buffer against stale statistics, which usually precedes an unexpected spill. The detection query below reads this directly from the analyzed plan:

EXPLAIN (FORMAT JSON) ANALYZE
SELECT vehicle_id, SUM(segment_dist_m) OVER (PARTITION BY vehicle_id ORDER BY ts)
FROM trajectory;
-- Walk the JSON tree and assert no node has "name":"SPILL"; compare
-- "cardinality" (estimated) against "operator_cardinality" (actual) per node.

Performance Trade-offs #

The frame specification drives both correctness and cost. ROWS counts physical positions, RANGE counts logical value differences, and GROUPS counts distinct ordering values. The choice is not cosmetic — it changes how the engine evaluates each frame boundary.

Performance Trade-off: RANGE frames force the engine to compute the value boundary dynamically for every row, which blocks some vectorized fast-paths and adds roughly 15–20% CPU over the equivalent ROWS frame on a uniformly distributed key. Reserve RANGE for queries where a temporal or distance gap must be honoured explicitly; default to ROWS for ordinal sequencing. The named-window form keeps the same frame readable across multiple measures:

SELECT
  sensor_id, ts,
  ST_Distance(ST_Point(lon, lat), ST_Point(LAG(lon) OVER w, LAG(lat) OVER w)) AS step_dist,
  SUM(ST_Distance(ST_Point(lon, lat), ST_Point(LAG(lon) OVER w, LAG(lat) OVER w))) OVER w AS cumulative_dist
FROM sensor_stream
WINDOW w AS (PARTITION BY sensor_id ORDER BY ts ROWS UNBOUNDED PRECEDING);

A second trade-off is pre-materialization. When the anchor-to-target ratio in a proximity rank exceeds roughly 1:500, materializing the ST_DWithin-filtered subset into a temporary table before ranking cuts sort pressure during window evaluation, because the engine sorts the bounded candidate set rather than re-filtering inside the window operator. Below that ratio the extra write is not worth it. This composes naturally with vectorized aggregations: bin first, then window over the bins.

Edge Cases & Anti-Patterns #

• Sorting GEOMETRY through the window. Wrapping ST_Point(lon, lat) in the OVER clause forces the buffer to hold WKB. Fix: order and LAG on the raw lon/lat columns and rebuild the point in the final projection, as in the trajectory pattern above.
• Single dominant partition. One key holding most rows makes that partition’s sort exceed the memory budget no matter how high memory_limit is set, because a partition cannot be split across the limit. Fix: add a sub-partition key (e.g. PARTITION BY vehicle_id, date_trunc('hour', ts)) or pre-aggregate before the window.
• Unbounded CROSS JOIN in a proximity rank. Omitting ST_DWithin (or any bounding predicate) makes the candidate set $O(N \times M)$ before the rank is ever computed. Fix: always bound the join in the WHERE/ON clause; the rank runs over the survivors only.
• RANGE on a skewed ordering column. With a non-uniform key, frame-boundary scanning degrades from $O(\log N)$ toward $O(N)$ per row. Fix: use RANGE only when the ordering column is roughly uniform; otherwise switch to ROWS.
• ROW_NUMBER() where ties matter. Using ROW_NUMBER() to keep “the nearest” silently drops equidistant features. Fix: use RANK() (or DENSE_RANK()) when tied distances must all be retained.
• Grid cells on unprojected coordinates. A degree-based floor(ST_X(geom)/0.001) produces cells that shrink toward the poles. Fix: transform to a metric CRS before deriving cell keys.

Query Regression Analysis #

Spatial window queries drift quietly: a statistics change or a new dominant partition can turn an in-memory sort into a spilling one with no error, only slower wall-clock time. Capture the analyzed plan into a baseline and diff it in CI so the regression is caught before deploy.

import duckdb
import time

def profile_spatial_window(query: str, baseline_sec: float | None = None,
                           db_path: str = ":memory:") -> dict:
    con = duckdb.connect(db_path)
    con.execute("INSTALL spatial; LOAD spatial;")
    con.execute("SET threads = 8; SET memory_limit = '12GB';")

    start = time.perf_counter()
    plan = con.execute(f"EXPLAIN ANALYZE {query}").fetchall()
    elapsed = time.perf_counter() - start

    plan_text = "\n".join(row[0] for row in plan)
    spilled = ("SPILL" in plan_text.upper()) or ("temporary file" in plan_text)
    regressed = baseline_sec is not None and elapsed > 2 * baseline_sec

    return {
        "elapsed_sec": round(elapsed, 3),
        "spill_detected": spilled,
        "regressed_vs_baseline": regressed,
        "action": (
            "Cap partition size / pre-sort coordinates" if spilled
            else "Investigate plan drift" if regressed
            else "Plan optimal"
        ),
    }

Diagnostic Boundary: treat elapsed_sec > 2× baseline or spill_detected == True as a hard CI failure — both mean the sort phase has stopped fitting in memory. When this fires in production orchestration, the Python and DuckDB integration workflows cover wiring this check into a batch pipeline so a regressed window query never silently ships. Re-baseline only after a deliberate schema or volume change, never to mask a regression.

Related #

See also

• Spatial Joins & Proximity Filters — when an indexed join beats a windowed proximity rank.
• Vectorized Aggregations — bin and aggregate before windowing over the result.
• DBSCAN-style spatial grouping in DuckDB — distance-based grouping that extends the grid-cell pattern.
• In-Memory vs Disk Storage — how the spill directory is sized when a sort exceeds memory.

Up: Modern Spatial SQL Query Patterns

---

External Reference Standards: For the formal specification of the spatial predicates used above, see the OGC Simple Features Access Standard; for kernel-level frame and window semantics, the DuckDB Spatial Extension Documentation.