Analytics Toolbox for Snowflake

Analytics Toolbox for Snowflake

Github Code

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

Description

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

  • index: STRING The H3 cell index as hexadecimal.

Return type

GEOGRAPHY

Example

1
2

SELECT carto.H3_BOUNDARY('847b59dffffffff');
-- { "coordinates": [ [ [ 40.46506362234518, -3.9352772457964957 ], [ 40.546540602670504, -3.706115055436962 ], ...

H3_COMPACT

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 of H3 cell indices of the same resolution as hexadecimal.

Return type

ARRAY

Example

1
2

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

H3_DISTANCE

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 The H3 cell index as hexadecimal.
  • destination: STRING The H3 cell index as hexadecimal.

Return type

BIGINT

Example

1
2

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

H3_FROMGEOGPOINT

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: INT number between 0 and 15 with the H3 resolution.

Return type

STRING

Example

1
2

SELECT carto.H3_FROMGEOGPOINT(ST_POINT(40.4168, -3.7038), 4);
-- 847b59dffffffff

H3_FROMLONGLAT

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: DOUBLE horizontal coordinate of the map.
  • latitude: DOUBLE vertical coordinate of the map.
  • resolution: INT number between 0 and 15 with the H3 resolution.

Return type

STRING

Example

1
2

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

H3_HEXRING

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: INT size of the ring (distance from the origin).

Return type

ARRAY

Example

1
2
3
4
5
6
7

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

H3_ISPENTAGON

Description

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

  • index: STRING The H3 cell index as hexadecimal.

Return type

BOOLEAN

Example

1
2

SELECT carto.H3_ISPENTAGON('837b59fffffffff');
-- false

1
2

SELECT carto.H3_ISPENTAGON('8075fffffffffff');
-- true

H3_ISVALID

Description

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

  • index: STRING The H3 cell index as hexadecimal.

Return type

BOOLEAN

Examples

1
2

SELECT carto.H3_ISVALID('847b59dffffffff');
-- true

1
2

SELECT carto.H3_ISVALID('1');
-- false

H3_KRING

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: INT size of the ring (distance from the origin).

Return type

ARRAY

Example

1
2
3
4
5
6
7
8

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

H3_KRING_DISTANCES

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: INT size of the ring (distance from the origin).

Return type

ARRAY

Example

1
2
3
4
5
6
7
8

SELECT 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

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

  • geography: GEOGRAPHY polygon or multipolygon representing the area to cover.
  • resolution: INT number between 0 and 15 with the H3 resolution.

Return type

ARRAY

Example

1
2
3
4
5
6
7

SELECT carto.H3_POLYFILL(
    TO_GEOGRAPHY('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), 4);
-- 842da29ffffffff
-- 843f725ffffffff
-- 843eac1ffffffff
-- 8453945ffffffff
-- ...

H3_TOCHILDREN

Description

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

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

Return type

ARRAY

Example

1
2
3
4
5
6
7
8

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

H3_TOPARENT

Description

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

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

Return type

STRING

Example

1
2

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

H3_UNCOMPACT

Description

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

  • indexArray: ARRAY of H3 cell indices as hexadecimal.
  • resolution: INT number between 0 and 15 with the H3 resolution.

Return type

ARRAY

Example

1
2
3
4
5
6
7
8

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