Comment on page
Pinpoint new store locations closest to your customers

Context
Understanding & analyzing spatial data is critical to the future of your business. CARTO 3 Location Intelligence platform allows organizations to store, enrich, analyze & visualize their data to make spatially-aware decisions.
In this example we are going to use points clustering to analyze how to find the best place to locate six stores in Portland city based on proximity to customers.
Steps To Reproduce
1.Go to the CARTO signup page.
Click on Log in.
Enter your email address and password. You can also log in with your existing Google account by clicking Continue with Google.
Once you have entered your credentials: click Continue.
2.From the Navigation Menu in the left panel, select Maps.
3.Click on New map. This will open the CARTO map-making tool, Builder.
4.Once in Builder, you can add data as layers to the map by clicking on “Add source from”, where you can access the contents from your existing data warehouse connections.
5.Select Custom Query (SQL) and “Type your own query” using the CARTO Data Warehouse connection and click on Add Source.
6.Once the process is finished, the SQL panel appears in the Builder interface, where you can run queries in CARTO Data Warehouse (based on Google BigQuery) and see the result in the map.
Note:
The following queries should be executed in order, and each of them will show a different result.
7.Let’s start by just plotting a table that we have through our connection with the CARTO Data Warehouse (note that you would achieve the same result creating a map from the Data Explorer).
SELECT * FROM carto-demo-data.demo_tables.sample_customer_home_locations
You can see how this query returns the table with the customer home locations that we will use in this analysis.
Note:
Remember that you can also persist the query as a table by clicking on Create table from query button that will be available when the query is successfully completed or when it takes too long.
8.Optionally, you could spend some time and style this layer based on the customer_value feature, either with the fill color of the points or their radius.
9.Now we are going to modify the SQL Query used to generate the map layer, and we are going to use the clustering functions in CARTO's Analytics Toolbox to generate 6 clusters (which is the number of stores we want to open).
WITH
clustered_points AS (
SELECT
`carto-un`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(geom ignore nulls), 6) AS cluster_arr
FROM `carto-demo-data.demo_tables.sample_customer_home_locations`
)
SELECT
cluster_element.cluster,
CAST(cluster_element.cluster AS string) AS cluster_str,
ST_UNION_AGG(cluster_element.geom) AS geom
FROM clustered_points,UNNEST(cluster_arr) AS cluster_element 
GROUP BY cluster_element.cluster
10.Let’s now change the name of the layer to “Clusters of customer homes”.
11.Style the layer by modifying the fill color of the points based on the feature cluster. You can change the color and width of the stroke in order to polish the visualization.
12.You can also add a HISTOGRAM Widget to be able to filter the home locations based on the cluster.
13.Let’s also add a tooltip to the points based on the cluster number.
14.We can change our basemap. Go to the Basemaps tab and select “Dark matter” from CARTO.
15.We are now going to create another layer. In order to do that, go back to Layers tab and click again on “Add source from”, Custom Query (SQL) and “Type your own query” from your CARTO Data Warehouse connection. Finally click on Add source.
16.For this second layer we are going to adapt the previous SQL Query and compute the centroid of each of the clusters using the transformation functions in the Analytics Toolbox; this would give us a potentially optimal location to open each store in the center of each of the previously computed clusters.
WITH clustered_points AS (
SELECT 
    `carto-un`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(geom ignore nulls), 6) AS cluster_arr
FROM `carto-demo-data.demo_tables.sample_customer_home_locations`
)
SELECT 
cluster_element.cluster, 
`carto-un`.carto.ST_CENTERMEAN(ST_UNION_AGG(cluster_element.geom)) AS geom 
FROM clustered_points, UNNEST(cluster_arr) AS cluster_element 
GROUP BY cluster_element.cluster
17.Let’s rename this second layer as “Cluster centers”.
18.Finally, we are going to style this layer by changing the fill color and increasing the radius of the points in order to make them more visible.
19.We can also make the map public and share it online with our colleagues. For more details, see Publishing and sharing maps.
20.Finally, we can visualize the result.