Using v4 of the Postgres H3 extension
I wrote about using the H3 extension last year in Using Uber's H3 hex grid in PostGIS and H3 indexes for performance with PostGIS data. Naturally, things have changed over the past 12 months, specifically version 4 of the H3 Postgres extension was released. The H3 Postgres extension (h3-pg) closely follows the upstream H3 project, including naming conventions. The changes made in H3 version 4 unfortunately changed every function name used in my original blog posts. It seems this mass renaming was a one-time alignment in the H3 project, hopefully they don't all get renamed again.
This post covers the changes required to migrate the code in my prior two posts work with version 4.x h3-pg.
Create the h3 extension
Creating the extension for PostGIS usage now involves installing two (2)
extensions. Some components have been split out into the
CASCADE when installing the
h3_postgis portion since that also
postgis_raster which I do not have installed by default.
CREATE EXTENSION h3; CREATE EXTENSION h3_postgis CASCADE;
It never hurts to know the exact version of the extensions you are using.
pg_extension relation in the
pg_catalog table has the information
to verify. The following query shows I installed h3-pg v4.1.2.
SELECT extname, extversion FROM pg_catalog.pg_extension WHERE extname IN ('h3', 'h3_postgis') ; ┌────────────┬────────────┐ │ extname │ extversion │ ╞════════════╪════════════╡ │ h3 │ 4.1.2 │ │ h3_postgis │ 4.1.2 │ └────────────┴────────────┘
Function mapping v3 to v4
This section lists the functions that changed between my prior blog posts
and this one.
The H3 3.x to 4.x migration notes includes a
section on changed function names. Be aware that the naming conventions are slightly different
from the main H3 project and the h3-pg Postgres extension. For example,
H3 has a function named
latLngToCell(). In h3-pg this function is
h3_lat_lng_to_cell(). The H3 project uses
and the h3-pg project changes it to
snake_case and appends the
The following table shows the old and new function names for the functions used in my prior posts.
|h3-pg v3.x||h3-pg v4.x|
* The more direct replacement for
h3_cell_to_center_child(). I listed
as the replacement because it was more appropriate for what the
code is trying to accomplish.
Example of the change
The following code block shows one of the original nested function calls using h3-pg version 3. This example builds the H3 hex geometries for familiar PostGIS uses.
h3_to_geo_boundary_geometry( h3_k_ring( h3_to_center_child(ix) ), True ) AS geom
The next code block shows the same functionality using the version 4
Note there are only 2 functions used, as I researched the conversion
I found I was able to simplify by using
instead of using both
h3_cell_to_boundary_geometry( h3_cell_to_children(ix) ) AS geom
One final change worth mentioning is that I noticed somewhere along the
way some geometries that were
POLYGON (I think...) now
MULTIPOLYGON instead. I didn't bother trying to pin down the underlying
changes, it could be a change in H3, or it could be I changed
something in my code without realizing. The important thing is
that the examples were adjusted to use
Reworked "Save the Hexes" code
This section provides the updated code from my original intro post, updated for h3-pg version 4.
DROP SCHEMA h3 CASCADE; CREATE SCHEMA h3; CREATE TABLE h3.hex ( ix H3INDEX NOT NULL PRIMARY KEY, resolution INT2 NOT NULL, -- v4 change: POLYGON to MULTIPOLYGON geom GEOMETRY (MULTIPOLYGON, 4326) NOT NULL, CONSTRAINT ck_resolution CHECK (resolution >= 0 AND resolution <= 15) ); CREATE INDEX gix_h3_hex ON h3.hex USING GIST (geom); INSERT INTO h3.hex (ix, resolution, geom) SELECT ix, 0 AS resolution, -- v4 change: Wrapped in MULTI for consistent MULTIPOLYGON ST_Multi(h3_cell_to_boundary_geometry(ix)) AS geom FROM h3_get_res_0_cells() ix ; INSERT INTO h3.hex (ix, resolution, geom) SELECT h3_cell_to_children(ix) AS ix, resolution + 1 AS resolution, -- v4 change: Wrapped in MULTI for consistent MULTIPOLYGON ST_Multi(h3_cell_to_boundary_geometry(h3_cell_to_children(ix))) AS geom FROM h3.hex WHERE resolution IN (SELECT MAX(resolution) FROM h3.hex) ;
Creating generated columns
Creating the generated H3 index columns as shown in
H3 indexes for performance with PostGIS data is updated to the following example.
This uses the renamed
ALTER TABLE osm.natural_point ADD h3_ix H3INDEX GENERATED ALWAYS AS ( h3_lat_lng_to_cell(ST_Transform(geom, 4326), 4) ) STORED ; CREATE INDEX gix_natural_point_h3_ix ON osm.natural_point (h3_ix);
This post has shown how to migrate queries from h3-pg version 3 to 4. These examples cover a handful of the functions included from H3, though hopefully sets the stage to make the migration easier.
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published May 22, 2023
Last Updated May 22, 2023