The spatial extension provides support for geospatial data processing in DuckDB. For an overview of the extension, see our blog post.

Installing and Loading

To install and load the spatial extension, run:

INSTALL spatial;
LOAD spatial;

GEOMETRY Type

The core of the spatial extension is the GEOMETRY type. If you’re unfamiliar with geospatial data and GIS tooling, this type probably works very different from what you’d expect.

In short, while the GEOMETRY type is a binary representation of “geometry” data made up out of sets of vertices (pairs of X and Y double precision floats), it actually stores one of several geometry subtypes. These are POINT, LINESTRING, POLYGON, as well as their “collection” equivalents, MULTIPOINT, MULTILINESTRING and MULTIPOLYGON. Lastly there is GEOMETRYCOLLECTION, which can contain any of the other subtypes, as well as other GEOMETRYCOLLECTIONs recursively.

This may seem strange at first, since DuckDB already have types like LIST, STRUCT and UNION which could be used in a similar way, but the design and behaviour of the GEOMETRY type is actually based on the Simple Features geometry model, which is a standard used by many other databases and GIS software.

That said, the spatial extension also includes a couple of experimental non-standard explicit geometry types, such as POINT_2D, LINESTRING_2D, POLYGON_2D and BOX_2D that are based on DuckDBs native nested types, such as structs and lists. In theory it should be possible to optimize a lot of operations for these types much better than for the GEOMETRY type (which is just a binary blob), but only a couple functions are implemented so far.

All of these are implicitly castable to GEOMETRY but with a conversion cost, so the GEOMETRY type is still the recommended type to use for now if you are planning to work with a lot of different spatial functions.

GEOMETRY is not currently capable of storing additional geometry types, Z/M coordinates, or SRID information. These features may be added in the future.

Spatial Scalar Functions

The spatial extension implements a large number of scalar functions and overloads. Most of these are implemented using the GEOS library, but we’d like to implement more of them natively in this extension to better utilize DuckDB’s vectorized execution and memory management. The following symbols are used to indicate which implementation is used:

🧭 – GEOS – functions that are implemented using the GEOS library

🦆 – DuckDB – functions that are implemented natively in this extension that are capable of operating directly on the DuckDB types

🔄 – CAST(GEOMETRY) – functions that are supported by implicitly casting to GEOMETRY and then using the GEOMETRY implementation

The currently implemented spatial functions can roughly be categorized into the following groups:

Geometry Conversion

Convert between geometries and other formats.

Geometry Construction

Construct new geometries from other geometries or other data.

Spatial Properties

Calculate and access spatial properties of geometries.

Spatial Relationships

Compute relationships and spatial predicates between geometries.

Spatial Aggregate Functions

Spatial Table Functions

ST_Read() – Read Spatial Data from Files

The spatial extension provides a ST_Read table function based on the GDAL translator library to read spatial data from a variety of geospatial vector file formats as if they were DuckDB tables. For example to create a new table from a GeoJSON file, you can use the following query:

CREATE TABLE ⟨table⟩ AS SELECT * FROM ST_Read('some/file/path/filename.json');

ST_Read can take a number of optional arguments, the full signature is:

ST_Read(
    VARCHAR,
    sequential_layer_scan : BOOLEAN,
    spatial_filter : WKB_BLOB,
    open_options : VARCHAR[],
    layer : VARCHAR,
    allowed_drivers : VARCHAR[],
    sibling_files : VARCHAR[],
    spatial_filter_box : BOX_2D,
    keep_wkb : BOOLEAN
)

• sequential_layer_scan (default: false): If set to true, the table function will scan through all layers sequentially and return the first layer that matches the given layer name. This is required for some drivers to work properly, e.g., the OSM driver.
• spatial_filter (default: NULL): If set to a WKB blob, the table function will only return rows that intersect with the given WKB geometry. Some drivers may support efficient spatial filtering natively, in which case it will be pushed down. Otherwise the filtering is done by GDAL which may be much slower.
• open_options (default: []): A list of key-value pairs that are passed to the GDAL driver to control the opening of the file. E.g., the GeoJSON driver supports a FLATTEN_NESTED_ATTRIBUTES=YES option to flatten nested attributes.
• layer (default: NULL): The name of the layer to read from the file. If NULL, the first layer is returned. Can also be a layer index (starting at 0).
• allowed_drivers (default: []): A list of GDAL driver names that are allowed to be used to open the file. If empty, all drivers are allowed.
• sibling_files (default: []): A list of sibling files that are required to open the file. E.g., the ESRI Shapefile driver requires a .shx file to be present. Although most of the time these can be discovered automatically.
• spatial_filter_box (default: NULL): If set to a BOX_2D, the table function will only return rows that intersect with the given bounding box. Similar to spatial_filter.
• keep_wkb (default: false): If set, the table function will return geometries in a wkb_geometry column with the type WKB_BLOB (which can be cast to BLOB) instead of GEOMETRY. This is useful if you want to use DuckDB with more exotic geometry subtypes that DuckDB spatial doesnt support representing in the GEOMETRY type yet.

Note that GDAL is single-threaded, so this table function will not be able to make full use of parallelism. We’re planning to implement support for the most common vector formats natively in this extension with additional table functions in the future.

We currently support over 50 different formats. You can generate the following table of supported GDAL drivers yourself by executing SELECT * FROM ST_Drivers().

Note that far from all of these drivers have been tested properly, and some may require additional options to be passed to work as expected. If you run into any issues please first consult the GDAL docs.

ST_ReadOsm() – Read Compressed OSM Data

The spatial extension also provides an experimental ST_ReadOsm() table function to read compressed OSM data directly from a .osm.pbf file.

This will use multithreading and zero-copy protobuf parsing which makes it a lot faster than using the st_read() OSM driver, but it only outputs the raw OSM data (Nodes, Ways, Relations), without constructing any geometries. For node entities you can trivially construct POINT geometries, but it is also possible to construct LINESTRING AND POLYGON by manually joining refs and nodes together in SQL.

Example usage:

SELECT *
FROM st_readosm('tmp/data/germany.osm.pbf')
WHERE tags['highway'] != []
LIMIT 5;

Spatial Replacement Scans

The spatial extension also provides “replacement scans” for common geospatial file formats, allowing you to query files of these formats as if they were tables.

SELECT * FROM './path/to/some/shapefile/dataset.shp';

In practice this is just syntax-sugar for calling ST_Read, so there is no difference in performance. If you want to pass additional options, you should use the ST_Read table function directly.

The following formats are currently recognized by their file extension:

• ESRI ShapeFile, .shp
• GeoPackage, .gpkg
• FlatGeoBuf, .fgb

Similarly there is a .osm.pbf replacement scan for ST_ReadOsm.

Spatial Copy Functions

Much like the ST_Read table function the spatial extension provides a GDAL based COPY function to export duckdb tables to different geospatial vector formats. For example to export a table to a GeoJSON file, with generated bounding boxes, you can use the following query:

COPY ⟨table⟩ TO 'some/file/path/filename.geojson'
WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES');

Available options:

• FORMAT: is the only required option and must be set to GDAL to use the GDAL based copy function.
• DRIVER: is the GDAL driver to use for the export. See the table above for a list of available drivers.
• LAYER_CREATION_OPTIONS: list of options to pass to the GDAL driver. See the GDAL docs for the driver you are using for a list of available options.
• SRS: Set a spatial reference system as metadata to use for the export. This can be a WKT string, an EPSG code or a proj-string, basically anything you would normally be able to pass to GDAL/OGR. This will not perform any reprojection of the input geometry though, it just sets the metadata if the target driver supports it.

Limitations

Raster types are not supported and there is currently no plan to add them to the extension.