# h3

[H3](https://eng.uber.com/h3/) is Uber’s Hexagonal Hierarchical Spatial Index. Full documentation of the project can be found at [h3geo](https://h3geo.org/docs). You can also learn more about H3 in the [Spatial Indexes section](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/key-concepts/spatial-indexes#h3) of this documentation.

## H3\_BOUNDARY <a href="#h3_boundary" id="h3_boundary"></a>

```sql
H3_BOUNDARY(index)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_BOUNDARY('84390cbffffffff');
-- POLYGON((-3.57692743539573 40.6134385959352, -3.85975632308016 ...
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_BOUNDARY('84390cbffffffff');
-- POLYGON((-3.57692743539573 40.6134385959352, -3.85975632308016 ...
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_BOUNDARY('84390cbffffffff');
-- POLYGON((-3.57692743539573 40.6134385959352, -3.85975632308016 ...
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [An H3 grid of Starbucks locations and simple cannibalization analysis](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/an-h3-grid-of-starbucks-locations-and-simple-cannibalization-analysis)
  {% endhint %}

## H3\_CENTER <a href="#h3_center" id="h3_center"></a>

```sql
H3_CENTER(index)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.

**Return type**

`GEOGRAPHY`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_CENTER('84390cbffffffff');
-- POINT(-3.61760324662829 40.3725405821658)
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_CENTER('84390cbffffffff');
-- POINT(-3.61760324662829 40.3725405821658)
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_CENTER('84390cbffffffff');
-- POINT(-3.61760324662829 40.3725405821658)
```

{% endtab %}
{% endtabs %}

## H3\_COMPACT <a href="#h3_compact" id="h3_compact"></a>

```sql
H3_COMPACT(indexArray)
```

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

**Input parameters**

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

**Return type**

`ARRAY<STRING>`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_COMPACT(['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_COMPACT(['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_COMPACT(['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
-- 84390cbffffffff
```

{% endtab %}
{% endtabs %}

## H3\_DISTANCE <a href="#h3_distance" id="h3_distance"></a>

```sql
H3_DISTANCE(origin, destination)
```

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

**Input parameters**

* `origin`: `STRING` origin H3 cell index.
* `destination`: `STRING` destination H3 cell index.

**Return type**

`INT64`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**tip**

If you want the distance in meters use [ST\_DISTANCE](https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_distance) between the cells ([H3\_BOUNDARY](#h3_boundary)) or their centroid.
{% endhint %}

{% hint style="info" %}
**Additional examples**

* [Opening a new Pizza Hut location in Honolulu](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/opening-a-new-pizza-hut-location-in-honolulu)
  {% endhint %}

## H3\_FROMGEOGPOINT <a href="#h3_fromgeogpoint" id="h3_fromgeogpoint"></a>

```sql
H3_FROMGEOGPOINT(point, resolution)
```

**Description**

Returns the H3 cell index that the point belongs to in the requested `resolution`. It will return `null` on error (invalid geography type or resolution out of bounds). This function is an alias for `H3_FROMGEOPOINT`.

**Input parameters**

* `point`: `GEOGRAPHY` point to get the H3 cell from.
* `resolution`: `INT64` number between 0 and 15 with the [H3 resolution](https://h3geo.org/docs/core-library/restable).

**Return type**

`STRING`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(-3.7038, 40.4168), 4);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(-3.7038, 40.4168), 4);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(-3.7038, 40.4168), 4);
-- 84390cbffffffff
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**tip**

If you want the cells covered by a POLYGON see [H3\_POLYFILL](#h3_polyfill).
{% endhint %}

{% hint style="info" %}
**Additional examples**

* [An H3 grid of Starbucks locations and simple cannibalization analysis](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/an-h3-grid-of-starbucks-locations-and-simple-cannibalization-analysis)
  {% endhint %}

## H3\_FROMLONGLAT <a href="#h3_fromlonglat" id="h3_fromlonglat"></a>

```sql
H3_FROMLONGLAT(longitude, latitude, resolution)
```

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

**Input parameters**

* `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](https://h3geo.org/docs/core-library/restable).

**Return type**

`STRING`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 84390cbffffffff
```

{% endtab %}
{% endtabs %}

## H3\_HEXRING <a href="#h3_hexring" id="h3_hexring"></a>

```sql
H3_HEXRING(origin, size)
```

**Description**

Returns all cell indexes in a **hollow hexagonal ring** centered at the origin in no particular order. Unlike [H3\_KRING](#h3_kring), this function will throw an exception if there is a pentagon anywhere in the ring.

**Input parameters**

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

**Return type**

`ARRAY<STRING>`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_HEXRING('84390cbffffffff', 1);
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_HEXRING('84390cbffffffff', 1);
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_HEXRING('84390cbffffffff', 1);
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
```

{% endtab %}
{% endtabs %}

## H3\_INT\_TOSTRING <a href="#h3_int_tostring" id="h3_int_tostring"></a>

```sql
H3_INT_TOSTRING(index)
```

**Description**

Converts the integer representation of the H3 index to the string representation.

**Input parameters**

* `index`: `INT64` The H3 cell index.

**Return type**

`STRING`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff
```

{% endtab %}
{% endtabs %}

## H3\_ISPENTAGON <a href="#h3_ispentagon" id="h3_ispentagon"></a>

```sql
H3_ISPENTAGON(index)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.

**Return type**

`BOOL`

**Examples**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_ISPENTAGON('84390cbffffffff');
-- FALSE
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_ISPENTAGON('84390cbffffffff');
-- FALSE
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_ISPENTAGON('84390cbffffffff');
-- FALSE
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_ISPENTAGON('8075fffffffffff');
-- TRUE
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_ISPENTAGON('8075fffffffffff');
-- TRUE
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_ISPENTAGON('8075fffffffffff');
-- TRUE
```

{% endtab %}
{% endtabs %}

## H3\_ISVALID <a href="#h3_isvalid" id="h3_isvalid"></a>

```sql
H3_ISVALID(index)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.

**Return type**

`BOOL`

**Examples**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_ISVALID('84390cbffffffff');
-- TRUE
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_ISVALID('84390cbffffffff');
-- TRUE
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_ISVALID('84390cbffffffff');
-- TRUE
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_ISVALID('1');
-- FALSE
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_ISVALID('1');
-- FALSE
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_ISVALID('1');
-- FALSE
```

{% endtab %}
{% endtabs %}

## H3\_KRING <a href="#h3_kring" id="h3_kring"></a>

```sql
H3_KRING(origin, size)
```

**Description**

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

**Input parameters**

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

**Return type**

`ARRAY<STRING>`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_KRING('84390cbffffffff', 1);
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_KRING('84390cbffffffff', 1);
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_KRING('84390cbffffffff', 1);
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [An H3 grid of Starbucks locations and simple cannibalization analysis](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/an-h3-grid-of-starbucks-locations-and-simple-cannibalization-analysis)
  {% endhint %}

## H3\_KRING\_DISTANCES <a href="#h3_kring_distances" id="h3_kring_distances"></a>

```sql
H3_KRING_DISTANCES(origin, size)
```

**Description**

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

**Input parameters**

* `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>>`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": "0"}
-- {"index": "84390c9ffffffff", "distance": "1"}
-- {"index": "84390c1ffffffff", "distance": "1"}
-- {"index": "84390c3ffffffff", "distance": "1"}
-- {"index": "84390ddffffffff", "distance": "1"}
-- {"index": "84392b7ffffffff", "distance": "1"}
-- {"index": "84392b5ffffffff", "distance": "1"}
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": "0"}
-- {"index": "84390c9ffffffff", "distance": "1"}
-- {"index": "84390c1ffffffff", "distance": "1"}
-- {"index": "84390c3ffffffff", "distance": "1"}
-- {"index": "84390ddffffffff", "distance": "1"}
-- {"index": "84392b7ffffffff", "distance": "1"}
-- {"index": "84392b5ffffffff", "distance": "1"}
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": "0"}
-- {"index": "84390c9ffffffff", "distance": "1"}
-- {"index": "84390c1ffffffff", "distance": "1"}
-- {"index": "84390c3ffffffff", "distance": "1"}
-- {"index": "84390ddffffffff", "distance": "1"}
-- {"index": "84392b7ffffffff", "distance": "1"}
-- {"index": "84392b5ffffffff", "distance": "1"}
```

{% endtab %}
{% endtabs %}

## H3\_POLYFILL <a href="#h3_polyfill" id="h3_polyfill"></a>

```sql
H3_POLYFILL(geog, resolution)
```

**Description**

Returns an array of H3 cell indexes contained in the given geography (Polygon, MultiPolygon) at a requested resolution. Containment is determined by the cells' center. This function is equivalent to [`H3_POLYFILL_MODE`](#h3_polyfill_mode) with mode `center`.

**Input parameters**

* `geog`: `GEOGRAPHY` representing the shape to cover.
* `resolution`: `INT64` level of detail. The value must be between 0 and 15 ([H3 resolution table](https://h3geo.org/docs/core-library/restable)).

{% hint style="warning" %}
Use [`H3_POLYFILL_MODE`](#h3_polyfill_mode) with mode `intersects` in the following cases:

* You want to provide the minimum covering set of a Polygon, MultiPolygon.
* The input geography type is Point, MultiPoint, LineString, MultiLineString.
  {% endhint %}

**Return type**

`ARRAY<STRING>`

**Examples**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
);
-- [89390cb1b4bffff]
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
);
-- [89390cb1b4bffff]
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
);
-- [89390cb1b4bffff]
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT h3
FROM UNNEST(`carto-un`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
)) AS h3;
-- 89390cb1b4bffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT h3
FROM UNNEST(`carto-un-eu`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
)) AS h3;
-- 89390cb1b4bffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT h3
FROM UNNEST(carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
)) AS h3;
-- 89390cb1b4bffff
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT h3
FROM <my-project>.<my-dataset>.<my-table>,
  UNNEST(`carto-un`.carto.H3_POLYFILL(geog, 9)) AS h3;
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT h3
FROM <my-project>.<my-dataset>.<my-table>,
  UNNEST(`carto-un-eu`.carto.H3_POLYFILL(geog, 9)) AS h3;
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT h3
FROM <my-project>.<my-dataset>.<my-table>,
  UNNEST(carto.H3_POLYFILL(geog, 9)) AS h3;
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
**Additional examples**

* [Opening a new Pizza Hut location in Honolulu](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/opening-a-new-pizza-hut-location-in-honolulu)
  {% endhint %}

## H3\_POLYFILL\_MODE <a href="#h3_polyfill_mode" id="h3_polyfill_mode"></a>

```sql
H3_POLYFILL_MODE(geog, resolution, mode)
```

**Description**

Returns an array of H3 cell indexes contained in the given geography at a requested resolution. Containment is determined by the mode: center, intersects, contains.

**Input parameters**

* `geog`: `GEOGRAPHY` representing the shape to cover.
* `resolution`: `INT64` level of detail. The value must be between 0 and 15 ([H3 resolution table](https://h3geo.org/docs/core-library/restable)).
* `mode`: `STRING`.
  * `center` returns the indexes of the H3 cells which centers intersect the input geography (polygon). The resulting H3 set does not fully cover the input geography, however, this is **significantly faster** that the other modes. This mode is not compatible with points or lines. Equivalent to [`H3_POLYFILL`](#h3_polyfill).
  * `intersects` returns the indexes of the H3 cells that intersect the input geography. The resulting H3 set will completely cover the input geography (point, line, polygon).
  * `contains` returns the indexes of the H3 cells that are entirely contained inside the input geography (polygon). This mode is not compatible with points or lines.

Mode `center`:

<figure><img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-1b47ba3ccecd5a3281889e53f1a0310c457d0997%2Fh3_polyfill_mode_center.png?alt=media" alt=""><figcaption></figcaption></figure>

Mode `intersects`:

<figure><img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-b5b98d05406d730178c9f0abd9cfcaaedfefc634%2Fh3_polyfill_mode_intersects.png?alt=media" alt=""><figcaption></figcaption></figure>

Mode `contains`:

<figure><img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-aff8edd843d4e19abe187c95890e51236b85221a%2Fh3_polyfill_mode_contains.png?alt=media" alt=""><figcaption></figcaption></figure>

**Return type**

`ARRAY<STRING>`

**Examples**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
);
-- [89390cb1b5bffff, 89390ca34b3ffff, 89390ca3487ffff, 89390ca3497ffff, 89390cb1b4bffff, 89390cb1b4fffff]
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
);
-- [89390cb1b5bffff, 89390ca34b3ffff, 89390ca3487ffff, 89390ca3497ffff, 89390cb1b4bffff, 89390cb1b4fffff]
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
);
-- [89390cb1b5bffff, 89390ca34b3ffff, 89390ca3487ffff, 89390ca3497ffff, 89390cb1b4bffff, 89390cb1b4fffff]
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT h3
FROM UNNEST(`carto-un`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
)) AS h3;
-- 89390cb1b5bffff
-- 89390ca34b3ffff
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT h3
FROM UNNEST(`carto-un-eu`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
)) AS h3;
-- 89390cb1b5bffff
-- 89390ca34b3ffff
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT h3
FROM UNNEST(carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
)) AS h3;
-- 89390cb1b5bffff
-- 89390ca34b3ffff
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT h3
FROM <my-project>.<my-dataset>.<my-table>,
  UNNEST(`carto-un`.carto.H3_POLYFILL_MODE(geog, 9, 'intersects')) AS h3;
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT h3
FROM <my-project>.<my-dataset>.<my-table>,
  UNNEST(`carto-un-eu`.carto.H3_POLYFILL_MODE(geog, 9, 'intersects')) AS h3;
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT h3
FROM <my-project>.<my-dataset>.<my-table>,
  UNNEST(carto.H3_POLYFILL_MODE(geog, 9, 'intersects')) AS h3;
```

{% endtab %}
{% endtabs %}

## H3\_POLYFILL\_TABLE (BETA) <a href="#h3_polyfill_table-beta" id="h3_polyfill_table-beta"></a>

```sql
H3_POLYFILL_TABLE(input_query, resolution, mode, output_table)
```

**Description**

Returns a table with the H3 cell indexes contained in the given geography at a requested resolution. Containment is determined by the mode: center, intersects, contains. All the attributes except the geography will be included in the output table, clustered by the h3 column.

**Input parameters**

* `input_query`: `STRING` input data to polyfill. It must contain a column `geom` with the shape to cover. Additionally, other columns can be included.
* `resolution`: `INT64` level of detail. The value must be between 0 and 15 ([H3 resolution table](https://h3geo.org/docs/core-library/restable)).
* `mode`: `STRING`.
  * `center` returns the indexes of the H3 cells which centers intersect the input geography (polygon). The resulting H3 set does not fully cover the input geography, however, this is **significantly faster** that the other modes. This mode is not compatible with points or lines. Equivalent to [`H3_POLYFILL`](#h3_polyfill).
  * `intersects` returns the indexes of the H3 cells that intersect the input geography. The resulting H3 set will completely cover the input geography (point, line, polygon).
  * `contains` returns the indexes of the H3 cells that are entirely contained inside the input geography (polygon). This mode is not compatible with points or lines.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-project>.<my-dataset>.<my-output-table>`. The process will fail if the table already exists.

Mode `center`:

<figure><img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-1b47ba3ccecd5a3281889e53f1a0310c457d0997%2Fh3_polyfill_mode_center.png?alt=media" alt=""><figcaption></figcaption></figure>

Mode `intersects`:

<figure><img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-b5b98d05406d730178c9f0abd9cfcaaedfefc634%2Fh3_polyfill_mode_intersects.png?alt=media" alt=""><figcaption></figcaption></figure>

Mode `contains`:

<figure><img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-aff8edd843d4e19abe187c95890e51236b85221a%2Fh3_polyfill_mode_contains.png?alt=media" alt=""><figcaption></figcaption></figure>

**Output**

The results are stored in the table named `<my-output-table>`, which contains the following columns:

* `h3`: `STRING` the geometry of the considered point.
* The rest of columns included in `input_query` except `geom`.

**Examples**

{% tabs %}
{% tab title="carto-un" %}

```sql
CALL `carto-un`.carto.H3_POLYFILL_TABLE(
  "SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
  9, 'intersects',
  '<my-project>.<my-dataset>.<my-output-table>'
);
-- The table `<my-project>.<my-dataset>.<my-output-table>` will be created
-- with column: h3
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
CALL `carto-un-eu`.carto.H3_POLYFILL_TABLE(
  "SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
  9, 'intersects',
  '<my-project>.<my-dataset>.<my-output-table>'
);
-- The table `<my-project>.<my-dataset>.<my-output-table>` will be created
-- with column: h3
```

{% endtab %}

{% tab title="manual" %}

```sql
CALL carto.H3_POLYFILL_TABLE(
  "SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
  9, 'intersects',
  '<my-project>.<my-dataset>.<my-output-table>'
);
-- The table `<my-project>.<my-dataset>.<my-output-table>` will be created
-- with column: h3
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="carto-un" %}

```sql
CALL `carto-un`.carto.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<my-project>.<my-dataset>.<my-table>`',
  9, 'center',
  '<my-project>.<my-dataset>.<my-output-table>'
);
-- The table `<my-project>.<my-dataset>.<my-output-table>` will be created
-- with columns: h3, name, value
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
CALL `carto-un-eu`.carto.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<my-project>.<my-dataset>.<my-table>`',
  9, 'center',
  '<my-project>.<my-dataset>.<my-output-table>'
);
-- The table `<my-project>.<my-dataset>.<my-output-table>` will be created
-- with columns: h3, name, value
```

{% endtab %}

{% tab title="manual" %}

```sql
CALL carto.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<my-project>.<my-dataset>.<my-table>`',
  9, 'center',
  '<my-project>.<my-dataset>.<my-output-table>'
);
-- The table `<my-project>.<my-dataset>.<my-output-table>` will be created
-- with columns: h3, name, value
```

{% endtab %}
{% endtabs %}

## H3\_RESOLUTION <a href="#h3_resolution" id="h3_resolution"></a>

```sql
H3_RESOLUTION(index)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.

**Return type**

`INT64`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_RESOLUTION('84390cbffffffff');
-- 4
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_RESOLUTION('84390cbffffffff');
-- 4
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_RESOLUTION('84390cbffffffff');
-- 4
```

{% endtab %}
{% endtabs %}

## H3\_STRING\_TOINT <a href="#h3_string_toint" id="h3_string_toint"></a>

```sql
H3_STRING_TOINT(index)
```

**Description**

Converts the string representation of the H3 index to the integer representation.

**Input parameters**

* `index`: `STRING` The H3 cell index.

**Return type**

`INT64`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567
```

{% endtab %}
{% endtabs %}

## H3\_TOCHILDREN <a href="#h3_tochildren" id="h3_tochildren"></a>

```sql
H3_TOCHILDREN(index, resolution)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.
* `resolution`: `INT64` number between 0 and 15 with the [H3 resolution](https://h3geo.org/docs/core-library/restable).

**Return type**

`ARRAY<STRING>`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_TOCHILDREN('83390cfffffffff', 4);
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_TOCHILDREN('83390cfffffffff', 4);
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_TOCHILDREN('83390cfffffffff', 4);
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff
```

{% endtab %}
{% endtabs %}

## H3\_TOPARENT <a href="#h3_toparent" id="h3_toparent"></a>

```sql
H3_TOPARENT(index, resolution)
```

**Description**

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

**Input parameters**

* `index`: `STRING` The H3 cell index.
* `resolution`: `INT64` number between 0 and 15 with the [H3 resolution](https://h3geo.org/docs/core-library/restable).

**Return type**

`STRING`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff
```

{% endtab %}
{% endtabs %}

## H3\_UNCOMPACT <a href="#h3_uncompact" id="h3_uncompact"></a>

```sql
H3_UNCOMPACT(indexArray, resolution)
```

**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](#h3_compact) input hexagons.

**Input parameters**

* `indexArray`: `ARRAY<STRING>` of H3 cell indices.
* `resolution`: `INT64` number between 0 and 15 with the [H3 resolution](https://h3geo.org/docs/core-library/restable).

**Return type**

`ARRAY<STRING>`

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT `carto-un`.carto.H3_UNCOMPACT(['83390cfffffffff'], 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT `carto-un-eu`.carto.H3_UNCOMPACT(['83390cfffffffff'], 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT carto.H3_UNCOMPACT(['83390cfffffffff'], 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff
```

{% endtab %}
{% endtabs %}

<img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-1c82685e4e434438152a8e3d867df996413489fe%2Feu-flag-website.png?alt=media&#x26;token=4343f6e5-973a-4e9a-8e14-50366a086f72" alt="EU flag" data-size="line">This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.