https://github.com/isaacbrodsky/h3-duckdb/actions/runs/6956834214 shows there are some actions used that are on deprecated node12. They should be upgraded to more recent versions to silence the warning.

Note this is also an issue upstream. https://github.com/duckdb/extension-template/actions/runs/6962748686

In my instance of DuckDB this query works fine:

select h3_h3_to_string(622236751406759935::ubigint);

However when I try to apply h3_h3_to_string to a column in my data with H3 cells stored, generated using h3_latlng_to_cell I get the following error:

????INVALID VALUE - Invalid unicode (byte sequence mismatch) detected in value construction?????

I am using DuckDB 0.7.1 and not the version that is packaged when building the H3 extension

For functions like h3_latlng_to_cell, a user could configure the extension to return VARCHAR instead of UBIGINT, if they want to take the debuggability/portability vs performance trade-off. This would apply to functions that currently return only UBIGINT because they cannot bind to the appropriate signature.

See here for an example of adding settings for an extension: https://github.com/duckdb/duckdb/blob/c89fe1055cfa9a98e3408b97657703334a1c9f1b/extension/httpfs/httpfs_extension.cpp#L20

Providing a struct latlng rather than an ordered pair makes it easier to be sure which coordinate is being used

https://duckdb.org/docs/sql/functions/nested#struct-functions struct_pact might be a reference for this

D install 'h3ext' from 'http://pub-cc26a6fd5d8240078bd0c2e0623393a5.r2.dev';
HTTP Error: Failed to download extension "h3ext" at URL "http://pub-cc26a6fd5d8240078bd0c2e0623393a5.r2.dev/v0.10.1/linux_amd64/h3ext.duckdb_extension.gz"

Candidate extensions: "s3", "http", "inet"

Perhaps this is because Cloudflare always redirects to HTTPS, and DuckDB does not support HTTPS? Trying to download from the same URL with scheme https gets misdetected as a local file path.

I think what is happening here is that DuckDB has an assumption that extensions are distributed via S3 and that S3 does not enforce HTTPS.

Depends on duckdb/duckdb#9835

• Tag releases, corresponding to particular upstream DuckDB releases.
• Github Actions should build artifacts for tags (and main) pushed on this repo
• Github Actions should publish to some way that DuckDB can directly load the extension
• CI should build all the "supported" architectures: https://duckdb.org/docs/extensions/overview.html#downloading-extensions-directly-from-s3

https://h3geo.org/docs/api/inspection

I want to convert the following location to an h3_7.

$ h3 latLngToCell \
    --resolution 7 \
    --latitude   70.63539478040273 \
    --longitude -160.04058837894056

This is the value I'm expecting:

870d1c130ffffff

The h3_7 produced by your extension doesn't match the above. I'm not sure if this is due to an integer casting issue or something else.

SELECT ST_X(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))) x,
       ST_Y(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))) y,
       h3_latlng_to_cell(
                    ST_X(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    ST_Y(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    9)::bigint as h3_7_uint64,
       printf('%X',
            h3_latlng_to_cell(
                    ST_X(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    ST_Y(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    9)::bigint) as h3_7;

┌─────────────────────┬───────────────────┬────────────────────┬─────────────────┐
│          x          │         y         │    h3_7_uint64     │      h3_7       │
│       double        │      double       │       int64        │     varchar     │
├─────────────────────┼───────────────────┼────────────────────┼─────────────────┤
│ -160.04058837894056 │ 70.63539478040273 │ 619503384080678911 │ 898EB0BA947FFFF │
└─────────────────────┴───────────────────┴────────────────────┴─────────────────┘

I tried flipping X and Y but this didn't help.

SELECT ST_X(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))) x,
       ST_Y(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))) y,
       h3_latlng_to_cell(
                    ST_Y(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    ST_X(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    9)::bigint as h3_7_uint64,
       printf('%X',
            h3_latlng_to_cell(
                    ST_Y(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    ST_X(ST_CENTROID(ST_GEOMFROMTEXT('POINT(-160.04058837894056 70.63539478040273)'))),
                    9)::bigint) as h3_7;

┌─────────────────────┬───────────────────┬────────────────────┬─────────────────┐
│          x          │         y         │    h3_7_uint64     │      h3_7       │
│       double        │      double       │       int64        │     varchar     │
├─────────────────────┼───────────────────┼────────────────────┼─────────────────┤
│ -160.04058837894056 │ 70.63539478040273 │ 617049409871151103 │ 890332B443BFFFF │
└─────────────────────┴───────────────────┴────────────────────┴─────────────────┘

It looks like it should be possible to define variants of the H3 functions that accept varchar or ubigint, so users do not need to always convert the types if they have indexes as strings.

Reference https://github.com/duckdb/duckdb/blob/master/src/function/scalar/list/list_concat.cpp#L75

Thanks for providing this amazing extension, it works wonderfully and provides a very valuable set of features!

Apologies if this is a silly question, but is it possible to distribute this extension as a pre-built binary, like other extensions in duckdb? If so, is that on your road map at some point?

Is it possible to add a method to install offline?

This options worked previously
install 'h3ext.duckdb_extension';

However without https access this doesn't work
INSTALL h3ext FROM 'https://pub-cc26a6fd5d8240078bd0c2e0623393a5.r2.dev';

Downloading manually the spatial extension - spatial.duckdb_extension
means it can be installed offline.

Thanks
Bevan

https://h3geo.org/docs/api/misc

What is the best way to convert a value like 610496184837996543 originating from h3_latlng_to_cell into 878eb0baa000000 as a varchar(15)?

My best attempt below results in INVALID VALUE.

SELECT h3_h3_to_string(
        h3_latlng_to_cell(
                ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                7)) h3_7_str,

       h3_latlng_to_cell(
                ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                7) h3_7,

       ST_ASTEXT(ST_CENTROID(ST_GEOMFROMTEXT(tile))) as centroid,

       ST_CENTROID(ST_GEOMFROMTEXT(tile)) centroid_wkb
FROM mobile_perf
LIMIT 1;

┌──────────────────────┬────────────────────┬────────────────────────────────┬────────────────────────────────────────────┐
│       h3_7_str       │        h3_7        │            centroid            │                centroid_wkb                │
│       varchar        │       uint64       │            varchar             │                 geography                  │
├──────────────────────┼────────────────────┼────────────────────────────────┼────────────────────────────────────────────┤
│ ????INVALID VALUE …  │ 610496184837996543 │ POINT(-160.04058837894056 70…  │ 0101000000B70400804C0164C03F78DE4EAAA85140 │
└──────────────────────┴────────────────────┴────────────────────────────────┴────────────────────────────────────────────┘

to avoid ST_Dump(geom).unnest(recursive := true) + flaten(list())...

https://h3geo.org/docs/api/traversal

Hi! First of all, fantastic work on this library. I've been using it a bit with DuckDB locally and it's super helpful for many analysis tasks.

Now I'm going a step further and attempting to build this library for the DuckDB Wasm distribution. After a bit of fiddling around with various build settings, I was successfully able to build a h3ext.duckdb_extension.wasm file which I can load into a Wasm instance of DuckDB with load 'h3ext.duckdb_extension.wasm'. Great!

Now I'm running into an issue when I try to call functions from this library. When I call any of the functions, I get a rather cryptic error message. For example:

TypeError: Cannot read properties of undefined (reading 'apply') at wasmImports.<computed>.stub._.<computed> (duckdb-browser-eh.worker.c067f4b4d5eff325690c.js:2:17523)

The interesting thing is that duckdb seems to have registered the functions correctly, because if I call them with the wrong types, it gives me a helpful error message:

So it seems that something is going on when the functions actually execute, or when results are returned.

I have no idea how to even begin to debug this... any ideas? Thanks!

duckdb -unsigned
v0.9.2 3c695d7ba9
Enter ".help" for usage hints.
D load h3ext;
[1] 133840 floating point exception (core dumped) duckdb -unsigned

x86_64 GNU/Linux

release build

(same commit works on Mac OSX)

The rest of the binding works with degrees, but it appears that h3_great_circle_distance expects inputs in radians.

Right now any invalid input in the table may result in an exception and the query failing. A noexcept version (returns H3Error or NULL on error?) would make that easier to use. This should probably be the default unless the user opts in to throwing, as it will terminate the entire query.

I've loaded a GeoJSON file into a DuckDB table that has two columns (own, geom) by leveraging the spatial extension.

I assume in order to perform a polyfill of polygons, I need to use the h3_polygon_wkt_to_cells.

In order to get a sense of the output, I tried executing the following SQL:

select h3_polygon_wkt_to_cells(geom,11) from nm_sma limit 1;

I'm seeing the following error message which I don't know how to interpret:

Error: Invalid Error: Invalid WKT: expected a hole loop or ')' at pos 990

Could someone point me in the right direction?

buffer1_g.json
buffer2_g.json
buffer3_g.json

These are 3 buffers around river Garonne (1000, 2000 and 3000 meters).
I want to get H3 cells for these buffers.

FROM st_read('buffer2_g.json')
SELECT geom.h3_polygon_wkt_to_cells(8);	

returns an empty list, which is unexpected.

FROM st_read('buffer1_g.json')
SELECT geom.h3_polygon_wkt_to_cells(8);	

returns a list with 2 443 cells: OK.

FROM st_read('buffer3_g.json')
SELECT geom.h3_polygon_wkt_to_cells(8);	

returns a list with 5 479 cells: OK.

https://h3geo.org/docs/api/vertex

I suspect having users compile DuckDB and your extension might be off-putting. The process usually took over an hour for me.

It would be nice if I could instruct my readers to launch DuckDB with an unsigned flag and install a binary from GitHub.

https://h3geo.org/docs/api/regions

Firstly, thanks for the great extension. Just wondering if it is possible to use this extension along side the python version of DuckDB.

I found the following bug when translating between the h3 hex and int format. It would be great if you can fix the bug or supply me with information about my wrongdoing.

Details:

• The representation of the resulting string has the wrong format
• The referenced h3Index ("85283473fffffff") should point to (37.34579337536848, -121.9763759725512)

https://github.com/duckdb/duckdb/releases/tag/v0.9.0

When running h3_latlng_to_cell it is possible to swap x, and y.

This means that values outside the range when swapped

-90 <-> 90 and -180 <-> 180

This leads to NULL values breaking downstream silently

https://h3geo.org/docs/api/hierarchy

Unfortunately I missed a v0.10.1 when releasing v0.10.2, so the old binaries appear to have been inadvertently overwritten: https://github.com/isaacbrodsky/h3-duckdb/actions/runs/8839528267/job/24273760316

The DuckDB build part of CI takes a very long time; it might be best to cache that build and only retrigger it when tracking a newer upstream version. #3 changes the Makefile to no longer automatically pull in new revisions from upstream since I would like that to be a conscious action.

https://h3geo.org/docs/api/uniedge

https://github.com/duckdb/duckdb/releases/tag/v0.8.1

It can use multiple cores by default and is much faster. Without it, DuckDB alone can take over an hour to compile.

$ GEN=ninja make duckdb_release release

I am getting this error when I am trying to build the bindings in my Windows operating system. When I tried to build the extension, I got this error.
I am not an expert on building extensions, but I couldn’t find any solution.
I am running on Windows 64-bit operating system.
Thanks.

Hi I cant load Extension.
I've tried with https://pub-cc26a6fd5d8240078bd0c2e0623393a5.r2.dev/v0.10.1/linux_amd64/h3ext.duckdb_extension.gz and with https://pub-cc26a6fd5d8240078bd0c2e0623393a5.r2.dev/v0.10.1/linux_amd64/h3ext.duckdb_extension.gz

jtorres@europa2:~/$ duckdb -unsigned
-- Loading resources from /home/jtorres/.duckdbrc
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
D install 'h3ext.duckdb_extension';
D load 'h3ext';
Violación de segmento (`core' generado)

It works only if I build from sources and run with bundled duckdb

Thank you for your work

I tried to follow the readme to install the h3 extension. However I failed at running CMAKE_BUILD_PARALLEL_LEVEL=4 make duckdb_release release at the terminal, which gives me the error 'CMAKE_BUILD_PARALLEL_LEVEL' is not recognized as an internal or external command, operable program or batch file.

I have cmake 3.13.1 installed, and my system is windows 11

Perhaps something like DuckDB's documentation where they show each function with example parameters and the output. It would help people skimming through documentation or looking for a function of interest quicker. It could also result in more of the codebase being used by strangers and potentially uncovering new issues.

https://duckdb.org/docs/sql/functions/timestamp