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. Users can select values from the UI that will replace the placeholder in the SQL query.
The option to create a new SQL Parameter will be available once there is at least one data source of type Query:
Creating a new SQL Parameter
Text parameters are replaced by an array of strings in your query. They are a good choice if you need to filter by an existing category in your data or a text identifier.
When creating a text parameter, there are a few settings to be defined:
- Display name: The name that will appear in the control UI.
- SQL name: The name that needs to be used in your SQL query. It always has to be enclosed between double curly brackets, like:
{{type_of_place}} - Values: Here we can define the list of values that will be available for selection in the control UI. We can add them manually, or pre-fill the list from an existing column in any of the data sources currently added to the map. Using the Add from source option will get the 20 most frequent values in the selected column and add them to the list. This list can later be modified manually, by removing values or adding new ones manually.
Fill the list with values from an existing source
Once the parameter is created, we should manually add it (using its SQL name) to one or more of your SQL Query data sources, like:
SELECT * FROM `carto-demo-data`.demo_tables.populated_places
WHERE featurecla IN {{type_of_place}}
After adding the parameter to your SQL query data sources, the control UI will appear in the right-side panel, allowing search, custom text input and multi-selection of values:
Control UI for a text parameter
Text parameters in your queries are replaced by an array of strings with all the values selected using the control UI.
Make sure to use a SQL syntax that works well with arrays, like the
IN operatorDates parameters are replaced by dates in your queries. They are always used in pairs, defining the start and the end of a period of time.
When creating a dates parameter, there are a few settings to be defined:
- Display name: The name that will appear in the control UI.
- Start date SQL name: The name to be used in the SQL query to be replaced by the starting date of the selected period.
- End date SQL name: The name to be used in the SQL query to be replaced by the ending date of the selected period.
- Values: Define the dates that will be available for selection in the control UI. The calendar will be limited to the dates defined in this setting.
Once the parameter has been created, it should be added manually (using its SQL names) to one or more of your SQL Query data sources, like:
SELECT * FROM `carto-demo-data`.demo_tables.fires_worldwide
WHERE acq_date > {{fire_date_from}} AND acq_date < {{fire_date_to}}
Control UI for dates parameter
Support for parameters that work with other types of data, such as numeric ranges, is still a work in progress. Please let us know if you need to leverage SQL parameters with other types of data.
As an Editor user, you can control wether the SQL Parameters controls will be available in the public map from the "Shared map settings" section of the "Share" modal.
This setting will allow Viewer users to select values and re-run SQL queries.
Please be aware that in many cases this might produce a lot of different combinations of parameters that might not be cached in CARTO's CDN, and queries will reach the data warehouse and be executed, which might have a cost and performance implication.
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:
First, 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
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 SQL Parameter'
- Select 'Text Parameter'
- Pick a display name, like 'Day/Night'. The SQL name gets automatically generated as
{{day_night}} - In the 'Values' section, click on 'Add from source'. Select your data source and pick the
daynightcolumn
Configuration of a text parameter
- 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.
Now, let's add a dates parameter to filter fires by its date:
- Click on 'Create a SQL parameter'
- Select 'Dates parameter'
- Give it a display name, like 'Date'
- Type or select from a calendar the range of dates that are going to be available from the control UI.
Configuration of a dates parameter
- 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.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}}
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:
Exploring worldwide fires concentration with H3 and SQL Parameters
Last modified 1mo ago