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 h3_postgis extension. I use CASCADE when installing the h3_postgis portion since that also requires 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. The 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 named h3_lat_lng_to_cell(). The H3 project uses CamelCase naming and the h3-pg project changes it to snake_case and appends the h3_ namespace.

The following table shows the old and new function names for the functions used in my prior posts.

* The more direct replacement for h3_to_center_child() is h3_cell_to_center_child(). I listed h3_cell_to_children() 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 functions. Note there are only 2 functions used, as I researched the conversion I found I was able to simplify by using h3_cell_to_children() instead of using both h3_cell_to_center_child() and h3_grid_disk().

h3_cell_to_boundary_geometry(
    h3_cell_to_children(ix)
    ) AS geom

Using MULTIPOLYGON

One final change worth mentioning is that I noticed somewhere along the way some geometries that were POLYGON (I think...) now are 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 MULTIPOLYGON.

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 h3_lat_lng_to_cell() function.

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);

Summary

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!