h3_try_distance
function
Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
This function behaves similarly to h3_distance, except that NULL is returned instead of an error if the grid distance between the two input H3 cell IDs of the same resolution is undefined. Specifically, it returns the grid distance of the two input H3 cell IDs, which are expected to be of the same resolution, or NULL if the distance is undefined.
Arguments
h3CellId1Expr
: A BIGINT expression, or a hexadecimal STRING expression representing an H3 cell ID.h3CellId2Expr
: A BIGINT expression, or a hexadecimal STRING expression representing an H3 cell ID.
Returns
A BIGINT value that is the grid distance of the two input H3 cells, that are expected to have the same resolution, or NULL if the distance is undefined.
The function returns NULL if any of the input expressions is NULL.
It partially validates the input arguments to determine if they represent valid H3 cell IDs. A necessary, but not sufficient, condition for a valid H3 ID is that its value is between 0x08001fffffffffff
and 0x08ff3b6db6db6db6
.
The behavior of the function is undefined if any of the two input cell IDs is not a valid cell ID.
Error conditions
If
h3CellId1Expr
orh3CellId2Expr
is a STRING that cannot be converted to a BIGINT or corresponds to a BIGINT value that is smaller than0x08001fffffffffff
or larger than0x08ff3b6db6db6db6
, the function returns H3_INVALID_CELL_ID.If the two input H3 cells are of different resolution, the function returns H3_UNDEFINED_GRID_DISTANCE.
If the grid distance between two H3 cells of the same resolution is undefined, the function returns NULL. The grid distance can be undefined for any of the following reasons:
Any one of the two input H3 cells is a pentagonal cell.
The two H3 cells are separated by a pentagonal cell.
The two H3 cells are too far apart from each other.
Examples
-- Example where the two arguments are BIGINTs representing H3 cells.
> SELECT h3_distance(599686030622195711, 599686015589810175);
2
-- Example where the two arguments are hexadecimal STRINGs representing H3 cells.
> SELECT h3_distance('85283447fffffff', '8528340ffffffff')
2
-- Example of two cells that too far apart from each other.
> SELECT h3_distance(h3_longlatash3(-120, 45, 13), h3_longlatash3(120, 45, 13))
NULL
-- Example of two cells with different resolutions.
> SELECT h3_distance(h3_longlatash3(120, 45, 13), h3_longlatash3(120, 45, 12));
[H3_UNDEFINED_GRID_DISTANCE] H3 grid distance between 635869868887430591 and 631366269260060159 is undefined
-- First cell ID is a pentagon.
> SELECT h3_distance(590112357393367039, 590678880759578623)
NULL
-- Distance between two hexagons separated by a pentagon.
> SELECT h3_distance(590112494832320511, 590112632271273983)
NULL