data

This module contains functions and procedures that make use of data (user-provided data) for their computations.

ENRICH_GRID

ENRICH_GRID(grid_type, input_query, input_index_column, data_query, data_geography_column, variables, output)

Description

This procedure enriches a query containing grid cell indexes of one of the supported types (H3, Quadbin) with data from another enrichment query that contains geographies, thus effectively transferring geography-based data to an spatial grid.

As a result of this process, each input grid cell will be enriched with the data of the enrichment query that spatially intersects it. When the input cell intersects with more than one feature of the enrichment query, the data is aggregated using the aggregation methods specified.

Valid aggregation methods are:

• SUM: It assumes the aggregated variable is an extensive property (e.g. population). Accordingly, the value corresponding to the enrichment feature intersected is weighted by the fraction of area or length intersected. If the enrichment features are points, then a simple sum is performed.

• MIN: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the intersected area/length as it's the case for SUM.

• MAX: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the intersected area/length as it's the case for SUM.

• AVG: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the intersected area/length as it's the case for SUM. However, a weighted average is computed, using the intersection areas or lengths as the weight. When the enrich features are points, a simple average is computed.

• COUNT It computes the number of enrich features that contain the enrichment variable and are intersected by the input geography.

For other types of aggregation, the ENRICH_GRID_RAW procedure can be used to obtain non-aggregated data that can be later applied to any desired custom aggregation.

{% hint style="info" %} If the enrichment of an input table needs to be repeated, please notice that dropping the added columns will generate problems in consecutive enrichments as Bigquery saves those columns during 7 days for time travel purposes. We recommend storing the original table columns in a temporal table, dropping the input table and then recreating the input table from the temporal table. {% endhint %}

Input parameters

Enrich grid cells with user-provided data.

• grid_type: Type of grid: "h3", "quadbin".

• input_query: STRING query to be enriched (Standard SQL); this query must produce valid grid indexes for the selected grid type in a column of the proper type (STRING for H3, and INT64 for Quadbin). It can include additional columns with data associated with the grid cells that will be preserved. A qualified table name can be given as well, e.g. 'project-id.dataset-id.table-name'.

• input_index_column: STRING name of a column in the query that contains the grid indexes.

• data_query: STRING query that contains both a geography column and the columns with the data that will be used to enrich the cells provided in the input query.

• data_geography_column: STRING name of the GEOGRAPHY column provided in the data_query.

• variables: ARRAY<STRUCT<column STRING, aggregation STRING>> with the columns that will be used to enrich the input polygons and their corresponding aggregation method (SUM, AVG, MAX, MIN, COUNT).

• output: ARRAY<STRING>|NULL containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it. The name of the output table should include project and dataset, e.g. ['project-id.dataset-id.table-name'] or ['project-id.dataset-id.table-name', 'PARTITION BY number']. If NULL the enrichment result is returned. When the output table is the same than then input, the input table will be enriched in place.

Output

The resulting table has all the input columns and one additional column for each variable in variables, named with a suffix indicating the aggregation method used.

The output table will be clustered by the spatial index to optimize its performance when filtering data by it or using it to join to other grid tables. This is important to visualize the results in a map efficiently. If an SQL clause is included in the output parameter this optimization will not be performed.

Examples

CALL `carto-un`.carto.ENRICH_GRID(
   'h3',
   R'''
   SELECT * FROM UNNEST(['8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff']) AS index
   ''',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, var1_sum, var2_sum, var2_max

CALL `carto-un-eu`.carto.ENRICH_GRID(
   'h3',
   R'''
   SELECT * FROM UNNEST(['8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff']) AS index
   ''',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, var1_sum, var2_sum, var2_max

CALL carto.ENRICH_GRID(
   'h3',
   R'''
   SELECT * FROM UNNEST(['8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff']) AS index
   ''',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, var1_sum, var2_sum, var2_max

CALL `carto-un`.carto.ENRICH_GRID(
   'h3',
   'my-project.my-dataset.my-table',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['my-project.my-dataset.my-table']
);
-- The columns var1_sum, var2_sum, var2_max will be added to the table
-- 'my-project.my-dataset.my-table'.

CALL `carto-un-eu`.carto.ENRICH_GRID(
   'h3',
   'my-project.my-dataset.my-table',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['my-project.my-dataset.my-table']
);
-- The columns var1_sum, var2_sum, var2_max will be added to the table
-- 'my-project.my-dataset.my-table'.

CALL carto.ENRICH_GRID(
   'h3',
   'my-project.my-dataset.my-table',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['my-project.my-dataset.my-table']
);
-- The columns var1_sum, var2_sum, var2_max will be added to the table
-- 'my-project.my-dataset.my-table'.

{% endtab %} {% endtabs %}

ENRICH_GRID_RAW

ENRICH_GRID_RAW(grid_type, input_query, input_index_column, data_query, data_geography_column, variables, output)

Description

This procedure enriches a query containing grid cell indexes of one of the supported types (H3, Quadbin) with data from another enrichment query that contains geographies, thus effectively transferring geography-based data to an spatial grid.

As a result of this process, each input grid cell will be enriched with the data of the enrichment query that spatially intersects it. The variable values corresponding to all intersecting enrichment features for a given input cell will be returned in an ARRAY column named __carto_enrichment. Each array contains STRUCTs with one field for each variable and additional measure fields __carto_intersection, __carto_total, __carto_dimension. See the output information for more details.

Input parameters

• grid_type: STRING Type of grid: "h3" or "quadbin". A qualified table name can be given as well, e.g. 'project-id.dataset-id.table-name'.

• input_query: STRING query to be enriched (Standard SQL); this query must produce valid grid indexes for the selected grid type in a column of the proper type (STRING for H3, and INT64 for Quadbin). It can include additional columns with data associated with the grid cells that will be preserved.

• input_index_column: STRING name of a column in the query that contains the grid indexes.

• data_query: STRING query that contains both a geography column and the columns with the data that will be used to enrich the cells provided in the input query.

• data_geography_column: STRING name of the GEOGRAPHY column provided in the data_query.

• variables: ARRAY<STRING> of names of the columns in the enrichment query that will be added to the enriched results.

• output: ARRAY<STRING>|NULL containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it. The name of the output table should include project and dataset, e.g. ['project-id.dataset-id.table-name'] or ['project-id.dataset-id.table-name', 'PARTITION BY number']. If NULL the enrichment result is returned. When the output table is the same than the input, the input table will be enriched in place.

{% hint style="info" %} If the enrichment of an input table needs to be repeated, please notice that dropping the added columns will generate problems in consecutive enrichments as Bigquery saves those columns during 7 days for time travel purposes. We recommend storing the original table columns in a temporal table, dropping the input table and then recreating the input table from the temporal table. {% endhint %}

Output

The output table will contain all the input columns provided in the input_query and one extra ARRAY column named __carto_enrichment. The array contains STRUCTs with one field for each variable. Additional fields will be included with information about the intersection of the grid cell and the enrichment features.

• __carto_dimension dimension of the enrichment geography: 2 for areas (polygons), 1 for lines, and 0 for points.

• __carto_intersection area in square meters (for dimension = 2) or length in meters (for dimension = 1) of the intersection.

• __carto_total area in square meters (for dimension = 2) or length in meters (for dimension = 1) of the enrichment feature.

The output table will be clustered by the spatial index to optimize its performance when filtering data by it or using it to join to other grid tables. This is important to visualize the results in a map efficiently. If an SQL clause is included in the output parameter this optimization will not be performed.

Examples

CALL `carto-un`.carto.ENRICH_GRID(
   'h3',
   R'''
   SELECT * FROM UNNEST(['8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff']) AS index
   ''',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, __carto_enrichment. The latter will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

CALL `carto-un-eu`.carto.ENRICH_GRID(
   'h3',
   R'''
   SELECT * FROM UNNEST(['8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff']) AS index
   ''',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, __carto_enrichment. The latter will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

CALL carto.ENRICH_GRID(
   'h3',
   R'''
   SELECT * FROM UNNEST(['8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff']) AS index
   ''',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, __carto_enrichment. The latter will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

CALL `carto-un`.carto.ENRICH_GRID(
   'h3',
   'my-project.my-dataset.my-table',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['my-project.my-dataset.my-table']
);
-- The column __carto_enrichment will be added to the table
-- 'my-project.my-dataset.my-table'.
-- The new column will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

CALL `carto-un-eu`.carto.ENRICH_GRID(
   'h3',
   'my-project.my-dataset.my-table',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['my-project.my-dataset.my-table']
);
-- The column __carto_enrichment will be added to the table
-- 'my-project.my-dataset.my-table'.
-- The new column will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

CALL carto.ENRICH_GRID(
   'h3',
   'my-project.my-dataset.my-table',
   'index',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['my-project.my-dataset.my-table']
);
-- The column __carto_enrichment will be added to the table
-- 'my-project.my-dataset.my-table'.
-- The new column will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

{% endtab %} {% endtabs %}

ENRICH_POINTS

ENRICH_POINTS(input_query, input_geography_column, data_query, data_geography_column, variables, output)

Description

This procedure enriches a query containing geographic points with data from another query, spatially matching both and aggregating the result.

As a result of this process, each input point will be enriched with the data from the enrichment query that spatially intersects it. When the input point intersects with more than one enrichment polygon, point, or line, the data is aggregated using the aggregation methods specified.

Valid aggregation methods are: SUM, MIN, MAX, AVG, and COUNT.

For special types of aggregation, the ENRICH_POINTS_RAW procedure can be used to obtain non-aggregated data that can be later applied to any desired custom aggregation.

{% hint style="info" %} If the enrichment of an input table needs to be repeated, please notice that dropping the added columns will generate problems in consecutive enrichments as Bigquery saves those columns during 7 days for time travel purposes. We recommend storing the original table columns in a temporal table, dropping the input table and then recreating the input table from the temporal table. {% endhint %}

Input parameters

• input_query: STRING query to be enriched (Standard SQL). A qualified table name can be given as well, e.g. 'project-id.dataset-id.table-name'.

• input_geography_column: STRING name of the GEOGRAPHY column in the query containing the points to be enriched.

• data_query: STRING query that contains both a geography column and the columns with the data that will be used to enrich the points provided in the input query.

• data_geography_column: STRING name of the GEOGRAPHY column provided in the data_query.

• variables: ARRAY<STRUCT<column STRING, aggregation STRING>> with the columns that will be used to enrich the input points and their corresponding aggregation method

• output: ARRAY<STRING>|NULL containing the name of an output table to store the results * output: ARRAY<STRING>|NULL containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it. The name of the output table should include project and dataset, e.g. ['project-id.dataset-id.table-name'] or ['project-id.dataset-id.table-name', 'PARTITION BY number']. If NULL the enrichment result is returned. When the output table is the same than then input, the input table will be enriched in place.

Output

The output table will contain all the input columns provided in the input_query and one extra column for each variable in variables, named after its corresponding enrichment column and including a suffix indicating the aggregation method used.

The output table will be clustered by the geography column to optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently. If an SQL clause is included in the output parameter this optimization will not be performed.

Examples

CALL `carto-un`.carto.ENRICH_POINTS(
   R'''
   SELECT id, geom FROM `my-project.my-dataset.my-input`
   ''',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: id, geom, var1_sum, var2_sum, var2_max

CALL `carto-un-eu`.carto.ENRICH_POINTS(
   R'''
   SELECT id, geom FROM `my-project.my-dataset.my-input`
   ''',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: id, geom, var1_sum, var2_sum, var2_max

CALL carto.ENRICH_POINTS(
   R'''
   SELECT id, geom FROM `my-project.my-dataset.my-input`
   ''',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: id, geom, var1_sum, var2_sum, var2_max

carto-un

CALL `carto-un`.carto.ENRICH_POINTS(
   'my-project.my-dataset.my-input',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['my-project.my-dataset.my-input']
);
-- The columns var1_sum, var2_sum, var2_max will be added to the table
-- 'my-project.my-dataset.my-input'.

carto-un-eu

manual

manual

carto-un

CALL `carto-un`.carto.ENRICH_POINTS(
   'my-project.my-dataset.my-input',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['my-project.my-dataset.my-input']
);
-- The column __carto_enrichment will be added to the table
-- 'my-project.my-dataset.my-input'.
-- The new column will contain STRUCTs with the fields var1, var2,
-- __carto_total, and __carto_dimension.

carto-un-eu

manual

ENRICH_POLYGONS

ENRICH_POLYGONS(input_query, input_geography_column, data_query, data_geography_column, variables, output))

Description

This procedure enriches a query containing geographic polygons with data from another query, spatially matching both and aggregating the result.

As a result of this process, each input polygon will be enriched with the data from the enrichment query that spatially intersects it. When the input polygon intersects with more than one enrichment polygon, point or, line, the data is aggregated using the aggregation methods specified.

Valid aggregation methods are:

• SUM: It assumes the aggregated variable is an extensive property (e.g. population). Accordingly, the value corresponding to the enrichment feature intersected is weighted by the fraction of area or length intersected. If the enrichment features are points, then a simple sum is performed.

• MIN: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the intersected area/length as it's the case for SUM.

• MAX: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the intersected area/length as it's the case for SUM.

• AVG: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the intersected area/length as it's the case for SUM. However, a weighted average is computed, using the intersection areas or lengths as the weight. When the enrichment features are points, a simple average is computed.

• COUNT It computes the number of enrichment features that contain the enrichment variable and are intersected by the input geography.

For other types of aggregation, the ENRICH_POLYGONS_RAW procedure can be used to obtain non-aggregated data that can be later applied to any desired custom aggregation.

If the enrichment of an input table needs to be repeated, please notice that dropping the added columns will generate problems in consecutive enrichments as Bigquery saves those columns during 7 days for time travel purposes. We recommend storing the original table columns in a temporal table, dropping the input table and then recreating the input table from the temporal table.

Input parameters

• input_query: STRING query to be enriched (Standard SQL). A qualified table name can be given as well, e.g. 'project-id.dataset-id.table-name'.

• input_geography_column: STRING name of the GEOGRAPHY column in the query containing the polygons to be enriched.

• data_query: STRING query that contains both a geography column and the columns with the data that will be used to enrich the polygons provided in the input query.

• data_geography_column: STRING name of the GEOGRAPHY column provided in the data_query.

• variables: ARRAY<STRUCT<column STRING, aggregation STRING>> with the columns that will be used to enrich the input polygons and their corresponding aggregation method (SUM, AVG, MAX, MIN, COUNT).

• output: ARRAY<STRING>|NULL containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it. The name of the output table should include project and dataset, e.g. ['project-id.dataset-id.table-name'] or ['project-id.dataset-id.table-name', 'PARTITION BY number']. If NULL the enrichment result is returned. When the output table is the same than then input, the input table will be enriched in place.

Output

The output table will contain all the input columns provided in the input_query and one extra column for each variable in variables, named after its corresponding enrichment column and including a suffix indicating the aggregation method used.

The output table will be clustered by the geography column to optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently. If an SQL clause is included in the output parameter this optimization will not be performed.

Examples

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS(
   R'''
   SELECT id, geom FROM `my-project.my-dataset.my-input`
   ''',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: id, geom, var1_sum, var2_sum, var2_max

carto-un-eu

manual

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS(
   'my-project.my-dataset.my-input',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
   ['my-project.my-dataset.my-input']
);
-- The columns var1_sum, var2_sum, var2_max will be added to the table
-- 'my-project.my-dataset.my-input'.

carto-un-eu

manual

ENRICH_POLYGONS_RAW

ENRICH_POLYGONS_RAW(input_query, input_geography_column, data_query, data_geography_column, variables, output))

Description

This procedure enriches a query containing geographic polygons with data from another query, spatially matching both.

As a result of this process, each input polygon will be enriched with the data of the enrichment query that spatially intersects it. The variable values corresponding to all intersecting enrichment features for a given input polygon will be returned in an ARRAY column named __carto_enrichment. Each array value in this column contains STRUCTs with one field for each variable and additional measure fields __carto_intersection, __carto_total, __carto_dimension. See the output information for details.

If the enrichment of an input table needs to be repeated, please notice that dropping the added columns will generate problems in consecutive enrichments as Bigquery saves those columns during 7 days for time travel purposes. We recommend storing the original table columns in a temporal table, dropping the input table and then recreating the input table from the temporal table.

Input parameters

• input_query: STRING query to be enriched (Standard SQL). A qualified table name can be given as well, e.g. 'project-id.dataset-id.table-name'.

• input_geography_column: STRING name of the GEOGRAPHY column in the query containing the polygons to be enriched.

• data_query: STRING query that contains both a geography column and the columns with the data that will be used to enrich the polygons provided in the input query.

• data_geography_column: STRING name of the GEOGRAPHY column provided in the data_query.

• variables: ARRAY<STRING> of names of the columns in the enrichment query that will be added to the enriched results.

• output: ARRAY<STRING>|NULL containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it. The name of the output table should include project and dataset, e.g. ['project-id.dataset-id.table-name'] or ['project-id.dataset-id.table-name', 'PARTITION BY number']. If NULL the enrichment result is returned. When the output table is the same than then input, the input table will be enriched in place.

Output

The output table will contain all the input columns provided in the input_query, and one extra ARRAY column named __carto_enrichment. The array contains STRUCTs with one field for each variable. Additional fields will be included with information about the intersection of the geographies:

• __carto_dimension dimension of the enrichment geography: 2 for areas (polygons), 1 for lines, and 0 for points.

• __carto_intersection area in square meters (for dimension = 2) or length in meters (for dimension = 1) of the intersection.

• __carto_total area in square meters (for dimension = 2) or length in meters (for dimension = 1) of the enrichment feature.

Moreover, another column named __carto_input_area will be added containing the area of the input polygon in square meters.

The output table will be clustered by the geography column to optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently. If an SQL clause is included in the output parameter this optimization will not be performed.

Examples

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS_RAW(
   R'''
   SELECT id, geom FROM `my-project.my-dataset.my-input`
   ''',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['`my-project.my-dataset.my-enriched-table`']
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: id, geom, __carto_enrichment. The latter will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

carto-un-eu

manual

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS_RAW(
   'my-project.my-dataset.my-input',
   'geom',
   R'''
   SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
   ''',
   'geom',
   ['var1', 'var2'],
   ['my-project.my-dataset.my-input']
);
-- The column __carto_enrichment will be added to the table
-- 'my-project.my-dataset.my-input'.
-- The new column will contain STRUCTs with the fields var1, var2,
-- __carto_intersection, __carto_total, and __carto_dimension.

carto-un-eu

manual

ENRICH_POLYGONS_WEIGHTED

ENRICH_POLYGONS_WEIGHTED(input_query, input_geography_column, data_query, data_geography_column, variables, weight_variable, custom_weight_query, output)

The December 2025 release will introduce breaking changes to this procedure. To maintain the current functionality and prevent breaking changes in existing workflows, use ENRICH_POLYGONS_WEIGHTED_LEGACY instead.

Description

This procedure enriches a query containing geographic polygons with custom data provided by the user.

As a result of this process, each input polygon will be enriched with the custom data that spatially intersect it, weighted accordingly by the specified feature. When the input polygon intersects with more than one polygon, point, or line of the provided datasets, the data is aggregated using the aggregation methods specified. Using a speficied feature as weight for the enrichment, it weights appropiately the intersection segments with regard to the total original segment. For example the attribution of the feature to each intersected segment results from the value of the weighted feature in the intersection segment over the total original segment.

Valid aggregation methods are:

• SUM: It assumes the aggregated variable is an extensive property (e.g. population). Accordingly, the value corresponding to the feature intersected is weighted by the fraction of the intersected weight variable.

• MIN: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the weight variable.

• MAX: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). Thus, the value is not altered by the weight variable.

• AVG: It assumes the aggregated variable is an intensive property (e.g. temperature, population density). A weighted average is computed, using the value of the intersected weight variable as weights.

• COUNT It computes the number of features that contain the enrichment variable and are intersected by the input geography.

If the enrichment of an input table needs to be repeated, please notice that dropping the added columns will generate problems in consecutive enrichments as Bigquery saves those columns during 7 days for time travel purposes. We recommend storing the original table columns in a temporal table, dropping the input table and then recreating the input table from the temporal table.

Input parameters

• input_query: STRING query to be enriched (Standard SQL). A qualified table name can be given as well, e.g. 'project-id.dataset-id.table-name'.

• input_geography_column: STRING name of the GEOGRAPHY column in the query containing the polygons to be enriched.

• data_query: STRING query that contains both a geography column and the columns with the data that will be used to enrich the polygons provided in the input query.

• data_geography_column: STRING name of the GEOGRAPHY column provided in the data_query.

• variables: ARRAY<STRUCT<variable STRING, aggregation STRING>> with the columns that will be used to enrich the input polygons and their corresponding aggregation method (SUM, AVG, MAX, MIN, COUNT). When enriching with multiple variables, all of them must have the same type.

• weight_variable: STRUCT<variable STRING, aggregation STRING> Variable that will be used to weight the intersections of the input polygons with the provided datasets polygons, lines, points. Its name and the aggregation method must be provided. Valid aggregation methods are: SUM, AVG, MAX, MIN, COUNT. This variable is mandatory. If NULL then an error is raised.

• custom_weight_query: STRING query that contains the custom variable to be used as weight together with a geography column geom. If it is set to NULL, then the data_query is used for the weight_variable.

• output: ARRAY<STRING>|NULL containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it. The name of the output table should include project and dataset, e.g. ['project-id.dataset-id.table-name'] or ['project-id.dataset-id.table-name', 'PARTITION BY number']. If NULL the enrichment result is returned. When the output table is the same than then input, the input table will be enriched in place.

Output

The output table will contain all the input columns provided in the input_query and one extra column for each variable in variables, named after its corresponding name and including a suffix indicating the aggregation method used.

If a new output table is created, it will be clustered by the geography column to optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently. If an SQL clause is included in the output parameter this optimization will not be performed.

Examples

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS_WEIGHTED(
  R'''
  SELECT id, geom FROM `my-project.my-dataset.my-table`
  ''',
  'geom',
  R'''
  SELECT id, v1, v2, geom FROM `my-project.my-dataset.my-table-enrich`
  ''',
  'geom',
   [('v1','avg'),('v2','avg')],
  ('v3','sum'),
  R'''
  SELECT id, v3, geom FROM `my-project.my-dataset.my-table-weight`
  ''',
  ['my-project.my-dataset.my-table']
);
-- The table `my-project.my-dataset.my-table` will be created
-- with columns: id, geom, v1_avg, v2_avg

carto-un-eu

manual

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS_WEIGHTED(
  R'''
  SELECT id, geom FROM `my-project.my-dataset.my-table`
  ''',
  'geom',
  R'''
  SELECT id, v1, v2, v3, geom FROM `my-project.my-dataset.my-table-enrich`
  ''',
  'geom',
  [('v1','avg'),('v2','avg')],
  ('v3','sum'),
  NULL,
  ['my-project.my-dataset.my-table']
);
-- The table `my-project.my-dataset.my-table` will be created
-- with columns: id, geom, v1_avg, v2_avg

carto-un-eu

manual

carto-un

CALL `carto-un`.carto.ENRICH_POLYGONS_WEIGHTED(
  R'''
  SELECT id, geom FROM `my-project.my-dataset.my-table`
  ''',
  'geom',
  R'''
  SELECT id, v1, v2, v3, geom FROM `my-project.my-dataset.my-table-enrich`
  ''',
  'geom',
  [('v1','avg'),('v2','avg'), ('v2','sum')],
  ('v3','sum'),
  NULL,
  ['my-project.my-dataset.my-table']
);
-- The table `my-project.my-dataset.my-table` will be created
-- with columns: id, geom, v1_avg, v2_avg, v2_sum

carto-un-eu

manual

GRIDIFY_ENRICH

GRIDIFY_ENRICH(input_query, grid_type, grid_level, do_variables, do_source, custom_query, custom_variables, output_table, options)

Description

This procedure converts the input geometries into a grid of the specified type and resolution, and enriches it with Data Observatory and custom data. The user must be subscribed to all the Data Observatory datasets involved in the enrichment.

The enrichment operations performed using Data Observatory data and custom data are those described in the DATAOBS_ENRICH_GRID and ENRICH_GRID procedures, respectively. Please refer to their definition for more detailed information on the process. For numerical variables, the user can also select a k-ring size to aggregate neighboring cells and a decay function to weight the data of the neighbors cells according to their distance (a.k.a. neighboring order).

Input parameters

• input_query: STRING query containing the geometries to be gridified and enriched, stored in a column named geom. The geometries can be either a set of points, a polygon or a collection of polygons (Polygons or MultiPolygons).

• grid_type: STRING type of grid. Supported values are h3, and quadbin.

• grid_level: INT64 level or resolution of the cell grid. Check the available H3 levels and Quadbin levels.

• do_variables: ARRAY<STRUCT<variable STRING, aggregation STRING>> variables of the Data Observatory that will be used to enrich the grid cells. For each variable, its slug and the aggregation method must be provided. Use default to use the variable’s default aggregation method. Valid aggregation methods are: sum, avg, max, min, count. The catalog procedure DATAOBS_SUBSCRIPTION_VARIABLES can be used to find available variables and their slugs and default aggregation. It can be set to NULL.

• do_source: STRING name of the location where the Data Observatory subscriptions of the user are stored, in <my-dataobs-project>.<my-dataobs-dataset> format. If only the <my-dataobs-dataset> is included, it uses the project carto-data by default. It can be set to NULL or ''.

• custom_query: STRING query that contains a geography column called geom and the columns with the custom data that will be used to enrich the grid cells. It can be set to NULL or ''.

• custom_variables: ARRAY<STRUCT<variable STRING, aggregation STRING>> list with the columns of the custom_query and their corresponding aggregation method (sum, avg, max, min, count) that will be used to enrich the grid cells. It can be set to NULL.

• output_table: STRING containing the name of the output table to store the results of the gridification and the enrichment processes performed by the procedure. The name of the output table should include project and dataset: project.dataset.table_name.

• options: STRING the JSON string containing the available options as described in the table below.

  Option	Description
  categorical_variables	ARRAY<STRING> The array containing the names of the categorical (a.k.a. qualitative) columns
  kring	INT64 size of the kring where the decay function will be applied. This value is set to 0, in which case no kring will be computed and the decay function won't be applied. This option is only applicable to numerical variables.
  decay	STRING decay function to compute the distance decay. Available functions are: uniform, inverse, inverse_square and exponential. If set to NULL or '', uniform is used by default. This option is only applicable to numerical variables.

Output

The output table will contain all the input columns enriched with Data Observatory and custom data for each cell of the specified grid.

Example

carto-un

CALL `carto-un`.carto.GRIDIFY_ENRICH(
    -- Input query
    'SELECT geom FROM `cartobq.docs.twin_areas_target`',
    -- Grid params: grid type and level
    'quadbin', 15,
    -- Data Observatory enrichment
    [('population_14d9cf55', 'sum')],
    'my-dataobs-project.my-dataobs-dataset',
    -- Custom data enrichment
    '''
    SELECT geom, var1, var2 FROM `my-project.my-dataset.my-data`
    ''',
    [('var1', 'sum'), ('var2', 'sum'), ('var2', 'max')],
    -- Output table
    'my-project.my-dataset.my-enriched-table',
    -- Options
    R'''
    {
        "kring": 1,
        "decay": "uniform"
    }
    '''
);
-- The table `my-project.my-dataset.my-enriched-table` will be created
-- with columns: index, population_14d9cf55_sum, var1_sum, var2_sum, var2_max

carto-un-eu

manual

Additional examples

• Find Twin Areas of top-performing stores

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.