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.

H3_BOUNDARY

H3_BOUNDARY(index)

Description

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

• index: STRING The H3 cell index as hexadecimal.

Return type

GEOGRAPHY

Example

SELECT CARTO.CARTO.H3_BOUNDARY('84390cbffffffff');
-- { "coordinates": [ [ [ -3.5769274353957314, 40.613438595935165 ], [ -3.85975632308016, 40.525472355369885 ], ...

H3_CENTER

H3_CENTER(index)

Description

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

• index: STRING The H3 cell index.

Return type

GEOGRAPHY

Example

SELECT CARTO.CARTO.H3_CENTER('84390cbffffffff');
-- { "coordinates": [ -3.6176032466282892, 40.37254058216577 ], "type": "Point" }

H3_COMPACT

H3_COMPACT(indexArray)

Description

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

• indexArray: ARRAY of H3 cell indices of the same resolution as hexadecimal.

Return type

ARRAY

Example

SELECT CARTO.CARTO.H3_COMPACT(ARRAY_CONSTRUCT('85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff'));
-- 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.

• origin: STRING The H3 cell index as hexadecimal.

• destination: STRING The H3 cell index as hexadecimal.

Return type

BIGINT

Example

SELECT CARTO.CARTO.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1

tip

If you want the distance in meters use ST_DISTANCE between the cells (H3_BOUNDARY) or their centroid.

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 (invalid geography type or resolution out of bounds). This function is an alias for H3_FROMGEOPOINT.

• point: GEOGRAPHY point to get the H3 cell from.

• resolution: INT number between 0 and 15 with the H3 resolution.

Return type

STRING

Example

SELECT CARTO.CARTO.H3_FROMGEOGPOINT(ST_POINT(-3.7038, 40.4168), 4);
-- 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. It will return null on error (resolution out of bounds).

• longitude: DOUBLE horizontal coordinate of the map.

• latitude: DOUBLE vertical coordinate of the map.

• resolution: INT number between 0 and 15 with the H3 resolution.

Return type

STRING

Example

SELECT CARTO.CARTO.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 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. Unlike H3_KRING, this function will throw an exception if there is a pentagon anywhere in the ring.

• origin: STRING H3 cell index of the origin.

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

Return type

ARRAY

Example

SELECT CARTO.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.

• index: INT The H3 cell index.

Return type

STRING

Example

SELECT CARTO.CARTO.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff

H3_ISPENTAGON

H3_ISPENTAGON(index)

Description

Returns true if given H3 index is a pentagon. Returns false otherwise, even on invalid input.

• index: STRING The H3 cell index as hexadecimal.

Return type

BOOLEAN

Example

SELECT CARTO.CARTO.H3_ISPENTAGON('84390cbffffffff');
-- false

SELECT CARTO.CARTO.H3_ISPENTAGON('8075fffffffffff');
-- true

H3_ISVALID

H3_ISVALID(index)

Description

Returns true when the given index is valid, false otherwise.

• index: STRING The H3 cell index as hexadecimal.

Return type

BOOLEAN

Examples

SELECT CARTO.CARTO.H3_ISVALID('84390cbffffffff');
-- true

SELECT CARTO.CARTO.H3_ISVALID('1');
-- false

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.

• origin: STRING H3 cell index of the origin.

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

Return type

ARRAY

Example

SELECT CARTO.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.

• origin: STRING H3 cell index of the origin.

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

Return type

ARRAY

Example

SELECT CARTO.CARTO.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": 0}
-- {"index": "84390c9ffffffff", "distance": 1}
-- {"index": "84390c1ffffffff", "distance": 1}
-- {"index": "84390c3ffffffff", "distance": 1}
-- {"index": "84390ddffffffff", "distance": 1}
-- {"index": "84392b7ffffffff", "distance": 1}
-- {"index": "84392b5ffffffff", "distance": 1}

H3_POLYFILL

H3_POLYFILL(geography, resolution [, mode])  

Description

Returns an array with all H3 cell indexes contained in the given polygon. There are three modes which decide if a H3 cell is contained in the polygon:

• geography: GEOGRAPHY polygon or multipolygon representing the shape to cover. GeometryCollections are also allowed but they should contain polygon or multipolygon geographies. Non-Polygon types will not raise an error but will be ignored instead.

• resolution: INT number between 0 and 15 with the H3 resolution.
• mode: STRING <center|contains|intersects>. Optional. Defaults to 'center' mode.

  • center The center point of the H3 cell must be within the polygon

  • contains The H3 cell must be fully contained within the polygon (least inclusive)

  • intersects The H3 cell intersects in any way with the polygon (most inclusive)

Mode center:

Mode intersects:

Mode contains:

Return type

ARRAY<STRING>

Examples

SELECT CARTO.CARTO.H3_POLYFILL(
    TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 842da29ffffffff
-- 843f725ffffffff
-- 843eac1ffffffff
-- 8453945ffffffff
-- ...

SELECT CARTO.CARTO.H3_POLYFILL(
    TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4, 'center');
-- 842da29ffffffff
-- 843f725ffffffff
-- 843eac1ffffffff
-- 8453945ffffffff
-- ...

SELECT CARTO.CARTO.H3_POLYFILL(
    TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4, 'contains');
-- 843f0cbffffffff
-- 842da01ffffffff
-- 843e467ffffffff
-- 843ea99ffffffff
-- 843f0c3ffffffff
-- ...

SELECT CARTO.CARTO.H3_POLYFILL(
    TO_GEOGRAPHY('POLYGON ((30 1040 4020 4010 2030 10))')4'intersects');
-- 843f0cbffffffff
-- 842da01ffffffff
-- 843e467ffffffff
-- 843ea99ffffffff
-- 843f0c3ffffffff
-- 843ea91ffffffff
-- ...

H3_POLYFILL_TABLE (BETA)

H3_POLYFILL_TABLE(input_query, resolution, mode, output_table)

Description

Returns a table with the H3 cell indexes contained in the given polygon at a requested resolution. Containment is determined by the mode: center, intersects, contains. All the attributes except the polygon will be included in the output table, clustered by the h3 column.

• input_query: STRING input data to polyfill. It must contain a column geom with the shape to cover. Additionally, other columns can be included.

• resolution: INT number between 0 and 15 with the H3 resolution.
• mode: STRING <center|contains|intersects>. Optional. Defaults to 'center' mode.

  • center The center point of the H3 cell must be within the polygon

  • contains The H3 cell must be fully contained within the polygon (least inclusive)

  • intersects The H3 cell intersects in any way with the polygon (most inclusive)

Mode center:

Mode intersects:

Mode contains:

Output

The results are stored in the table named <output_table>, which contains the following columns:

• h3: STRING the geometry of the considered point.

• The rest of columns included in input_query except geom.

Examples

CALL CARTO.CARTO.H3_POLYFILL_TABLE(
  'SELECT TO_GEOGRAPHY(''POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'') AS geom',
  9, 'intersects',
  '<database>.<schema>.<output_table>'
);
-- The table `<database>.<schema>.<output_table>` will be created
-- with column: h3

CALL CARTO.CARTO.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<database>.<schema>.<table>`',
  9, 'center',
  '<database>.<schema>.<output_table>'
);
-- The table `<database>.<schema>.<output_table>` will be created
-- with columns: h3, name, value

H3_RESOLUTION

H3_RESOLUTION(index)

Description

Returns the H3 cell resolution as an integer. It will return null on error (invalid input).

• index: STRING The H3 cell index.

Return type

INT

Example

SELECT CARTO.CARTO.H3_RESOLUTION('84390cbffffffff');
-- 4

H3_STRING_TOINT

H3_STRING_TOINT(index)

Description

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

• index: STRING The H3 cell index.

Return type

INT

Example

SELECT CARTO.CARTO.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567

H3_TOCHILDREN

H3_TOCHILDREN(index, resolution)

Description

Returns an array with the indexes of the children/descendents of the given hexagon at the given resolution.

• index: STRING The H3 cell index as hexadecimal.

• resolution: INT number between 0 and 15 with the H3 resolution.

Return type

ARRAY

Example

SELECT CARTO.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.

• index: STRING The H3 cell index as hexadecimal.

• resolution: INT number between 0 and 15 with the H3 resolution.

Return type

STRING

Example

SELECT CARTO.CARTO.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff

H3_UNCOMPACT

H3_UNCOMPACT(indexArray, resolution)

Description

Returns an array with the indexes of a set of hexagons of the same resolution that represent the same area as the compacted input hexagons.

• indexArray: ARRAY of H3 cell indices as hexadecimal.

• resolution: INT number between 0 and 15 with the H3 resolution.

Return type

ARRAY

Example

SELECT CARTO.CARTO.H3_UNCOMPACT(ARRAY_CONSTRUCT('84390cbffffffff'), 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff