h3_distance function (Databricks SQL)

Returns the H3 cells that are within (grid) distance k of the origin cell. The set of these H3 cells is called the k-ring of the origin cell.

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


h3_distance ( h3CellId1Expr, h3CellId2Expr )


  • 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.


A BIGINT value that is the grid distance of the two input H3 cells, that are expected to have the same resolution.

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 any of the two input cell IDs is not a valid cell ID.

Error conditions

  • If h3CellId1Expr or h3CellId2Expr 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 the grid distance may not be computed, the function returns H3_UNDEFINED_GRID_DISTANCE. The grid distance may be undefined for any of the following reasons:

    • The two input H3 cells are of different resolution.

    • 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.


-- Example where the two arguments are BIGINTs representing H3 cells.
> SELECT h3_distance(599686030622195711, 599686015589810175);

-- Example where the two arguments are hexadecimal STRINGs representing H3 cells.
> SELECT h3_distance('85283447fffffff', '8528340ffffffff')

-- Example of two cells that too far apart from each other.
> SELECT h3_distance(h3_longlatash3(-120, 45, 13), h3_longlatash3(120, 45, 13))
  [H3_UNDEFINED_GRID_DISTANCE] H3 grid distance between 635723017894513407 and 635869868887430591 is undefined

-- 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)
  [H3_UNDEFINED_GRID_DISTANCE] H3 grid distance between 590112357393367039 and 590678880759578623 is undefined

-- Distance between two hexagons separated by a pentagon.
> SELECT h3_distance(590112494832320511, 590112632271273983)
  [H3_UNDEFINED_GRID_DISTANCE] H3 grid distance between 590112494832320511 and 590112632271273983 is undefined