DuckDB Spatial Analytical SQL for GIS

Fixing Arrow Type Mismatches on Geometry Columns

An Arrow round trip preserves types exactly right up until one side has no counterpart for what the other sent, and then it widens silently — which for a geometry pipeline usually means a binary column arriving as something that no longer parses. This walkthrough, part of the Arrow interop patterns reference, covers where the mismatches happen, how to declare a schema so inference never gets the chance, and the checks that catch a widened column before it becomes a comparison that never matches.

Root-Cause Analysis: where a type quietly changes

  • Geometry crosses as a binary column with no semantics. Arrow has no geometry type, so WKB travels as binary or large_binary. Which of the two depends on the producer, and a consumer expecting one may or may not accept the other.
  • Large offsets are chosen by size, not by declaration. A column whose total bytes exceed the 32-bit offset limit becomes large_binary automatically. The same pipeline therefore produces different types on different days, depending on how much data it happened to move.
  • Nullability is inferred from the batch. A batch with no nulls can be inferred as non-nullable, and the next batch with a null then fails to match the schema. This shows up as an error on a later chunk rather than on the first.
  • Dictionary encoding appears without being asked for. A low-cardinality string column may arrive dictionary-encoded, which is a different Arrow type from a plain string even though the values are identical.
  • Registration infers rather than enforces. Registering an Arrow table into DuckDB adopts whatever the table says its types are, so a mismatch upstream becomes a table definition rather than an error.

The distinguishing question is whether a schema was declared anywhere. If every stage infers, the types are a function of the data that happened to flow through, which is why the failure appears on the second batch rather than the first.

Five silent type changes across an Arrow round trip Binary to large binary, inferred non-nullability, dictionary encoding, integer widening and timestamp unit changes. CHANGE WHEN IT HAPPENS WHAT IT COSTS binary → large_binary total bytes exceed 2 GB a strict consumer rejects it nullable → non-nullable a batch with no nulls failure on a later batch string → dictionary low cardinality a different type, same values int32 → int64 one large value the column type changes for all rows timestamp unit changes producer default differs equal instants compare unequal

Not one of these changes a value. Every one of them changes a comparison.

Deterministic Configuration 

import duckdb, pyarrow as pa

con = duckdb.connect("gis.duckdb")
con.execute("INSTALL spatial; LOAD spatial")

# Declare the schema once, at module level, and use it at every boundary.
# Inference is the enemy here: it produces a type that depends on the data.
GEOM_SCHEMA = pa.schema([
    pa.field("parcel_id", pa.int64(),        nullable=False),
    pa.field("land_use",  pa.string(),       nullable=True),
    pa.field("wkb",       pa.large_binary(), nullable=True),   # declared, not inferred
])

Optimized Execution Pattern

The pattern is to declare the schema and cast to it at each boundary, so a mismatch becomes an explicit cast rather than an implicit widening nobody notices.

# ANTI-PATTERN: everything inferred. This works, until a batch is large
# enough to trigger large_binary or contains the first null in a column.
tbl = pa.table({"parcel_id": ids, "wkb": wkbs})
con.register("staged", tbl)
con.execute("CREATE TABLE parcels AS SELECT parcel_id, ST_GeomFromWKB(wkb) FROM staged")
# PATTERN: build against the declared schema, so the types are a decision.
tbl = pa.table(
    {"parcel_id": ids, "land_use": uses, "wkb": wkbs},
    schema=GEOM_SCHEMA,
)
assert tbl.schema.equals(GEOM_SCHEMA), tbl.schema      # fail here, not three stages later

con.register("staged", tbl)
con.execute("""
    CREATE OR REPLACE TABLE parcels AS
    SELECT parcel_id, land_use, ST_GeomFromWKB(wkb) AS geom
    FROM staged
""")

The assertion is the part that earns its keep. Building against a schema still permits PyArrow to accept a compatible-but-different type in some cases, and comparing the resulting schema against the declared one is what turns a silent widening into a failure at the line that caused it.

How to resolve each mismatch Declare large_binary, declare nullable, decode dictionaries at the boundary, declare the widest integer, and state the timestamp unit. MISMATCH RESOLUTION WHY THAT DIRECTION binary vs large_binary declare large_binary accommodates any size nullability declare nullable a false claim fails later dictionary encoding decode at the boundary downstream sees one type integer width declare the widest reachable narrow now means a change later timestamp unit state it explicitly equal instants must compare equal

Every resolution is the permissive direction, because the strict one fails on a later batch.

Why the failure appears on the second batch

Inference looks at what it has been given. A first batch with no nulls in a column produces a non-nullable field; a first batch under two gigabytes produces binary rather than large_binary; a first batch of low cardinality produces a dictionary. Every one of those is a correct description of that batch and a wrong description of the stream.

The consequence is a characteristic failure shape: the pipeline works in development, works on the first chunk in production, and fails partway through the run with a schema error naming a type nobody wrote. Declaring the schema converts that into an error on the line that constructs the table, which is both earlier and vastly easier to attribute.

Diagnostic Queries & Plan Validation

The check that matters is a schema comparison rather than a value comparison, because the values are usually fine.

# Round-trip check: compare the schema out against the schema in. Row counts
# and checksums both pass while the type has changed underneath them.
out = con.execute("SELECT parcel_id, land_use, ST_AsWKB(geom) AS wkb FROM parcels").fetch_arrow_table()

for field in GEOM_SCHEMA:
    got = out.schema.field(field.name)
    assert got.type == field.type, f"{field.name}: expected {field.type}, got {got.type}"

Comparing field by field rather than comparing whole schemas gives an error that names the column, which matters when a table has forty of them and one has widened.

Three symptoms of a type mismatch A mid-run schema error means inference; an empty comparison means a type difference; a parse failure means the binary width changed. SYMPTOM POINTS AT FIX schema error partway through a run inference from the first batch declare the schema a comparison matches nothing a unit or encoding difference declare the unit; decode dictionaries geometry fails to parse after a trip binary width changed declare large_binary everywhere

All three present as data problems. All three are schema problems.

Casting rather than hoping

Where a producer is outside your control and sends whatever it sends, the fix is an explicit cast at the boundary rather than a hope that the types will line up. Table.cast against the declared schema is cheap for compatible types and raises for incompatible ones, which is exactly the behaviour you want: the compatible cases are normalised and the incompatible ones fail where they can be diagnosed.

That single line at the ingress of a pipeline removes an entire class of intermittent failure, and it is worth applying even when the producer is yours, because “the producer is ours” is a statement about today rather than about the version that will be deployed next quarter.

# Normalise whatever arrived to the declared schema. Compatible types are
# converted; incompatible ones raise here rather than three stages later.
incoming = pa.Table.from_batches(reader)          # whatever the producer sent
normalised = incoming.cast(GEOM_SCHEMA)           # raises if it genuinely cannot
con.register("staged", normalised)

Geometry Validation & Fallback Routing

Where a cast is not possible because the column genuinely holds something unexpected, fail with the value rather than with the type.

# Diagnose rather than assert: report which column and which value defeated
# the cast, so the fix is a change upstream rather than a guess.
for field in GEOM_SCHEMA:
    col = incoming.column(field.name)
    try:
        col.cast(field.type)
    except Exception as exc:
        sample = col.slice(0, 3).to_pylist()
        raise TypeError(f"{field.name}: cannot cast {col.type} → {field.type}; sample={sample}") from exc

Frequently Asked Questions

Why did my geometry column change from binary to large_binary?

Because the total bytes in the column crossed the 32-bit offset limit, and PyArrow chose the wider offset automatically. The values are identical; the Arrow type is not, so a strict consumer rejects it. Declaring large_binary everywhere removes the variability at the cost of a slightly wider offset.

Why does the failure appear halfway through a run?

Because inference described the first batch rather than the stream. A first batch with no nulls yields a non-nullable field, and the first batch containing a null then fails to match. Declaring the schema moves the error to the line that constructs the table.

Should I declare nullability strictly?

No — declare columns nullable unless a constraint genuinely holds for the whole stream. A false non-nullable claim buys nothing and fails on whichever batch first contains a null, which is usually in production rather than in a test.

What is the safest type for WKB?

large_binary, declared rather than inferred. It accommodates any column size, so the type does not change with the data volume, and the only cost is a wider offset per value — which is negligible next to the geometry itself.

Why does my comparison match nothing when the values look equal?

Almost always a type difference the values do not show: a timestamp in microseconds against one in nanoseconds, or a dictionary-encoded string against a plain one. Comparing schemas field by field rather than comparing values is what surfaces it.

Does registering an Arrow table validate anything?

No — registration adopts whatever the table says its types are, so an upstream mismatch becomes a table definition rather than an error. Casting to a declared schema before registering is what turns adoption into validation.

Up: Arrow Interop Patterns for DuckDB Spatial