# Identifying amenity hotspots in Stockholm

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FUx7fNjcfw9KvGNf1JTaW%2Fadvanced%20banner.png?alt=media&#x26;token=ea2ec56e-2c6a-4c54-bae4-561b2fa33b7b" alt="Advanced difficulty banner" width="175"><figcaption></figcaption></figure></div>

In this example we are going to identify hotspots of amenity POIs in Stockholm using OpenStreetMap data and the [GETIS\_ORD\_H3\_TABLE](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/statistics#getis_ord_h3_table) function of the statistics module. POIs data can be found in the publicly available `cartobq.docs.osm_pois_stockholm` table.

The process consists of three simple steps:

* First, we retrieve all POIs from OpenstreetMaps which belong to the category “amenity”.
* Next, we find the H3 cell of resolution 9 to which each POI belongs and count the number of amenity POIs inside each cell.
* Finally, we call the [GETIS\_ORD\_H3\_TABLE](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/statistics#getis_ord_h3_table) function, which returns the Getis-Ord Gi\* statistic for each H3 cell, calculated over *n\_amenity\_pois* (number of amenity POIs in the cell).

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

```sql
-- Create table with POI counts by grid cell
CREATE TABLE project.dataset.stockholm_poi_count_grid AS
SELECT
    h3, COUNT(*) AS n_amenity_pois
FROM (
    SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
    FROM cartobq.docs.osm_pois_stockholm
    WHERE amenity IS NOT NULL )
GROUP BY h3;

-- Compute Getis-Ord Gi*
CALL `carto-un`.carto.GETIS_ORD_H3_TABLE(
    'project.dataset.stockholm_poi_count_grid', 
    'project.dataset.stockholm_poi_count_grid_gi',
    'h3', 
    'n_amenity_pois', 
    4, 
    'triangular');
```

{% endtab %}

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

```sql
-- Create table with POI counts by grid cell
CREATE TABLE project.dataset.stockholm_poi_count_grid AS
SELECT
    h3, COUNT(*) AS n_amenity_pois
FROM (
    SELECT `carto-un-eu`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
    FROM cartobq.docs.osm_pois_stockholm
    WHERE amenity IS NOT NULL )
GROUP BY h3;

-- Compute Getis-Ord Gi*
CALL `carto-un-eu`.carto.GETIS_ORD_H3_TABLE(
    'project.dataset.stockholm_poi_count_grid', 
    'project.dataset.stockholm_poi_count_grid_gi',
    'h3', 
    'n_amenity_pois', 
    4, 
    'triangular');
```

{% endtab %}

{% tab title="manual" %}

```sql
-- Create table with POI counts by grid cell
CREATE TABLE project.dataset.stockholm_poi_count_grid AS
SELECT
    h3, COUNT(*) AS n_amenity_pois
FROM (
    SELECT carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
    FROM cartobq.docs.osm_pois_stockholm
    WHERE amenity IS NOT NULL )
GROUP BY h3;

-- Compute Getis-Ord Gi*
CALL carto.GETIS_ORD_H3_TABLE(
    'project.dataset.stockholm_poi_count_grid', 
    'project.dataset.stockholm_poi_count_grid_gi',
    'h3', 
    'n_amenity_pois', 
    4, 
    'triangular');
```

{% endtab %}
{% endtabs %}

The results can be explored in the map below, where we can use the histogram widget to narrow down the cells with the highest Gi\* values, which correspond to the location of the hotspots of amenity POIs in Stockholm.

{% embed url="<https://gcp-us-east1.app.carto.com/map/e3702384-809c-4d0d-847c-af71c8b4a30a>" %}

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