# Calculate population living around top retail locations

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FhB2W9xXbzzo0kEuXMe3S%2Fintermediate%20banner.png?alt=media&#x26;token=4acd2cc7-c7e8-46c0-9669-6f6b73c030dd" alt="Intermediate difficulty banner" width="175"><figcaption></figcaption></figure></div>

In this example we will create drive-time isolines for selected retail locations and we will then enrich them with population data leveraging the power of the H3 spatial index. This tutorial includes some examples of simple data manipulation, including filtering, ordering and limiting datasets, plus some more advanced concepts such as polyfiling areas with H3 cells and joining data using a spatial index.

As input data we will leverage a point-based dataset representing retail location that is available in the demo data accessible from the CARTO Data Warehouse connection (i.e. retail\_stores), and a table with data from CARTO's Spatial Feature dataset in the USA aggregated at H3 Resolution 8 (i.e. derived\_spatialfeatures\_usa\_h3res8\_v1\_yearly\_v2).&#x20;

Let's get to it!&#x20;

**Creating a workflow and loading your point data**

1. In your CARTO Workspace under the Workflows tab, create a new workflow.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/NgLQSSuibqxvouAurVy0/workflows_isolines_1.png" alt=""><figcaption></figcaption></figure>

2. Select the data warehouse where you have the table with the point data accessible. We'll be using the CARTO Data Warehouse, which should be available to all users.
3. Navigate the data sources panel to locate your table, and drag it onto the canvas. In this example we will be using the `retail_stores` table available in demo data. You should be able to preview the data both in tabular and map format.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/1elJCEcJPafiHTd8A2J3/workflows_isolines_2.png" alt=""><figcaption></figcaption></figure>

**Selecting relevant stores**

In this example, we want to select the 100 stores with the highest revenue, our top performing locations.

4. First, we want to eliminate irrelevant store types. Drag the **Select Distinct** component from the Data Preparation toolbox onto the canvas. Connect the stores source to the input side of this component (the left side) and change the column type to **storetype**.
5. Click run.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/Vbn2qy5V0MnKs39ulnnH/workflows_isolines_3.png" alt=""><figcaption></figcaption></figure>

6. Once run, click on the Select Distinct component and switch to the data preview at the bottom of the window. You will see a list of all distinct store type values. In this example, let’s say we’re only interested in supermarkets.
7. To select supermarkets, add a **Simple Filter** component from the Data Preparation toolbox.
8. Connect the retail stores to the filter, and specify the **column** as storetype, the **operator** as equal to, and the **value** as Supermarket (it's case sensitive).
9. Run!

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/dAz9sX1uGpxXwk4R1jTY/workflows_isolines_4.png" alt=""><figcaption></figcaption></figure>

This leaves us with 10,202 stores. The next step is to select the top 100 stores in terms of revenue.

10. &#x20;Add an **Order By** component from the Data Preparation toolbox and connect it to the top output from Simple Filter. Note that the top output is all features which match the filter, and the bottom is all of those which don't.
11. Change the column to revenue and the order to descending.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/e0eVRxTageKI0WGKIzXT/workflows_isolines_5.png" alt=""><figcaption></figcaption></figure>

12. Next add a **Limit** component - again from Data Preparation - and change the limit to 100, connecting this to the output of Order By.
13. Click run, to select only the top 100 stores in terms of generated revenue.&#x20;

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/rW5T7beuTHXuV30hTESt/workflows_isolines_6.png" alt=""><figcaption></figcaption></figure>

**Creating walk-time isolines around the stores**

14. Next, add a **Create Isolines** component from the Spatial Constructors toolbox. Join the output of Limit to this.
15. Change the **mode** to car, the **range type** to time and **range limit** to 600 (10 minutes).

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/aFFDefS1tHXWaLrZ7aw5/workflows_isolines_7.png" alt=""><figcaption></figcaption></figure>

16. Click run to create 10-minute drive-time isolines. Note this is quite an intensive process compared to many other functions in Workflows (it's calling to an external location data services provider), and so may take a little longer to run.&#x20;

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/8ubDTV1PBO5awcC5Jqqn/workflows_isolines_8.png" alt=""><figcaption></figcaption></figure>

17. We now add a second input table to the canvas, we will drag and drop the table `derived_spatialfeatures_usa_h3res8_v1_yearly_v2` from `demo_tables`. This table include different spatial features (e.g. population, POIs, climatology, urbanity level, etc.) aggregated at H3 grid with resolution 8.&#x20;

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/CG9IqNrVYAcKETjLwpXF/17.png" alt=""><figcaption></figcaption></figure>

18. In order to be able to join the population data with the areas around each retail store, we will use the component **H3 Polyfill** in order to compute the H3 grid cells in resolution 8 that cover each of the isolines around the stores. We configure the node by selecting the Geo column "geom", configuring the Resolution value to 8 and enabling the option to keep input table columns.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/myhJZmHc6ucHRQny8l2j/18.png" alt=""><figcaption></figcaption></figure>

19. Next step is to join both tables based on their H3 indices. For that, we will use the **Join** component. We select the columns named h3 present in both tables to perform an _inner_ join operation.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/HIf3kYmjPXS6j5uuya7X/19.png" alt=""><figcaption></figcaption></figure>

20. &#x20;Check in the results tab that now you have joined data coming from the retail\_stores table with data from CARTO's spatial features dataset.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/8NjqkovkUpAk5XB4UEmY/20.png" alt=""><figcaption></figcaption></figure>

21. As we now have multiple H3 grid cells for each retail store, what we want to do is to aggregate the population associated with the area around each store (the H3 polyfilled isoline). In order to do that we are going to use the **Group By** component, and we are going to aggregate the `population_joined` column with a SUM as the aggregation operation and we are going to group by the table by the `store_id` column.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/OtWuBKwj7Ovgn7KhNNNj/21.png" alt=""><figcaption></figcaption></figure>

22. &#x20;Now, check that in the results what we have again is one row per retail store (i.e. 100 rows) and in each of them we have the store\_id and the result of the sum of the population\_joined values for the different H3 cells that were associated with the isoline around each store.&#x20;

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/G6aUNPoHdbwtDtSHOx8g/22.png" alt=""><figcaption></figcaption></figure>

23. We are going to re-join with a **Join** component the data about the retail\_stores (including the point geometry) with the aggregated population we have now. We take the output of the previous **Limit** component and we add it to a new **Join** component together with the data we generated in the previous step to perform an _inner_ join. We will use the column `store_id` to join both tables.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/SPIIDJ50dgskczGA8dEd/23.png" alt=""><figcaption></figcaption></figure>

24. &#x20;Finally we use the **Save as table** component to save the results as a new table in our data warehouse. We can then use the "Create map" option to build an interactive map to explore this data further.&#x20;

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/LGdLZzj2nyiriAsO2FsK/24.png" alt=""><figcaption></figcaption></figure>

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/eXad2xdD2nf6o1oEfmII/25.png" alt=""><figcaption></figcaption></figure>
