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

## Requirements

To run this example you'll need:

* An active CARTO organization
* The latest version of the [Analytics Toolbox Advanced](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/step-by-step-tutorials/broken-reference) installed in your Snowflake database
* [CARTO Academy - Data for tutorials and examples](https://app.snowflake.com/marketplace/listing/GZT0Z4CM1E9J2) from Snowflake's Marketplace. With this free listing, users can access a set of sample tables with geospatial data that can be used to test and evaluate the CARTO geospatial analytics platform running natively on Snowflake

## Guide

In this example we are going to identify hotspots of amenity POIs in Stockholm using OpenStreetMap data and the [GETIS\_ORD\_H3](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-snowflake/sql-reference/statistics#getis_ord_h3) function of the statistics module. POI data can be found in the publicly available `CARTO_ACADEMY_DATA.CARTO.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](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-snowflake/sql-reference/statistics#getis_ord_h3) function, which returns the Getis-Ord Gi\* statistic for each H3 cell, calculated over `N_AMENITY_POIS` (number of amenity POIs in the cell).

<pre class="language-sql"><code class="lang-sql"><strong>-- Create table with POI counts by grid cell
</strong><strong>CREATE TABLE MYDB.MYSCHEMA.STOCKHOLM_POI_COUNT_GRID AS
</strong>SELECT
    H3, COUNT(*) AS N_AMENITY_POIS
FROM (
    SELECT CARTO.CARTO.H3_FROMGEOGPOINT(GEOM, 9) AS H3
    FROM CARTO_ACADEMY_DATA.CARTO.OSM_POIS_STOCKHOLM
    WHERE AMENITY IS NOT NULL)
GROUP BY H3;

-- Compute Getis-Ord Gi*
CALL CARTO.CARTO.GETIS_ORD_H3(
    'MYDB.MYSCHEMA.STOCKHOLM_POI_COUNT_GRID', 
    'MYDB.MYSCHEMA.STOCKHOLM_POI_COUNT_GRID_GI',
    'H3', 
    'N_AMENITY_POIS', 
    4, 
    'triangular');
</code></pre>

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.