CARTO for Spatial Analysis
CARTO’s Location Intelligence platform lets you store, enrich, analyze, & visualize spatial data to make insightful decisions. Our platform extends the geospatial capabilities of the leading cloud data warehouse platform ensuring you can get the most out of your location data.
Builder is our mapping tool to design, build, and publish interactive web maps with your location data. Maps can be easily shared or embedded on any website or blog.
In this guide, you will learn how to use the sample data available in the CARTO Data Warehouse to find the best place to create a store close to the potential customers:
Log in to CARTO.
Go to Maps and click on “+ New Map”.
Once in Builder, click on “Add source from …”
Select “Custom Query (SQL)” and “Type your own query” using the
carto_dw connection and click on “Add Source”
Now, you have a SQL panel where you can run queries in CARTO Data Warehouse (based on Google BigQuery) and see the result in the map.
The following queries should be executed in order, and each of them will show a different result:
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
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.
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`.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
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 column “cluster”. You can change the color and width of the stroke in order to polish the visualization.
You can also add a 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 are now going to create another layer. In order to do that, click again on “Add source from”, “Customer Query (SQL)” and “Type your own query” from your
carto_dw connection. 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 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`.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
Let’s rename this second layer as “Cluster centers” and style this layer by changing the fill color and increasing the radius of the points in order to make them more visible
That’s it! The resulting map shows the different clusters of customer locations and the potential location of each store.
CARTO’s Analytics Toolbox is a suite of +100 functions and procedures extending the spatial analysis capabilities of your cloud data warehouse platform. The toolbox is organized in modules related to different types of functionalities, such as operating with spatial indexes, creating tilesets out of your large datasets, location data services, geostatistics and much more.
This guide is intended for those users who want to perform spatial analytics natively in their data warehouse using CARTO’s set of prepared analytical functions. In this guide we will focus on the implementation of the Analytics Toolbox for BigQuery, but this component is also available for Snowflake, Redshift, Databricks and PostgreSQL.
In this example we are going to identify hotspots of amenity POIs in Stockholm using OpenStreetMap data and the GETIS_ORD_H3 function available in the statistics module. POIs can be found in the publicly available
cartobq.docs.osm_pois_stockholm table (via the CARTO Data Warehouse or a BigQuery connection). We will implement this analysis using the SQL panel available for adding data sources from SQL queries in Builder, but it can also be run directly on your BigQuery console.
In order to visualize the OpenStreetMap data that we are using for POIs, you can simply execute the following query:
SELECT * FROM cartobq.docs.osm_pois_stockholm WHERE amenity IS NOT NULL
With Builder, you can use widgets and tooltips in order to explore the data in more detail, as illustrated in the image below.
With the execution of the following query, we run the H3_FROMGEOGPOINT method in order to transform and aggregate a point-based dataset into a grid based on the H3 spatial index.
SELECT h3, CAST(COUNT(*) AS FLOAT64) AS n_amenity_pois,
FROM (
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
FROM cartobq.docs.osm_pois_stockholm
WHERE amenity IS NOT NULL )
GROUP BY h3
In order to visualize the output of this query remember to change the type of geometry in the SQL panel to an H3 index.
Then you can style the map layer that gets generated based on the
n_amenity_pois feature.
The final result can be obtained by executing the following query, in which we combine both the aggregation of POIs in an H3-based grid and the computation of the Getis-Ord Gi* statistic for each of the grid cells. This measure tells you where features with either high or low values (of the n_aminity_pois) cluster spatially or not. It’s a good statistic in order to run a hotspot analysis.
WITH
stockholm AS (
SELECT h3, CAST(COUNT(*) AS FLOAT64) AS n_amenity_pois,
FROM (
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
FROM cartobq.docs.osm_pois_stockholm
WHERE amenity IS NOT NULL )
GROUP BY h3),
getis_ord AS (
SELECT `carto-un`.carto.GETIS_ORD_H3(ARRAY_AGG(STRUCT(h3, n_amenity_pois)),
4, 'triangular') AS output
FROM stockholm )
SELECT unnested.INDEX AS h3, unnested.gi
FROM getis_ord, UNNEST(getis_ord.output) AS unnested
The results can be explored on a map such as in the example below, where we can use the histogram widget to narrow down the cells with the highest Gi* values, which correspond to the location of the hotspots of amenity POIs in Stockholm.
Remember once again to change the type of geometry in the SQL panel to an H3 index.
Last modified 4mo ago