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
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
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 polygoncontains
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 columngeom
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 polygoncontains
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
exceptgeom
.
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
Last updated
Was this helpful?