# An H3 grid of Starbucks locations and simple cannibalization analysis

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FhB2W9xXbzzo0kEuXMe3S%2Fintermediate%20banner.png?alt=media&#x26;token=4acd2cc7-c7e8-46c0-9669-6f6b73c030dd" alt="Intermediate difficulty banner" width="175"><figcaption></figcaption></figure></div>

## Building the H3 grid <a href="#building-the-h3-grid" id="building-the-h3-grid"></a>

We are going to demonstrate how fast and easy it is to make a visualization of an H3 grid to identify the concentration of Starbucks locations in the US.

The first step is to [import](https://cloud.google.com/bigquery/docs/batch-loading-data#loading_data_from_local_files) the Starbucks locations [dataset](https://libs.cartocdn.com/spatial-extension/samples/starbucks-locations-usa.csv) into a BigQuery table called `starbucks_locations_usa`. If you want to skip this step, you can use the publicly available table `cartobq.docs.starbucks_locations_usa` instead. Then, with a single query, we are going to calculate how many Starbucks locations fall within each H3 grid cell of resolution 4.

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

```sql
WITH
  data AS (
  SELECT
    `carto-un`.carto.H3_FROMGEOGPOINT(geog, 4) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  GROUP BY h3id
  )
SELECT
  h3id,
  agg_total,
  `carto-un-eu`.carto.H3_BOUNDARY(h3id) AS geom
FROM
  data;
```

{% endtab %}

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

```sql
WITH
  data AS (
  SELECT
    `carto-un-eu`.carto.H3_FROMGEOGPOINT(geog, 4) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  GROUP BY h3id
  )
SELECT
  h3id,
  agg_total,
  `carto-un-eu`.carto.H3_BOUNDARY(h3id) AS geom
FROM
  data;
```

{% endtab %}

{% tab title="manual" %}

```sql
WITH
  data AS (
  SELECT
    carto.H3_FROMGEOGPOINT(geog, 4) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  GROUP BY h3id
  )
SELECT
  h3id,
  agg_total,
  `carto-un-eu`.carto.H3_BOUNDARY(h3id) AS geom
FROM
  data;
```

{% endtab %}
{% endtabs %}

This query adds two new columns to our dataset: `geom`, representing the boundary of each of the H3 grid cells where there’s at least one Starbucks, and `agg_total`, containing the total number of locations that fall within each cell. Finally, we can visualize the result.

{% embed url="<https://public.carto.com/builder/e88dc8a5-522b-4e62-8998-adbf8348174e>" %}

Note: this visualization is made using Builder, where you can easily import your BigQuery data using our connector, but you can also create a quick visualization using [BigQuery Geo Viz](https://bigquerygeoviz.appspot.com/).

## Using finer resolution H3 for simple cannibalization analysis <a href="#using-finer-resolution-h3-for-simple-cannibalization-analysis" id="using-finer-resolution-h3-for-simple-cannibalization-analysis"></a>

Next, we will analyze in finer detail the grid cell that we have identified contains the highest concentration of Starbucks locations, with ID `8428d55ffffffff`.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/wVPnyubOUG6MONJmNfya/h3-most-starbucks.png" alt="Multiresolution quadkeys"><figcaption><p>H3 grid of resolution 4 with 293 Starbucks locations.</p></figcaption></figure>

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

```sql
WITH
  data AS (
  SELECT
    `carto-un`.carto.H3_FROMGEOGPOINT(geog, 9) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  WHERE
    ST_INTERSECTS(geog,
      `carto-un`.carto.H3_BOUNDARY('8428d55ffffffff'))
  GROUP BY h3id
  )
SELECT
  h3id,
  agg_total,
  `carto-un`.carto.H3_BOUNDARY(h3id) AS geom
FROM
  data;b
```

{% endtab %}

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

```sql
WITH
  data AS (
  SELECT
    `carto-un-eu`.carto.H3_FROMGEOGPOINT(geog, 9) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  WHERE
    ST_INTERSECTS(geog,
      `carto-un-eu`.carto.H3_BOUNDARY('8428d55ffffffff'))
  GROUP BY h3id
  )
SELECT
  h3id,
  agg_total,
  `carto-un-eu`.carto.H3_BOUNDARY(h3id) AS geom
FROM
  data;
```

{% endtab %}

{% tab title="manual" %}

```sql
WITH
  data AS (
  SELECT
    carto.H3_FROMGEOGPOINT(geog, 9) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  WHERE
    ST_INTERSECTS(geog,
      carto.H3_BOUNDARY('8428d55ffffffff'))
  GROUP BY h3id
  )
SELECT
  h3id,
  agg_total,
  carto.H3_BOUNDARY(h3id) AS geom
FROM
  data;
```

{% endtab %}
{% endtabs %}

{% embed url="<https://public.carto.com/builder/38bcfc88-d53c-4d1b-b399-28bea935fa18>" %}

We can clearly identify that there are two H3 cells with the highest concentration of Starbucks locations, and therefore at risk of suffering cannibalization. These are cells with IDs `8928d542c17ffff` and `8928d542c87ffff` respectively. Finally, to complete our analysis, we can calculate how many locations are within one cell distance of this first cell:

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

```sql
WITH
  data AS (
  SELECT
    `carto-un`.carto.H3_FROMGEOGPOINT(geog, 9) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  WHERE
    ST_INTERSECTS(geog,
      `carto-un`.carto.H3_BOUNDARY('8428d55ffffffff'))
  GROUP BY h3id
  )
SELECT
SUM(agg_total)
FROM data
WHERE h3id IN UNNEST(`carto-un`.carto.H3_KRING('8928d542c17ffff', 1));
-- 13
```

{% endtab %}

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

```sql
WITH
  data AS (
  SELECT
    `carto-un-eu`.carto.H3_FROMGEOGPOINT(geog, 9) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  WHERE
    ST_INTERSECTS(geog,
      `carto-un-eu`.carto.H3_BOUNDARY('8428d55ffffffff'))
  GROUP BY h3id
  )
SELECT
SUM(agg_total)
FROM data
WHERE h3id IN UNNEST(`carto-un-eu`.carto.H3_KRING('8928d542c17ffff', 1));
-- 13
```

{% endtab %}

{% tab title="manual" %}

```sql
WITH
  data AS (
  SELECT
    carto.H3_FROMGEOGPOINT(geog, 9) AS h3id,
    COUNT(*) AS agg_total
  FROM `cartobq.docs.starbucks_locations_usa`
  WHERE
    ST_INTERSECTS(geog,
      carto.H3_BOUNDARY('8428d55ffffffff'))
  GROUP BY h3id
  )
SELECT
SUM(agg_total)
FROM data
WHERE h3id IN UNNEST(carto.H3_KRING('8928d542c17ffff', 1));
-- 13
```

{% endtab %}
{% endtabs %}

<img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/GWtJUWkKFcCzxcPMkgpe/eu-flag-website.png" 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.