h3_uncompact function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

Uncompacts the input set of H3 cells to the specified resolution. The uncompacted set covers the same set of H3 cells as the original one using cells at the specified resolution.

Syntax

h3_uncompact ( h3CellIdsExpr, resolutionExpr )

Arguments

  • h3CellIdsExpr: An ARRAY of BIGINTs expression, or an ARRAY of hexadecimal STRINGs expression representing an array of H3 cell IDs.
  • resolutionExpr: An INTEGER expression, whose value is expected to be between the maximum resolution of the input H3 cells and 15 inclusive, specifying the resolution of the H3 cell IDs in the output ARRAY.

Returns

An ARRAY of values of the same type as the values in the input ARRAY expression h3cellIdsExpr. The resolution of the H3 cell IDs in the output ARRAY is equal to the value of the resolutionExpr.

The function returns NULL if the input 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 cell IDs in the input ARRAY is not a valid cell ID. NULL values in the input array are ignored.

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 resolutionExpr is smaller than the maximum resolution of the H3 cell in the input ARRAY, or larger than 15, the function returns H3_INVALID_RESOLUTION_VALUE.

Examples

-- Example where the input is an ARRAY of BIGINTs
> SELECT h3_uncompact(ARRAY(599686030622195711,599686015589810175,599686014516068351,599686034917163007,599686029548453887,599686032769679359,599686198125920255,599686023106002943,599686027400970239,599686013442326527,599686012368584703,599686018811035647,595182446027210751), 5);
  [599686030622195711,599686015589810175,599686014516068351,599686034917163007,599686029548453887,599686032769679359,599686198125920255,599686023106002943,599686027400970239,599686013442326527,599686012368584703,599686018811035647,599686038138388479,599686039212130303,599686040285872127,599686041359613951,599686042433355775,599686043507097599,599686044580839423]

-- In this example we verify that uncompacting the compacted set of the 2-ring of an H3 cell at the cell's resolution returns the original 2-ring.
> SELECT COUNT(*) FROM ((SELECT explode(h3_uncompact(h3_compact(h3_kring(599686042433355775, 2)), h3_resolution(599686042433355775)))) MINUS (SELECT  explode(h3_kring(599686042433355775, 2))))
  0

-- Second input is an invalid resolution value.
> SELECT h3_uncompact(ARRAY(599686030622195711,599686015589810175,599686014516068351,599686034917163007,599686029548453887,599686032769679359,599686198125920255,599686023106002943,599686027400970239,599686013442326527,599686012368584703,599686018811035647,595182446027210751), 2);
  [H3_INVALID_RESOLUTION_VALUE] H3 resolution 2 must be between 5 and 15, inclusive