Handling CRS Loss in DuckDB Spatial Extracts

An analyst runs a proximity query against last week’s extract to count incidents within 500 metres of each shelter, gets a plausible-looking table, and puts it in the after-action briefing. The extract’s geometries are in EPSG:4326, the query assumed metres, and every “within 500 metres” is really within 500 degrees. The numbers are wrong by a factor no reviewer will spot, because the output is a count and counts always look reasonable.

Root Cause and Operational Impact

A PostGIS geometry column carries its SRID as part of the column type, which is why the operational store can refuse a mismatched insert. Nothing downstream of that column has the same guarantee, and the standard export path drops it at the first step.

Where the SRID is lost between PostGIS and a Parquet extract A geometry travelling from PostGIS to a Parquet file read by DuckDB passes through four representations. In the PostGIS column the SRID is enforced by the column type itself. Converting to well-known binary drops it, because plain WKB has no field for a coordinate reference system. Extended WKB does carry an SRID, but not every writer emits it and not every reader honours it. In Parquet the SRID exists only if something wrote it as key-value metadata or as a separate column. In DuckDB the geometry arrives with no CRS at all, and every spatial predicate then operates on raw numbers. The loss is silent at each step: the geometry is well-formed throughout and no operation fails. the geometry stays well-formed the whole way — only its frame disappears PostGIS column ST_AsBinary → WKB Parquet DuckDB GEOMETRY SRID enforced by the column type no SRID field exists in plain WKB at all SRID present only if written as metadata no CRS attached — predicates use raw numbers safe lost here recoverable if written too late why nothing errors A geometry with no CRS is still a valid geometry. ST_Intersects compares coordinate numbers, and two layers in different systems produce a confident answer about points that are nowhere near each other — the same failure the unconstrained PostGIS column produces, minus the mixed-SRID error that would at least have raised.

Plain well-known binary has no field for a coordinate reference system. Extended WKB does, but it is not what ST_AsBinary produces, not every reader honours it, and it does not survive a round trip through most columnar writers. By the time the geometry reaches DuckDB it is a well-formed shape with no frame, and every spatial function operates on the raw numbers.

That is the whole danger: nothing errors. A geometry without a CRS is still a valid geometry, ST_Intersects still returns booleans, and ST_Distance still returns floats. The engine cannot tell that it is comparing degrees with metres, so it produces confident answers to questions nobody asked.

A distance query answered in three coordinate systems from the same numbers The same pair of coordinates, 1,200 units apart, interpreted under three assumptions. Read as EPSG 4326 degrees the separation is about 133 kilometres. Read as UTM metres it is 1.2 kilometres. Read as State Plane survey feet it is about 366 metres. All three are arithmetically correct answers to ST_Distance and only one corresponds to the ground. Because no engine can choose between them without a declared coordinate reference system, an extract that lost its SRID does not fail — it silently selects whichever interpretation the query author happened to assume, and reports it with full confidence. the same 1 200 units, three defensible answers read as EPSG:4326 read as UTM metres read as survey feet 133 km 1.2 km 366 m degrees of separation the correct answer here State Plane units a county away on the same street the next building No engine can choose between these without a declared CRS, so an extract that lost its SRID does not fail — it silently adopts whichever interpretation the query author assumed, and reports it with full confidence. Which is why the SRID must be data in the extract, not knowledge in someone's head.

Tiered Resolution Strategy

  1. Carry the SRID as a column, not only as metadata (definitive). A dedicated srid column is data: no rewrite, repartition or copy can silently drop it, and a mixed-SRID extract becomes detectable with a single SELECT DISTINCT.
  2. Write it as Parquet key-value metadata as well. Redundant on purpose. Metadata is readable before scanning a row, which lets a loader reject an extract without reading it, and it survives the case where a consumer selects a subset of columns.
  3. Reapply the CRS at load time and refuse without it. The loader should attach the SRID explicitly and raise when neither carrier is present, rather than proceeding with an unframed geometry.
  4. Reproject at extract time for distance work (safe default). If the analytical workload measures distances or areas, write the extract in the incident’s projected CRS rather than in EPSG:4326, so a query that forgets to reproject is still measuring metres. This does not remove the need for the declaration; it removes the most common consequence of ignoring it.
  5. Assert single-SRID on every extract. A mixed-SRID extract means the operational column constraint has been relaxed upstream, which is a defect worth surfacing at the extract rather than at the query.
Three places to carry the SRID through an extract, and what each survives Three ways to keep a coordinate reference system attached to an extract. A Parquet key-value metadata entry travels with the file, survives copying and is readable before any row is scanned, but it is easy to omit and some tools drop unknown keys when rewriting. A dedicated SRID column is impossible to lose because it is data rather than metadata, costs a few bytes per row after compression, and makes a mixed-SRID extract detectable with a single query. Encoding the CRS in the filename survives nothing: a rename, a copy into a data lake, or an automated partitioning step all discard it. The recommendation is both of the first two, because they fail in different ways. carry it in two places, because they fail differently Parquet key-value metadata + travels with the file · readable before scanning a single row · costs nothing per row − easy to omit at write time · some rewriting tools drop unknown keys a dedicated srid column + it is data, not metadata — no rewrite can silently drop it · a mixed-SRID extract is one query away − a few bytes per row before compression, and effectively nothing after it the filename − survives nothing: a rename, a copy into a lake, or an automated partitioning step all discard it and the file remains perfectly readable afterwards, which is what makes it a trap rather than a limitation

The recommendation to use both carriers is not belt-and-braces for its own sake — they fail in genuinely different ways. Key-value metadata is easy to omit at write time and is dropped by some tools that rewrite Parquet files, but it is free per row and readable without a scan. A column cannot be dropped by a rewrite, but it is invisible to a loader that has not read any rows yet. Carrying both means a defect in either path is caught by the other.

Production Python Implementation

python 
from __future__ import annotations

import logging
from pathlib import Path

import duckdb

logger = logging.getLogger("incidentgis.duckdb_crs")

METADATA_KEY = "incidentgis_srid"


class MissingCRSError(RuntimeError):
    """Raised when an extract cannot state its coordinate reference system."""


def load_extract(path: Path, *, expected_srid: int) -> duckdb.DuckDBPyConnection:
    """Open a Parquet extract with its CRS verified from both carriers.

    Refuses to return a usable connection unless the extract states its SRID
    and that SRID is the one the caller expected. An unframed geometry is not
    a degraded input — it is an input that produces confident wrong answers.
    """
    con = duckdb.connect()
    con.execute("INSTALL spatial; LOAD spatial;")

    # Carrier one: file metadata, readable without scanning any rows.
    meta = con.execute(
        "SELECT value FROM parquet_kv_metadata(?) WHERE key = ?",
        [str(path), METADATA_KEY],
    ).fetchall()
    meta_srid = int(meta[0][0]) if meta else None

    # Carrier two: the srid column, which no rewrite can silently drop.
    column_srids = [
        row[0] for row in con.execute(
            "SELECT DISTINCT srid FROM read_parquet(?)", [str(path)]
        ).fetchall()
    ]

    if meta_srid is None and not column_srids:
        raise MissingCRSError(
            f"{path} states no SRID in metadata or column — refusing to guess"
        )
    if len(column_srids) > 1:
        raise MissingCRSError(
            f"{path} spans multiple SRIDs {sorted(column_srids)}; the "
            "operational column constraint has been relaxed upstream"
        )

    found = column_srids[0] if column_srids else meta_srid
    if meta_srid is not None and column_srids and meta_srid != found:
        raise MissingCRSError(
            f"{path} disagrees with itself: metadata {meta_srid}, column {found}"
        )
    if found != expected_srid:
        raise MissingCRSError(
            f"{path} is EPSG:{found}, caller expected EPSG:{expected_srid}"
        )

    # Attach the frame explicitly so every downstream predicate is measured
    # in the units the caller believes it is using.
    con.execute(
        "CREATE VIEW incidents AS "
        "SELECT * EXCLUDE (srid), ST_GeomFromWKB(geom_wkb) AS geom "
        "FROM read_parquet(?)",
        [str(path)],
    )
    logger.info("extract_loaded", extra={
        "path": str(path), "srid": found, "rows_scanned_for_srid": len(column_srids),
    })
    return con

Validation Checklist

  • Every extract writes the SRID both as a column and as Parquet key-value metadata.
  • The loader raises when neither carrier is present, rather than proceeding unframed.
  • The loader raises when the two carriers disagree.
  • A SELECT DISTINCT srid assertion runs on every extract and fails on more than one value.
  • Extracts feeding distance or area work are written in a projected CRS, not in EPSG:4326.
  • Any query returning a distance states its units in the column name, so distance_m cannot be read as degrees.
  • A smoke test loads an extract with the SRID deliberately stripped and asserts the loader refuses it.

Edge Cases and Gotchas

  • Extended WKB that looks like it worked. Some writers emit EWKB carrying an SRID, and some readers ignore the SRID while parsing the geometry successfully. The result passes a visual check and still arrives unframed. Do not rely on the geometry encoding to carry the frame.
  • A partitioned extract where only one partition has metadata. Rewriting or repartitioning frequently preserves metadata on the first file and drops it on the rest. The column carrier is what saves this case.
  • Degrees that look like metres. UTM eastings and State Plane values are large numbers, and a query written against a projected extract will produce absurd results if handed EPSG:4326 — but a query written the other way round produces small numbers that look entirely plausible. The dangerous direction is projected-expected, degrees-supplied.
  • ST_Distance on geographic coordinates in DuckDB. It returns degrees, not metres, with no warning. There is no geography type to fall back on, so the projected-extract rule in tier four is doing real work.
  • A CRS that is correct and inappropriate. An extract in EPSG:3857 carries a valid SRID and will still misreport areas by a factor of two at temperate latitudes, exactly as the coordinate reference system standard describes. Declaring the frame is necessary and not sufficient.

Frequently Asked Questions

Why does a PostGIS geometry lose its SRID on the way to DuckDB? Because plain well-known binary has no field for a coordinate reference system. A PostGIS column enforces its SRID as part of the column type, but ST_AsBinary produces WKB, which carries only the shape. Extended WKB does include an SRID, yet it is not what ST_AsBinary emits, not every reader honours it, and it rarely survives a round trip through a columnar writer. The geometry that arrives in DuckDB is well-formed and unframed, and every spatial function then operates on raw coordinate numbers without any way to know what they mean.

What actually goes wrong when the SRID is missing? Nothing errors, which is the problem. The same pair of coordinates 1,200 units apart is about 133 kilometres if the numbers are degrees, 1.2 kilometres if they are UTM metres, and about 366 metres if they are survey feet. All three are arithmetically correct answers to ST_Distance and only one describes the ground. Without a declared frame the engine silently adopts whichever interpretation the query author assumed and reports it with full confidence, so a proximity count in an after-action briefing can be wrong by orders of magnitude while looking entirely reasonable.

Is Parquet key-value metadata enough to carry the CRS? It is necessary and not sufficient, so carry the SRID in a column as well. Metadata is free per row and readable before any scan, which lets a loader reject a bad extract cheaply, but it is easy to omit at write time and several tools drop unknown keys when rewriting or repartitioning a file. A column cannot be dropped by a rewrite and makes a mixed-SRID extract detectable with a single query, but it is invisible until rows are read. The two carriers fail in different ways, so each catches the other’s failure.

Up: PostGIS vs DuckDB for Incident Analytics

Other guides in PostGIS vs DuckDB for Incident Analytics

Related
Benchmarking Spatial Aggregations Across Both Engines
→
Related
Migrating Incident Analytics Queries to DuckDB
→