This is a DuckDB extension that adds support for the H3 discrete global grid system, so you can index points and geometries to hexagons in SQL.

Get started

Load from the community extensions repository:

INSTALL h3 FROM community;
LOAD h3;

Test running an H3 function:

SELECT h3_cell_to_latlng('822d57fffffffff');

Or, using the integer API, which generally has better performance:

SELECT h3_cell_to_latlng(586265647244115967);

Implemented functions

This extension implements the entire H3 API. The full list of functions is below.

All functions support H3 indexes specified as UBIGINT (uint64) or BIGINT (int64), but the unsigned one is preferred and is returned when the extension can't detect which one to use. The unsigned and signed APIs are identical. All functions also support VARCHAR H3 index input and output.

Full list of functions

Function	Description
h3_latlng_to_cell	Convert latitude/longitude coordinate to cell ID
h3_latlng_to_cell_string	Convert latitude/longitude coordinate to cell ID (returns VARCHAR)
h3_cell_to_lat	Convert cell ID to latitude
h3_cell_to_lng	Convert cell ID to longitude
h3_cell_to_latlng	Convert cell ID to latitude/longitude
h3_cell_to_boundary_wkt	Convert cell ID to cell boundary
h3_get_resolution	Get resolution number of cell ID
h3_get_base_cell_number	Get base cell number of cell ID
h3_string_to_h3	Convert VARCHAR cell ID to UBIGINT
h3_h3_to_string	Convert BIGINT or UBIGINT cell ID to VARCHAR
h3_is_valid_cell	True if this is a valid cell ID
h3_is_res_class_iii	True if the cell's resolution is class III
h3_is_pentagon	True if the cell is a pentagon
h3_get_icosahedron_faces	List of icosahedron face IDs the cell is on
h3_cell_to_parent	Get coarser cell for a cell
h3_cell_to_children	Get finer cells for a cell
h3_cell_to_center_child	Get the center finer cell for a cell
h3_cell_to_child_pos	Get a sub-indexing number for a cell inside a parent
h3_child_pos_to_cell	Convert parent and sub-indexing number to a cell ID
h3_compact_cells	Convert a set of single-resolution cells to the minimal mixed-resolution set
h3_uncompact_cells	Convert a mixed-resolution set to a single-resolution set of cells
h3_grid_disk	Find cells within a grid distance
h3_grid_disk_distances	Find cells within a grid distance, sorted by distance
h3_grid_disk_unsafe	Find cells within a grid distance, with no pentagon distortion
h3_grid_disk_distances_unsafe	Find cells within a grid distance, sorted by distance, with no pentagon distortion
h3_grid_disk_distances_safe	Find cells within a grid distance, sorted by distance
h3_grid_ring_unsafe	Find cells exactly a grid distance away, with no pentagon distortion
h3_grid_path_cells	Find a grid path to connect two cells
h3_grid_distance	Find the grid distance between two cells
h3_cell_to_local_ij	Convert a cell ID to a local I,J coordinate space
h3_local_ij_to_cell	Convert a local I,J coordinate to a cell ID
h3_cell_to_vertex	Get the vertex ID for a cell ID and vertex number
h3_cell_to_vertexes	Get all vertex IDs for a cell ID
h3_vertex_to_lat	Convert a vertex ID to latitude
h3_vertex_to_lng	Convert a vertex ID to longitude
h3_vertex_to_latlng	Convert a vertex ID to latitude/longitude coordinate
h3_is_valid_vertex	True if passed a valid vertex ID
h3_is_valid_directed_edge	True if passed a valid directed edge ID
h3_origin_to_directed_edges	Get all directed edge IDs for a cell ID
h3_directed_edge_to_cells	Convert a directed edge ID to origin/destination cell IDs
h3_get_directed_edge_origin	Convert a directed edge ID to origin cell ID
h3_get_directed_edge_destination	Convert a directed edge ID to destination cell ID
h3_cells_to_directed_edge	Convert an origin/destination pair to directed edge ID
h3_are_neighbor_cells	True if the two cell IDs are directly adjacent
h3_directed_edge_to_boundary_wkt	Convert directed edge ID to linestring WKT
h3_get_hexagon_area_avg	Get average area of a hexagon cell at resolution
h3_cell_area	Get the area of a cell ID
h3_get_hexagon_edge_length_avg	Average hexagon edge length at resolution
h3_edge_length	Get the length of a directed edge ID
h3_get_num_cells	Get the number of cells at a resolution
h3_get_res0_cells	Get all resolution 0 cells
h3_get_res0_cells_string	Get all resolution 0 cells (returns VARCHAR)
h3_get_pentagons	Get all pentagons at a resolution
h3_get_pentagons_string	Get all pentagons at a resolution (returns VARCHAR)
h3_great_circle_distance	Compute the great circle distance between two points (haversine)
h3_cells_to_multi_polygon_wkt	Convert a set of cells to multipolygon WKT
h3_polygon_wkt_to_cells	Convert polygon WKT to a set of cells
h3_polygon_wkt_to_cells_string	Convert polygon WKT to a set of cells (returns VARCHAR)

Alternative download / install

If you'd like to install the H3 extension from the version published here, rather than the community extension version, you will need to run DuckDB with the unsigned option:

duckdb -unsigned

Load the extension:

INSTALL h3 FROM 'https://pub-cc26a6fd5d8240078bd0c2e0623393a5.r2.dev';
LOAD h3;

If you want to directly download the latest version of the extension: Linux AMD64 Linux AMD64 GCC4 Linux Arm64 OSX AMD64 OSX Arm64 wasm eh wasm mvp wasm threads Windows AMD64

Development

To build, type:

git submodule update --init
GEN=ninja make release

You will need Git, CMake, and a C compiler. The build instructions suggest using ninja because it enables parallelism by default. Using make instead is fine, but you will want to enable the following parallelism option, because building DuckDB can take a very long time (>=1 hour is not unusual). Run the below replacing 4 with the number of CPU cores on your machine.

CMAKE_BUILD_PARALLEL_LEVEL=4 make duckdb_release release

To run, run the bundled duckdb shell:

./build/release/duckdb -unsigned

Load the extension:

load 'build/release/extension/h3/h3.duckdb_extension';

To run tests:

make test

To update the submodules to latest upstream, run:

make update_deps

License

h3-duckdb Copyright 2022 Isaac Brodsky. Licensed under the Apache 2.0 License.

H3 Copyright 2018 Uber Technologies Inc. (Apache 2.0 License)

DGGRID Copyright (c) 2015 Southern Oregon University

DuckDB Copyright 2018-2022 Stichting DuckDB Foundation (MIT License)

DuckDB extension-template Copyright 2018-2022 DuckDB Labs BV (MIT License)