DuckDB Spatial — Embedded Analytical Database from CWI Amsterdam for GIS

DuckDB is an in-process analytical database described by its creators as "SQLite for analytics": a single-file .duckdb container, ACID transactions, zero infrastructure, yet with a vectorized columnar engine optimized for OLAP workloads and bulk SQL queries. With the official Spatial extension released in February 2023, DuckDB became a direct competitor to GeoPackage and SpatiaLite for analytical tasks: the same "one file — one database" approach but with the familiar ST_* function set backed by GEOS, GDAL, and PROJ. For notebook-driven GIS workflows — Jupyter, R Markdown, dbt models, Wasm browser apps — DuckDB Spatial has become the fastest way to run spatial joins, aggregations, and transformations without bringing up a PostgreSQL/PostGIS server. On osm2cdr.ru we export a bbox of an OSM area into a .duckdb file with layer tables (polygons, lines, points, roads) and a WKB-encoded GEOMETRY column — ready to open with the duckdb CLI or import duckdb in Python without installing external servers.

History: CWI Amsterdam, MonetDB heritage, v1.0.0

DuckDB was born inside the Centrum Wiskunde & Informatica (CWI) in Amsterdam — the Dutch national research center for mathematics and computer science, known to the world as the birthplace of Python (Guido van Rossum worked at CWI in the early 1990s) and of MonetDB. Two CWI PhD researchers, Mark Raasveldt and Hannes Mühleisen, presented the first formal description of the project at SIGMOD 2019 in the paper "DuckDB: an Embeddable Analytical Database". Their motivation was practical: ad-hoc analytics in data-science workflows kept hitting two extremes — either in-memory pandas/dplyr that couldn't scale beyond RAM and had no transactions, or heavyweight server DBMSs (PostgreSQL, ClickHouse) requiring separate infrastructure. The gap between them is exactly what DuckDB filled: an embedded library linked directly into the application process, but with a real query optimizer, vectorized execution, and support for sub-RAM datasets through columnar storage.

Architecturally DuckDB inherits ideas from MonetDB and MonetDB/X100 — both projects of the same CWI database group. From MonetDB came the columnar layout and aggressive query optimization, from X100 — vectorized execution (processing not one row at a time but batches of 1024–2048 values that take advantage of CPU SIMD). But unlike MonetDB, which was a server requiring separate installation, DuckDB was designed from the start as a single-binary library with no network layer, no separate process, and an in-process API in the SQLite style. License is MIT, which removes any limitations on embedding into commercial products.

In 2021 Raasveldt and Mühleisen founded DuckDB Labs — a Dutch B.V. that supports the open-source project through consulting and enterprise support. That solved the classic problem of academic open-source projects: sustainable funding without vendor lock-in (DuckDB Labs does not own the source code; copyright is distributed among contributors, and the project is governed by the DuckDB Foundation).

3 June 2024 marked a key moment in the format's history — the release of DuckDB v1.0.0 with a public commitment to maintain backward compatibility of the storage format. Prior to v1.0.0, every minor release could require a .duckdb file migration; starting with v1.0.0 the team guarantees reading old-version files with new library versions (forward compatibility). That turned DuckDB from "a data scientist's tool for today" into a format suitable for long-term storage and data exchange.

The Spatial extension evolved as a separate branch of development. The first release shipped with DuckDB v0.7.0 in February 2023. The lead developer is Max Gabrielsson (DuckDB Labs). The extension is built on three heavyweight open-source GIS libraries: GEOS for spatial predicates and topological operations (ST_Intersects, ST_Buffer, ST_Union), GDAL/OGR for I/O in dozens of formats (ST_Read, ST_Write), and PROJ for coordinate-system transformations (ST_Transform). In functional coverage the extension quickly caught up with a PostGIS subset that covers 80% of typical tasks — geometry predicates, buffer/centroid/convex hull, projections, point-in-polygon joins, area aggregations.

Inside DuckDB: columnar storage, vectorized execution, Spatial extension

DuckDB stores data in a columnar format inside the .duckdb file — each table column is serialized separately with the appropriate compression codec applied (RLE for repeated values, dictionary encoding for low-cardinality strings, bit-packing for integer ranges, FSST for general string compression). This gives two advantages: a radical reduction in file size relative to row-oriented formats (typically 3–10× more compact than SQLite on the same data), and extremely efficient reads for analytical queries that usually need 2–5 columns out of 50 rather than the entire row.

Vectorized execution means each query-plan operator processes not one row at a time (the classic tuple-at-a-time approach of PostgreSQL/SQLite) but a batch of 1024–2048 values. This lets the compiler apply SIMD instructions (AVX2 on x86, NEON on ARM), drastically reduces plan-interpretation overhead, and yields 5× to 50× speedup on typical OLAP queries relative to row-by-row engines.

Single-file format holds everything in one .duckdb file: schemas, tables, indexes, views, sequences, and an embedded WAL (write-ahead log) for ACID semantics. Transactions are fully atomic — BEGIN; ...; COMMIT; is guaranteed via WAL, and ROLLBACK reverts any DML. At the same time there is no concurrent-writer support out of the box — DuckDB is designed for single-writer workloads (which is usually not a problem for analytics: one Jupyter process writes, many consumers read).

Zero-copy interop with Arrow and Parquet is a separate architectural feature. DuckDB reads Parquet files directly without materializing them in its own format: SELECT * FROM read_parquet('s3://bucket/data.parquet') scans Parquet column chunks via mmap and operates on the original memory layout. Similarly with Arrow — exchange with pandas, Polars, PyArrow happens through the Arrow C Data Interface without copying data. For GeoParquet this means you can read enormous spatial datasets (Overture Maps, Microsoft Building Footprints — terabyte-scale Parquet files) directly without preliminary import.

The Spatial extension is activated with a single command: INSTALL spatial; LOAD spatial;. After that a GEOMETRY type appears (internal representation is WKB) and the full set of ST_* functions becomes available. The ST_Read function is a universal reader through GDAL/OGR supporting dozens of formats automatically: Shapefile, GeoJSON, GeoPackage, GeoParquet, FlatGeobuf, KML, MapInfo TAB, FileGDB, and many more. ST_Write symmetrically writes to any OGR-supported format. This turns DuckDB into a universal spatial format converter without needing to call ogr2ogr from a shell.

Spatial indexing in the extension is still under active development as of 2025 — R-tree indexes are supported but are not always automatically picked by the planner for every query type. For performant spatial joins on large data the team recommends Hilbert curve sorting — CREATE TABLE sorted AS SELECT * FROM unsorted ORDER BY ST_Hilbert(geom, ST_Extent_Agg(geom) OVER ()) — which gives good spatial locality and efficient range scanning. This is a pattern borrowed from FlatGeobuf and Overture Maps tooling.

Use cases: Jupyter, dbt, edge analytics, Wasm in the browser

Ad-hoc spatial analytics in Jupyter. The most frequent scenario. An analyst in a Jupyter notebook does import duckdb, reads GeoParquet from Overture Maps or Microsoft Building Footprints through ST_Read, performs a spatial join with their own GeoJSON city polygon, aggregates — all without bringing up PostgreSQL, without pip install psycopg2, without configuring a connection string. The result serializes back to a pandas DataFrame via con.execute('...').df() and is visualized through folium or plotly. A typical pipeline for urban analytics, real-estate analytics, retail site selection — DuckDB has fully replaced the old "PostGIS + GeoPandas + SQLAlchemy" combination here.

ETL pipelines (Parquet → spatial join → GeoPackage). Performance ETL tasks: input dataset in Parquet (e.g., 50M buildings from Microsoft GlobalMLBuildingFootprints), need enrichment with admin-level codes through a spatial join with administrative boundaries, output as GeoPackage. DuckDB handles this in minutes on a laptop where raw PostGIS with a standalone server would require an import-index-join workflow taking hours. ST_Write(geom, 'output.gpkg') finalizes the result into a portable format.

dbt models with spatial transformations. dbt (data build tool) is the modern data-stack standard for managing SQL models. With the dbt-duckdb plugin you can describe spatial transformations declaratively as dbt models, version them in git, test them through dbt tests. This is the de-facto standard for analytics-engineering teams using DuckDB as a warehouse-on-laptop for prototyping before migrating to Snowflake/BigQuery/Databricks.

R & Python notebooks. Native packages duckdb for Python (pip install duckdb) and R (install.packages("duckdb")) — officially supported by the DuckDB Labs team. In R workflow dbplyr translates dplyr expressions to DuckDB SQL transparently, which gives the R community spatial analytics without the sf package overhead.

Edge analytics in Wasm (DuckDB-Wasm). DuckDB compiles to WebAssembly and runs entirely in the browser without a backend. This means: you publish a GeoParquet file on a static CDN, the frontend through @duckdb/duckdb-wasm loads it in chunks (HTTP range requests), executes SQL queries locally in the browser without sending data to the server. Latency-free OLAP over gigabyte-scale datasets becomes reality for public dashboards. This pattern is used by such projects as Overture Maps Explorer, kepler.gl plugins, and a series of journalistic data-visualization stories at the New York Times and the Financial Times.

Apache Superset, Hex, Posit Connect, Mode Analytics. SaaS BI tools increasingly embed DuckDB as an embedded query engine — for cached analytics, ad-hoc explore tabs, query acceleration. This lets them show interactive dashboards without a round-trip to the warehouse on every click.

OSM analytics specifics. Communities around ohsome (HeiGIT, Heidelberg) and the Overture Maps Foundation (consortium of Microsoft + Meta + AWS + TomTom, 2022) actively use DuckDB as the processing engine for Parquet-formatted OSM extracts. Overture publishes its releases directly in the GeoParquet format specifically targeting DuckDB-driven workflows — monthly updated snapshots (places, buildings, transportation, divisions) are available for direct SELECT * FROM read_parquet('s3://overturemaps-us-west-2/release/...') without downloading or importing.

Software and tools support

The tools ecosystem around DuckDB has grown over five years to a level comparable with PostgreSQL in client diversity:

• CLI: duckdb REPL — a single binary without dependencies, available for Linux/macOS/Windows via brew install duckdb, winget install duckdb, or a direct download from duckdb.org. An interactive shell with tab-completion, .help, .tables, syntax highlighting.
• Python: pip install duckdb — the official package, native bindings without an ODBC layer. Integration with pandas/Polars/Arrow through zero-copy. SQLAlchemy support via duckdb-engine. For spatial — INSTALL spatial; LOAD spatial; inside the connection.
• R: install.packages("duckdb") via CRAN. Full integration with DBI, dbplyr, arrow. The R community is actively adopting DuckDB as a drop-in replacement for SQLite and for local OLAP.
• Java/JVM: JDBC driver org.duckdb:duckdb_jdbc. Used in Scala/Spark interop scenarios, JupyterLab Java kernels.
• Node.js: npm install duckdb (or duckdb-async for async/await). Rapidly growing use in edge functions and Cloudflare Workers (via the Wasm build).
• Go, Rust, C, C++: official bindings available through GitHub releases. Used for embedding DuckDB in systems software and CLI tools.
• DBeaver: bundled support with a UI plugin — connection wizard, schema browser, query editor with syntax highlighting for DuckDB.
• JupyterLab + ipython-sql: %load_ext sql, %sql duckdb:///mydb.duckdb — magic commands for inline SQL in notebooks.
• Motherduck: a managed cloud DuckDB SaaS (founded by Jordan Tigani, ex-BigQuery founding engineer, in 2022). A hybrid "local-first" approach: your DuckDB connects to the Motherduck cloud, the query planner automatically decides what to execute locally and what in the cloud. This is not an alternative to DuckDB but a way to scale local-first analytics workflows to team-scale without migrating to Snowflake.

Workflow in OSM2CDR

duckdb_exporter.py in osm2cdr takes a bbox of an OSM area and produces a single-file .duckdb container, ready to open with duckdb file.duckdb or import duckdb; con = duckdb.connect('file.duckdb').

Step 1: PostGIS extract. Through local_osm_loader.py we extract OSM data of four classes from the local PostGIS — polygons (the planet polygon layer: buildings, landuse, water bodies), lines (the linear layer: roads, rivers, barriers), points (the point layer: POIs, addresses), and the specialized roads layer. For each class we return a set of columns: osm_id, name, key tags (highway, building, amenity, landuse, etc.) and geometry as WKB.

Step 2: DuckDB schema creation. We create the .duckdb file, activate INSTALL spatial; LOAD spatial;, and create four tables — polygons, lines, points, roads — each with a geom GEOMETRY column (WKB-encoded). Attribute columns are class-specific: for polygons it's building, landuse, amenity, name, height; for lines — highway, waterway, barrier, name; and so on.

Step 3: Bulk insert via the Appender API. DuckDB provides a high-performance Appender API (con.append(table, df) in Python) allowing millions of rows to be inserted in seconds without the parsing overhead of individual INSERTs. Geometries are converted from PostGIS WKB into DuckDB geometry through ST_GeomFromWKB(?).

Step 4: Views and metadata. We create an auxiliary view all_features (UNION ALL over the four tables with an extra layer column) for typical analytical queries like "find all objects within radius X". Export metadata is saved in a system table osm2cdr_metadata — bbox, export date, exporter version, OSM snapshot date.

Step 5: Compaction and finalization. Before closing the connection we call CHECKPOINT; — this flushes the WAL into the main file and physically compacts the data, producing a final single-file artifact without a residual write-ahead log.

Alternative mode — GeoParquet output. For users who work with DuckDB as a streaming query engine without needing to store the data inside a .duckdb container, we offer GeoParquet export (a separate geoparquet_exporter.py). That yields a compact portable artifact that can be consumed through DuckDB with a one-liner: SELECT name, ST_Area(ST_Buffer(geom, 0.001)) FROM read_parquet('moscow_osm.parquet') WHERE building = 'residential'; — no import, no infrastructure setup, no copying — DuckDB reads Parquet column chunks through HTTP range requests directly from a CDN.

What our exporter does not do. It does not create spatial indexes automatically — the user decides whether an R-tree is needed for their specific workload. It does not sort by Hilbert curve — if spatial locality is important, the user runs that operation through CREATE TABLE sorted AS SELECT * FROM polygons ORDER BY ST_Hilbert(geom, ...). It does not apply GDAL transformations to coordinates — data stays in EPSG:4326 (WGS84) as stored in PostGIS; the user runs ST_Transform(geom, 3857) themselves if a projection is needed.

FAQ

What's the difference between DuckDB and SQLite? SQLite is a row-oriented OLTP database optimized for transactional workloads (insert/update of one row at a time). DuckDB is a column-oriented OLAP database optimized for analytical workloads (aggregation over millions of rows, group by, join). API and ergonomics are similar (single-file, embedded library), but on typical analytical tasks DuckDB outperforms SQLite by 10–100×. For transactional applications — pick SQLite; for analytics — DuckDB.

DuckDB vs PostgreSQL/PostGIS — does one replace the other? No, different scenarios. PostgreSQL is a server-side multi-user OLTP+OLAP database for production applications with concurrent writers, replication, fine-grained security. DuckDB is an embedded single-writer OLAP library for analytics workflows: notebook, ETL job, BI engine. For a web application with millions of users — PostgreSQL. For an analytics task in Jupyter or a dbt model — DuckDB. They complement, not replace, each other.

DuckDB vs MotherDuck — are they the same? No. DuckDB is an open-source library (MIT license) that you install locally. MotherDuck is a commercial SaaS founded by Jordan Tigani in 2022, providing managed cloud DuckDB with additional capabilities (shared catalogs, hybrid execution, billing). You can use DuckDB completely without MotherDuck; MotherDuck is an optional upgrade for team-scale workflows.

What does "v1.0.0 storage format stability" mean? Prior to DuckDB v1.0.0 (3 June 2024) every minor release could break the .duckdb file format — upgrading required migration (export → upgrade → import). Starting with v1.0.0 the team committed to forward compatibility: new library versions read .duckdb files of older v1.x versions. This is critical for long-term storage and exchanging .duckdb files between teams.

Can DuckDB be used in production server-side? Yes, but with limitations: the single-writer model means that for multi-user concurrent write workloads DuckDB is not suitable. For read-heavy nightly batch jobs, ETL pipelines, internal analytics dashboards with one writer — an excellent choice. As a replacement for the transactional database of a user-facing application — pick PostgreSQL.

What spatial functionality is covered by the DuckDB Spatial extension? Geometry predicates (ST_Intersects, ST_Contains, ST_Within, ST_Touches, ST_Crosses), constructors (ST_GeomFromText, ST_GeomFromWKB, ST_MakePoint), measurements (ST_Area, ST_Length, ST_Distance), set operations (ST_Union, ST_Intersection, ST_Difference), buffer/centroid/hull (ST_Buffer, ST_Centroid, ST_ConvexHull), transformations (ST_Transform via PROJ), I/O through GDAL (ST_Read, ST_Write — dozens of formats). Not covered: raster operations (vector geometry only), topology layers (as in PostGIS Topology), geocoding (no built-in PostGIS-style address standardizer). For 80% of typical spatial-analytics tasks — sufficient.

How fast is DuckDB on large data? In typical benchmarks on a single machine DuckDB outperforms ClickHouse, Pandas, dplyr on OLAP workloads for datasets up to RAM size. On out-of-RAM datasets DuckDB uses disk spilling (temporary files) and continues working, although more slowly. For terabyte-scale Parquet through HTTP range requests from cloud storage — performance is bound by the network, but DuckDB efficiently uses predicate pushdown, reading only the necessary column chunks. Comparative benchmarks are available at duckdblabs.github.io/db-benchmark.

Conclusion

DuckDB Spatial is a "Postgres-free PostGIS" for notebook-driven workflows: an embedded library, single-file format, ACID transactions, vectorized columnar engine, and at the same time a full set of ST_* functions backed by GEOS + GDAL + PROJ. For an analyst who needs to perform a spatial join of 50M buildings with administrative boundaries inside Jupyter without spinning up a PostgreSQL server, for a dbt engineer who describes spatial transformations declaratively as versioned SQL models, for a journalist who publishes an interactive data story with DuckDB-Wasm in the browser — DuckDB fills the niche previously occupied by the heavyweight "PostGIS + server + ETL + client" stack. MIT license, v1.0.0 storage-format stability since June 2024, an active maintainer team at CWI Amsterdam and DuckDB Labs make the format suitable both for short-term ad-hoc tasks and for long-term archival of spatial data.

On osm2cdr.ru we provide a .duckdb export of an OSM bbox area through /api/render with format duckdb — a single-file container with thematic tables (polygons, lines, points, roads), a WKB-encoded GEOMETRY column, and built-in views for typical queries. Alternatively — GeoParquet output for users working with DuckDB as a streaming query engine without storing data in a .duckdb file.

Try exporting OSM to DuckDB for your city, open the result through duckdb file.duckdb or import duckdb in Jupyter, and feel how much simpler analytics gets without server infrastructure.

Related

• GeoParquet — columnar format for spatial big data from OGC
• GeoPackage — open OGC standard for single-file spatial databases
• SpatiaLite — spatial extension for SQLite
• FlatGeobuf — binary streamable format for web maps

Sources

• Raasveldt, Mühleisen "DuckDB: an Embeddable Analytical Database" (CWI Amsterdam, SIGMOD 2019) — duckdb.org/why_duckdb
• DuckDB GitHub repository — github.com/duckdb/duckdb
• DuckDB v1.0.0 release announcement (3 June 2024) — duckdb.org/2024/06/03/announcing-duckdb-100
• DuckDB Spatial extension documentation — duckdb.org/docs/extensions/spatial
• DuckDB Labs (commercial entity supporting open-source project) — duckdblabs.com
• Overture Maps Foundation — GeoParquet releases for DuckDB workflows — overturemaps.org