# New supplier offices based on store locations clusters

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FhB2W9xXbzzo0kEuXMe3S%2Fintermediate%20banner.png?alt=media&#x26;token=4acd2cc7-c7e8-46c0-9669-6f6b73c030dd" alt="Intermediate difficulty banner" width="175"><figcaption></figcaption></figure></div>

## Requirements

To run this example you'll need:

* The latest version of the [Analytics Toolbox C](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/step-by-step-tutorials/broken-reference)[ore](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/step-by-step-tutorials/broken-reference) Native App installed in your Snowflake database
* *Optional:* An active CARTO organization to visualize the results in a map

## Example

In this example we are going to use points clustering to analyze where to locate 10 new supplier offices in US so they can best serve all Starbucks locations.

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

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

```sql
WITH data AS(
  SELECT geog
  FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
  WHERE geog IS NOT null
  ORDER BY id
),
clustered_points AS
(
    SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_CLUSTERKMEANS(ARRAY_AGG(ST_ASGEOJSON(geog)::STRING), 10) AS cluster_arr
    FROM data
)
SELECT GET(VALUE, 'cluster') AS cluster, TO_GEOGRAPHY(GET(VALUE, 'geom')) AS geom
FROM clustered_points, lateral FLATTEN(input => cluster_arr)
```

This query gathers the geometries of the Starbucks locations in order to establish clusters and then groups the different geometry clusters. Each of them is represented in a different colour in the following visualization.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/J60DPkWEl9N2Z4JJtrXK/sf_new_map1.png" alt=""><figcaption></figcaption></figure>

### 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 offices. The Analytics Toolbox offers different functions for this task, for example `ST_CENTERMEAN`, `ST_CENTERMEDIAN` and `ST_CENTEROFMASS`.

In this case we are going to use `ST_CENTEROFMASS` to calculate the location of the new offices:

```sql
WITH data AS(
  SELECT geog
  FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
  WHERE geog IS NOT null
  ORDER BY id
),
clustered_points AS
(
    SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_CLUSTERKMEANS(ARRAY_AGG(ST_ASGEOJSON(geog)::STRING), 10) AS cluster_arr
    FROM data
)
SELECT GET(VALUE, 'cluster') AS cluster, CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_CENTEROFMASS(ST_COLLECT(TO_GEOGRAPHY(GET(VALUE, 'geom')))) AS geom
FROM clustered_points, lateral FLATTEN(input => cluster_arr)
GROUP BY cluster
```

We can see the result in the following visualization, where the bigger dots represent the supplier offices locations we have decided to open based on our analysis.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/B1aVX7q0YEQOowzvRJqA/sf_new_map2.png" alt=""><figcaption></figcaption></figure>