boundaryQuerySource

You can use boundaryQuerySource to build layers and widgets using a combination of:

1. Properties: A custom SQL query to your data warehouse that returns points.

2. Boundaries: A pre-generated tileset in your data warehouse that contains the polygon boundaries that will be used to aggregate the properties.

Learn more about building highly-performant large-scale boundaries visualization in our using Boundaries guide for developers.

Usage

import {boundaryQuerySource} from '@carto/api-client';

const data = boundaryTableSource({
  accessToken: 'XXX',
  connectionName: 'carto_dw',
  tilesetTableName: 'carto-data.my_tilesets.zipcodes_boundaries',
  propertiesSqlQuery: 'SELECT * FROM carto-demo-data.demo_tables.data_sample'
});

Options

type BoundaryTableSourceOptions = {
  tilesetTableName: string;
  propertiesSqlQuery: string;
  columns?: string[];
  filters?: Filters;
  filtersLogicalOperator?: 'and' | 'or'; 
  queryParameters?: QueryParameters;
};

• tilesetTableName: The fully qualified name (FQN) of the table that will contain the polygon-based boundaries in your visualization. For example, for a BigQuery connection, carto-data.my_tilesets.zipcodes_boundaries would be a valid boundaries table name.

• propertiesSqlQuery: A custom SQL query that returns the point-based properties in your visualization. You should use valid syntax for your data warehouse, which allows you to use all kinds of advanced processing directly in the data source. For example, for a BigQuery connection, SELECT * FROM carto-demo-data.demo_tables.chicago_crime_sample LIMIT 100 would be a valid SQL query.

• columns (optional): The list of columns to retrieve from the table specified in propertiesSqlQuery . Useful to increase performance and optimize query costs in queries with a large number of columns. By default, all columns will be retrieved.

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

About queryParameters and parameterized queries

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

sqlQuery: 'SELECT * FROM carto-demo-data.demo_tables.retail_stores WHERE storetype = @type AND revenue > @minRevenue'
queryParameters: { 'minRevenue': 100000, 'type': 'Supermarket' },

Please note that empty arrays are not supported as parameter values.

Snowflake

Snowflake supports positional parameters, such as:

sqlQuery: 'SELECT * FROM demo_db.public.import_retail_stores WHERE storetype = :2 AND revenue > :1'
queryParameters: [100000, "Supermarket"],

Redshift and PostgreSQL

Snowflake supports positional parameters, such as:

sqlQuery: 'SELECT * FROM carto_demo_data.demo_tables.retail_stores WHERE storetype = $2 AND revenue > $1...'
queryParameters: [100000, "Supermarket"],

Response

The response of boundaryQuerySource is a promise that can be resolved and used in layers.

Connection compatibility

Connection Data Warehouse	Compatible
BigQuery	✅
Snowflake	✅
Redshift	❌
Databricks	❌
PostgreSQL	❌
CARTO Data Warehouse	✅

Layer compatibility

boundaryQuerySource is compatible with the following layers from the @deck.gl/carto module:

• VectorTileLayer

Widget model compatibility

boundaryQuerySource is not compatible with widgets.

A recommended workaround is to use a vectorQuerySource using the same properties, attached to the widgets. The same filters can then be attached to both your vector source and your boundary source.