h3

H3 is Uber's Hexagonal Hierarchical Spatial Index. Full documentation of the project can be found at h3geo. You can also learn more about H3 in the Spatial Indexes section of this documentation.

Oracle output conventions

Array-returning H3 functions in Oracle are pipelined and return nested-table types — H3_INDEX_ARRAY for cell IDs, H3_DISTANCE_ARRAY for (h3, distance) pairs. Consume them with TABLE(...):

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_TOCHILDREN('83390cfffffffff', 4));

H3_COMPACT and H3_UNCOMPACT accept the same H3_INDEX_ARRAY type as input — pass cell IDs with carto.H3_INDEX_ARRAY('a', 'b', ...) or by piping another nested-table result through CAST(MULTISET(...) AS carto.H3_INDEX_ARRAY).

Boolean-style functions (H3_ISVALID, H3_ISPENTAGON) return NUMBER constrained to 1/0, since Oracle SQL has no BOOLEAN. Compare with = 1 rather than using truthy predicates.

H3_BOUNDARY

H3_BOUNDARY(index)

Description

Returns a geometry representing the H3 cell. It will return null on error (invalid input).

Input parameters

• index: VARCHAR2(16) The H3 cell index as hexadecimal.

Return type

SDO_GEOMETRY (polygon, SRID 4326)

Example

SELECT SDO_UTIL.TO_WKTGEOMETRY(carto.H3_BOUNDARY('84390cbffffffff'))
FROM DUAL;
-- POLYGON ((-3.5769274353957314 40.613438595935165, -3.85975632308016 40.525472355369885, ...

H3_CENTER

H3_CENTER(index)

Description

Returns the center of the H3 cell as a point geometry. It will return null on error (invalid input).

Input parameters

• index: VARCHAR2(16) The H3 cell index.

Return type

SDO_GEOMETRY (point, SRID 4326)

Example

SELECT SDO_UTIL.TO_WKTGEOMETRY(carto.H3_CENTER('84390cbffffffff'))
FROM DUAL;
-- POINT (-3.6176032466282892 40.37254058216577)

H3_COMPACT

H3_COMPACT(indexArray)

Description

Returns a set of hexagons across multiple resolutions that represent the same area as the input set of hexagons.

Input parameters

• indexArray: H3_INDEX_ARRAY of H3 cell indices as hexadecimal.

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

Example

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_COMPACT(carto.H3_INDEX_ARRAY(
    '85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff',
    '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff',
    '85390cbbfffffff'
)));
-- 84390cbffffffff

To pipe the output of another nested-table function into H3_COMPACT, cast it via MULTISET:

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_COMPACT(
    CAST(MULTISET(
        SELECT COLUMN_VALUE
        FROM TABLE(carto.H3_TOCHILDREN('84390cbffffffff', 5))
    ) AS carto.H3_INDEX_ARRAY)
));
-- 84390cbffffffff

H3_DISTANCE

H3_DISTANCE(origin, destination)

Description

Returns the grid distance between two hexagon indexes. This function may fail to find the distance between two indexes if they are very far apart or on opposite sides of a pentagon. Returns null on failure or invalid input. The two cells must share the same resolution.

Input parameters

• origin: VARCHAR2(16) The H3 cell index as hexadecimal.

• destination: VARCHAR2(16) The H3 cell index as hexadecimal.

Return type

NUMBER

Example

SELECT carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff') FROM DUAL;
-- 1

tip

If you want the distance in meters use SDO_GEOM.SDO_DISTANCE between the cell boundaries (H3_BOUNDARY) or their centroids (H3_CENTER).

H3_FROMGEOGPOINT

H3_FROMGEOGPOINT(point, resolution)

Description

Returns the H3 cell index that the point belongs to in the requested resolution. It will return null on error (non-point geometry, resolution out of bounds, or non-WGS84 SRID).

The point must be a 2D SDO_GEOMETRY in WGS84 (SRID 4326). The function does not auto-transform: a point with an explicit SRID other than 4326 returns null. A null SRID is accepted and treated as WGS84 (matching the convention used by SDO_UTIL.FROM_WKTGEOMETRY).

Input parameters

• point: SDO_GEOMETRY 2D point in WGS84.

• resolution: NUMBER between 0 and 15 with the H3 resolution.

Return type

VARCHAR2(16)

Example

SELECT carto.H3_FROMGEOGPOINT(
    SDO_GEOMETRY(2001, 4326,
                 SDO_POINT_TYPE(-3.7038, 40.4168, NULL),
                 NULL, NULL),
    4
) FROM DUAL;
-- 84390cbffffffff

tip

If you want the cells covered by a polygon see H3_POLYFILL.

H3_FROMLONGLAT

H3_FROMLONGLAT(longitude, latitude, resolution)

Description

Returns the H3 cell index that the point belongs to in the required resolution. Inputs are interpreted as WGS84 (SRID 4326). Coordinates outside the valid range are normalized. Returns null on error (resolution out of bounds).

Input parameters

• longitude: NUMBER horizontal coordinate of the map.

• latitude: NUMBER vertical coordinate of the map.

• resolution: NUMBER between 0 and 15 with the H3 resolution.

Return type

VARCHAR2(16)

Example

SELECT carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4) FROM DUAL;
-- 84390cbffffffff

H3_HEXRING

H3_HEXRING(origin, size)

Description

Returns all cell indexes in a hollow hexagonal ring centered at the origin in no particular order. For size = 0 returns just the origin. Returns no rows for invalid input or negative size.

Input parameters

• origin: VARCHAR2(16) H3 cell index of the origin.

• size: NUMBER size of the ring (distance from the origin).

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

Example

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_HEXRING('84390cbffffffff', 1));
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff

H3_INT_TOSTRING

H3_INT_TOSTRING(index)

Description

Converts the integer representation of the H3 index to the string representation.

Input parameters

• index: NUMBER The H3 cell index.

Return type

VARCHAR2(16)

Example

SELECT carto.H3_INT_TOSTRING(595478781590765567) FROM DUAL;
-- 84390cbffffffff

H3_ISPENTAGON

H3_ISPENTAGON(index)

Description

Returns 1 if the given H3 index is a pentagon. Returns 0 otherwise, even on invalid input.

Input parameters

• index: VARCHAR2(16) The H3 cell index as hexadecimal.

Return type

NUMBER (1/0). Oracle has no SQL BOOLEAN; callers compare with = 1.

Examples

SELECT carto.H3_ISPENTAGON('84390cbffffffff') FROM DUAL;
-- 0

SELECT carto.H3_ISPENTAGON('8075fffffffffff') FROM DUAL;
-- 1

H3_ISVALID

H3_ISVALID(index)

Description

Returns 1 when the given index is valid, 0 otherwise.

Input parameters

• index: VARCHAR2(16) The H3 cell index as hexadecimal.

Return type

NUMBER (1/0). Oracle has no SQL BOOLEAN; callers compare with = 1.

Examples

SELECT carto.H3_ISVALID('84390cbffffffff') FROM DUAL;
-- 1

SELECT carto.H3_ISVALID('1') FROM DUAL;
-- 0

H3_KRING

H3_KRING(origin, size)

Description

Returns all cell indexes in a filled hexagonal k-ring centered at the origin in no particular order. Returns no rows for invalid input or negative size.

Input parameters

• origin: VARCHAR2(16) H3 cell index of the origin.

• size: NUMBER size of the ring (distance from the origin).

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

Example

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_KRING('84390cbffffffff', 1));
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff

H3_KRING_DISTANCES

H3_KRING_DISTANCES(origin, size)

Description

Returns all cell indexes and their distances in a filled hexagonal k-ring centered at the origin in no particular order. Returns no rows for invalid input or negative size.

Input parameters

• origin: VARCHAR2(16) H3 cell index of the origin.

• size: NUMBER size of the ring (distance from the origin).

Return type

H3_DISTANCE_ARRAY (pipelined; TABLE OF H3_DISTANCE_PAIR(h3 VARCHAR2(16), distance NUMBER)). Project the named fields when consuming with TABLE(...).

Example

SELECT t.h3, t.distance
FROM TABLE(carto.H3_KRING_DISTANCES('84390cbffffffff', 1)) t;
-- H3               DISTANCE
-- 84390cbffffffff  0
-- 84390c9ffffffff  1
-- 84390c1ffffffff  1
-- 84390c3ffffffff  1
-- 84390ddffffffff  1
-- 84392b7ffffffff  1
-- 84392b5ffffffff  1

H3_POLYFILL

H3_POLYFILL(geom, resolution)

Description

Returns the H3 cell indexes whose centers are contained in the given polygon at the requested resolution (equivalent to mode = 'center' in other CARTO Analytics Toolbox implementations). The resulting H3 set does not fully cover the input geometry but is significantly faster than coverage-based modes.

For coverage modes (center and intersects) over an entire query, use the H3_POLYFILL_TABLE procedure.

Returns no rows on error (invalid input, resolution out of bounds, or non-polygon geometry).

Input parameters

• geom: SDO_GEOMETRY polygon or multipolygon representing the shape to cover. GeometryCollections containing polygons or multipolygons are also allowed. Non-polygon geometries (POINT, LINESTRING, etc.) are silently ignored — no error is raised. Interpreted as WGS84 (EPSG:4326).

• resolution: NUMBER level of detail. The value must be between 0 and 15 (H3 resolution table).

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

Examples

Single-row polyfill:

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_POLYFILL(
    SDO_UTIL.FROM_WKTGEOMETRY(
        'POLYGON ((-3.71219873428345 40.413365349070865,'
        ' -3.7144088745117 40.40965661286395,'
        ' -3.70659828186035 40.409525904775634,'
        ' -3.71219873428345 40.413365349070865))'
    ),
    9
));
-- 89390cb1b4bffff

Polyfill applied across a table of geometries (lateral join via comma + TABLE(...)):

SELECT t.h3
  FROM <my-schema>.<my-table> g,
       TABLE(carto.H3_POLYFILL(g.geom, 9)) t;

H3_POLYFILL_MODE

H3_POLYFILL_MODE(geom, resolution, polyfill_mode)

Description

Returns the H3 cell indexes contained in the given polygon at a requested resolution. Containment is determined by polyfill_mode: center, intersects, or contains.

• center: keeps the H3 cells whose centers fall inside the input polygon. Faster, does not fully cover the input. Equivalent to H3_POLYFILL.

• intersects: keeps the H3 cells whose boundaries intersect the input polygon. Fully covers the input.

• contains: keeps the H3 cells whose boundaries are fully inside the input polygon. Strictest mode.

Input parameters

• geom: SDO_GEOMETRY polygon or multipolygon representing the shape to cover. GeometryCollections containing polygons or multipolygons are also allowed. Non-polygon geometries (POINT, LINESTRING, etc.) are silently ignored — no error is raised. Interpreted as WGS84 (EPSG:4326).

• resolution: NUMBER H3 resolution between 0 and 15 (H3 resolution table).

• polyfill_mode: VARCHAR2 'center', 'intersects', or 'contains'. (Named polyfill_mode rather than mode because mode is a reserved word in Oracle PL/SQL.)

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

NULL inputs, an out-of-range resolution, or an unknown mode produce no rows.

Example

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_POLYFILL_MODE(
    SDO_UTIL.FROM_WKTGEOMETRY(
        'POLYGON ((-3.71219873428345 40.413365349070865,'
        ' -3.7144088745117 40.40965661286395,'
        ' -3.70659828186035 40.409525904775634,'
        ' -3.71219873428345 40.413365349070865))'
    ),
    9,
    'intersects'
));
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390ca34b3ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
-- 89390cb1b5bffff

H3_POLYFILL_TABLE

H3_POLYFILL_TABLE(input_query, resolution, polyfill_mode, output_table)

Description

Materializes the H3 polyfill of every row in input_query into a new table. The resulting table joins each input row with the polyfill cells of its geom column, preserving every other column the input query exposes.

This is the procedural form of H3_POLYFILL and supports three coverage modes:

• center: keeps the H3 cells whose centers fall inside the input polygon. Faster, does not fully cover the input.

• intersects: keeps the H3 cells whose boundaries intersect the input polygon. Fully covers the input.

• contains: keeps the H3 cells whose boundaries are fully inside the input polygon. Strictest mode.

Invalid polyfill_mode, resolution outside 0..15, or NULL arguments cause the procedure to silently no-op (output table is not created).

Input parameters

• input_query: VARCHAR2 SELECT statement; must expose a column named geom of type SDO_GEOMETRY (a polygon or multipolygon; non-polygon geometries are silently ignored). Any other columns are passed through to the output table.

• resolution: NUMBER H3 resolution between 0 and 15 (H3 resolution table).

• polyfill_mode: VARCHAR2 'center', 'intersects', or 'contains'. (Named polyfill_mode rather than mode because mode is a reserved word in Oracle PL/SQL.)

• output_table: VARCHAR2 fully-qualified name of the table to create. Sanitized via DBMS_ASSERT.QUALIFIED_SQL_NAME.

Return type

None — creates the named table as a side effect. The output table has columns:

• h3 VARCHAR2(16) — the polyfill cell.

• every other column produced by input_query.

Example

BEGIN
    carto.H3_POLYFILL_TABLE(
        'SELECT SDO_UTIL.FROM_WKTGEOMETRY(''POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'') AS geom FROM DUAL',
        9,
        'intersects',
        '<my-schema>.<my-output-table>'
    );
END;
/

SELECT h3 FROM <my-schema>.<my-output-table> ORDER BY h3;
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390ca34b3ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
-- 89390cb1b5bffff

H3_RESOLUTION

H3_RESOLUTION(index)

Description

Returns the resolution (0–15) of the given H3 cell. Returns null for invalid input.

Input parameters

• index: VARCHAR2(16) The H3 cell index as hexadecimal.

Return type

NUMBER

Example

SELECT carto.H3_RESOLUTION('84390cbffffffff') FROM DUAL;
-- 4

H3_STRING_TOINT

H3_STRING_TOINT(index)

Description

Converts the string representation of the H3 index to the integer representation.

Input parameters

• index: VARCHAR2(16) The H3 cell index.

Return type

NUMBER

Example

SELECT carto.H3_STRING_TOINT('84390cbffffffff') FROM DUAL;
-- 595478781590765567

H3_TOCHILDREN

H3_TOCHILDREN(index, resolution)

Description

Returns the indexes of the children/descendents of the given hexagon at the given resolution. Returns no rows when the requested resolution is coarser than the input cell, or for invalid input.

Input parameters

• index: VARCHAR2(16) The H3 cell index as hexadecimal.

• resolution: NUMBER between 0 and 15 with the H3 resolution.

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

Example

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_TOCHILDREN('83390cfffffffff', 4));
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff

H3_TOPARENT

H3_TOPARENT(index, resolution)

Description

Returns the H3 cell index of the parent of the given hexagon at the given resolution. Returns null if the requested resolution is not strictly coarser than the cell's resolution, or for invalid input.

Input parameters

• index: VARCHAR2(16) The H3 cell index as hexadecimal.

• resolution: NUMBER between 0 and 15 with the H3 resolution.

Return type

VARCHAR2(16)

Example

SELECT carto.H3_TOPARENT('84390cbffffffff', 3) FROM DUAL;
-- 83390cfffffffff

H3_UNCOMPACT

H3_UNCOMPACT(indexArray, resolution)

Description

Returns the cells of indexArray expanded to the requested target resolution, representing the same area as the compacted input. Cells already at the target resolution pass through unchanged; coarser cells are expanded to their descendants; cells finer than the target are skipped.

Input parameters

• indexArray: H3_INDEX_ARRAY of H3 cell indices as hexadecimal.

• resolution: NUMBER between 0 and 15 with the H3 resolution.

Return type

H3_INDEX_ARRAY (pipelined; TABLE OF VARCHAR2(16)).

Example

SELECT COLUMN_VALUE AS h3
FROM TABLE(carto.H3_UNCOMPACT(
    carto.H3_INDEX_ARRAY('84390cbffffffff'), 5
));
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff