Downloads 1.2kthis week

GitHub stars 9

Extension descriptor (YAML)

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

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

This extension does not add any settings.