duckdb / duckdb/duckdb-spatial
H3 values modified when exported as JSON but not as CSV
- Dominant language
- C
- Stars
- 708
- Forks
- 96
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 5
Description
I've compiled the H3 extension for DuckDB and loaded it into the DuckDB binary with your extension.
```bash
$ git clone https://github.com/isaacbrodsky/h3-duckdb ./duckdb_h3
$ cd ./duckdb_h3
$ git submodule update --init
$ GEN=ninja \
make release
```
```bash
$ /Volumes/Seagate/duckdb_spatial/build/release/duckdb -unsigned aus1.duckdb
```
```sql
install '/Volumes/Seagate/duckdb_h3/build/release/h3.duckdb_extension';
load h3;
load json;
```
I'm able to pull an example record where the H3_7 value starts with ``87be``.
```sql
.mode line
SELECT geom,
st_centroid(geom) centroid,
st_x(st_centroid(geom)) x,
st_y(st_centroid(geom)) y,
printf('%x', h3_latlng_to_cell(
ST_Y(ST_CENTROID(geom)),
ST_X(ST_CENTROID(geom)),
7)::bigint) h3_7_yx
from roads2
limit 1;
```
```
geom = LINESTRING (147.084939479828 -30.7773677126431, 147.0849609375 -30.7773584948334, 147.084971666336 -30.7773400592114)
centroid = POINT (147.08495789089685 -30.777356503454)
x = 147.08495789089685
y = -30.777356503454
h3_7_yx = 87beee8a3ffffff
```
`87beee8a3ffffff` is in Australia as I expected. https://what-the-h3index.vercel.app/
I then exported that table with the aggregate counts for each H3_7.
```sql
COPY (
SELECT printf('%x',
h3_latlng_to_cell(
ST_Y(ST_CENTROID(geom)),
ST_X(ST_CENTROID(geom)),
7)::bigint) as h3_7,
count(*) count
FROM roads2
GROUP BY 1
) TO 'aus_h3.json';
```
None of the exported records start with `87be`, for some reason only H3_7s prefixed with `87[9ac]` are exporting and none of these are anywhere near Australia.
```bash
$ jq -S . aus_h3.json | grep h3 | cut -c1-15 | sort | uniq -c
```
```
151 "h3_7": "8798
469 "h3_7": "8799
841 "h3_7": "87a4
144 "h3_7": "87a5
520 "h3_7": "87c4
357 "h3_7": "87c5
```
```bash
$ head aus_h3.json
```
```json
{"h3_7":"87c529c10ffffff","count":6287}
{"h3_7":"87a4c5d6effffff","count":2441}
{"h3_7":"87c431431ffffff","count":19481}
{"h3_7":"87a4c0860ffffff","count":12708}
{"h3_7":"87a4de11cffffff","count":2375}
{"h3_7":"87c519272ffffff","count":4185}
{"h3_7":"87c52b8c4ffffff","count":5311}
{"h3_7":"87a4de95effffff","count":1665}
{"h3_7":"87c433b98ffffff","count":10999}
{"h3_7":"87c52b80dffffff","count":4654}
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.