`h3_coverash3` function

Article
05/27/2024

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

Returns an ARRAY of BIGINT values representing H3 cell IDs. These values correspond to the minimal set of hexagons or pentagons, of the specified resolution that fully cover the input linear or areal geography.

Syntax

h3_coverash3 ( geographyExpr, resolutionExpr )

Arguments

geographyExpr: A BINARY or STRING expression representing a linear (linestring or multilinestring) or areal (polygon or multipolygon) geography in WKB, WKT, or GeoJSON. The geography must 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 BIGINT values corresponding to the minimal set of H3 cell IDs, of the specified resolution, that fully cover the input linear or areal geography.

The function returns NULL if any one of the input expressions is NULL. If the first input argument is of type BINARY, the input value must be the WKB description of a polygon or a multipolygon.

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

The dimension of the input linestring, polygon, multilinestring, or multipolygon can be 2D, 3DZ, 3DM, or 4D.

Error conditions

If geographyExpr is of type BINARY and the value is either an invalid WKB or does not represent a linestring, polygon, multilinestring, or multipolygon, the function returns WKB_PARSE_ERROR.
If geographyExpr is of type STRING and the value is either an invalid WKT or does not represent a linestring, polygon, multilinestring, or multipolygon, the function returns WKT_PARSE_ERROR.
If geographyExpr is of type STRING and the value is either an invalid GeoJSON or does not represent a linestring, polygon, multilinestring, or multipolygon, the function returns GEOJSON_PARSE_ERROR.
If resolutionExpr is smaller than 0 or larger than 15, the function returns H3_INVALID_RESOLUTION_VALUE.

Examples

-- Simple example where the input is a triangle in WKT format.
> SELECT h3_coverash3('POLYGON((-122.4194 37.7749,-118.2437 34.0522,-74.0060 40.7128,-122.4194 37.7749))', 0)
  [577164439745200127, 577199624117288959, 577234808489377791, 577762574070710271]

-- Simple example where the input is a triangle in hexadecimal WKB format.
> SELECT h3_coverash3(unhex('0103000000010000000400000050fc1873d79a5ec0d0d556ec2fe342404182e2c7988f5dc0f46c567dae064140aaf1d24d628052c05e4bc8073d5b444050fc1873d79a5ec0d0d556ec2fe34240'), 0)
  [577164439745200127, 577199624117288959, 577234808489377791, 577762574070710271]

-- Simple example where the input is a linestring in GeoJSON format.
SELECT h3_coverash3('{"type":"LineString","coordinates":[[-122.4194,37.7749],[-118.2437,34.0522],[-74.0060,40.7128]]}', 1)
  [582248581512036351, 581698825698148351, 581707621791170559, 581716417884192767, 581641651093503999, 581650447186526207, 581672437419081727]

-- Feeding an empty multipoint in GeoJSON format (as opposed to a linestring, polygon, multilinestring, or multipolygon).
> SELECT h3_coverash3('{"type":"MultiPoint","coordinates":[]}', 2)
  [GEOJSON_PARSE_ERROR] Error parsing GeoJSON: Invalid or unsupported type '"MultiPoint"' at position 9

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

-- Feeding an invalid polygon in WKT (polygon is not closed)
> SELECT h3_coverash3('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

-- Resolution is out of range.
> SELECT h3_coverash3('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

h3_coverash3 function