Better DuckDB-Wasm Integration

[kylebarron]

DuckDB is the standard now for client-side geometry operations. We should improve the story for integrating with and rendering from DuckDB.

Ideally, our DuckDB integration would work without any required changes to the user's queries. The user should be able to perform any normal query against DuckDB and have it render data automatically, ideally.

How should we parse geometry data from DuckDB?

DuckDB exports geometry data as WKB. This means we need a way to parse WKB. See discussion in geoarrow-js (geoarrow/geoarrow-js#29, geoarrow/geoarrow-js#46, geoarrow/geoarrow-js#44, geoarrow/geoarrow-js#45).

Alternatively, @am2222 suggested in #172 (comment) that we have a DuckDB extension using geoarrow-c. While this would be possible, it would require the user to change their DuckDB queries. I also wouldn't be able to build it myself because I don't know C/C++.

Document support for native geometry formats

DuckDB also has native geoarrow-like formats, like POINT_2D, etc. We should document how to render from these formats.

Create examples for rendering from DuckDB

Ideally on observable notebooks

[am2222]

@kylebarron thanks for the new issue! So I am not aware of POINT_2D and if it supports other geometry types, but as for the comment about geoarrow-c

• It would require the user to change their DuckDB queries.-> yes unfortunately
• I also wouldn't be able to build it myself because I don't know C/C++-> I can address this and try to see if I can publish a duckdb community extension if you think this approach is more sustainable and better!

Do you know if Duckdb spatial team are planning to address this issue from their end?

[kylebarron]

See https://duckdb.org/docs/current/core_extensions/spatial/overview#the-geometry-type.

Besides operating on GEOMETRY, 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 STRUCT and LIST

I don't think DuckDB Spatial cares to "address" this from their end, because WKB is valid GeoArrow; just not the type of GeoArrow that geoarrow/deck.gl-layers requires

[am2222]

@kylebarron I published this initial extension (https://duckdb.org/community_extensions/extensions/duck_geoarrow)

initial tests
https://codepen.io/Am3333/pen/dPprbax

It still needs some column mapping, unfortunetly it deos not work with mixed geometry columns very well! :(

[kylebarron]

That looks cool. Perhaps it's worth putting that on the radar of the duckdb-spatial maintainers and see if they have feedback.

I started working on generic TypeScript WKB to GeoArrow in geoarrow/geoarrow-js#54 and geoarrow/geoarrow-js#55

[am2222]

awesome, thanks! I guess it opens up multiple options for developers!

[Maxxen]

Hola!

Id be happy to try to help on the duckdb-side as well, but If I remember right there is currently a limitation in DuckDB where only one type can register automatic conversion to/from specific arrow metadata, and so for geoarrow that is currently occupied by GEOMETRY. It's a kind of arbitrary limitation that should be addressed at some point though, but may require some messing around in core DuckDB.