h3_kringdistances function (Databricks SQL)

Returns all H3 cells (represented as long integers or strings) within grid distance k from the origin H3 cell, along with their distance from the origin H3 cell.

Requires: SQL warehouse version 2022.35 or higher. This version is available in the Preview channel.

Syntax

h3_kringdistances ( h3CellIdExpr, kExpr )

Arguments

  • h3CellIdExpr: A BIGINT expression, or a hexadecimal STRING expression representing an H3 cell ID.

  • kExpr: An INTEGER expression representing the grid distance. kExpr must be non-negative.

Returns

An ARRAY of named structs with two fields, named cellid and distance, respectively, where the first field in the struct is an H3 cell ID (represented as a long integer or string) and the second field in the struct is its distance from the origin H3 cell (represented as an integer). The type for the H3 cell IDs in the output is the same as the type of h3CellIdExpr.

The elements in the returned ARRAY are sorted with respect to their distance from the origin H3 cell. The elements corresponding to the same distance in the returned ARRAY may be returned in any order. The function returns NULL if any one of the input expressions is NULL. The function does partial validation regarding whether the input argument is a valid H3 cell ID. 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 the input cell ID is not a valid cell ID. If the value of the grid distance is zero, the returned array contains a single value equal to the input H3 cell ID.

Error conditions

  • If h3CellIdExpr is a STRING that cannot be converted to a BIGINT or corresponds to a BIGINT value that is smaller than 0x08001fffffffffff or larger than 0x08ff3b6db6db6db6, the function returns H3_INVALID_CELL_ID

  • If kExpr is negative, the function returns H3_INVALID_GRID_DISTANCE_VALUE

Examples

-- Simple example where the first argument is a BIGINT.
> SELECT h3_kringdistances(599686042433355775, 1)
  [{"cellid":599686042433355775,"distance":0},{"cellid":599686030622195711,"distance":1},{"cellid":599686044580839423,"distance":1},{"cellid":599686038138388479,"distance":1},{"cellid":599686043507097599,"distance":1},{"cellid":599686015589810175,"distance":1},{"cellid":599686014516068351,"distance":1}]

-- Simple example where the first argument is a STRING.
> SELECT h3_kringdistances('85283473fffffff', 1)
  [{"cellid":"85283473fffffff","distance":0},{"cellid":"85283447fffffff","distance":1},{"cellid":"8528347bfffffff","distance":1},{"cellid":"85283463fffffff","distance":1},{"cellid":"85283477fffffff","distance":1},{"cellid":"8528340ffffffff","distance":1},{"cellid":"8528340bfffffff","distance":1}]

-- First input is an invalid H3 cell ID.
> SELECT h3_kringdistances(0, 0)
  [H3_INVALID_CELL_ID] 0 is not a valid H3 cell ID

-- Second input is an invalid grid distance value.
> SELECT h3_kringdistances('85283473fffffff', -1)
  [H3_INVALID_GRID_DISTANCE_VALUE] H3 grid distance -1 must be non-negative