DuckDB Spatial Analytical SQL for GIS

Running Spatial Totals with Window Frames

A running distance along a GPS trace, a cumulative area up a hierarchy, a rolling density over a sequence of cells — all three are window functions, and all three go wrong in the same three places: the partition, the ordering, and the frame. This walkthrough, part of the window functions for geospatial reference, works through each in turn on a trajectory example, because a trajectory makes every mistake visible.

Root-Cause Analysis: where a running spatial total goes wrong

  • The partition is missing. Without PARTITION BY the frame covers the whole table, so a running total meant to reset per trace runs across every trace concatenated together — producing plausible, monotonically increasing nonsense.
  • The ordering is spatial rather than temporal. A trajectory ordered by distance from an origin reorders the journey, so a path that doubles back yields a “distance travelled” nobody travelled. Time is the ordering; space is the measurement.
  • The frame is RANGE over a continuous value. RANGE finds frame boundaries by value, which on a column of distinct floats becomes a boundary search per row and turns a linear operation quadratic.
  • The ordering has ties. Equal timestamps leave the relative order unspecified, so the frame edge falls differently between runs and the result is not reproducible.
  • The geometry is re-computed inside the OVER clause. An expression in ORDER BY is evaluated during the sort comparisons rather than once per row, so a distance computed there is computed many times.

The distinguishing question is what the running total is supposed to reset on. That answer is the partition key, and getting it right removes the largest failure before any of the others matter.

Three clauses, three decisions, three silent failures PARTITION BY decides the reset, ORDER BY decides the sequence, and the frame decides which rows contribute. CLAUSE DECIDES FAILURE IF WRONG PARTITION BY what the total resets on one total across every group ORDER BY the sequence it accumulates in the journey is reordered the frame which rows contribute RANGE over floats goes quadratic

One line each, and every one of the three failures returns numbers rather than errors.

Deterministic Configuration 

INSTALL spatial; LOAD spatial;

-- The sort inside each partition is the dominant cost, so give it room and
-- somewhere to spill when one partition is large.
SET memory_limit = '6GB';
SET threads = 8;
SET temp_directory = '/var/tmp/duckdb_window';

Optimized Execution Pattern

The pattern for a running distance is lag over a temporally ordered partition, with the distance computed between the current geometry and the previous one and summed across the frame.

-- ANTI-PATTERN: no partition, spatial ordering, and the distance recomputed
-- inside the OVER clause. Three failures in four lines, none of them an error.
SELECT fix_id,
       sum(ST_Distance(geom, lag(geom) OVER (ORDER BY ST_Distance(geom, ST_Point(0,0)))))
         OVER (ORDER BY ST_Distance(geom, ST_Point(0,0))) AS running_m
FROM gps_fixes;
-- PATTERN: partition per trace, order by time with a tiebreaker, compute the
-- step distance once, then sum it over a ROWS frame.
WITH stepped AS (
    SELECT trace_id, fix_id, recorded_at, geom,
           ST_Distance(
               geom,
               lag(geom) OVER (PARTITION BY trace_id ORDER BY recorded_at, fix_id)
           ) AS step_m
    FROM gps_fixes
    WHERE ST_IsValid(geom)
)
SELECT trace_id, fix_id, recorded_at,
       coalesce(step_m, 0)                                          AS step_m,
       sum(coalesce(step_m, 0)) OVER (
           PARTITION BY trace_id ORDER BY recorded_at, fix_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )                                                            AS running_m
FROM stepped;

The fix_id in the ordering is the tiebreaker, and it is not decoration: GPS traces routinely carry several fixes with identical timestamps, and without it the frame boundary falls differently on every run.

Four running-total shapes and their frames Cumulative needs unbounded preceding; rolling needs a bounded preceding window; smoothing needs a centred frame; a partition total needs the whole partition. SHAPE FRAME NOTE cumulative from the start UNBOUNDED PRECEDING → CURRENT the default with ORDER BY rolling over the last 10 9 PRECEDING → CURRENT bounded, linear centred smoothing 5 PRECEDING → 5 FOLLOWING both directions total for the whole partition UNBOUNDED → UNBOUNDED state it, or ORDER BY truncates it

The last row is the one that surprises: adding ORDER BY silently changes a partition total into a running one.

Why the ordering must be temporal

A trajectory is a sequence of positions in time, and the distance travelled is the sum of the gaps between consecutive positions in that sequence. Ordering by anything else does not merely reorder the output; it changes the quantity being computed, because the pairs whose distances are summed are different pairs.

The failure is at its most convincing on a route that doubles back — a delivery round, a survey transect, a patrol. Ordered by distance from an origin, the two passes along the same street interleave, and the “running distance” is the sum of many small hops between points that were visited hours apart. It increases monotonically, it looks like a distance, and it corresponds to no journey.

-- The check: a trace's computed total against its own straight-line extent.
-- A total below the extent means the ordering is wrong, since a path cannot
-- be shorter than the distance between its endpoints.
SELECT trace_id,
       max(running_m)                                            AS computed_total,
       ST_Distance(first(geom ORDER BY recorded_at), last(geom ORDER BY recorded_at)) AS straight_line
FROM running_totals GROUP BY trace_id
HAVING max(running_m) < ST_Distance(first(geom ORDER BY recorded_at), last(geom ORDER BY recorded_at));

Diagnostic Queries & Plan Validation

The plan tells you which of the three clauses is costing you, because each maps to a distinct operator.

-- Expect a hash partition, a sort per partition, and a window sweep. A single
-- partition means PARTITION BY is missing or the key has one value; a sort
-- that dominates means the ordering expression is being recomputed.
EXPLAIN ANALYZE
SELECT trace_id, sum(step_m) OVER (
    PARTITION BY trace_id ORDER BY recorded_at, fix_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_m
FROM stepped;

A window that uses one core throughout is the clearest signal available: DuckDB parallelises across partitions, so single-threaded execution means one partition — either the key is missing or one trace holds nearly all the rows.

Three wrong answers and the check for each No reset means a missing partition; a total below the straight-line extent means wrong ordering; a total that changes between runs means ties. WRONG ANSWER CAUSE CHECK the total never resets no PARTITION BY max per group increases across groups total < straight-line extent wrong ordering compare per trace the total changes between runs ties in the ordering run twice, compare checksums

All three return numbers. All three checks are one query.

Keeping the sort cheap

The sort inside each partition dominates a window function, and the most common way to make it more expensive than it needs to be is to compute the ordering expression in the OVER clause. A comparison-based sort evaluates its key many times per row, so a ST_Distance(...) there is evaluated many times rather than once.

Materialising the scalar in a preceding SELECT or CTE turns a repeated geometry call into a plain numeric sort. On a trajectory ordered by a timestamp this is already the case and costs nothing; on a window ordered by a computed distance it is frequently the single largest saving available, and it does not change the result at all.

Geometry Validation & Fallback Routing

Where one partition is large enough that its sort spills continuously, the fallback is to make the partition finer rather than to raise the limit.

-- Sub-partition a long trace by day. The running total then resets daily,
-- which is usually what was wanted anyway, and each sort is bounded.
SELECT trace_id, date_trunc('day', recorded_at) AS day, fix_id,
       sum(step_m) OVER (
           PARTITION BY trace_id, date_trunc('day', recorded_at)
           ORDER BY recorded_at, fix_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_m_today
FROM stepped;

Frequently Asked Questions

Why does my running total never reset?

Because there is no PARTITION BY, so the frame covers the whole table and the total accumulates across every group concatenated together. The result is monotonically increasing and entirely plausible, which is why it survives — the check is whether the maximum per group keeps rising from one group to the next.

Should I order a trajectory by time or by distance?

By time, always. Distance ordering changes which pairs of positions are being measured between, so a route that doubles back produces a total that corresponds to no journey. A total shorter than the straight-line distance between the first and last fix is proof the ordering is wrong.

Why is ROWS better than RANGE here?

Because RANGE finds frame boundaries by comparing values, and a computed distance or a high-resolution timestamp has almost no repeated values — so the boundary search becomes per row and the operation goes quadratic. ROWS counts positions and stays linear. For spatial work ROWS is nearly always what was meant.

My results change between runs. Why?

Ties in the ordering. When several rows compare equal their relative order is unspecified, so a frame boundary falling among them lands differently each time. Adding a deterministic tiebreaker — a primary key is ideal — makes the result reproducible.

Why does my window use only one core?

DuckDB parallelises across partitions, so one core means one partition: either the key is missing, or one group holds nearly all the rows. The second case is skew, and sub-partitioning on a second key — a day, a tile — restores the parallelism and usually the sanity of the answer as well.

Can I compute the distance inside the OVER clause?

You can and you should not. A sort evaluates its ordering key many times per row, so a geometry call there is repeated rather than computed once. Materialising the scalar in a preceding CTE gives an identical result from a plain numeric sort.

Up: Window Functions for Geospatial