Enrichment of catchment areas for store characterization

Requirements

To run this example you'll need:

• The latest version of the Analytics Toolbox Core Native App installed in your Snowflake database

• Optional: An active CARTO organization to visualize the results in a map

Example

In this example we are going to characterize all Starbucks locations in the US by the total population covered by their catchment areas. We are going to define these catchment areas as a 3km buffer around each store.

First, let’s start by visualizing all Starbucks locations in the US.

SELECT *, geog AS geom FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA

To quickly explore this data, using the Analytics Toolbox, we can easily compute an aggregation of these locations using Quadkeys at resolution 15, which lets us visualize the result as a heatmap. Here is a close-up of the Manhattan area, where we can easily identify the areas of highest concentration of Starbucks stores.

WITH qks AS(
SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_FROMGEOGPOINT(geog, 15) AS qk
FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
WHERE CITY = 'New York')

SELECT count(*) AS num_stores, CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_BOUNDARY(qk) AS geom FROM qks GROUP BY qk

Now, let’s enhance our analysis by computing a 3 km buffer around each store, using the geometry constructor module from the Analytics Toolbox:

SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_MAKEELLIPSE(geog,3,3,0,'kilometers',12) AS geom
FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
WHERE STATE_PROVINCE = 'AL'

We can then deepen our analysis by computing the Quadkeys at resolution 15 that intersect with these buffers using the available polyfill function.

WITH qks AS(
SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_POLYFILL(
    CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_MAKEELLIPSE(geog,3,3,0,'kilometers',12), 15) AS qk
FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
WHERE STATE_PROVINCE = 'AL'
)

SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_BOUNDARY(VALUE) AS geom
FROM qks, lateral FLATTEN(input => qk)

Next, we enrich the resulting Quadkeys with the total population and the population by gender using CARTO’s Spatial Features dataset, available for free in the Snowflake Data Marketplace. Since this dataset is already delivered in Quadkeys at resolution 15, enrichment can be performed by a simple JOIN.

WITH qks AS(
SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_POLYFILL(
    CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_MAKEELLIPSE(geog,3,3,0,'kilometers',12), 15) AS qk
FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
WHERE STATE_PROVINCE = 'AL'
),

geom_data AS(
SELECT VALUE::BIGINT AS geoid, CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_BOUNDARY(VALUE) AS geom
FROM qks, lateral FLATTEN(input => qk)
GROUP BY VALUE
)

SELECT t1.geom AS geom, population, t2.female, t2.male
FROM geom_data t1
INNER JOIN DATA_MARKETPLACE.CARTO.DERIVED_SPATIALFEATURES_USA_QUADGRID15_V1_YEARLY_V2 t2
ON t1.geoid = t2.geoid

By zooming into a particular area we can see the distribution of the total population (where darker colors represent higher population levels).

Once we have this data ready, we can now calculate the total population that is within a 3km radius from each store location. This catchment analysis can be performed from start to finish with the following query:

WITH qks AS(
SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.QUADBIN_POLYFILL(
    CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_MAKEELLIPSE(geog,3,3,0,'kilometers',12), 15) AS qk, id AS store_id
FROM CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA
),

flat_qks AS(
SELECT t1.store_id, VALUE::BIGINT AS geoid
FROM qks t1, lateral FLATTEN(input => qk)
),

enriched_qks AS(
SELECT t1.store_id, t1.geoid, t2.population, t2.female, t2.male
FROM flat_qks t1
INNER JOIN DATA_MARKETPLACE.CARTO.DERIVED_SPATIALFEATURES_USA_QUADGRID15_V1_YEARLY_V2 t2
ON t1.geoid = t2.geoid
),

kpis_per_store AS(
SELECT store_id, SUM(population) AS tot_pop, SUM(female) AS tot_female, sum(male) AS tot_male
FROM enriched_qks
GROUP BY store_id
)

SELECT t1.store_id, t1.tot_pop, t1.tot_female, t1.tot_male, geog AS geom
FROM kpis_per_store t1
INNER JOIN CARTO_ANALYTICS_TOOLBOX_CORE.PUBLIC.STARBUCKS_LOCATIONS_USA t2
ON t1.store_id = t2.id

The result is a beautiful map visualization, where each Starbucks location is represented by a point and whose catchment is proportional to the total population within a 3km radius: