h3-pg PostgreSQL Extension
Extension Architecture
Two extensions:
CREATE EXTENSION h3 — Core H3 bindings (indexing, traversal, hierarchy, inspection, <-> operator). No PostGIS dependency.
CREATE EXTENSION h3_postgis — PostGIS integration (geometry/geography casts, @ operator, polygon fill, raster helpers). Requires postgis and optionally postgis_raster.
Postgres-Specific H3 Features
These exist only in h3-pg, not other H3 language bindings.
Direct casts (h3_postgis):
h3index::geometry → cell centroid as POINT, SRID 4326
h3index::geography → cell centroid as POINT, SRID 4326
Operators:
a <-> b — grid distance in cells between two h3index values (h3)
geom @ resolution — index geometry/geography at resolution (h3_postgis)
Inspection and construction helpers (h3):
h3_get_index_digit(cell, resolution) — inspect the digit at a 1-based resolution step
h3_construct_cell(resolution, base_cell_number, digits[]) — rebuild a cell from explicit components
h3_is_valid_index(index) — validate any H3 index mode: cell, directed edge, or vertex
Directed edge convenience (h3):
h3_reverse_directed_edge(edge) — returns the same directed edge with origin and destination reversed
Operator classes for h3index: BTREE, HASH, BRIN, SP-GIST (h3)
Boundary helpers (h3_postgis):
h3_cell_to_boundary_geometry(h3index) → polygon with SRID 4326, splits at antimeridian
h3_cell_to_boundary_geography(h3index) → geography polygon, splits at antimeridian
Polygon fill (h3_postgis):
h3_polygon_to_cells(geometry, resolution) → SETOF h3index
h3_polygon_to_cells(geography, resolution) → SETOF h3index
Raster integration (h3_postgis, requires postgis_raster):
h3_raster_summary(raster, resolution, [band]) — per-cell stats (count, sum, mean, stddev, min, max)
h3_raster_summary_clip(...) — clips raster by cell boundaries
h3_raster_summary_centroids(...) — uses cell centroids, avoids pentagon edge cases
h3_raster_summary_subpixel(...) — for cells smaller than pixels
h3_raster_class_summary*(...) — discrete class counts per cell
Tile helper (h3_postgis):
h3_get_resolution_from_tile_zoom(z, [max_h3_resolution=15], [min_h3_resolution], [hex_edge_pixels=44], [tile_size=512]) — returns optimal H3 resolution for XYZ tile zoom level z, targeting hexagons approximately hex_edge_pixels wide on screen
Common Pattern Fixes
| Pattern |
DO NOT |
DO |
| Get centroid |
ST_Centroid(h3_cell_to_boundary_geometry(h3)) |
h3::geometry |
| Get boundary with SRID |
h3_cell_to_boundary(h3)::geometry |
h3_cell_to_boundary_geometry(h3) |
| Distance check (meters) |
ST_Distance(a.h3::geography, b.h3::geography) <= N |
ST_DWithin(a.h3::geography, b.h3::geography, N) |
| Grid distance (cells) |
h3_distance(a, b) |
a <-> b |
Antimeridian gotcha: Cells crossing 180° are split into valid polygons. ST_Centroid of a split polygon may fall outside the cell — use h3::geometry for centroids instead.
Ring traversal gotcha: Prefer h3_grid_ring(origin, k) for rings because it handles pentagon distortion internally. Use h3_grid_ring_unsafe(origin, k) only when fail-fast behavior or ring-walk ordering matters.
Critical v3 → v4 Renames
| v3 Name (DO NOT USE) |
v4 Replacement |
h3_geo_to_h3 |
h3_latlng_to_cell |
h3_to_geo |
h3_cell_to_latlng |
h3_to_geo_boundary |
h3_cell_to_boundary |
h3_k_ring |
h3_grid_disk |
h3_k_ring_distances |
h3_grid_disk_distances |
h3_hex_ring |
h3_grid_ring_unsafe |
h3_line |
h3_grid_path_cells |
h3_distance |
h3_grid_distance |
h3_to_parent |
h3_cell_to_parent |
h3_to_children |
h3_cell_to_children |
h3_compact |
h3_compact_cells |
h3_uncompact |
h3_uncompact_cells |
h3_polyfill |
h3_polygon_to_cells |
h3_indexes_are_neighbors |
h3_are_neighbor_cells |
h3_hex_area |
h3_get_hexagon_area_avg |
h3_hex_area_km2 / h3_hex_area_m2 |
h3_get_hexagon_area_avg(res, unit) |
h3_edge_length_km / h3_edge_length_m |
h3_get_hexagon_edge_length_avg(res, unit) |
Unit parameters (v4): Area and length functions now take a unit text parameter:
h3_get_hexagon_area_avg(resolution, [unit = 'km^2']) — use 'km^2' or 'm^2'
h3_get_hexagon_edge_length_avg(resolution, [unit = 'km']) — use 'km' or 'm'
h3_cell_area(cell, [unit = 'km^2']) — exact area for specific cell
h3_edge_length(edge, [unit = 'km']) — exact length for specific edge
Spelling change (v4.2.3+): Prefer h3_latlng_to_cell over h3_lat_lng_to_cell (underscore version deprecated). Same for h3_cell_to_latlng, h3_vertex_to_latlng.
Documentation
1---2name: h3-pg3description: PostgreSQL bindings for H3 hexagonal grid system. Use when working with H3 cells in Postgres, including spatial indexing, geometry/geography integration, and raster analysis.4metadata:5 source: https://skillmd.com/skills/postgis/h3-pg6---78# h3-pg PostgreSQL Extension910## Extension Architecture1112**Two extensions:**13- `CREATE EXTENSION h3` — Core H3 bindings (indexing, traversal, hierarchy, inspection, `<->` operator). No PostGIS dependency.14- `CREATE EXTENSION h3_postgis` — PostGIS integration (geometry/geography casts, `@` operator, polygon fill, raster helpers). Requires `postgis` and optionally `postgis_raster`.1516## Postgres-Specific H3 Features1718These exist only in h3-pg, not other H3 language bindings.1920**Direct casts** (`h3_postgis`):21- `h3index::geometry` → cell centroid as POINT, SRID 432622- `h3index::geography` → cell centroid as POINT, SRID 43262324**Operators:**25- `a <-> b` — grid distance in cells between two h3index values (`h3`)26- `geom @ resolution` — index geometry/geography at resolution (`h3_postgis`)2728**Inspection and construction helpers** (`h3`):29- `h3_get_index_digit(cell, resolution)` — inspect the digit at a 1-based resolution step30- `h3_construct_cell(resolution, base_cell_number, digits[])` — rebuild a cell from explicit components31- `h3_is_valid_index(index)` — validate any H3 index mode: cell, directed edge, or vertex3233**Directed edge convenience** (`h3`):34- `h3_reverse_directed_edge(edge)` — returns the same directed edge with origin and destination reversed3536**Operator classes** for `h3index`: BTREE, HASH, BRIN, SP-GIST (`h3`)3738**Boundary helpers** (`h3_postgis`):39- `h3_cell_to_boundary_geometry(h3index)` → polygon with SRID 4326, splits at antimeridian40- `h3_cell_to_boundary_geography(h3index)` → geography polygon, splits at antimeridian4142**Polygon fill** (`h3_postgis`):43- `h3_polygon_to_cells(geometry, resolution)` → SETOF h3index44- `h3_polygon_to_cells(geography, resolution)` → SETOF h3index4546**Raster integration** (`h3_postgis`, requires `postgis_raster`):47- `h3_raster_summary(raster, resolution, [band])` — per-cell stats (count, sum, mean, stddev, min, max)48- `h3_raster_summary_clip(...)` — clips raster by cell boundaries49- `h3_raster_summary_centroids(...)` — uses cell centroids, avoids pentagon edge cases50- `h3_raster_summary_subpixel(...)` — for cells smaller than pixels51- `h3_raster_class_summary*(...)` — discrete class counts per cell5253**Tile helper** (`h3_postgis`):54- `h3_get_resolution_from_tile_zoom(z, [max_h3_resolution=15], [min_h3_resolution], [hex_edge_pixels=44], [tile_size=512])` — returns optimal H3 resolution for XYZ tile zoom level `z`, targeting hexagons approximately `hex_edge_pixels` wide on screen5556## Common Pattern Fixes5758| Pattern | DO NOT | DO |59|---------|--------|----|60| Get centroid | `ST_Centroid(h3_cell_to_boundary_geometry(h3))` | `h3::geometry` |61| Get boundary with SRID | `h3_cell_to_boundary(h3)::geometry` | `h3_cell_to_boundary_geometry(h3)` |62| Distance check (meters) | `ST_Distance(a.h3::geography, b.h3::geography) <= N` | `ST_DWithin(a.h3::geography, b.h3::geography, N)` |63| Grid distance (cells) | `h3_distance(a, b)` | `a <-> b` |6465**Antimeridian gotcha:** Cells crossing 180° are split into valid polygons. `ST_Centroid` of a split polygon may fall outside the cell — use `h3::geometry` for centroids instead.6667**Ring traversal gotcha:** Prefer `h3_grid_ring(origin, k)` for rings because it handles pentagon distortion internally. Use `h3_grid_ring_unsafe(origin, k)` only when fail-fast behavior or ring-walk ordering matters.6869## Critical v3 → v4 Renames7071| v3 Name (DO NOT USE) | v4 Replacement |72|---------------------|----------------|73| `h3_geo_to_h3` | `h3_latlng_to_cell` |74| `h3_to_geo` | `h3_cell_to_latlng` |75| `h3_to_geo_boundary` | `h3_cell_to_boundary` |76| `h3_k_ring` | `h3_grid_disk` |77| `h3_k_ring_distances` | `h3_grid_disk_distances` |78| `h3_hex_ring` | `h3_grid_ring_unsafe` |79| `h3_line` | `h3_grid_path_cells` |80| `h3_distance` | `h3_grid_distance` |81| `h3_to_parent` | `h3_cell_to_parent` |82| `h3_to_children` | `h3_cell_to_children` |83| `h3_compact` | `h3_compact_cells` |84| `h3_uncompact` | `h3_uncompact_cells` |85| `h3_polyfill` | `h3_polygon_to_cells` |86| `h3_indexes_are_neighbors` | `h3_are_neighbor_cells` |87| `h3_hex_area` | `h3_get_hexagon_area_avg` |88| `h3_hex_area_km2` / `h3_hex_area_m2` | `h3_get_hexagon_area_avg(res, unit)` |89| `h3_edge_length_km` / `h3_edge_length_m` | `h3_get_hexagon_edge_length_avg(res, unit)` |9091**Unit parameters (v4):** Area and length functions now take a `unit` text parameter:92- `h3_get_hexagon_area_avg(resolution, [unit = 'km^2'])` — use `'km^2'` or `'m^2'`93- `h3_get_hexagon_edge_length_avg(resolution, [unit = 'km'])` — use `'km'` or `'m'`94- `h3_cell_area(cell, [unit = 'km^2'])` — exact area for specific cell95- `h3_edge_length(edge, [unit = 'km'])` — exact length for specific edge9697**Spelling change (v4.2.3+):** Prefer `h3_latlng_to_cell` over `h3_lat_lng_to_cell` (underscore version deprecated). Same for `h3_cell_to_latlng`, `h3_vertex_to_latlng`.9899## Documentation100101- API reference: https://github.com/postgis/h3-pg/blob/main/docs/api.md102- v3→v4 migration: https://h3geo.org/docs/library/migration-3.x/functions