CARTO User Manual

CARTO User Manual

Find the best place to create a store near the 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.

    Log in Email and password

  2. From the Navigation Menu in the left panel, select Maps.

    Menu features maps

  3. Click on New map. This will open the CARTO map-making tool, Builder.

    Maps section new map

  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.

    Add source to your map

  5. Select Custom Query (SQL) and “Type your own query” using the CARTO Data Warehouse connection and click on Add Source.

    Add source custom query

  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.

    Map sql console

  1. 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).

    1
    
    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.

    Map add query

  2. 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.

    Map style fill based and radius

    Map style fill and radius

  3. 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).

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    
    WITH
    clustered_points AS (
    SELECT
        `carto-un`.clustering.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
    

    Map sql cluster

  4. Let’s now change the name of the layer to “Clusters of customer homes”.

Map layers rename

  1. 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.

    Map fill style based on field

  2. You can also add a Widget to be able to filter the home locations based on the cluster.

    Map widgets first widget

  3. Let’s also add a tooltip to the points based on the cluster number.

    Map tooltip new tooltip

  4. We can change our basemap. Go to Basemaps tab and select “Dark matter” from CARTO.

    Map basemap

  5. 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.

    Add source custom query

  6. 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.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    
    WITH clustered_points AS (
    SELECT 
        `carto-un`.clustering.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
    
  7. Let’s rename this second layer as “Cluster centers”.

Map layers rename

  1. 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.

    Map style centroid fill and radius

  2. We can also make the map public and share it online with our colleagues. For more details, see Publishing and sharing maps.

    Map public map

  3. Finally, we can visualize the result.