Skip to content

Latest commit

 

History

History
523 lines (344 loc) · 12.5 KB

h3.md

File metadata and controls

523 lines (344 loc) · 12.5 KB
aliases
/analytics-toolbox-bq/sql-reference/h3/

h3

H3 is Uber’s Hexagonal Hierarchical Spatial Index. Full documentation of the project can be found at h3geo. You can also learn more about H3 in the Overview section of this documentation.

H3_BOUNDARY

{{% bannerNote type="code" %}} carto.H3_BOUNDARY(index) {{%/ bannerNote %}}

Description

Returns a geography representing the H3 cell. It will return null on error (invalid input).

  • index: STRING The H3 cell index.

Return type

GEOGRAPHY

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_BOUNDARY('847b59dffffffff');
-- POLYGON((40.4650636223452 -3.9352772457965, 40.5465406026705 ...

{{% bannerNote type="note" title="ADDITIONAL EXAMPLES"%}}

H3_CENTER

{{% bannerNote type="code" %}} carto.H3_CENTER(index) {{%/ bannerNote %}}

Description

Returns the center of the H3 cell as a GEOGRAPHY point. It will return null on error (invalid input).

  • index: STRING The H3 cell index.

Return type

GEOGRAPHY

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_CENTER('847b59dffffffff');
-- POINT(40.3054764231743 -3.74320332556168)

H3_COMPACT

{{% bannerNote type="code" %}} carto.H3_COMPACT(indexArray) {{%/ bannerNote %}}

Description

Returns an array with the indexes of a set of hexagons across multiple resolutions that represent the same area as the input set of hexagons.

  • indexArray: ARRAY<STRING> of H3 cell indices of the same resolution.

Return type

ARRAY<STRING>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_COMPACT(['857b59c3fffffff', '857b59c7fffffff', '857b59cbfffffff', '857b59cffffffff', '857b59d3fffffff', '857b59d7fffffff', '857b59dbfffffff']);
-- 847b59dffffffff

H3_DISTANCE

{{% bannerNote type="code" %}} carto.H3_DISTANCE(origin, destination) {{%/ bannerNote %}}

Description

Returns the grid distance between two hexagon indexes. This function may fail to find the distance between two indexes if they are very far apart or on opposite sides of a pentagon. Returns null on failure or invalid input.

  • origin: STRING origin H3 cell index.
  • destination: STRING destination H3 cell index.

Return type

INT64

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_DISTANCE('847b591ffffffff', '847b59bffffffff');
-- 1

{{% bannerNote type="note" title="tip"%}} If you want the distance in meters use ST_DISTANCE between the cells (H3_BOUNDARY) or their centroid. {{%/ bannerNote %}}

{{% bannerNote type="note" title="ADDITIONAL EXAMPLES"%}}

H3_FROMGEOGPOINT

{{% bannerNote type="code" %}} carto.H3_FROMGEOGPOINT(point, resolution) {{%/ bannerNote %}}

Description

Returns the H3 cell index that the point belongs to in the required resolution. It will return null on error (invalid geography type or resolution out of bounds).

  • point: GEOGRAPHY point to get the H3 cell from.
  • resolution: INT64 number between 0 and 15 with the H3 resolution.

Return type

STRING

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(40.4168, -3.7038), 4);
-- 847b59dffffffff

{{% bannerNote type="note" title="tip"%}} If you want the cells covered by a POLYGON see H3_POLYFILL. {{%/ bannerNote %}}

{{% bannerNote type="note" title="ADDITIONAL EXAMPLES"%}}

H3_FROMLONGLAT

{{% bannerNote type="code" %}} carto.H3_FROMLONGLAT(longitude, latitude, resolution) {{%/ bannerNote %}}

Description

Returns the H3 cell index that the point belongs to in the required resolution. It will return null on error (resolution out of bounds).

  • longitude: FLOAT64 horizontal coordinate of the map.
  • latitude: FLOAT64 vertical coordinate of the map.
  • resolution: INT64 number between 0 and 15 with the H3 resolution.

Return type

STRING

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_FROMLONGLAT(40.4168, -3.7038, 4);
-- 847b59dffffffff

H3_HEXRING

{{% bannerNote type="code" %}} carto.H3_HEXRING(origin, size) {{%/ bannerNote %}}

Description

Returns all cell indexes in a hollow hexagonal ring centered at the origin in no particular order. Unlike H3_KRING, this function will throw an exception if there is a pentagon anywhere in the ring.

  • origin: STRING H3 cell index of the origin.
  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRING>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_HEXRING('837b59fffffffff', 1);
-- 837b5dfffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff

H3_ISPENTAGON

{{% bannerNote type="code" %}} carto.H3_ISPENTAGON(index) {{%/ bannerNote %}}

Description

Returns true if given H3 index is a pentagon. Returns false otherwise, even on invalid input.

  • index: STRING The H3 cell index.

Return type

BOOLEAN

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_ISPENTAGON('837b59fffffffff');
-- false
SELECT `carto-os`.carto.H3_ISPENTAGON('8075fffffffffff');
-- true

H3_ISVALID

{{% bannerNote type="code" %}} carto.H3_ISVALID(index) {{%/ bannerNote %}}

Description

Returns true when the given index is a valid H3 index, false otherwise.

  • index: STRING The H3 cell index.

Return type

BOOLEAN

{{% customSelector %}} Examples {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_ISVALID('847b59dffffffff');
-- true
SELECT `carto-os`.carto.H3_ISVALID('1');
-- false

H3_KRING

{{% bannerNote type="code" %}} carto.H3_KRING(origin, size) {{%/ bannerNote %}}

Description

Returns all cell indexes in a filled hexagonal k-ring centered at the origin in no particular order.

  • origin: STRING H3 cell index of the origin.
  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRING>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_KRING('837b59fffffffff', 1);
-- 837b59fffffffff
-- 837b58fffffffff
-- 837b5bfffffffff
-- 837a66fffffffff
-- 837a64fffffffff
-- 837b4afffffffff
-- 837b5dfffffffff

{{% bannerNote type="note" title="ADDITIONAL EXAMPLES"%}}

H3_KRING_DISTANCES

{{% bannerNote type="code" %}} carto.H3_KRING_DISTANCES(origin, size) {{%/ bannerNote %}}

Description

Returns all cell indexes and their distances in a filled hexagonal k-ring centered at the origin in no particular order.

  • origin: STRING H3 cell index of the origin.
  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRUCT<index STRING, distance INT64>>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_KRING_DISTANCES('837b59fffffffff', 1);
-- {"index": "837b59fffffffff", "distance": "0"}
-- {"index": "837b5dfffffffff", "distance": "1"}
-- {"index": "837b58fffffffff", "distance": "1"}
-- {"index": "837b5bfffffffff", "distance": "1"}
-- {"index": "837a66fffffffff", "distance": "1"}
-- {"index": "837a64fffffffff", "distance": "1"}
-- {"index": "837b4afffffffff", "distance": "1"}

H3_POLYFILL

{{% bannerNote type="code" %}} carto.H3_POLYFILL(geography, resolution) {{%/ bannerNote %}}

Description

Returns an array with all the H3 cell indexes with centers contained in a given polygon. It will return null on error (invalid geography type or resolution out of bounds). In case of lines, it will return the H3 cell indexes intersecting those lines. For a given point, it will return the H3 index of cell in which that point is contained.

{{% bannerNote type="note" title="warning"%}} Lines polyfill is calculated by approximating S2 cells to H3 cells, in some cases some cells might be missing. {{%/ bannerNote %}}

  • geography: GEOGRAPHY representing the area to cover.
  • resolution: INT64 number between 0 and 15 with the H3 resolution.

Return type

ARRAY<STRING>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_POLYFILL(
    ST_GEOGFROMTEXT('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 846b26bffffffff
-- 843e8b1ffffffff
-- 842d1e5ffffffff
-- 843ece5ffffffff
-- ...

{{% bannerNote type="note" title="ADDITIONAL EXAMPLES"%}}

H3_RESOLUTION

{{% bannerNote type="code" %}} carto.H3_RESOLUTION(index) {{%/ bannerNote %}}

Description

Returns the H3 cell resolution as an integer. It will return null on error (invalid input).

  • index: STRING The H3 cell index.

Return type

INT64

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_RESOLUTION('847b59dffffffff');
-- 4

H3_TOCHILDREN

{{% bannerNote type="code" %}} carto.H3_TOCHILDREN(index, resolution) {{%/ bannerNote %}}

Description

Returns an array with the H3 indexes of the children/descendents of the given hexagon at the given resolution.

  • index: STRING The H3 cell index.
  • resolution: INT64 number between 0 and 15 with the H3 resolution.

Return type

ARRAY<STRING>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_TOCHILDREN('837b59fffffffff', 4);
-- 847b591ffffffff
-- 847b593ffffffff
-- 847b595ffffffff
-- 847b597ffffffff
-- 847b599ffffffff
-- 847b59bffffffff
-- 847b59dffffffff

H3_TOPARENT

{{% bannerNote type="code" %}} carto.H3_TOPARENT(index, resolution) {{%/ bannerNote %}}

Description

Returns the H3 cell index of the parent of the given hexagon at the given resolution.

  • index: STRING The H3 cell index.
  • resolution: INT64 number between 0 and 15 with the H3 resolution.

Return type

STRING

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_TOPARENT('847b59dffffffff', 3);
-- 837b59fffffffff

H3_UNCOMPACT

{{% bannerNote type="code" %}} carto.H3_UNCOMPACT(indexArray, resolution) {{%/ bannerNote %}}

Description

Returns an array with the H3 indexes of a set of hexagons of the same resolution that represent the same area as the compacted input hexagons.

  • indexArray: ARRAY<STRING> of H3 cell indices.
  • resolution: INT64 number between 0 and 15 with the H3 resolution.

Return type

ARRAY<STRING>

{{% customSelector %}} Example {{%/ customSelector %}}

SELECT `carto-os`.carto.H3_UNCOMPACT(['847b59dffffffff'], 5);
-- 857b59c3fffffff
-- 857b59c7fffffff
-- 857b59cbfffffff
-- 857b59cffffffff
-- 857b59d3fffffff
-- 857b59d7fffffff
-- 857b59dbfffffff

{{% euFlagFunding %}}