# Filtering multiple data sources simultaneously with SQL Parameters

## **Context**

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

Data, particularly visualized on a map, provides powerful insights that can guide and accelerate decision-making. However, working with multiple data sources, each of them filled with numerous variables, can be a challenge.&#x20;

In this tutorial, we're going to show you how to use SQL Parameters to handle multiple data sources at once when building an interactive map with CARTO Builder. We'll be focusing on the start and end locations of Citi Bike trips in New York City, considering different time periods and neighborhoods. By the end, you'll have a well-crafted, interactive Builder map completed with handy widgets and parameters. It'll serve as your guide for understanding biking patterns across the city. Sounds good? Let's dive in!

## **Step-by-Step Guide:**

1. Access the _Data Explorer_ from your CARTO Workspace using the Navigation menu.

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

2. Search for the _demo\_data > demo\_tables_ within the **CARTO Data Warehouse** and select “manhattan\_citibike\_trips”.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/70lvA8DZ9QEaYdj6t3Lh/image.png" alt=""><figcaption></figcaption></figure>

3. Examine "manhattan\_citibike\_trips" Map and Data preview, focusing on the geometry columns (`start_geom` and `end_geom`) that correspond to trip start and end bike station points.

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

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

4. Return to the Navigation Menu, select _Maps_, and create a "New map".

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/32bJHYv6Z6IvdkAExwwh/image.png" alt=""><figcaption></figcaption></figure>

5. Begin by adding the **start station locations** of Citi Bike Trips as the first data source.&#x20;
   * Select the _Add source from_ button at the bottom left on the page.&#x20;
   * Click on the _CARTO Data Warehouse_ connection.
   * Select _Type your own query_.
   * Click on the _Add Source button_.

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

The SQL Editor panel will be opened.&#x20;

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

6. Extract the bike stations of the **start of the Citi bike trips** grouping by the `start_station_name` while obtaining the `COUNT()` of all the trips starting at that specific location. For that, run the query below:

```sql
SELECT 
    start_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(start_geom) as geom, 
    ANY_VALUE(start_ntaname) as start_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
GROUP BY start_station_name
```

7. Rename the layer to "Trip Start" and style it by `Trip_count` using _Color based on_ option and set the radius size by the same `Trip_count` variable using 2 to 6 _range._

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

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

8. Extract the **bike stations of the end of the trips**. We will repeat _Step 7_ and _Step 8_, this time retrieving the end station variables. For that, execute the following query.&#x20;

```sql
SELECT 
    end_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(end_geom) as geom, 
    ANY_VALUE(end_ntaname) as end_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
GROUP BY end_station_name
```

Once the data has been added to the map display, you will notice that is overlaying with the 'Trip Start' layer.&#x20;

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

9. Edit the name, style of the new layer and update the visualisation of 'Trip Start' layer as follows:
   * Disable 'Trip Start' layer visibility by clicking over the eye located right on the layer tab.
   * Rename "Layer &#x32;_"_ to "Trip End".
   * Style 'Trip End' layer by `trip_count` using a different color palette.&#x20;

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

10. Change the _Basemap_ to `Dark Matter` for better visibility.

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

11. Enable the _Layer selector_ and _Open when loading the map options_ within _Legend_ > _More Legend Options_.

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

12. Use the _Split View_ mode to examine the 'Trip Start' and 'Trip End' layers before creating SQL Parameters.

Ensure that the 'Trip Start' layer is positioned above the 'Trip End' layer. You can adjust layer visibility by toggling the eye icon in the Legend.

As per below screenshot, the left panel is dedicated to showcasing the 'Trip Start' layer, while the right panel displays the 'Trip End' layer. Split View mode is highly beneficial for comparison purposes.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/6FS9b41pKXuEWHF7Gbpf/image.png" alt=""><figcaption></figcaption></figure>

Now we are ready to start using SQL Parameters over both SQL Query sources.&#x20;

[SQL Parameters](broken-reference) are a powerful feature in Builder that serve as placeholders in SQL Query data sources. They provide flexibility and ease in performing data analysis by allowing dynamic input and customization of queries.&#x20;

13. Create a SQL Parameter by clicking over _Create a SQL Parameter_ icon located on the top right of your Sources panel.

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

A pop-up window will be opened where you can extract further information about SQL Parameters and select the SQL Parameter type you would like to use.&#x20;

14. Click _Continue_ to jump into the next page where you can choose the parameter type.
15. Select _Dates_ as the parameter type and click _Continue_.&#x20;

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

16. Navigate to the configuration page for the _Dates parameter_ and set the parameters as indicated in the following screenshot and click _Create parameter_.&#x20;

Please note that the dataset for Manhattan Citi Bike Trips only includes data from January until May 2018. Please ensure your date selection falls within this range.

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

A new parameter named Time Period appears on the left panel.&#x20;

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

17. Edit the SQL Query for both 'SQL Query 1' and 'SQL Query 2' data sources to include the WHERE statement that will filter `starttime` column by the input _Time Period_ date range and execute the queries.

```sql
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
```

The output query for 'SQL Query 1' linked to 'Trip Start' layer should be as follows:

```sql
SELECT 
    start_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(start_geom) as geom, 
    ANY_VALUE(start_ntaname) as start_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
GROUP BY start_station_name
```

The output query for 'SQL Query 2' linked to 'Trip End' layer should be as below, as we are interested on the start time of the trip for both sources:

```sql
SELECT 
    end_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(end_geom) as geom, 
    ANY_VALUE(end_ntaname) as end_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
GROUP BY end_station_name
```

Once you have executed the SQL Queries, a calendar will appear within _Trip Period_ parameter.&#x20;

Users will have the flexibility to **alter the time frame using the provided calendar**. This allows you to filter the underlying data sources to suit your needs, affecting both the 'Trip Start' and 'Trip End' data sources.

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

18. Create a new SQL Parameter. This time, select the _Text_ parameter type and set the configuration as below, using `start_ntaname` column from 'SQL Query 1' source to add Manhattan neighborhoods. Once complete, click on _Create Parameter button_.&#x20;

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

A new parameter named _Start Neighborhood_ will be added to the Map.

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

19. Edit the SQL Query for both 'SQL Query 1' and 'SQL Query 2' to include the WHERE statement that will filter `start_ntaname` column by the input of _Start Neighborhood_ parameter and execute the queries.

```
start_ntaname IN {{start_neighborhood}}
```

The output query for 'SQL Query 1' linked to 'Trip Start layer' should be as follows:

```sql
SELECT 
    start_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(start_geom) as geom, 
    ANY_VALUE(start_ntaname) as start_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
AND start_ntaname IN {{start_neighborhood}}
GROUP BY start_station_name
```

The output query for 'SQL Query 2' linked to 'Trip End' layer should be as below, as we are interested on the start time of the trip for both sources.

```sql
SELECT 
    end_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(end_geom) as geom, 
    ANY_VALUE(end_ntaname) as end_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
AND start_ntaname IN {{start_neighborhood}}
GROUP BY end_station_name
```

After executing the SQL Queries, a drop-down list of start trip neighborhoods will populate. This interactive element allows users to **selectively choose which neighborhood(s) serve as the starting point of their trip.**

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/6bTCaWnnlW6lC90gltbZ/image.png" alt=""><figcaption></figcaption></figure>

20. Repeat _Step 20_ and _Step 21_ to create a SQL Parameter, but this time we will filter the end trip neighborhoods.&#x20;

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

The output query for 'SQL Query 1' linked to Trip Start layer should be as follows:

```sql
SELECT 
    start_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(start_geom) as geom, 
    ANY_VALUE(start_ntaname) as start_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
AND start_ntaname IN {{start_neighborhood}} AND end_ntaname IN {{end_neighborhood}}
GROUP BY start_station_name
```

The output query for 'SQL Query 2' linked to 'Trip Start' layer should be as follows:

```sql
SELECT 
    end_station_name, 
    COUNT(*) as trip_count, 
    ANY_VALUE(geoid) as geoid,
    ANY_VALUE(end_geom) as geom, 
    ANY_VALUE(end_ntaname) as end_ntaname
FROM `carto-demo-data.demo_tables.manhattan_citibike_trips` 
WHERE starttime >= {{trip_period_from}} AND starttime <= {{trip_period_to}}
AND start_ntaname IN {{start_neighborhood}} AND end_ntaname IN {{end_neighborhood}}
GROUP BY end_station_name
```

21. Disable _Split View_ Mode, make both 'Trip Start' and 'Trip Layer' visible using the _Legend_ eye icons and **compare the bike trips between two different neighborhoods**. For that, set the _Start Neighborhood_ parameter to be "Upper West Side" and the End _Neighborhood_ parameter to be "Chinatown".

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

We can clearly see which are the start and end stations which are gathering most of the bike trips for this neighborhood combination.

22. Create a _Formula Widget_ to represent the `Total Trips` setting the configuration as below.

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

23. Add a _Category Widget_ to display the `Start Stations` ordered by the `Total Trips`.

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

24. Add a _Category Widget_ to display the `End Stations` ordered by the `Total Trips`.

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

The Builder Map provides user with an interactive application to gather insights about New York Citi Trips and the patterns between the different neighborhoods. However, it is difficult to visualize the boundary limits between both the start trips and the end trips.

For that, let's use "newyork\_neighborhood\_tabulation\_areas" table, available on **CARTO Data Warehouse** within _demo\_data > demo\_tables_.

25. Add a new _SQL Query_ as the data source using the following query which aggregates **geometry of the start trip neighborhood(s).**&#x20;

```sql
SELECT 
    ST_UNION_AGG(geom) as geom 
FROM `carto-demo-data.demo_tables.newyork_neighborhood_tabulation_areas` 
WHERE ntaname IN {{start_neighborhood}}
```

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

26. Add a new SQL Query as the data source using the following query. This time the aggregated **geometry will be for the end trip neighborhood(s).**

```sql
SELECT 
    ST_UNION_AGG(geom) as geom 
FROM `carto-demo-data.demo_tables.newyork_neighborhood_tabulation_areas` 
WHERE ntaname IN {{end_neighborhood}}
```

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

27. Rename the recently added layers, and position them beneath the 'Trip Start' and 'Trip End' layers for better visibility.&#x20;
28. &#x20;Feel free to experiment with styling options - adjusting layer opacity, trying out different color palettes, until you achieve the optimal visual representation.

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

29. Change the name of the map to "New York Citi Bike Trips".
30. &#x20;Finally we can make the map public and share the link to anybody.&#x20;
    * For that you should go to _Share_ section on the top right corner and set the map as _Public_.
    * Activate _SQL parameters controls_ options so that Viewer users can control the exposed parameters.&#x20;

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

Finally, we can visualize the results!

{% embed url="https://clausa.app.carto.com/map/44788359-73f7-41d7-a161-945dda7fe1af" %}

By the end of this tutorial, you should have a clear understanding of how to utilize SQL Parameters to filter multiple data sources, particularly in the context of Citi Bike trips in New York City.
