# Computing the spatial autocorrelation of POIs locations in Berlin

<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 analyze the spatial correlation of POIs locations in Berlin using OpenStreetMap data and the [Moran’s I](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/statistics#morans_i_h3_table) function available in the statistics module. POIs data can be found in the publicly available `cartobq.docs.osm_pois_berlin` table.

First, we are going to visually analyze the distribution of the POIs in the Berlin area by plotting the aggregation of POIs in each H3 cell of resolution 9. This can be done simply by applying the [H3\_FROMGEOGPOINT](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/h3#h3_fromgeogpoint) function to compute the H3 cell that each POI belongs to and then performing a group by to count the number of POIs inside each cell (*n\_pois*).

By looking at the resulting map below, it is clear that there is a level of spatial autocorrelation in the distribution of the POIs:

{% embed url="<https://gcp-us-east1.app.carto.com/map/a1bd26d7-0a30-4f3d-a2e5-8cbc06a04124>" %}

We can measure this spatial autocorrelation using the [MORANS\_I\_H3\_TABLE](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-bigquery/sql-reference/statistics#morans_i_h3_table) function, which yields a result of `0.673` by applying the query below:

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

```sql
-- Create table with POI counts by grid cell
CREATE OR REPLACE TABLE project.dataset.berlin_poi_count_grid AS
SELECT
    h3, COUNT(*) AS n_pois
FROM (
    SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3
    FROM cartobq.docs.osm_pois_berlin )
GROUP BY h3;

-- Compute Moran's I
CALL `carto-un`.carto.MORANS_I_H3_TABLE(
    'project.dataset.berlin_poi_count_grid', 
    'project.dataset.berlin_poi_count_grid_mi',
    'h3',
    'n_pois',
    1,
    'exponential'
);

-- Read computed value
SELECT * FROM project.dataset.berlin_poi_count_grid_mi;
```

{% endtab %}

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

```sql
-- Create table with POI counts by grid cell
CREATE OR REPLACE TABLE project.dataset.berlin_poi_count_grid AS
SELECT
    h3, COUNT(*) AS n_pois
FROM (
    SELECT `carto-un-eu`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3
    FROM cartobq.docs.osm_pois_berlin )
GROUP BY h3;

-- Compute Moran's I
CALL `carto-un-eu`.carto.MORANS_I_H3_TABLE(
    'project.dataset.berlin_poi_count_grid', 
    'project.dataset.berlin_poi_count_grid_mi',
    'h3',
    'n_pois',
    1,
    'exponential'
);

-- Read computed value
SELECT * FROM project.dataset.berlin_poi_count_grid_mi;
```

{% endtab %}

{% tab title="manual" %}

```sql
-- Create table with POI counts by grid cell
CREATE OR REPLACE TABLE project.dataset.berlin_poi_count_grid AS
SELECT
    h3, COUNT(*) AS n_pois
FROM (
    SELECT carto.H3_FROMGEOGPOINT(geom, 9) AS h3
    FROM cartobq.docs.osm_pois_berlin )
GROUP BY h3;

-- Compute Moran's I
CALL carto.MORANS_I_H3_TABLE(
    'project.dataset.berlin_poi_count_grid', 
    'project.dataset.berlin_poi_count_grid_mi',
    'h3',
    'n_pois',
    1,
    'exponential'
);

-- Read computed value
SELECT * FROM project.dataset.berlin_poi_count_grid_mi;
```

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


---

# 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/computing-the-spatial-autocorrelation-of-pois-locations-in-berlin.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.