h3
CORE
​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(index)
Description
Returns a geography representing the H3 cell. It will return
null
on error (invalid input).index
:STRING
The H3 cell index.
Return type
GEOGRAPHY
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_BOUNDARY('847b59dffffffff');
-- POLYGON((40.4650636223452 -3.9352772457965, 40.5465406026705 ...
SELECT `carto-un-eu`.carto.H3_BOUNDARY('847b59dffffffff');
-- POLYGON((40.4650636223452 -3.9352772457965, 40.5465406026705 ...
SELECT `carto-os`.carto.H3_BOUNDARY('847b59dffffffff');
-- POLYGON((40.4650636223452 -3.9352772457965, 40.5465406026705 ...
SELECT carto.H3_BOUNDARY('847b59dffffffff');
-- POLYGON((40.4650636223452 -3.9352772457965, 40.5465406026705 ...
Additional examples
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
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_CENTER('847b59dffffffff');
-- POINT(40.3054764231743 -3.74320332556168)
SELECT `carto-un-eu`.carto.H3_CENTER('847b59dffffffff');
-- POINT(40.3054764231743 -3.74320332556168)
SELECT `carto-os`.carto.H3_CENTER('847b59dffffffff');
-- POINT(40.3054764231743 -3.74320332556168)
SELECT carto.H3_CENTER('847b59dffffffff');
-- POINT(40.3054764231743 -3.74320332556168)
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<STRING>
of H3 cell indices of the same resolution.
Return type
ARRAY<STRING>
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_COMPACT(['857b59c3fffffff', '857b59c7fffffff', '857b59cbfffffff', '857b59cffffffff', '857b59d3fffffff', '857b59d7fffffff', '857b59dbfffffff']);
-- 847b59dffffffff
SELECT `carto-un-eu`.carto.H3_COMPACT(['857b59c3fffffff', '857b59c7fffffff', '857b59cbfffffff', '857b59cffffffff', '857b59d3fffffff', '857b59d7fffffff', '857b59dbfffffff']);
-- 847b59dffffffff
SELECT `carto-os`.carto.H3_COMPACT(['857b59c3fffffff', '857b59c7fffffff', '857b59cbfffffff', '857b59cffffffff', '857b59d3fffffff', '857b59d7fffffff', '857b59dbfffffff']);
-- 847b59dffffffff
SELECT carto.H3_COMPACT(['857b59c3fffffff', '857b59c7fffffff', '857b59cbfffffff', '857b59cffffffff', '857b59d3fffffff', '857b59d7fffffff', '857b59dbfffffff']);
-- 847b59dffffffff
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
origin H3 cell index.destination
:STRING
destination H3 cell index.
Return type
INT64
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_DISTANCE('847b591ffffffff', '847b59bffffffff');
-- 1
SELECT `carto-un-eu`.carto.H3_DISTANCE('847b591ffffffff', '847b59bffffffff');
-- 1
SELECT `carto-os`.carto.H3_DISTANCE('847b591ffffffff', '847b59bffffffff');
-- 1
SELECT carto.H3_DISTANCE('847b591ffffffff', '847b59bffffffff');
-- 1
tip
If you want the distance in meters use ST_DISTANCE between the cells (H3_BOUNDARY) or their centroid.
Additional examples
H3_FROMGEOGPOINT(point, resolution)
Description
Returns the H3 cell index that the point belongs to in the required
resolution
. It will return null
on error (invalid geography type or resolution out of bounds).point
:GEOGRAPHY
point to get the H3 cell from.
Return type
STRING
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(40.4168, -3.7038), 4);
-- 847b59dffffffff
SELECT `carto-un-eu`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(40.4168, -3.7038), 4);
-- 847b59dffffffff
SELECT `carto-os`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(40.4168, -3.7038), 4);
-- 847b59dffffffff
SELECT carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(40.4168, -3.7038), 4);
-- 847b59dffffffff
tip
Additional examples
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
:FLOAT64
horizontal coordinate of the map.latitude
:FLOAT64
vertical coordinate of the map.
Return type
STRING
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_FROMLONGLAT(40.4168, -3.7038, 4);
-- 847b59dffffffff
SELECT `carto-un-eu`.carto.H3_FROMLONGLAT(40.4168, -3.7038, 4);
-- 847b59dffffffff
SELECT `carto-os`.carto.H3_FROMLONGLAT(40.4168, -3.7038, 4);
-- 847b59dffffffff
SELECT carto.H3_FROMLONGLAT(40.4168, -3.7038, 4);
-- 847b59dffffffff
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
:INT64
size of the ring (distance from the origin).
Return type
ARRAY<STRING>
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_HEXRING('837b59fffffffff', 1);
-- 837b5dfffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
SELECT `carto-un-eu`.carto.H3_HEXRING('837b59fffffffff', 1);
-- 837b5dfffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
SELECT `carto-os`.carto.H3_HEXRING('837b59fffffffff', 1);
-- 837b5dfffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
SELECT carto.H3_HEXRING('837b59fffffffff', 1);
-- 837b5dfffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
H3_INT_TOSTRING(index)
Description
Converts the integer representation of the H3 index to the string representation.
index
:INT64
The H3 cell index.
Return type
STRING
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_INT_TOSTRING(596645165859340287);
-- 847b59dffffffff
SELECT `carto-un-eu`.carto.H3_INT_TOSTRING(596645165859340287);
-- 847b59dffffffff
SELECT `carto-os`.carto.H3_INT_TOSTRING(596645165859340287);
-- 847b59dffffffff
SELECT carto.H3_INT_TOSTRING(596645165859340287);
-- 847b59dffffffff
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.
Return type
BOOLEAN
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_ISPENTAGON('837b59fffffffff');
-- false
SELECT `carto-un-eu`.carto.H3_ISPENTAGON('837b59fffffffff');
-- false
SELECT `carto-os`.carto.H3_ISPENTAGON('837b59fffffffff');
-- false
SELECT carto.H3_ISPENTAGON('837b59fffffffff');
-- false
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_ISPENTAGON('8075fffffffffff');
-- true
SELECT `carto-un-eu`.carto.H3_ISPENTAGON('8075fffffffffff');
-- true
SELECT `carto-os`.carto.H3_ISPENTAGON('8075fffffffffff');
-- true
SELECT carto.H3_ISPENTAGON('8075fffffffffff');
-- true
H3_ISVALID(index)
Description
Returns
true
when the given index is a valid H3 index, false
otherwise.index
:STRING
The H3 cell index.
Return type
BOOLEAN
Examples
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_ISVALID('847b59dffffffff');
-- true
SELECT `carto-un-eu`.carto.H3_ISVALID('847b59dffffffff');
-- true
SELECT `carto-os`.carto.H3_ISVALID('847b59dffffffff');
-- true
SELECT carto.H3_ISVALID('847b59dffffffff');
-- true
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_ISVALID('1');
-- false
SELECT `carto-un-eu`.carto.H3_ISVALID('1');
-- false
SELECT `carto-os`.carto.H3_ISVALID('1');
-- false
SELECT carto.H3_ISVALID('1');
-- false
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
:INT64
size of the ring (distance from the origin).
Return type
ARRAY<STRING>
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_KRING('837b59fffffffff', 1);
-- 837b59fffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
-- 837b5dfffffffff
SELECT `carto-un-eu`.carto.H3_KRING('837b59fffffffff', 1);
-- 837b59fffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
-- 837b5dfffffffff
SELECT `carto-os`.carto.H3_KRING('837b59fffffffff', 1);
-- 837b59fffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
-- 837b5dfffffffff
SELECT carto.H3_KRING('837b59fffffffff', 1);
-- 837b59fffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
-- 837b5dfffffffff
Additional examples
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
:INT64
size of the ring (distance from the origin).
Return type
ARRAY<STRUCT<index STRING, distance INT64>>
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_KRING_DISTANCES('837b59fffffffff', 1);
-- {"index": "837b59fffffffff", "distance": "0"}
-- {"index": "837b5dfffffffff", "distance": "1"}
-- {"index": "837b58fffffffff", "distance": "1"}
-- {"index": "837b5bfffffffff", "distance": "1"}
-- {"index": "837a66fffffffff", "distance": "1"}
-- {"index": "837a64fffffffff", "distance": "1"}
-- {"index": "837b4afffffffff", "distance": "1"}
SELECT `carto-un-eu`.carto.H3_KRING_DISTANCES('837b59fffffffff', 1);
-- {"index": "837b59fffffffff", "distance": "0"}
-- {"index": "837b5dfffffffff", "distance": "1"}
-- {"index": "837b58fffffffff", "distance": "1"}
-- {"index": "837b5bfffffffff", "distance": "1"}
-- {"index": "837a66fffffffff", "distance": "1"}
-- {"index": "837a64fffffffff", "distance": "1"}
-- {"index": "837b4afffffffff", "distance": "1"}
SELECT `carto-os`.carto.H3_KRING_DISTANCES('837b59fffffffff', 1);
-- {"index": "837b59fffffffff", "distance": "0"}
-- {"index": "837b5dfffffffff", "distance": "1"}
-- {"index": "837b58fffffffff", "distance": "1"}
-- {"index": "837b5bfffffffff", "distance": "1"}
-- {"index": "837a66fffffffff", "distance": "1"}
-- {"index": "837a64fffffffff", "distance": "1"}
-- {"index": "837b4afffffffff", "distance": "1"}
SELECT carto.H3_KRING_DISTANCES('837b59fffffffff', 1);
-- {"index": "837b59fffffffff", "distance": "0"}
-- {"index": "837b5dfffffffff", "distance": "1"}
-- {"index": "837b58fffffffff", "distance": "1"}
-- {"index": "837b5bfffffffff", "distance": "1"}
-- {"index": "837a66fffffffff", "distance": "1"}
-- {"index": "837a64fffffffff", "distance": "1"}
-- {"index": "837b4afffffffff", "distance": "1"}
H3_POLYFILL(geography, resolution)
Description
Returns an array with all the H3 cell indexes with centers contained in a given polygon. It will return
null
on error (invalid geography type or resolution out of bounds). In case of lines, it will return the H3 cell indexes intersecting those lines. For a given point, it will return the H3 index of cell in which that point is contained.warning
Lines polyfill is calculated by approximating S2 cells to H3 cells, in some cases some cells might be missing.
geography
:GEOGRAPHY
representing the area to cover.
Return type
ARRAY<STRING>
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_POLYFILL(
ST_GEOGFROMTEXT('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 846b26bffffffff
-- 843e8b1ffffffff
-- 842d1e5ffffffff
-- 843ece5ffffffff
-- ...
SELECT `carto-un-eu`.carto.H3_POLYFILL(
ST_GEOGFROMTEXT('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 846b26bffffffff
-- 843e8b1ffffffff
-- 842d1e5ffffffff
-- 843ece5ffffffff
-- ...
SELECT `carto-os`.carto.H3_POLYFILL(
ST_GEOGFROMTEXT('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 846b26bffffffff
-- 843e8b1ffffffff
-- 842d1e5ffffffff
-- 843ece5ffffffff
-- ...
SELECT carto.H3_POLYFILL(
ST_GEOGFROMTEXT('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 846b26bffffffff
-- 843e8b1ffffffff
-- 842d1e5ffffffff
-- 843ece5ffffffff
-- ...
Additional examples
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
INT64
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_RESOLUTION('847b59dffffffff');
-- 4
SELECT `carto-un-eu`.carto.H3_RESOLUTION('847b59dffffffff');
-- 4
SELECT `carto-os`.carto.H3_RESOLUTION('847b59dffffffff');
-- 4
SELECT carto.H3_RESOLUTION('847b59dffffffff');
-- 4
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
INT64
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_STRING_TOINT('847b59dffffffff');
-- 596645165859340287
SELECT `carto-un-eu`.carto.H3_STRING_TOINT('847b59dffffffff');
-- 596645165859340287
SELECT `carto-os`.carto.H3_STRING_TOINT('847b59dffffffff');
-- 596645165859340287
SELECT carto.H3_STRING_TOINT('847b59dffffffff');
-- 596645165859340287
H3_TOCHILDREN(index, resolution)
Description
Returns an array with the H3 indexes of the children/descendents of the given hexagon at the given resolution.
index
:STRING
The H3 cell index.
Return type
ARRAY<STRING>
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_TOCHILDREN('837b59fffffffff', 4);
-- 847b591ffffffff
-- 847b593ffffffff
-- 847b595ffffffff
-- 847b597ffffffff
-- 847b599ffffffff
-- 847b59bffffffff
-- 847b59dffffffff
SELECT `carto-un-eu`.carto.H3_TOCHILDREN('837b59fffffffff', 4);
-- 847b591ffffffff
-- 847b593ffffffff
-- 847b595ffffffff
-- 847b597ffffffff
-- 847b599ffffffff
-- 847b59bffffffff
-- 847b59dffffffff
SELECT `carto-os`.carto.H3_TOCHILDREN('837b59fffffffff', 4);
-- 847b591ffffffff
-- 847b593ffffffff
-- 847b595ffffffff
-- 847b597ffffffff
-- 847b599ffffffff
-- 847b59bffffffff
-- 847b59dffffffff
SELECT carto.H3_TOCHILDREN('837b59fffffffff', 4);
-- 847b591ffffffff
-- 847b593ffffffff
-- 847b595ffffffff
-- 847b597ffffffff
-- 847b599ffffffff
-- 847b59bffffffff
-- 847b59dffffffff
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.
Return type
STRING
Example
carto-un
carto-un-eu
carto-os
manual
SELECT `carto-un`.carto.H3_TOPARENT('847b59dffffffff', 3);
-- 837b59fffffffff
SELECT `carto-un-eu`.carto.H3_TOPARENT('847b59dffffffff', 3);
-- 837b59fffffffff
SELECT `carto-os`.carto.H3_TOPARENT('847b59dffffffff', 3);
-- 837b59fffffffff
SELECT carto.H3_TOPARENT('847b59dffffffff', 3);
-- 837b59fffffffff
H3_UNCOMPACT(indexArray, resolution)
Description
Returns an array with the H3 indexes of a set of hexagons of the same
resolution
that represent the same area as the compacted input hexagons.indexArray
:ARRAY<STRING>
of H3 cell indices.
Return type
ARRAY<STRING>
Example
carto-un