# Identifying amenity hotspots in Stockholm

<div align="left"><figure><img src="/files/YUiOye9yS8uvdGFnFppD" 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="/files/4m1BK9j4Wq34gat4HHd2" 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/identifying-amenity-hotspots-in-stockholm.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.