You can use h3QuerySource to build layers and widgets using custom SQL queries powered by your data warehouse that contain:
h3-based data: a column containing an h3 cell identifier
Point-based vector data
When you use point-based vector data in your h3QuerySource, CARTO will dynamically aggregate it into h3 cells, using an aggregation expression of your choice.
tableName: The fully qualified name (FQN) of the table you want to retrieve from your data warehouse. For example, for a BigQuery connection, carto-demo-data.demo_tables.h3_sample would be a valid table name.
aggregationExp: A valid SQL expression that will define how this source aggregates h3 cells starting from the base resolution into each parent h3 cell in lower zoom levels. For example, for an aggregationExp containing AVG(income) , each parent h3 cell will contain the average income from all its children.
The response of h3QuerySource is a promise that can be resolved and used in and .
h3QuerySource is compatible with the following layers from the @deck.gl/carto module:
h3QuerySource is compatible with the following widget models:
aggregationResLevel (optional): A number that expresses the resolution detail that will be used for each zoom level. By default this value is 4 .
Numbers lower than 4 will produce a less detailed visualization, with better performance.
Numbers higher than 4 will product a more detailed visualization, with worse performance.
spatialDataColumn (optional): The name of the column that contains the geospatial information (geometries/geographies) that will be used for visualization and etc. By default, CARTO assumes this column is named geom.
filters (optional): A valid CARTO Filters object, used to perform server-side filtering of this data source with column-based filters.
filtersLogicalOperator (optional): Indicates whether filters are applied following an AND logic or an OR logic.
queryParameters (optional): If your sqlQuery contains query parameters, pass the values for each parameter in this field, following your data warehouse mechanism for query parameters, like named or positional parameters.
✅
✅
✅
✅
✅
✅
import {h3QuerySource} from '@carto/api-client';
const data = h3QuerySource({
accessToken: 'XXX',
connectionName: 'carto_dw',
sqlQuery: 'SELECT * FROM carto-demo-data.demo_tables.h3_table',
aggregationExp: 'SUM(population)'
});type H3TableSourceOptions = {
tableName: string;
aggregationExp: string;
aggregationResLevel?: number;
spatialDataColumn?: string;
filters?: Filters;
filtersLogicalOperator?: 'and' | 'or';
queryParameters?: QueryParameters;
};queryParameters and parameterized queriesCARTO uses the native mechanisms available in each data warehouse to build parameterized queries, which prevents any SQL injection.
Therefore, to specify the parameters in the query and the expected syntax for queryParameters , you will need to use the specific providers' syntax:
BigQuery
BigQuery uses named parameters.
Please note that empty arrays are not supported as parameter values.
Snowflake
Snowflake supports positional parameters, such as:
Redshift and PostgreSQL
Snowflake supports positional parameters, such as:
sqlQuery: 'SELECT * FROM carto-demo-data.demo_tables.retail_stores WHERE storetype = @type AND revenue > @minRevenue'
queryParameters: { 'minRevenue': 100000, 'type': 'Supermarket' },sqlQuery: 'SELECT * FROM demo_db.public.import_retail_stores WHERE storetype = :2 AND revenue > :1'
queryParameters: [100000, "Supermarket"],sqlQuery: 'SELECT * FROM carto_demo_data.demo_tables.retail_stores WHERE storetype = $2 AND revenue > $1...'
queryParameters: [100000, "Supermarket"],