GeoJSON Ingestion in DuckDB Spatial

GeoJSON ingestion is fundamentally an I/O- and memory-bound operation: every feature carries a verbose, deeply nested coordinate array and an arbitrary properties object that must be parsed, validated, and materialized before any spatial work begins. This page, part of the DuckDB Spatial Architecture & Fundamentals reference, addresses one specific workflow — turning a raw .geojson payload into a queryable, topology-clean GEOMETRY table — and the execution semantics, configuration knobs, and failure modes that decide whether that pipeline runs at memory-bandwidth speed or thrashes against disk. DuckDB Spatial resolves the parse through the GDAL/OGR vector reader (st_read) or the native JSON reader (read_json_auto), both of which decode coordinate arrays into contiguous columnar buffers rather than per-row objects. The patterns below are execution-ready for data-engineering and GIS pipelines.

Runtime Configuration & Memory Guardrails #

A GeoJSON file is a single, often un-splittable text blob. Unlike Parquet row groups, it offers no native parallel scan boundary, so the dominant cost is parsing the JSON tree and the dominant risk is materializing the entire feature collection in memory at once. Set the guardrails before you touch the data:

-- Minimal reproducible session for a GeoJSON ingestion workload
INSTALL spatial; LOAD spatial;

SET threads = 8;                       -- match physical cores; oversubscription thrashes L3 cache
SET memory_limit = '16GB';             -- hard ceiling; parser spills past this instead of OOM-ing
SET preserve_insertion_order = false;  -- frees the engine to reorder rows -> enables parallel write
SET temp_directory = '/mnt/nvme/duckdb_temp';  -- spill target on fast local disk, not network storage
SET max_temp_directory_size = '100GB'; -- caps spill so a runaway parse fails loudly, not silently

Trade-off: keeping preserve_insertion_order = true (the default) forces the writer to retain source row order, which serializes the final materialization stage and inflates ingestion latency 2–4× on wide feature collections. Disable it for ingestion unless a downstream consumer genuinely depends on file order.

Because the JSON reader cannot stream a single document the way it streams newline-delimited records, peak resident memory tracks the uncompressed size of the feature collection plus its decoded geometry. As with the broader in-memory vs disk storage decision, size memory_limit to the largest single file you expect to ingest and reserve temp_directory on NVMe so that spill, when it happens, does not collapse into network I/O.

Primary Execution Patterns #

The canonical pipeline is a two-stage flow: parse + project into a raw staging table, then validate + repair in place before the data is exposed to indexes or joins. Splitting the stages keeps the expensive geometry-validity pass off the parser’s critical path and gives you a clean quarantine boundary.

The two-stage pipeline: Stage 1 parses and projects into a raw staging table; Stage 2 gates on ST_IsValid, passing clean rows straight through, repairing lightly-invalid rows with ST_MakeValid, and quarantining a source that is too dirty to repair safely.

Stage 1 — Parse and project #

For well-formed, RFC 7946-compliant input, st_read is the recommended path. It returns a geom column already typed as GEOMETRY and lets you pull scalar attributes out of the properties object with the JSON arrow operators:

-- Stage 1: parse with st_read (GDAL/OGR reader) and project only needed columns
CREATE OR REPLACE TABLE raw_geojson AS
SELECT
    (properties->>'id')::BIGINT   AS id,        -- cast at parse time; avoids a second full scan later
    properties->>'name'           AS name,
    properties->>'category'       AS category,
    geom                                        -- native GEOMETRY, no WKB round-trip needed
FROM st_read('s3://data-lake/ingest/parcels_2024.geojson');

Column projection here is load-bearing: selecting only the attributes you need lets the reader skip the rest of each feature’s properties tree, which on property-rich data cuts peak RSS by 30–50%. Avoid SELECT * on wide GeoJSON.

When the source is not a single compliant FeatureCollection — for example newline-delimited JSON, one feature per object, or geometry nested under a non-standard key — fall back to read_json_auto and lift the geometry manually with st_geomfromgeojson:

-- Stage 1 (non-standard JSON): manual geometry extraction
CREATE OR REPLACE TABLE raw_geojson_manual AS
SELECT
    (data->'properties'->>'id')::BIGINT AS id,
    data->'properties'->>'name'         AS name,
    st_geomfromgeojson(data->>'geometry') AS geom  -- parse the embedded geometry sub-document
FROM read_json_auto(
    's3://data-lake/ingest/parcels_2024.json',
    maximum_object_size = 10485760  -- raise the 16MB default only as far as your largest feature needs
);

When orchestrating from Python, load the extension explicitly, disable the progress bar for headless runs, and bind the source path as a parameter so the plan is cacheable and injection-safe. This staging table is also the natural hand-off point for a DuckDB-to-GeoPandas sync:

import duckdb

con = duckdb.connect(config={"threads": 8, "memory_limit": "16GB"})
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("SET preserve_insertion_order = false;")  # parallel write for ingestion

# Parameterized, repeatable ingestion
con.execute(
    """
    CREATE OR REPLACE TABLE raw_geojson AS
    SELECT (properties->>'id')::BIGINT AS id, geom
    FROM st_read($source)
    """,
    {"source": "s3://data-lake/ingest/parcels_2024.geojson"},
)

Stage 2 — Validate and repair #

GeoJSON in the wild routinely contains self-intersecting rings, duplicate vertices, and non-planar polygons. Repair them once, immediately after the parse, so every downstream operator can assume valid topology:

-- Stage 2: repair only the rows that need it (targeted UPDATE, not a blanket rewrite)
UPDATE raw_geojson
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

GeoJSON is defined in WGS84 (EPSG:4326, lon/lat) by RFC 7946. If downstream analytics need metric units — distance buffers, area, or proximity filters that measure in meters — apply the CRS transformation once at ingestion rather than re-projecting on every query:

-- Project to a metric CRS once, post-ingestion
CREATE OR REPLACE TABLE parcels_metric AS
SELECT id, name, category,
       st_transform(geom, 'EPSG:4326', 'EPSG:32633') AS geom  -- to UTM 33N; reproject once, not per-query
FROM raw_geojson;

For datasets that exceed memory_limit, write the validated result straight into an attached database file so the working set is disk-backed rather than held in RAM:

-- Persist large results to disk instead of in-memory materialization
ATTACH '/mnt/warehouse/warehouse.duckdb' AS warehouse;
CREATE OR REPLACE TABLE warehouse.parcels AS
SELECT id, name, category, geom FROM raw_geojson;

Execution Plan Validation #

Run EXPLAIN ANALYZE against the ingestion query to confirm the engine is doing what you expect before you commit the pipeline:

EXPLAIN ANALYZE
CREATE OR REPLACE TABLE raw_geojson AS
SELECT properties->>'name' AS name, geom
FROM st_read('s3://data-lake/ingest/parcels_2024.geojson');

Expected plan nodes and what each one tells you:

• ST_READ (or READ_JSON) scan — the source operator. Its output row count must equal the feature count of the file; a shortfall means the reader silently dropped malformed features or hit maximum_object_size.
• PROJECTION — confirms column pruning. If you projected three attributes but the node lists the full properties struct, your projection is not pushing down and you are paying to decode every property.
• CREATE_TABLE_AS writer — should show multi-thread cardinality when preserve_insertion_order = false. A single-threaded writer here is the signature of insertion-order preservation still being on.

Diagnostic boundary: any spill/external node in the plan, or rapid growth in SELECT * FROM duckdb_temporary_files();, means the parse no longer fits in memory_limit. Raise the limit, partition the source by bounding box or feature count, or move to disk-backed materialization — do not let it thrash a network-attached spill directory.

A useful row-estimate-drift check: a single-file GeoJSON scan has no statistics, so DuckDB’s row estimate is a guess. If the actual row count is orders of magnitude off the estimate and a downstream join is planned, materialize the staging table first (as above) so the join planner sees real cardinalities instead of a blind estimate.

Performance Trade-offs #

When deciding between st_read and read_json_auto: st_read is faster and simpler for compliant files because GDAL handles geometry typing, but read_json_auto is the only viable path for non-standard layouts and gives finer control over object-size limits and which sub-document becomes the geometry. For repeated ingestion of the same schema, converting the source to GeoParquet once and reading that thereafter eliminates the JSON parse entirely and restores true parallel, predicate-pushed scans.

Edge Cases & Anti-Patterns #

Anti-pattern — validating inside the scan. Wrapping ST_MakeValid(geom) directly in the Stage-1 st_read projection forces validity repair on every feature during the parse, including the majority that are already valid, and blocks the parser from streaming. Repair in a targeted Stage-2 UPDATE ... WHERE NOT ST_IsValid(geom) instead.

Anti-pattern — globally repairing a dirty source. When more than ~5% of features are invalid, blanket ST_MakeValid can silently alter topology (collapsing rings, merging vertices) and corrupts determinism for downstream joins. Quarantine instead and resolve at the source ETL layer:

-- Route bad geometries to a quarantine table with a reason code
CREATE OR REPLACE TABLE geojson_quarantine AS
SELECT id, name, geom,
       CASE WHEN ST_IsEmpty(geom)      THEN 'empty'
            WHEN NOT ST_IsValid(geom)  THEN 'invalid'
       END AS failure_reason
FROM raw_geojson
WHERE ST_IsEmpty(geom) OR NOT ST_IsValid(geom);

DELETE FROM raw_geojson
WHERE id IN (SELECT id FROM geojson_quarantine);

Edge case — coordinate order. RFC 7946 mandates [longitude, latitude]. Sources exported from lat/lon tooling silently swap the axes; the parse succeeds but every point lands in the wrong hemisphere. Sanity-check with ST_X(geom) BETWEEN -180 AND 180 before trusting the data.

Edge case — maximum_object_size truncation. A single very large feature (a country-level multipolygon, say) can exceed the 16MB default in read_json_auto, and the feature is dropped without error. If the scan row count is one or two short of the source feature count, raise maximum_object_size.

Edge case — S3 credentials. For cloud sources, configure access via CREATE SECRET (or environment variables) and set SET s3_region = '...' when auto-detection fails; a missing region surfaces as an opaque HTTP 400 mid-scan, not a clear auth error.

Query Regression Analysis #

Ingestion pipelines drift silently: a source schema change, a new DuckDB version, or a config regression can flip parallel writes back to single-threaded or reintroduce a spill without any error. Capture the plan as a baseline and diff it on every run. The same approach extends naturally to batch processing pipelines that ingest many files on a schedule:

import duckdb, json, hashlib, pathlib

def capture_plan(con, sql: str) -> dict:
    rows = con.execute("EXPLAIN ANALYZE " + sql).fetchall()
    plan = "\n".join(r[1] for r in rows)
    return {
        "fingerprint": hashlib.sha256(plan.encode()).hexdigest()[:16],
        "spilled": "TEMPORARY" in plan or "EXTERNAL" in plan,
        "single_threaded_write": plan.count("CREATE_TABLE_AS") == 1
                                 and "Thread" not in plan,
        "plan": plan,
    }

INGEST = """
    SELECT (properties->>'id')::BIGINT AS id, geom
    FROM st_read('s3://data-lake/ingest/parcels_2024.geojson')
"""

con = duckdb.connect(config={"threads": 8, "memory_limit": "16GB"})
con.execute("INSTALL spatial; LOAD spatial; SET preserve_insertion_order = false;")

current = capture_plan(con, INGEST)
baseline_path = pathlib.Path("baselines/geojson_ingest.json")

if baseline_path.exists():
    baseline = json.loads(baseline_path.read_text())
    if current["fingerprint"] != baseline["fingerprint"]:
        raise SystemExit(
            f"Plan regression: {baseline['fingerprint']} -> {current['fingerprint']} "
            f"(spilled={current['spilled']}, "
            f"single_threaded_write={current['single_threaded_write']})"
        )
else:
    baseline_path.parent.mkdir(parents=True, exist_ok=True)
    baseline_path.write_text(json.dumps(current, indent=2))

Wire this into CI so an unexpected spill or a writer that drops back to single-threaded fails the build instead of quietly doubling ingestion time in production.

Related #

See also

• GeoParquet Parsing in DuckDB Spatial — convert GeoJSON once and read the columnar format thereafter for parallel, predicate-pushed scans.
• In-Memory vs Disk Storage — engine-level allocation and spill behavior that bounds how large a feature collection you can ingest.
• CRS Mapping & Transformations — projecting WGS84 GeoJSON into metric coordinate systems without precision loss.
• Spatial Joins & Proximity Filters — the downstream consumer that depends on the valid topology this pipeline guarantees.
• DuckDB-to-GeoPandas Sync — moving an ingested staging table into a GeoDataFrame for Python-side analysis.

Up: DuckDB Spatial Architecture & Fundamentals

---

External Reference Standards: GeoJSON structure, the mandatory WGS84 datum, and [longitude, latitude] axis order referenced throughout this page follow RFC 7946: The GeoJSON Format.