SQL Parameters

SQL Parameters are placeholders that can be used on any SQL Query data source in Builder.

After creating a new SQL Parameter, it needs to be added manually to one or more SQL Query data sources.

Once added, the actual value for the parameter can be defined through a control UI in the right side panel's 'Parameters' tab. By selecting values from this UI, the placeholders in the SQL query will be dynamically replaced with the chosen values, enabling users to interactively customize the data displayed and analyzed in their maps.

SQL Parameter Types

SQL Parameters are categorized based on the data format of the values expected to be received, ensuring flexibility and ease of use. Below are the current type of SQL Parameters:

• Date Parameter: Ideal for handling date values, date parameters allow users to input a specific date range, enabling data analysis over precise time periods. For example, analyzing sales data for a specific month or quarter.

• Text Parameter: Tailored for text values, users can input or select a specific category to obtain precise insights. For instance, filtering Points of Interest (POI) types like "Supermarket" or "Restaurant".

• Numeric Parameter: Designed for numeric values, users can input specific numerical criteria to filter data or perform analysis based on their preferences. For example, updating the radius size of a geofence to update an analysis result.

Please let us know if you need to leverage SQL parameters with other types of data.

Using SQL Parameters

SQL Parameters can be used in many different ways. One of the most common is allowing viewers to interact with the data in a controlled manner. Let's cover a simple use case step by step:

Add a SQL Query data source

The option to create a new SQL Parameter will be available once there is at least one data source of type Query:

So, let's create a SQL Query data source with a table that contains information about fires all over the world:

• On a new map, click on 'Add source from...' and select 'Custom query (SQL)' .

• Select CARTO Data Warehouse as connection.

• Use the following query

SELECT * FROM `carto-demo-data.demo_tables.fires_worldwide`

Create and configure a text parameter

Once we have the data rendered in the map, we'll add a text parameter that helps us select between fires that happened during the day or the night.

• Click on 'Create a SQL Parameter'

• Select 'Text Parameter'

• In the 'Values' section, click on 'Add from source'. Select your data source and pick the daynight column

• In the 'Naming' section, pick a display name, like 'Day/Night'. The SQL name gets automatically generated as {{day_night}}

• After the parameter has been created, open the SQL panel and add it to your query:

SELECT * FROM `carto-demo-data`.demo_tables.fires_worldwide
WHERE daynight IN {{day_night}}

You can now use the control UI to add/remove values and check how the map changes.

Create and configure a date parameter

Now, let's add a date parameter to filter fires by its date:

• Click on 'Create a SQL parameter'

• Select 'Date parameter'

• Type or select from a calendar the range of dates that are going to be available from the control UI.

• Give it a display name, like 'Date'. The SQL names gets automatically generated as {{date_from}} and {{date_to}}

Open the SQL Panel and add the parameters to your query, like:

SELECT * FROM `carto-demo-data`.demo_tables.fires_worldwide
WHERE daynight IN {{day_night}}
AND acq_date > {{date_from}} AND acq_date < {{date_to}}

The parameters {{date_from}} and {{date_to}} will be replaced by the dates selected in the calendar.

Create and configure a numeric parameter

Next, we'll incorporate a range slider to introduce a numeric parameter. It will allow users to focus on fires based on their brightness temperature to identify the most intense fires.

• Click on 'Create a SQL parameter'

• Select 'Numeric parameter'

• In the 'Values' section, select Range Slider and enter the 'Min Value' and 'Max Value' within the range a user will be able to select.

• Give it a display name, like 'Bright Temp'. The SQL names gets automatically generated as {{bright_temp_from}} and {{bright_temp_to}}

Open the SQL Panel and add the parameters to your query, like:

SELECT * FROM `carto-demo-data`.demo_tables.fires_worldwide
WHERE daynight IN {{day_night}}
AND acq_date > {{date_from}} AND acq_date < {{date_to}}
AND bright_ti4 >= {{bright_temp_from}} AND bright_ti4 <= {{bright_temp_to}}

Bonus track: Filter points and aggregate them into an H3 grid

In order to have a more effective visualization that help us identify areas of higher concentration of fires, we should aggregate the points into a grid that uses spatial indexes.

For that, we could just pick 'Quadbin' as a visualization type in the layer settings. Learn more about it here.

Another option would be to leverage H3 indexes using the functions from the Analytics Toolbox to create an aggregated grid from the points in our dataset. For that, open the SQL Panel and modify your query so it looks like:

SELECT 
`carto-un`.carto.H3_FROMGEOGPOINT(geom, 8) AS h3,
COUNT(*) AS num_fires
FROM `carto-demo-data`.demo_tables.fires_worldwide
WHERE daynight IN {{day_night}}
AND acq_date > {{date_from}} AND acq_date < {{date_to}}
AND bright_ti4 >= {{brightness_temperature_from}} AND bright_ti4 <= {{brightness_temperature_to}}
GROUP BY h3

Make sure to select H3 as geospatial type before running the query. After that, selecting different options in the parameters controls should trigger a dynamic recalculation of the aggregation.

We can use SUM(num_features) to style our H3 grid, in order to detect areas with higher concentration of fires: