[gdal-dev] DuckDB queries in GDAL/OGR master branch

[Michael Smith]

With some recent commits to GDAL master branch, you can now do some amazing DuckDB queries in GDAL!

 

For example, h3 level 6 indexing for POIs in NH from Overture Maps

 

ogr2ogr -f parquet POI_NH_H3.parquet -nlt POINT -a_srs epsg:4326  -oo ADBC_DRIVER=libduckdb -oo PRELUDE_STATEMENTS="LOAD SPATIAL" -oo PRELUDE_STATEMENTS="load parquet" -oo PRELUDE_STATEMENTS="load httpfs" -oo PRELUDE_STATEMENTS="load aws" -oo PRELUDE_STATEMENTS="install h3 from community" -oo PRELUDE_STATEMENTS="load h3" -oo SQL="select h3_id, ST_GeomFromText(h3_cell_to_boundary_wkt(h3_id)) geometry, count(*) POI_count from (select id, h3_latlng_to_cell_string(st_y(geometry), st_x(geometry), 6) as h3_id from read_parquet('s3://overturemaps-us-west-2/release/2024-12-18.0/theme=places/type=place/*') where addresses[1].region = 'NH' and addresses[1].country='US' and bbox.xmin between -73 and -70 and bbox.ymin between  42 and 46) group by h3_id"  ADBC:

 

 

15.60s user  2.36s system 

48% cpu 

36.935 total

 

This is using gdal-master from conda on my mac arm64.

 

https://imgur.com/a/zGp5Mwg

 

https://imgur.com/a/1H5JwwZ

 

 

-- 

Michael Smith

Remote Sensing/GIS Center

US Army Corps of Engineers

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20241227/cee701cb/attachment.htm>