DuckDB Spatial Analytical SQL for GIS

Handling Null and Empty Geometries

A null geometry and an empty geometry are different things, they behave differently in every predicate, and neither of them is invalid — which is why a validity gate lets both straight through. This walkthrough sits under geometry validity and repair and isolates the third state: where nulls and empties come from, what each one does to a join, an aggregate and an index, and how to decide their fate explicitly rather than discovering it in a total that will not reconcile.

Root-Cause Analysis: three states, not two

Most code is written as though geometry is either present or invalid. There are three states, and the two that are not “present” behave in opposite ways.

  • NULL geometry. The column has no value at all. Every predicate involving it evaluates to NULL, which in a WHERE clause is not true, so the row silently fails every filter. In an inner join it disappears; in an aggregate it is skipped by count(geom) but counted by count(*).
  • EMPTY geometry. The column has a value — a well-formed POLYGON EMPTY or GEOMETRYCOLLECTION EMPTY — that describes no space. It is valid: ST_IsValid returns true. Predicates against it return false rather than null, so the row survives a WHERE NOT ST_Intersects(...) that a null row would fail.
  • Present geometry. Everything else, valid or not.

The two sources are equally common and quite different. Nulls arrive from left joins, from optional fields in a source format, and from failed conversions where a parse returned nothing. Empties arrive from operations: an intersection of two shapes that do not touch, a buffer with a negative distance larger than the shape, a difference that removed everything. An empty is usually a correct answer to a question, which is exactly why it is easy to propagate without noticing.

Null, empty and present geometry across five operations A null propagates nullness and fails every filter; an empty is valid, returns false from predicates and zero from area; a present geometry behaves normally. OPERATION NULL EMPTY PRESENT ST_IsValid NULL true true or false ST_Intersects NULL — fails every filter false a real answer ST_Area NULL 0 a real area inner join on a predicate row disappears row disappears kept if it matches R-tree index skipped entirely degenerate bbox indexed normally

The two middle columns disagree on four of the five rows. Treating them as one state is where the bugs come from.

Deterministic Configuration

Neither state needs special session configuration, but both need a decision recorded before the data is loaded, because the decision changes what the load produces.

INSTALL spatial; LOAD spatial;
SET memory_limit = '4GB';
SET threads = 8;

Optimized Execution Pattern

The pattern is to make both states explicit at ingest and never let them travel as a surprise. That means counting them, labelling them, and choosing a representation — rather than filtering them out, which loses the row, or ignoring them, which loses the count.

-- ANTI-PATTERN: the null rows are gone and nobody knows how many there were.
CREATE TABLE parcels AS
SELECT * FROM staging WHERE geom IS NOT NULL;
-- PATTERN: label the state, keep the row, and let downstream queries filter
-- on an explicit column rather than on the absence of a value.
CREATE OR REPLACE TABLE parcels AS
SELECT
    parcel_id, zone_id, geom,
    CASE
        WHEN geom IS NULL       THEN 'null'
        WHEN ST_IsEmpty(geom)   THEN 'empty'
        WHEN NOT ST_IsValid(geom) THEN 'invalid'
        ELSE 'ok'
    END AS geom_state
FROM staging;

-- Every downstream query then says what it wants, out loud.
SELECT zone_id, count(*) AS n, sum(ST_Area(geom)) AS area
FROM parcels
WHERE geom_state = 'ok'
GROUP BY zone_id;

The gain is not that the filter is shorter — it is that geom_state = 'ok' is a statement about intent that survives code review, whereas geom IS NOT NULL AND NOT ST_IsEmpty(geom) AND ST_IsValid(geom) is a condition three people will each edit differently and one of them will drop a clause from.

Diagnostic Queries & Plan Validation

Nulls and empties are cheap to count and the count is the whole diagnostic. What matters is running it at the right moments: after ingest, and after any operation that can produce an empty.

-- The three-state census. Run it after ingest and after every overlay.
SELECT
    count(*)                                     AS rows_total,
    count(*) FILTER (WHERE geom IS NULL)         AS rows_null,
    count(*) FILTER (WHERE ST_IsEmpty(geom))     AS rows_empty,
    count(*) FILTER (WHERE geom IS NOT NULL AND NOT ST_IsEmpty(geom)) AS rows_present
FROM parcels;

Diagnostic — empties produced by an operation: an intersection that returns no rows and an intersection that returns rows containing empty geometry look identical in a row count and are completely different findings. The first means nothing overlapped; the second means something overlapped in a way that produced no area, which is usually a precision problem.

-- After an overlay: how many results are empty rather than absent?
-- A high proportion points at a precision or a snapping problem, not at
-- genuinely disjoint inputs.
SELECT
    count(*)                                  AS result_rows,
    count(*) FILTER (WHERE ST_IsEmpty(geom))  AS empty_results,
    count(*) FILTER (WHERE ST_IsEmpty(geom))::DOUBLE / nullif(count(*), 0) AS empty_share
FROM (SELECT ST_Intersection(a.geom, b.geom) AS geom
      FROM zones a JOIN parcels b ON a.geom && b.geom);
Three policies for null geometry, and what each costs Rejecting nulls loses the attribute row; labelling them keeps everything at the cost of a column and discipline; substituting an empty conflates missing with zero-area. reject at ingest the column is guaranteed present downstream queries stay simple loses the attribute row which may be the only place that record exists keep, with a state column row and attributes preserved queries must say what they want the default worth defending costs one column and the discipline of using it substitute an empty makes the column non-nullable simplifies some code conflates two meanings “no boundary recorded” becomes indistinguishable from “no area” The third option is tempting because it removes a null check. It removes the null check by removing the information the check was for. Five operations that produce empty geometry, all of them correctly Boundary-only intersections, over-large negative buffers, exhaustive differences, clips outside the mask, and precision reduction of a narrow sliver. OPERATION WHY IT PRODUCES AN EMPTY IS IT A DEFECT? intersection along a shared edge the overlap has zero area no — a precision signal negative buffer larger than the shape the shape erodes to nothing no — check the distance difference that removes everything nothing of the input is left no — a true answer clip outside the mask the feature falls entirely outside no — a true answer precision reduction of a sliver narrower than the grid depends on the tolerance

Every row is a correct answer. Counting them after an overlay is more useful than trying to prevent them.

Geometry Validation & Fallback Routing

The guard that matters most is on aggregates, because that is where the two states diverge most visibly and least noisily. count(*) counts every row including nulls; count(geom) counts only rows where the geometry is present; sum(ST_Area(geom)) skips nulls and adds zero for empties. Three plausible counts of “how many features are in this zone” that disagree, all correct, all silent.

-- State the intent in the aggregate rather than relying on the default.
SELECT
    zone_id,
    count(*)                                          AS records,        -- every row
    count(*) FILTER (WHERE geom_state = 'ok')         AS with_geometry,  -- usable rows
    count(*) FILTER (WHERE geom_state = 'empty')      AS zero_area,      -- present, no space
    sum(ST_Area(geom)) FILTER (WHERE geom_state = 'ok') AS total_area
FROM parcels
GROUP BY zone_id;

For a join, the guard is to decide whether a row with no usable geometry should survive. An inner join drops it either way; a left join keeps it with nulls on the right, which is usually what a record-keeping query wants and never what a spatial analysis wants. Choosing deliberately, per query, is the whole of the discipline.

Why the distinction survives into the output

It is tempting to treat the three states as an internal concern that can be flattened before anything leaves the pipeline. It cannot, because the consumer usually needs to distinguish them too. A map that draws nothing for a feature has to know whether the feature has no boundary recorded, or has a boundary that encloses no area, or was never in the extract — three different things that look identical once the state is discarded.

The practical form is to carry the state column through to the output and to name the three counts in whatever summary accompanies it. A row count on its own invites the reader to assume every row is drawable, and the moment two counts of “how many parcels are in this zone” disagree by a handful, the reconciliation costs more than carrying the column ever did.

-- The summary that travels with an extract. Three numbers rather than one,
-- because the consumer will otherwise derive their own and get it wrong.
SELECT
    count(*)                                     AS records_extracted,
    count(*) FILTER (WHERE geom_state = 'ok')    AS drawable,
    count(*) FILTER (WHERE geom_state <> 'ok')   AS not_drawable,
    string_agg(DISTINCT geom_state, ', ')        AS states_present
FROM parcels_export;

Frequently Asked Questions

Is an empty geometry invalid?

No — it is valid, and ST_IsValid returns true for it. That is precisely what makes it slip past a validity gate. An empty geometry is a well-formed value that describes no space, which is a legitimate result of an intersection between disjoint shapes and a legitimate thing to store.

What is the difference between NULL and EMPTY in practice?

A null propagates nullness: every predicate involving it returns null, which is not true, so the row fails every filter including a negated one. An empty propagates falseness: predicates return false, area returns zero, and the row behaves like a real feature that happens to match nothing. They need different guards, and code that checks only for null misses the other half.

Should I convert nulls to empty geometries?

Usually not. It removes a null check by removing the information the check existed for — “no boundary was ever recorded” and “this feature has no area” become the same value, and no later query can tell them apart. Keep them distinct and make the distinction explicit with a state column.

Why does my overlay produce empty geometries rather than no rows?

Because the inputs overlapped by an amount that rounds to nothing. That is usually a precision signal rather than a topology one: two shapes sharing an edge produce a zero-area intersection along it. Snapping both inputs to a common precision grid before the overlay eliminates most of these, and counting the empty share afterwards tells you whether the remainder is worth investigating.

Up: Geometry Validity and Repair