Reference for H3 functions syntax and examples - Lindorm

Lindorm GanosBase provides SQL functions for working with H3 spatial indexes in LindormTable queries. Use these functions to convert coordinates to H3 indexes, inspect cell properties, navigate cell hierarchies, and calculate distances.

Background

H3 is a hierarchical geospatial indexing system that divides the Earth's surface into a grid of hexagonal cells. Each cell has a unique 64-bit integer identifier called an H3 index. The grid has 16 levels of precision, called resolution, numbered 0 to 15. Resolution 0 has the largest, coarsest cells; resolution 15 has the smallest, finest cells.

The hierarchy is nested: each cell at a given resolution (the parent cell) contains exactly seven smaller cells at the next finer resolution (the child cells). A latitude/longitude pair maps to a specific H3 index at any resolution.

A higher resolution number means finer granularity (smaller cells), not coarser. Resolution 5 is a parent of resolution 6, not the other way around.

Prerequisites

LindormTable version 2.6.5 or later.

To check or upgrade your LindormTable version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

If your LindormTable version is earlier than 2.6.5 and cannot be upgraded, contact technical support (DingTalk ID: s0s3eg3).

Function summary

Category	Function	Description
Data import and conversion	H3	Converts a lat/lon pair or a POINT to an H3 index at the specified resolution.
	H3_H3ToString	Converts an H3 index from LONG to STRING.
	H3_StringToH3	Converts an H3 index from STRING to LONG.
	H3_PolygonToCells	Returns the H3 index array of all cells that cover a polygon at the specified resolution.
Data export	H3_CellToBoundary	Returns the polygon boundary of an H3 cell.
Cell determination	H3_IsValidCell	Returns whether an H3 index is valid.
	H3_GetResolution	Returns the resolution of an H3 index.
	H3_AreNeighborCells	Returns whether two H3 cells are adjacent.
	H3_Contains	Returns whether a set of H3 cells contains a given H3 cell.
H3 cell operations	H3_CellToParent	Returns the parent cell of an H3 cell at the specified resolution.
	H3_CellToChildren	Returns the child cells of an H3 cell at the specified resolution.
Queries related to H3 cells	H3_GridPathCells	Returns the H3 cells along the path between two cells.
	H3_GridDisk	Returns all cells within grid distance k of a center cell.
Distance calculation	H3_Distance	Returns the Euclidean distance between the center points of two H3 cells.
	H3_DistanceSphere	Returns the spherical distance in meters (WGS84) between the center points of two H3 cells.

Data import and conversion

H3

Converts a latitude/longitude pair or a POINT coordinate to an H3 index at the specified resolution.

Syntax

Long H3(Point p)
Long H3(Double lng, Double lat)
Long H3(Point p, Int resolution)
Long H3(Double lng, Double lat, Int resolution)

Parameters

Parameter	Type	Description
`p`	POINT	A coordinate point.
`lng`	DOUBLE	Longitude value.
`lat`	DOUBLE	Latitude value.
`resolution`	INT	H3 index resolution. Valid values: 0–15. Default: 15.

Returns

The H3 index (LONG) for the given coordinate at the specified resolution.

Examples

All four forms produce the same result for the same coordinate and resolution:

SELECT H3(128.2, 20.5) AS H3Cell;
SELECT H3(128.2, 20.5, 15) AS H3Cell;
SELECT H3(ST_MakePoint(128.2, 20.5)) AS H3Cell;
SELECT H3(ST_MakePoint(128.2, 20.5), 15) AS H3Cell;

Result:

+--------------------+
|       H3Cell       |
+--------------------+
| 645317832955184368 |
+--------------------+

H3_H3ToString

Converts an H3 index from LONG to STRING.

Syntax

String H3_H3ToString(Long H3Cell)

Parameters

Parameter	Type	Description
`H3Cell`	LONG	An H3 index.

Returns

The H3 index as a STRING.
An empty string if the input is NULL or invalid.

Examples

Convert a valid H3 index:

SELECT H3_H3ToString(599686042433355775) AS H3Address;

Result:

+-----------------+
|    H3Address    |
+-----------------+
| 85283473fffffff |
+-----------------+

NULL or invalid input returns an empty string:

-- Invalid H3 index
SELECT H3_H3ToString(0) AS H3Address;

-- NULL input
SELECT H3_H3ToString(NULL) AS H3Address;

Result:

+-----------+
| H3Address |
+-----------+
|           |
+-----------+

H3_StringToH3

Converts an H3 index from STRING to LONG.

Syntax

Long H3_StringToH3(String H3Cell)

Parameters

Parameter	Type	Description
`H3Cell`	STRING	An H3 index.

Returns

The H3 index as a LONG.
-1 if the input is an empty string or invalid.

Examples

Convert a valid H3 index:

SELECT H3_StringToH3('85283473fffffff') AS H3Cell;

Result:

+--------------------+
|       H3Cell       |
+--------------------+
| 599686042433355775 |
+--------------------+

Empty string or invalid input returns -1:

-- Invalid H3 index
SELECT H3_StringToH3('abc') AS H3Cell;

-- Empty string
SELECT H3_StringToH3('') AS H3Cell;

Result:

+--------+
| H3Cell |
+--------+
| -1     |
+--------+

H3_PolygonToCells

Returns the H3 index array of all cells that cover the given polygon at the specified resolution.

Syntax

Set<Long> H3_PolygonToCells(Polygon poly, Int resolution)

Parameters

Parameter	Type	Description
`poly`	POLYGON	A polygon object.
`resolution`	INT	H3 index resolution. Valid values: 0–15.

Returns

An array of LONG H3 indexes representing the cells that cover the polygon at the specified resolution.

Data export

H3_CellToBoundary

Returns the polygon boundary of the specified H3 cell as an array of POINT coordinates.

Syntax

Set<Point> H3_CellToBoundary(Long H3Cell)
Set<Point> H3_CellToBoundary(String H3Cell)

Parameters

Parameter	Type	Description
`H3Cell`	LONG or STRING	An H3 index.

Returns

An array of POINT values forming the boundary polygon.
An empty array if the input is an empty string, NULL, or invalid.

Examples

Boundary from a STRING H3 index:

SELECT H3_CellToBoundary('85283473fffffff') AS boundary;

Result:

+--------------------------------+
|            boundary            |
+--------------------------------+
| [Point (-121.92354999630157    |
| 37.42834118609436), Point      |
| (-122.02910130919003           |
| 37.26319797461824), Point      |
| (-121.91508032705622           |
| 37.27135586673191),            |
| Point (-122.090428929044       |
| 37.33755608435299), Point      |
| (-121.86222328902491           |
| 37.353926450852256),           |
| Point (-122.03773496427027     |
| 37.42012867767779)]            |
+--------------------------------+

Boundary from a LONG H3 index:

SELECT H3_CellToBoundary(599686042433355775) AS boundary;

The result is identical to the STRING form above.

Empty string, NULL, or invalid input returns an empty array:

SELECT H3_CellToBoundary('') AS boundary;
SELECT H3_CellToBoundary(NULL) AS boundary;
SELECT H3_CellToBoundary(0) AS boundary;

Result:

+----------+
| boundary |
+----------+
| []       |
+----------+

Cell determination

H3_IsValidCell

Returns whether an H3 index is valid.

Syntax

Boolean H3_IsValidCell(Long H3Cell)
Boolean H3_IsValidCell(String H3Cell)

Parameters

Parameter	Type	Description
`H3Cell`	LONG or STRING	An H3 index to validate.

Returns

true — the index is a valid H3 cell.
false — the index is invalid, an empty string, or NULL.

Examples

Valid H3 indexes:

-- LONG input
SELECT H3_IsValidCell(599686042433355775) AS isValid;

-- STRING input
SELECT H3_IsValidCell('85283473fffffff') AS isValid;

Result:

+---------+
| isValid |
+---------+
| true    |
+---------+

Invalid H3 indexes:

SELECT H3_IsValidCell(12) AS isValid;
SELECT H3_IsValidCell('abc') AS isValid;

Result:

+---------+
| isValid |
+---------+
| false   |
+---------+

Empty string or NULL:

SELECT H3_IsValidCell('') AS isValid;
SELECT H3_IsValidCell(NULL) AS isValid;

Result:

+---------+
| isValid |
+---------+
| false   |
+---------+

H3_GetResolution

Returns the resolution of an H3 index.

Syntax

Int H3_GetResolution(Long H3Cell)
Int H3_GetResolution(String H3Cell)

Parameters

Parameter	Type	Description
`H3Cell`	LONG or STRING	An H3 index.

Returns

An INT in the range 0–15 representing the resolution of the H3 cell.

Examples

-- LONG input
SELECT H3_GetResolution(599686042433355775) AS resolution;

-- STRING input
SELECT H3_GetResolution('85283473fffffff') AS resolution;

Both return:

+------------+
| resolution |
+------------+
| 5          |
+------------+

H3_AreNeighborCells

Returns whether two H3 cells are spatially adjacent (share an edge).

Both cells must be at the same resolution. Use H3_GetResolution to verify resolution before calling this function.

Syntax

Boolean H3_AreNeighborCells(Long H3Cell1, Long H3Cell2)
Boolean H3_AreNeighborCells(String H3Cell1, String H3Cell2)

Parameters

Parameter	Type	Description
`H3Cell1`, `H3Cell2`	LONG or STRING	The two H3 indexes to compare.

Returns

true — the two cells are adjacent.
false — the cells are not adjacent, either index is NULL or invalid, or the two indexes are at different resolutions.

Examples

Adjacent cells (LONG):

SELECT H3_AreNeighborCells(605546022931791871, 605546023066009599) AS isNeighbor;

Result:

+------------+
| isNeighbor |
+------------+
| true       |
+------------+

Adjacent cells (STRING):

SELECT H3_AreNeighborCells('86754e66fffffff', '86754e64fffffff') AS isNeighbor;

Result:

+------------+
| isNeighbor |
+------------+
| true       |
+------------+

Invalid indexes, NULL, or mismatched resolutions return false:

-- Invalid indexes
SELECT H3_AreNeighborCells(1234, 5678) AS isNeighbor;

-- One index is NULL
SELECT H3_AreNeighborCells(1234, NULL) AS isNeighbor;

-- Different resolutions: '85283473fffffff' is resolution 5, '87283082bffffff' is resolution 7
SELECT H3_AreNeighborCells('85283473fffffff', '87283082bffffff') AS isNeighbor;

Result:

+------------+
| isNeighbor |
+------------+
| false      |
+------------+

H3_Contains

Returns whether a set of H3 cells contains a given target cell.

The function returns true when at least one cell A in H3Cells satisfies either of the following conditions:

A and the target cell H3 are identical.
The target cell H3 is a child cell of A.

Invalid H3 indexes in the H3Cells array are silently ignored.

Syntax

Boolean H3_Contains(Set<Long> H3Cells, Long H3)

Parameters

Parameter	Type	Description
`H3Cells`	Set\<Long\>	An array of H3 indexes to search within.
`H3`	LONG	The target H3 index to look up.

Returns

true — the target cell is contained in H3Cells (identical or child match).
false — no match found, or the target H3 is NULL or invalid.

Examples

Target cell is inside the polygon:

SELECT H3_Contains(
  H3_PolygonToCells(ST_GeomFromText('POLYGON((-122.481889 37.826683,-122.479487 37.808548,-122.474150 37.808904,-122.476510 37.826935,-122.481889 37.826683))'), 9),
  H3(-122.47801264775836, 37.81777525405899)
) AS isContained;

Result:

+-------------+
| isContained |
+-------------+
| true        |
+-------------+

Target cell is outside the polygon:

SELECT H3_Contains(
  H3_PolygonToCells(ST_GeomFromText('POLYGON((-122.481889 37.826683,-122.479487 37.808548,-122.474150 37.808904,-122.476510 37.826935,-122.481889 37.826683))'), 9),
  605546022931791871
) AS isContained;

Result:

+-------------+
| isContained |
+-------------+
| false       |
+-------------+

NULL or invalid target returns false:

-- NULL target
SELECT H3_Contains(
  H3_PolygonToCells(ST_GeomFromText('POLYGON((-122.481889 37.826683,-122.479487 37.808548,-122.474150 37.808904,-122.476510 37.826935,-122.481889 37.826683))'), 9),
  NULL
) AS isContained;

-- Invalid target
SELECT H3_Contains(
  H3_PolygonToCells(ST_GeomFromText('POLYGON((-122.481889 37.826683,-122.479487 37.808548,-122.474150 37.808904,-122.476510 37.826935,-122.481889 37.826683))'), 9),
  1233453435457
) AS isContained;

Result:

+-------------+
| isContained |
+-------------+
| false       |
+-------------+

H3 cell operations

H3_CellToParent

Returns the H3 index of the parent cell of the specified cell at the given resolution.

"Parent" means a coarser resolution. Because H3 uses higher numbers for finer resolutions, the resolution parameter must be *less than or equal to* the resolution of the input cell.

Syntax

Long H3_CellToParent(Long H3Cell, Int resolution)

Parameters

Parameter	Type	Description
`H3Cell`	LONG	An H3 index.
`resolution`	INT	The target resolution. Valid values: (0, resolution of `H3Cell`] (0 is excluded). For example, if `H3Cell` is at resolution 5, valid values are 1–5.

Returns

The parent cell's H3 index at the specified resolution.
The same H3 index if the target resolution equals the current resolution.
-1 if the H3 index is invalid, the resolution is NULL, or the resolution is finer than the current cell's resolution.

Examples

Get the parent at resolution 0:

SELECT H3_CellToParent(599686042433355775, 0) AS parent;

Result:

+--------------------+
|       parent       |
+--------------------+
| 577199624117288959 |
+--------------------+

Same resolution returns the input cell:

SELECT H3_CellToParent(599686042433355775, 5) AS parent;

Result:

+--------------------+
|       parent       |
+--------------------+
| 599686042433355775 |
+--------------------+

Invalid input or out-of-range resolution returns -1:

-- Invalid H3 index
SELECT H3_CellToParent(123, 5) AS parent;

-- Resolution finer than the cell (cell is at resolution 5, requesting resolution 9)
SELECT H3_CellToParent(599686042433355775, 9) AS parent;

-- NULL resolution
SELECT H3_CellToParent(599686042433355775, NULL) AS parent;

-- NULL index and NULL resolution
SELECT H3_CellToParent(NULL, NULL) AS parent;

Result:

+--------+
| parent |
+--------+
| -1     |
+--------+

H3_CellToChildren

Returns the H3 indexes of all child cells of the specified cell at the given finer resolution.

Because H3 uses higher numbers for finer resolutions, the resolution parameter must be *greater than* the resolution of the input cell.

Syntax

Set<Long> H3_CellToChildren(Long H3Cell, Int resolution)
Set<String> H3_CellToChildren(String H3Cell, Int resolution)

Parameters

Parameter	Type	Description
`H3Cell`	LONG or STRING	An H3 index.
`resolution`	INT	The target child resolution. Valid values: (resolution of `H3Cell`) to 15 (exclusive lower bound). For example, if `H3Cell` is at resolution 5, valid values are 6–15.

Returns

An array of child cell H3 indexes at the specified resolution.
An empty array if the H3 index is invalid, or if the target resolution is equal to or coarser than the current resolution.

Examples

Child cells at resolution 6 (LONG input):

SELECT H3_CellToChildren(599686042433355775, 6) AS children;

Result:

+--------------------------------+
|            children            |
+--------------------------------+
| [604189641121202175,           |
| 604189641255419903,            |
| 604189641389637631,            |
| 604189641523855359,            |
| 604189641658073087,            |
| 604189641792290815,            |
| 604189641926508543]            |
+--------------------------------+

Child cells at resolution 6 (STRING input):

SELECT H3_CellToChildren('85283473fffffff', 6) AS children;

Result:

+--------------------------------+
|            children            |
+--------------------------------+
| [86283470fffffff,              |
| 862834727ffffff,               |
| 862834737ffffff,               |
| 862834707ffffff,               |
| 862834717ffffff,               |
| 86283471fffffff,               |
| 86283472fffffff]               |
+--------------------------------+

Invalid index, or resolution equal to or coarser than current, returns empty array:

-- Invalid H3 index
SELECT H3_CellToChildren(0, 15) AS children;

-- Resolution coarser than cell (cell is at resolution 5, requesting resolution 3)
SELECT H3_CellToChildren('85283473fffffff', 3) AS children;

-- Resolution equal to current
SELECT H3_CellToChildren('85283473fffffff', 5) AS children;

Result:

+----------+
| children |
+----------+
| []       |
+----------+

Queries related to H3 cells

H3_GridPathCells

Returns the H3 cells along the path between two cells, including both the start cell and end cell.

startCell and endCell must have the same data type (both LONG or both STRING) and must be at the same resolution.

Syntax

Set<Long> H3_GridPathCells(Long startCell, Long endCell)
Set<String> H3_GridPathCells(String startCell, String endCell)

Parameters

Parameter	Type	Description
`startCell`	LONG or STRING	The H3 index of the start cell. Must be the same type as `endCell`.
`endCell`	LONG or STRING	The H3 index of the end cell. Must be the same type as `startCell`.

Returns

An array of H3 indexes forming the path from startCell to endCell.
An empty array if either index is invalid.
null if the two indexes are at different resolutions.

Examples

Path between two cells at resolution 8:

SELECT H3_GridPathCells(H3(123.1, 25.1, 8), H3(123.2, 25.2, 8)) AS pathCell;

Result:

+--------------------------------+
|            pathCell            |
+--------------------------------+
| [613820806174081023,           |
| 613820806136332287,            |
| 613820806132137983,            |
| 613820806325075967,            |
| 613820789795323903,            |
| 613820789791129599,            |
| 613820789942124543,            |
| 613820806163595263,            |
| 613820806327173119,            |
| 613820806314590207,            |
| 613820789986164735,            |
| 613820789981970431,            |
| 613820789944221695,            |
| 613820789940027391,            |
| 613820789969387519,            |
| 613820789965193215,            |
| 613820789709340671,            |
| 613820789705146367]            |
+--------------------------------+

Invalid index returns empty array:

SELECT H3_GridPathCells(587769229395099647, 123) AS pathCell;

Result:

+----------+
| pathCell |
+----------+
| []       |
+----------+

Different resolutions return null:

-- 587769229395099647 is at resolution 2; 599686042433355775 is at resolution 5
SELECT H3_GridPathCells(587769229395099647, 599686042433355775) AS pathCell;

Result:

+----------+
| pathCell |
+----------+
| null     |
+----------+

H3_GridDisk

Returns all H3 cells within grid distance k of the specified center cell, including the center cell itself.

Syntax

Set<Long> H3_GridDisk(Long H3Cell, Int k)
Set<String> H3_GridDisk(String H3Cell, Int k)

Parameters

Parameter	Type	Description
`H3Cell`	LONG or STRING	The H3 index of the center cell.
`k`	INT	The maximum grid distance. All cells at distance 0 through `k` are returned.

Returns

An array of H3 indexes for all cells at grid distance <= k from the center cell.
An empty array if the H3 index is invalid or NULL.
Only the center cell if k is 0.

Examples

All cells within grid distance 1:

SELECT H3_GridDisk(599686042433355775, 1) AS grid;

Result:

+--------------------------------+
|              grid              |
+--------------------------------+
| [599686015589810175,           |
| 599686014516068351,            |
| 599686038138388479,            |
| 599686042433355775,            |
| 599686044580839423,            |
| 599686043507097599,            |
| 599686030622195711]            |
+--------------------------------+

k = 0 returns only the center cell:

SELECT H3_GridDisk(599686042433355775, 0) AS grid;

Result:

+----------------------+
|         grid         |
+----------------------+
| [599686042433355775] |
+----------------------+

Invalid or NULL index returns empty array:

SELECT H3_GridDisk(NULL, 0) AS grid;
SELECT H3_GridDisk(123456, 0) AS grid;

Result:

+------+
| grid |
+------+
| []   |
+------+

Distance calculation

H3_Distance

Returns the Euclidean distance between the center points of two H3 cells, calculated in the 2D coordinate plane.

This function computes distance in the 2D longitude/latitude coordinate space, not along the Earth's surface. For real-world distances in meters, use H3_DistanceSphere. Both startCell and endCell must have the same data type and must be at the same resolution.

Syntax

Double H3_Distance(Long startCell, Long endCell)
Double H3_Distance(String startCell, String endCell)

Parameters

Parameter	Type	Description
`startCell`	LONG or STRING	The H3 index of the first cell. Must be the same type as `endCell`.
`endCell`	LONG or STRING	The H3 index of the second cell. Must be the same type as `startCell`.

Returns

A DOUBLE representing the 2D Euclidean distance between the two cell centers.
-1 if either index is invalid or NULL, or if the two indexes are at different resolutions.

Examples

Distance between two cells (LONG):

SELECT H3_Distance(587769229395099647, 587026509290536959) AS distance;

Result:

+-------------------+
|     distance      |
+-------------------+
| 4.489061432072522 |
+-------------------+

Distance between two cells (STRING):

SELECT H3_Distance('825897fffffffff', '8282cffffffffff') AS distance;

Result:

+-------------------+
|     distance      |
+-------------------+
| 4.489061432072522 |
+-------------------+

Invalid index, NULL, or mismatched resolutions return -1:

-- Invalid index
SELECT H3_Distance(587769229395099647, 345) AS distance;

-- NULL index
SELECT H3_Distance(587769229395099647, NULL) AS distance;

-- Different resolutions: 587769229395099647 is resolution 2, 599686042433355775 is resolution 5
SELECT H3_Distance(587769229395099647, 599686042433355775) AS distance;

Result:

+----------+
| distance |
+----------+
| -1       |
+----------+

H3_DistanceSphere

Returns the spherical distance in meters between the center points of two H3 cells, calculated along the Earth's surface using the WGS84 coordinate system.

Both startCell and endCell must have the same data type and must be at the same resolution.

Syntax

Double H3_DistanceSphere(Long startCell, Long endCell)
Double H3_DistanceSphere(String startCell, String endCell)

Parameters

Parameter	Type	Description
`startCell`	LONG or STRING	The H3 index of the first cell. Must be the same type as `endCell`.
`endCell`	LONG or STRING	The H3 index of the second cell. Must be the same type as `startCell`.

Returns

A DOUBLE representing the spherical distance in meters (WGS84) between the two cell centers.
-1 if either index is invalid or NULL, or if the two indexes are at different resolutions.

Examples

Spherical distance between two cells (LONG):

SELECT H3_DistanceSphere(587769229395099647, 587026509290536959) AS distance;

Result:

+--------------------+
|      distance      |
+--------------------+
| 497180.06581361144 |
+--------------------+

Spherical distance between two cells (STRING):

SELECT H3_DistanceSphere('825897fffffffff', '8282cffffffffff') AS distance;

Result:

+--------------------+
|      distance      |
+--------------------+
| 497180.06581361144 |
+--------------------+

Invalid index, NULL, or mismatched resolutions return -1:

-- Invalid index
SELECT H3_DistanceSphere(587769229395099647, 345) AS distance;

-- NULL index
SELECT H3_DistanceSphere(587769229395099647, NULL) AS distance;

-- Different resolutions: 587769229395099647 is resolution 2, 599686042433355775 is resolution 5
SELECT H3_DistanceSphere(587769229395099647, 599686042433355775) AS distance;

Result:

+----------+
| distance |
+----------+
| -1       |
+----------+