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
:VARCHAR(16)
The H3 cell index as hexadecimal.
Return type
GEOMETRY
Example
1
SELECT carto.H3_BOUNDARY('84390cbffffffff');
2
-- POLYGON ((-3.5769274353957314 40.613438595935165, -3.85975632308016 40.525472355369885, -3.899552298996668 40.28411330409504, ...
H3_CENTER(index)
Description
Returns the center of the H3 cell as a GEOMETRY point. It will return
null
on error (invalid input).index
:VARCHAR(16)
The H3 cell index.
Return type
GEOMETRY
Example
1
SELECT carto.H3_CENTER('84390cbffffffff');
2
-- POINT (-3.6176032466282892 40.37254058216577)
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
:VARCHAR(16)[]
of H3 cell indices of the same resolution as hexadecimal.
Return type
VARCHAR(16)[]
Example
1
SELECT carto.H3_COMPACT(ARRAY['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff','85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
2
-- { 84390cbffffffff }
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
:VARCHAR(16)
The H3 cell index as hexadecimal.destination
:VARCHAR(16)
The H3 cell index as hexadecimal.
Return type
BIGINT
Example
1
SELECT carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
2
-- 1
tip
If you want the distance in meters use ST_DISTANCE between the cells (H3_BOUNDARY) or their centroid.
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
:GEOMETRY
point to get the H3 cell from.
Return type
VARCHAR(16)
Example
1
SELECT carto.H3_FROMGEOGPOINT(ST_POINT(-3.7038, 40.4168), 4);
2
-- 84390cbffffffff
tip
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 PRECISION
horizontal coordinate of the map.latitude
:DOUBLE PRECISION
vertical coordinate of the map.
Return type
VARCHAR(16)
Example
1
SELECT carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
2
-- 84390cbffffffff
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
:VARCHAR(16)
H3 cell index of the origin.size
:INT
size of the ring (distance from the origin).
Return type
VARCHAR(16)[]
Example
1
SELECT carto.H3_HEXRING('84390cbffffffff', 1);
2
-- { 84392b5ffffffff,
3
-- 84390c9ffffffff,
4
-- 84390c1ffffffff,
5
-- 84390c3ffffffff,
6
-- 84390ddffffffff,
7
-- 84392b7ffffffff }
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
VARCHAR(16)
Example
1
SELECT carto.H3_INT_TOSTRING(595478781590765567);
2
-- 84390cbffffffff
H3_ISPENTAGON(index)
Description
Returns
true
if given H3 index is a pentagon. Returns false
otherwise, even on invalid input.index
:VARCHAR(16)
The H3 cell index as hexadecimal.
Return type
BOOLEAN
Example
1
SELECT carto.H3_ISPENTAGON('84390cbffffffff');
2
-- false
1
SELECT carto.H3_ISPENTAGON('8075fffffffffff');
2
-- true
H3_ISVALID(index)
Description
Returns
true
when the given index is valid, false
otherwise.index
:VARCHAR(16)
The H3 cell index as hexadecimal.
Return type
BOOLEAN
Examples
1
SELECT carto.H3_ISVALID('84390cbffffffff');
2
-- true
1
SELECT carto.H3_ISVALID('1');
2
-- 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
:VARCHAR(16)
H3 cell index of the origin.size
:INT
size of the ring (distance from the origin).
Return type
VARCHAR(16)[]
Example
1
SELECT carto.H3_KRING('84390cbffffffff', 1);
2
-- { 84390cbffffffff,
3
-- 84390c9ffffffff,
4
-- 84390c1ffffffff,
5
-- 84390c3ffffffff,
6
-- 84390ddffffffff,
7
-- 84392b7ffffffff,
8
-- 84392b5ffffffff }
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
:VARCHAR(16)
H3 cell index of the origin.size
:INT
size of the ring (distance from the origin).
Return type
JSON[]
Example
1
SELECT carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
2
-- {{"index": "84390cbffffffff", "distance": 0}"
3
-- {"index": "84390c9ffffffff", "distance": 1}
4
-- {"index": "84390c1ffffffff", "distance": 1}
5
-- {"index": "84390c3ffffffff", "distance": 1}
6
-- {"index": "84390ddffffffff", "distance": 1}
7
-- {"index": "84392b7ffffffff", "distance": 1}
8
-- {"index": "84392b5ffffffff", "distance": 1}}
H3_POLYFILL(geom, resolution [, mode])
Description
Returns an array of H3 cell indexes contained in the given geometry at a given level of detail. Containment is determined by the mode: center, intersects, contains.
geom
:GEOMETRY
representing the shape to cover.mode
(optional):VARCHAR
center
(default) returns the indexes of the H3 cells which centers intersect the input geometry (polygon). The resulting H3 set does not fully cover the input geometry, however, this is significantly faster that the other modes. This mode is not compatible with points or lines.intersects
returns the indexes of the H3 cells that intersect the input geometry. The resulting H3 set will completely cover the input geometry (point, line, polygon).contains
returns the indexes of the H3 cells that are entirely contained inside the input geometry (polygon). This mode is not compatible with points or lines.
Mode
center
:
Mode
intersects
:
Mode
contains
:
Return type
VARCHAR(16)[]
Examples
1
SELECT carto.H3_POLYFILL(
2
ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
3
9
4
);
5
-- [89390cb1b4bffff]
1
SELECT h3
2
FROM UNNEST(carto.H3_POLYFILL(
3
ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
4
9
5
)) AS h3;
6
-- 89390cb1b4bffff
1
SELECT h3
2
FROM <database>.<schema>.<table>,
3
UNNEST(carto.H3_POLYFILL(geom, 9)) AS h3;
1
SELECT carto.H3_POLYFILL(
2
ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
3
9, 'intersects'
4
);
5
-- [89390cb1b4fffff, 89390ca3497ffff, 89390ca34b3ffff, 89390cb1b4bffff, 89390ca3487ffff, 89390cb1b5bffff]
1
SELECT h3
2
FROM UNNEST(carto.H3_POLYFILL(
3
ST_GEOMFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
4
9, 'intersects'
5
)) AS h3;
6
-- 89390cb1b4fffff
7
-- 89390ca3497ffff
8
-- 89390ca34b3ffff
9
-- 89390cb1b4bffff
10
-- 89390ca3487ffff
11
-- 89390cb1b5bffff
1
SELECT h3
2
FROM <database>.<schema>.<table>,
3
UNNEST(carto.H3_POLYFILL(geom, 9, 'intersects')) AS h3;
H3_STRING_TOINT(index)
Description
Converts the string representation of the H3 index to the integer representation.
index
:VARCHAR(16)
The H3 cell index.
Return type
INT
Example
1
SELECT carto.H3_STRING_TOINT('84390cbffffffff');
2
-- 595478781590765567
H3_TOCHILDREN(index, resolution)
Description
Returns an array with the indexes of the children/descendents of the given hexagon at the given resolution.
index
:VARCHAR(16)
The H3 cell index as hexadecimal.
Return type
VARCHAR(16)[]
Example
1
SELECT carto.H3_TOCHILDREN('83390cfffffffff', 4);
2
-- { 84390c1ffffffff,
3
-- 84390c3ffffffff,
4
-- 84390c5ffffffff
5
-- 84390c7ffffffff,
6
-- 84390c9ffffffff,
7
-- 84390cbffffffff,
8
-- 84390cdffffffff }
H3_TOPARENT(index, resolution)
Description
Returns the H3 cell index of the parent of the given hexagon at the given resolution.
index
:VARCHAR(16)
The H3 cell index as hexadecimal.
Return type
VARCHAR
Example
1
SELECT carto.H3_TOPARENT('84390cbffffffff', 3);
2
-- 83390cfffffffff
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
:VARCHAR(16)
of H3 cell indices as hexadecimal.
Return type
VARCHAR(16)[]
Example
1
SELECT carto.H3_UNCOMPACT(ARRAY['84390cbffffffff'], 5);
2
-- { 85390ca3fffffff,
3
-- 85390ca7fffffff,
4
-- 85390cabfffffff,
5
-- 85390caffffffff,
6
-- 85390cb3fffffff,
7
-- 85390cb7fffffff,
8
-- 85390cbbfffffff }
Last modified 1mo ago