Comment on page
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.
- 1.
- 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_locationsYou 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
inCARTO's Analytics Toolbox
to generate 6 clusters (which is the number of stores we want to open).WITHclustered_points AS (SELECT`carto-un`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(geom ignore nulls), 6) AS cluster_arrFROM `carto-demo-data.demo_tables.sample_customer_home_locations`)SELECTcluster_element.cluster,CAST(cluster_element.cluster AS string) AS cluster_str,ST_UNION_AGG(cluster_element.geom) AS geomFROM clustered_points,UNNEST(cluster_arr) AS cluster_elementGROUP 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 theAnalytics 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_arrFROM `carto-demo-data.demo_tables.sample_customer_home_locations`)SELECTcluster_element.cluster,`carto-un`.carto.ST_CENTERMEAN(ST_UNION_AGG(cluster_element.geom)) AS geomFROM clustered_points, UNNEST(cluster_arr) AS cluster_elementGROUP 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.