Pinpoint new store locations closest to your customers
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
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.
From the Navigation Menu in the left panel, select Maps.
Click on New map. This will open the CARTO map-making tool, Builder.
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.
Select Custom Query (SQL) and “Type your own query” using the
CARTO Data Warehouseconnection and click on Add Source.
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.
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.
Optionally, you could spend some time and style this layer based on the
customer_valuefeature, either with the fill color of the points or their radius.
Now we are going to modify the SQL Query used to generate the map layer, and we are going to use the
CARTO's Analytics Toolboxto generate 6 clusters (which is the number of stores we want to open).
1 2 3 4 5 6 7 8 9 10 11
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, ST_UNION_AGG(cluster_element.geom) AS geom FROM clustered_points,UNNEST(cluster_arr) AS cluster_element GROUP BY cluster_element.cluster
Let’s now change the name of the layer to “Clusters of customer homes”.
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.
You can also add a HISTOGRAM Widget to be able to filter the home locations based on the cluster.
Let’s also add a tooltip to the points based on the cluster number.
We can change our basemap. Go to Basemaps tab and select “Dark matter” from CARTO.
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 Warehouseconnection. Finally click on Add source.
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 functionsin 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.
1 2 3 4 5 6 7 8 9 10
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`.transformations.ST_CENTERMEAN(ST_UNION_AGG(cluster_element.geom)) AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element GROUP BY cluster_element.cluster
Let’s rename this second layer as “Cluster centers”.
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.
We can also make the map public and share it online with our colleagues. For more details, see Publishing and sharing maps.
Finally, we can visualize the result.