DuckDB Spatial Analytical SQL for GIS

Streaming Large GeoJSON Without OOM

A GeoJSON file is a single JSON document with no index and no random access, which means the only two options are to read all of it or none of it — and the difference between a load that completes and one that is killed is entirely about what the reader is asked to hold while it does. This walkthrough, part of the GeoJSON ingestion reference, covers converting a file larger than memory in one pass, the settings that decide whether the pass streams or accumulates, and what to do when even one pass will not fit.

Root-Cause Analysis: why a GeoJSON load runs out of memory

  • Schema inference reads ahead. To decide the column types the reader samples features, and on a heterogeneous properties object it may sample a great many. Declaring the columns removes both the memory and the time that costs.
  • The whole document may be materialised. A reader that treats the file as one JSON value has to parse it as one value. The feature-collection reader streams; a generic JSON path may not, which is the difference between the two ingestion routes.
  • Every property becomes a column. Unprojected, a load materialises every key any feature carries, including the ones the query never references — and on real data that is often most of them.
  • The target is accumulated rather than written. A CREATE TABLE AS holds the result until it commits. Writing straight to Parquet with COPY streams instead, which changes the peak from the size of the output to the size of one row group.
  • Compression forecloses everything. A gzipped file must be decompressed in full before parsing starts and cannot be split, so every option above becomes unavailable at once.

The distinguishing question is whether the pipeline holds the output or streams it. A conversion that streams has a peak set by the writer rather than by the dataset, and that peak is a setting.

What each stage holds at once Inference holds its sample, parsing holds one feature, projection holds one vector per thread, an accumulated table holds everything, and a streaming write holds one row group per thread. STAGE HOLDS AT ONCE SCALES WITH schema inference the sample it read property heterogeneity streaming parse one feature nothing projected columns one vector per thread thread count CREATE TABLE AS the entire output the dataset — the failure COPY to Parquet one row group per thread a setting

Only one row scales with the dataset, and it is the one most conversions use.

Deterministic Configuration 

INSTALL spatial; LOAD spatial;

-- A streaming conversion should not need a large ceiling. If it does, the
-- pipeline is accumulating somewhere rather than streaming.
SET memory_limit = '4GB';
SET threads = 4;                      -- each writer thread holds a row group
SET temp_directory = '/var/tmp/duckdb_geojson';
SET preserve_insertion_order = false; -- lets the writer emit as it reads

Optimized Execution Pattern

The pattern is a single streaming pass from the reader straight to a Parquet writer, with the columns named so nothing is inferred and nothing unwanted is built.

-- ANTI-PATTERN: infers the schema, materialises every property, and holds
-- the whole result until it commits. Three ways to exceed memory at once.
CREATE TABLE boundaries AS SELECT * FROM st_read('boundaries.geojson');
-- PATTERN: named columns, no inference, and a streaming write whose peak is
-- one row group per thread rather than the size of the dataset.
COPY (
    SELECT
        name::VARCHAR      AS name,
        code::VARCHAR      AS code,
        population::BIGINT AS population,
        geom
    FROM st_read('boundaries.geojson')
) TO 'boundaries.parquet' (FORMAT PARQUET, ROW_GROUP_SIZE 20000);

The row-group size is doing real work here. On dense boundary geometry the default buffers far more than expected, and lowering it is usually a more effective fix for a failing conversion than raising the memory limit — it reduces the peak rather than accommodating it.

Three source shapes and what each permits A feature collection streams but does not split; newline-delimited splits at line boundaries; a directory parallelises across files at the cost of per-file inference. SOURCE SHAPE PARALLELISM NOTE one feature collection writer only streams, but sequentially newline-delimited (.ndjson) read and write the only variant that scales a directory of files across files per-file inference cost applies

If you control the producer, ask for newline-delimited. It is the only shape that scales.

Declaring the schema instead of inferring it

Schema inference is both the largest time cost in a GeoJSON load and a source of surprising types. GeoJSON puts no schema on properties, so one feature carrying "floors": "3" among a million carrying "floors": 3 widens the whole column to text — and the pipeline downstream then compares a number against a string and silently matches nothing.

Declaring the columns fixes both problems at once. It replaces an inference pass over the file with a lookup, and it makes the type a decision rather than an outcome. Where a value genuinely varies, the declaration forces the question to be answered deliberately: cast it, reject it, or keep both forms in separate columns.

-- Find the heterogeneity before it becomes a VARCHAR column. This is one
-- pass over a sample and it turns a surprise into a decision.
SELECT typeof(json_extract(properties, '$.floors')) AS observed_type, count(*)
FROM read_json_auto('boundaries.geojson', maximum_object_size = 20000000)
GROUP BY 1 ORDER BY 2 DESC;

Diagnostic Queries & Plan Validation

The diagnostic for a conversion that is about to fail is resident memory over time rather than a single number: a streaming pass has a flat profile and an accumulating one has a rising line.

-- During the conversion, from a second connection: spill activity that grows
-- without the query progressing means accumulation rather than streaming.
SELECT count(*) AS spill_files, sum(size) / 1e9 AS spill_gb
FROM duckdb_temporary_files();

A streaming conversion may spill a little and should not spill continuously. Continuous growth means something in the statement is holding the whole result — most often a CREATE TABLE AS, an ORDER BY over the full output, or a window function with no partition.

Four constructs that stop a conversion streaming A full ORDER BY, an unpartitioned window, a global DISTINCT, and CREATE TABLE AS each hold the whole result. CONSTRUCT WHY IT ACCUMULATES INSTEAD ORDER BY over the output a full sort before the first write sort a second pass over the Parquet a window with no PARTITION BY one frame over everything partition it, or move it downstream DISTINCT over the result every seen value must be held deduplicate per partition CREATE TABLE AS held until commit COPY to a file instead

All four look innocuous, and all four turn a bounded pass into an unbounded one.

When one pass genuinely will not fit

If the source cannot be streamed — because it is compressed, or because a single feature is enormous — the only remaining option is to split it before reading. A GeoJSON feature collection can be split at feature boundaries by a text-processing pass that never parses the whole document, and each piece then converts independently.

That is a last resort rather than a technique, and it is worth saying why: splitting JSON safely requires respecting nesting, so a naive line-based split corrupts features. Where the producer can be asked for newline-delimited output instead, that is always the better answer, because it makes the file splittable by construction and removes the problem rather than working around it.

Geometry Validation & Fallback Routing

Guard the conversion so a single malformed feature does not lose the pass, and so the result is countable against the source.

-- Count what arrived and what was usable, in the same pass as the write.
-- A gap between the two is a data question rather than a memory question.
CREATE OR REPLACE TABLE ingest_report AS
SELECT count(*)                                      AS features_read,
       count(*) FILTER (WHERE geom IS NULL)          AS features_without_geometry,
       count(*) FILTER (WHERE NOT ST_IsValid(geom))  AS features_invalid
FROM st_read('boundaries.geojson');

Frequently Asked Questions

Can DuckDB read a GeoJSON file larger than memory?

Yes, provided the pipeline streams — a COPY from st_read straight to Parquet has a peak set by the writer rather than by the file. What cannot be done is querying such a file repeatedly, because there is no index and no random access, so every query reads all of it. Convert once and query the Parquet.

Why does declaring the columns help so much?

It removes the inference pass, which is the largest single cost in a GeoJSON load, and it removes the memory that pass holds. It also stops one anomalous feature widening a numeric column to text, which is a correctness benefit as much as a performance one.

Should I decompress first or read the gzip directly?

Decompress as a separate step. A compressed file must be decompressed in full before parsing can begin and cannot be split, so it forecloses every parallelism option at once. Decompressing and converting in the same pipeline run costs one temporary file and restores all of them.

My conversion spills continuously. What is wrong?

Something in the statement is holding the whole result. The usual four are an ORDER BY over the output, a window function with no PARTITION BY, a global DISTINCT, and CREATE TABLE AS instead of COPY. All look innocuous and all turn a bounded pass into an unbounded one.

Is newline-delimited GeoJSON worth asking for?

Yes, whenever you can influence the producer. It is the only variant that can be split at line boundaries, so both the read and the write parallelise, and it removes the whole class of problem this page is about rather than working around it.

What row-group size should the output use?

Derived from the geometry payload rather than left at the default — on dense boundaries the default buffers far more than expected, and lowering it reduces the write peak directly. The arithmetic is in row-group sizing for spatial scans.

Up: GeoJSON Ingestion in DuckDB Spatial