SQL Parameters

SQL Parameters are dynamic placeholders that can be used in any SQL Query data source in Builder. They let users interactively adjust inputs—like filters, buffer sizes, or index scores—so that maps respond to specific questions and uncover tailored insights.

Once created, parameters must be manually linked to one or more SQL sources. Users then interact with them through the Parameters tab in the right-side panel. These inputs dynamically update the SQL query behind the map.

SQL Parameter Types

SQL Parameters are categorized based on the format of input values:

• Date Parameter: Set a default date range users can adjust to filter results over time. E.g., analyze data for a specific month or quarter.

• Text Parameter: Select specific categories to filter or group data. E.g., choose POI types like 'Supermarket' or 'Restaurant'.

• Numeric Parameter: Define a numeric input or range to filter or drive calculations. E.g., control a buffer radius or signal threshold.

Using SQL Parameters

Add a SQL Query data source

Parameters can only be added once at least one SQL Query source is available.

• Go to "Add source from..."

• Choose "Custom SQL query"

• Select CARTO Data Warehouse

• Paste this example:

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 'Create a SQL Parameter'

• Select 'Text Parameter'

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

• In the 'Order by' option choose 'Alphabetically ascending'

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

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'

• Set a default range using the calendar for both start and end dates.

• 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. The calendar allow users to select any desired date.

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