`h3_tessellateaswkb` function

Article
07/01/2024

Applies to: check marked yes Databricks SQL Databricks Runtime 14.2 and above

Returns a tessellation of the input geography using H3 cells at the specified resolution. The tessellation is represented by an ARRAY of structs, each representing an element of the tessellation. Each element of the tessellation consists of a H3 cell ID (represented as a long integer), a Boolean value indicating whether the input geography fully covers the cell, and a BINARY value corresponding to the WKB description of the intersection of the input geography with the H3 cell.

The returned H3 cells collectively form a minimal covering set of hexagons or pentagons, at the specified resolution, that fully cover the input geography. The geographies returned (via their WKB representations) form a tessellation of the input geography.

Syntax

h3_tessellateaswkb ( geographyExpr, resolutionExpr )

Arguments

geographyExpr: A BINARY or STRING expression representing a geography in WKB, WKT, or GeoJSON. The geography is expected to have longitude and latitude coordinates in degrees that refer to the WGS84 coordinate reference system.

resolutionExpr: An INT expression, with a value between 0 and 15 inclusive, specifying the resolution for the H3 cell IDs.

Returns

An ARRAY of named structs with three fields of type BIGINT, BOOLEAN, and BINARY, named cellid, core, and chip, respectively, representing the tessellation of the input geography with H3 cells at the specified resolution.

The first field in the struct is an H3 cell ID (represented as a long integer). The second field in the struct is a Boolean value indicating whether the H3 cell is a core cell, in which case the field's value is true or not, in which case the field's value is false. A core cell is an H3 cell that is fully covered by the input geography (that is, its intersection with the input geography is the cell itself). The third field is a BINARY value representing the WKB description of the geography that is the intersection of the input geography and the H3 cell. The returned H3 cells collectively form a minimal covering set of the input geography. The geographies, corresponding to the returned WKB descriptions, collectively form a tessellation (decomposition) of the input geography.

The function returns NULL if any of the input expressions is NULL.

If the first input argument is of type BINARY, the input value is expected to be the WKB description of a point, linestring, polygon, multipoint, multilinestring, or multipolygon.

If the first input argument is of type STRING, the input value is expected to be either the WKT or the GeoJSON description of a point, linestring, polygon, multipoint, multilinestring, or multipolygon.

The dimension of the input geography can be 2D, 3DZ, 3DM, or 4D.

Error conditions

If geographyExpr is of type BINARY and the value is either an invalid WKB or represents a geometry collection, the function returns WKB_PARSE_ERROR.
If geographyExpr is of type STRING and the value is either an invalid WKT or represents a geometry collection, the function returns WKT_PARSE_ERROR.
If geographyExpr is of type STRING and the value is either an invalid GeoJSON or represents a geometry collection, the function returns GEOJSON_PARSE_ERROR.
If resolutionExpr is smaller than 0 or larger than 15, the function returns H3_INVALID_RESOLUTION_VALUE.

Examples

> SELECT h3_h3tostring(cellid), core, hex(chip) FROM (SELECT inline(h3_tessellateaswkb('MULTIPOINT(20 0,20 10,40 30)', 0))) ORDER BY 1
  802dfffffffffff     false 010100000000000000000044400000000000003E40
  806bfffffffffff     false 010400000002000000010100000000000000000034400000000000000000010100000000000000000034400000000000002440

-- Feeding an empty geometry collection in GeoJSON format (geometry collections are not supported).
> SELECT h3_tessellateaswkb('{"type":"GeometryCollection","geometries":[]}', 2)
  [GEOJSON_PARSE_ERROR] Error parsing GeoJSON: Invalid or unsupported type '"GeometryCollection"' at position 9 SQLSTATE: 22023

-- Feeding an invalid WKB (invalid endianness value)
> SELECT h3_tessellateaswkb(unhex('020700000000'), 2)
  [WKB_PARSE_ERROR] Error parsing WKB: Invalid byte order 2 at position 1 SQLSTATE: 22023

-- Feeding an invalid polygon in WKT (polygon is not closed)
> SELECT h3_tessellateaswkb('POLYGON((-122.4194 37.7749,-118.2437 34.0522,-74.0060 40.7128,-74.0060 40.7128))', 2)
  [WKT_PARSE_ERROR] Error parsing WKT: Found non-closed ring at position 80 SQLSTATE: 22023

-- Resolution is out of range.
> SELECT h3_tessellateaswkb('POLYGON((-122.4194 37.7749,-118.2437 34.0522,-74.0060 40.7128,-122.4194 37.7749))', 16)
  [H3_INVALID_RESOLUTION_VALUE] H3 resolution 16 must be between 0 and 15, inclusive SQLSTATE: 22023

h3_tessellateaswkb function