DuckDB Spatial Extension

The WKB_BLOB type is used to represent geometries known to be in Well-Known Binary (WKB) format.

Note: This type is deprecated. It is maintained solely for backward compatibility with previous versions of DuckDB Spatial. For new development, you should use the standard geometry types provided by the spatial extension instead of WKB_BLOB.

To work with geospatial data formats like Shapefiles and Parquet, you must first load the spatial and parquet extensions in DuckDB.

LOAD spatial;
LOAD parquet;

When using the COPY command to export data via the GDAL driver, you can specify format-specific options using the LAYER_CREATION_OPTIONS parameter within the WITH clause. This is equivalent to using the -lco flag in the GDAL command-line interface.

For example, when exporting to GeoJSON, you can enable options like WRITE_BBOX and RFC7946 to control the output structure.

COPY (SELECT * from st_read('input.shp'))
TO 'output.geojson'
WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS ('WRITE_BBOX=YES', 'RFC7946=YES'))

When performing multiple spatial predicate tests against the same geometry, use get_prepared() to create a PreparedGeosGeometry. This object pre-processes the geometry to significantly speed up subsequent predicate operations like contains, intersects, and within.

Workflow

1. Call geom.get_prepared() to create the prepared instance.
2. Use the prepared instance to run multiple queries against different geometries.

Supported Predicates

PreparedGeosGeometry supports the same core predicates as GeosGeometry (e.g., contains, covers, crosses, disjoint, intersects, overlaps, touches, within) but at a higher performance level.

// Assuming 'geom' is an existing GeosGeometry
PreparedGeosGeometry prepared = geom.get_prepared();

if (prepared.intersects(other_geom)) {
    // Perform fast intersection check
}

The ST_ZMax function returns the maximum Z coordinate found within the provided geometry.

SELECT ST_ZMax(ST_Point(1, 2, 3));

Use ST_M to retrieve the M (measure) coordinate of a point geometry.

SELECT ST_M(ST_Point(1, 2, 3, 4))

Standard spatial predicate functions:

• ST_CoveredBy: Returns true if geom1 is covered by geom2.
• ST_Covers: Returns true if geom1 covers geom2.
• ST_Crosses: Returns true if geom1 crosses geom2.
• ST_Disjoint: Returns true if the geometries do not intersect.
• ST_Equals: Returns true if the geometries are equal.

BOOLEAN ST_CoveredBy (geom1 GEOMETRY, geom2 GEOMETRY)
BOOLEAN ST_Covers (geom1 GEOMETRY, geom2 GEOMETRY)
BOOLEAN ST_Crosses (geom1 GEOMETRY, geom2 GEOMETRY)
BOOLEAN ST_Disjoint (geom1 GEOMETRY, geom2 GEOMETRY)
BOOLEAN ST_Equals (geom1 GEOMETRY, geom2 GEOMETRY)

The ST_ZMin function returns the minimum Z coordinate found within the provided geometry.

SELECT ST_ZMin(ST_Point(1, 2, 3));

Use ST_Perimeter_Spheroid to calculate the perimeter in meters using an ellipsoidal model of the Earth.

Requirements:

• Input must be in EPSG:4326 (WGS84) with [latitude, longitude] axis order.
• Input must be a POLYGON, MULTIPOLYGON, or a GEOMETRYCOLLECTION containing polygons.

Note: This is highly accurate but is the slowest perimeter calculation method.