# New police stations based on Chicago crime location clusters

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2F8iAlvj1s2Th85m6IvcG5%2Fbeginner%20banner.png?alt=media&#x26;token=10e8f40a-3632-4920-87c3-6b0dca2a6775" alt="Beginner difficulty banner" width="175"><figcaption></figcaption></figure></div>

In this example we are going to use points clustering to analyze where to locate five new police stations in Chicago based on 5000 samples of crime locations.

## Generating the clusters <a href="#generating-the-clusters" id="generating-the-clusters"></a>

First, we calculate crime locations clusters using the `ST_CLUSTERKMEANS` function:

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

```sql
WITH clustered_points AS
(
    SELECT `carto-un`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude)), 5) AS cluster_arr
    FROM cartobq.docs.chicago_crime_sample
)
SELECT cluster_element.cluster, cluster_element.geom AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element;
```

{% endtab %}

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

```sql
WITH clustered_points AS
(
    SELECT `carto-un-eu`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude)), 5) AS cluster_arr
    FROM cartobq.docs.chicago_crime_sample
)
SELECT cluster_element.cluster, cluster_element.geom AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element;
```

{% endtab %}

{% tab title="manual" %}

```sql
WITH clustered_points AS
(
    SELECT carto.ST_CLUSTERKMEANS(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude)), 5) AS cluster_arr
    FROM cartobq.docs.chicago_crime_sample
)
SELECT cluster_element.cluster, cluster_element.geom AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element;
```

{% endtab %}
{% endtabs %}

This query takes the crimes samples data, gathers their geometries in order to establish clusters and then groups the different geometry clusters. In the following visualization we can easily identify the different clusters by colors.

{% embed url="<https://clausa.app.carto.com/map/4da1b3e0-a780-40bf-b8c0-b43b920f1883>" %}

## Calculating the clusters' centers <a href="#calculating-the-clusters-centers" id="calculating-the-clusters-centers"></a>

Once we have split the sample of points into clusters, we can easily work with them to calculate their centers, envelopes, concave/convex hulls and other different transformations. In this particular example we are interested in finding the center of the clusters, since that is where we are going to place the police stations. The Analytics Toolbox offers different functions for this task: `ST_CENTERMEAN`, `ST_CENTERMEDIAN` and `ST_CENTEROFMASS`. In this case let’s use `ST_CENTERMEDIAN` to calculate the location of the new police stations:

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

```sql
WITH clustered_points AS
(
    SELECT `carto-un`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude)), 5) AS cluster_arr
    FROM cartobq.docs.chicago_crime_sample
)
SELECT cluster_element.cluster, `carto-un`.carto.ST_CENTERMEDIAN(ST_UNION_AGG(cluster_element.geom)) AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element GROUP BY cluster_element.cluster;
```

{% endtab %}

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

```sql
WITH clustered_points AS
(
    SELECT `carto-un-eu`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude)), 5) AS cluster_arr
    FROM cartobq.docs.chicago_crime_sample
)
SELECT cluster_element.cluster, `carto-un-eu`.carto.ST_CENTERMEDIAN(ST_UNION_AGG(cluster_element.geom)) AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element GROUP BY cluster_element.cluster;
```

{% endtab %}

{% tab title="manual" %}

```sql
WITH clustered_points AS
(
    SELECT carto.ST_CLUSTERKMEANS(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude)), 5) AS cluster_arr
    FROM cartobq.docs.chicago_crime_sample
)
SELECT cluster_element.cluster, carto.ST_CENTERMEDIAN(ST_UNION_AGG(cluster_element.geom)) AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element GROUP BY cluster_element.cluster;
```

{% endtab %}
{% endtabs %}

We can see the result in the following visualization, where the bigger dots represent the police stations we have decided to open based on our analysis:

{% embed url="<https://clausa.app.carto.com/map/72375129-1fcf-40f1-81bf-3722314edc23>" %}

<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/new-police-stations-based-on-chicago-crime-location-clusters.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.