Downloads 6.4kthis week

GitHub stars 9

Extension descriptor (YAML)

DuckDB extension for converting GEOMETRY/WKB to and from GeoArrow native encodings, powered by geoarrow-c.

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL duck_geoarrow FROM community;
LOAD duck_geoarrow;

Example

INSTALL duck_geoarrow FROM community;
LOAD duck_geoarrow;

-- Read a GeoArrow-encoded GeoParquet file written by geopandas. A geometry column
-- written with encoding="polygon" arrives as STRUCT(x DOUBLE, y DOUBLE)[][].
SELECT county_name, st_geomfromgeoarrow('polygon', geometry) AS geom
FROM read_parquet('county_regions.parquet');

-- Write GEOMETRY back out as a GeoArrow native encoding
COPY (SELECT county_name, st_asgeoarrowpolygon(geom) AS geometry FROM counties)
TO 'counties.parquet';

-- GEOMETRY -> GeoArrow native encoding, one function per geometry type
SELECT st_asgeoarrowpoint('POINT(30 10)'::GEOMETRY);
-- {'x': 30.0, 'y': 10.0}

SELECT st_asgeoarrowpolygon('POLYGON((0 0, 1 0, 1 1, 0 0))'::GEOMETRY);
-- [[{'x': 0.0, 'y': 0.0}, {'x': 1.0, 'y': 0.0}, {'x': 1.0, 'y': 1.0}, {'x': 0.0, 'y': 0.0}]]

-- GeoArrow native encoding -> GEOMETRY; the type name disambiguates colliding shapes
SELECT st_geomfromgeoarrow('linestring', [{'x': 0.0, 'y': 0.0}, {'x': 1.0, 'y': 1.0}]),
       st_geomfromgeoarrow('multipoint', [{'x': 0.0, 'y': 0.0}, {'x': 1.0, 'y': 1.0}]);
-- LINESTRING (0 0, 1 1)  |  MULTIPOINT (0 0, 1 1)

-- Z and M: reading infers them from the value, writing takes them as an argument
SELECT st_geomfromgeoarrow('point', {'x': 1.0, 'y': 2.0, 'z': 3.0});
-- POINT Z (1 2 3)
SELECT st_asgeoarrowpoint('POINT Z (1 2 3)'::GEOMETRY, 'xyz');
-- {'x': 1.0, 'y': 2.0, 'z': 3.0}

-- Flat struct form: one struct per geometry with parallel coordinate arrays
SELECT st_asgeoarrow('POINT(30 10)'::GEOMETRY);
-- {'geometry_type': 1, 'xs': [30.0], 'ys': [10.0], 'ring_offsets': [], 'geom_offsets': []}

SELECT duck_geoarrow_version();

About duck_geoarrow

duck_geoarrow converts between DuckDB GEOMETRY (or WKB BLOB) and the GeoArrow coordinate encodings, powered by the geoarrow-c library.

Two GeoArrow representations are supported:

• Native encodings: nested lists of separated coordinates (STRUCT(x, y)[][] for a Polygon, and so on). This is what GeoParquet, geoarrow-pyarrow and geopandas produce, so it is the one to reach for when reading or writing files.
• A flat struct: one struct per geometry holding parallel coordinate and offset arrays (geometry_type, xs, ys, optional zs / ms, ring_offsets, geom_offsets). Convenient for hand-building geometries in SQL.

Both directions support XY, XYZ, XYM and XYZM.

Functions

<type> is one of point, linestring, polygon, multipoint, multilinestring or multipolygon. All conversion functions accept either GEOMETRY or a BLOB holding WKB.

Native encodings

These are the separated-coordinate layouts from the GeoArrow specification, so the values interoperate directly with other GeoArrow implementations.

The generic st_geomfromgeoarrow(type, value) exists because the DuckDB types collide: LineString and MultiPoint are both STRUCT(x, y)[], and Polygon and MultiLineString are both STRUCT(x, y)[][]. The type name is resolved at bind time, so an unknown type or a mismatched shape is a binder error rather than a runtime one. Names are case- and separator-insensitive and may carry a z, m or zm suffix.

Z and M dimensions

GeoArrow carries extra ordinates in the coordinate struct (STRUCT(x, y, z), STRUCT(x, y, m), STRUCT(x, y, z, m)), and the flat struct gains matching zs / ms lists. Reading needs no extra argument since the value's type says which ordinates are present. Writing takes the dimensions as an argument (xy, xyz, xym, xyzm) because a SQL function's return type must be fixed before any data is seen; omitting it means xy. Dropping ordinates is allowed, inventing them is not: asking for xyz from a 2D geometry raises an error rather than filling in NaNs.

Notes

• NULL input produces NULL output. A NULL inside a list (a null ring, say) is read as an empty ring, since the GeoArrow native encoding only carries validity at the top level.
• GeometryCollection, and the geoarrow.box and interleaved-coordinate encodings, are not supported.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings