data

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

ENRICH_POINTS

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

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.

Input parameters

• input_query: STRING query to be enriched. A qualified table name can be given as well, e.g. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• variables: STRING a JSON array of objects with the columns that will be used to enrich the input points and their corresponding aggregation method, e.g. '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "avg"}]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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.

Examples

CALL carto.ENRICH_POINTS(
    'SELECT id, geom FROM <my-catalog>.<my-schema>.<my-input>',
    'geom',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "max"}]',
    '<my-catalog>.<my-schema>.<my-enriched-table>'
);
-- The table <my-catalog>.<my-schema>.<my-enriched-table> will be created
-- with columns: id, geom, var1_sum, var2_max

CALL carto.ENRICH_POINTS(
    'SELECT * FROM <my-catalog>.<my-schema>.<my-input>',
    'geom',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "max"}]',
    '<my-catalog>.<my-schema>.<my-input>'
);
-- The columns var1_sum, var2_max will be added to the table
-- <my-catalog>.<my-schema>.<my-input>

ENRICH_POINTS_RAW

ENRICH_POINTS_RAW(input_query, input_geography_column, data_query, data_geography_column, variables, output_table)

Description

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

As a result of this process, each input point 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 point 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_total and __carto_dimension. See the output information for details.

Input parameters

• input_query: STRING query to be enriched. A qualified table name can be given as well, e.g. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• variables: STRING a JSON array of strings with the names of the columns in the enrichment query that will be added to the enriched results, e.g. '["var1", "var2"]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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_total area in square meters (for dimension = 2) or length in meters (for dimension = 1) of the enrichment feature.

Example

CALL carto.ENRICH_POINTS_RAW(
    'SELECT id, geom FROM <my-catalog>.<my-schema>.<my-input>',
    'geom',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '["var1", "var2"]',
    '<my-catalog>.<my-schema>.<my-enriched-table>'
);
-- The table <my-catalog>.<my-schema>.<my-enriched-table> will be created
-- with columns: id, geom, __carto_enrichment. The latter will contain
-- STRUCTs with the fields var1, var2, __carto_total, and __carto_dimension.

ENRICH_POLYGONS

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

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.

Input parameters

• input_query: STRING query to be enriched. A qualified table name can be given as well, e.g. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• variables: STRING a JSON array of objects with the columns that will be used to enrich the input polygons and their corresponding aggregation method (SUM, AVG, MAX, MIN, COUNT), e.g. '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "avg"}]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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.

Example

CALL carto.ENRICH_POLYGONS(
    'SELECT id, geom FROM <my-catalog>.<my-schema>.<my-input>',
    'geom',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "max"}]',
    '<my-catalog>.<my-schema>.<my-enriched-table>'
);
-- The table <my-catalog>.<my-schema>.<my-enriched-table> will be created
-- with columns: id, geom, var1_sum, var2_max

ENRICH_POLYGONS_RAW

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

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, and __carto_dimension. See the output information for details.

Input parameters

• input_query: STRING query to be enriched. A qualified table name can be given as well, e.g. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• variables: STRING a JSON array of strings with the names of the columns in the enrichment query that will be added to the enriched results, e.g. '["var1", "var2"]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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.

Example

CALL carto.ENRICH_POLYGONS_RAW(
    'SELECT id, geom FROM <my-catalog>.<my-schema>.<my-input>',
    'geom',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '["var1", "var2"]',
    '<my-catalog>.<my-schema>.<my-enriched-table>'
);
-- The table <my-catalog>.<my-schema>.<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.

ENRICH_POLYGONS_WEIGHTED

ENRICH_POLYGONS_WEIGHTED(input_query, input_geography_column, data_query, data_geography_column, weight_query, weight_geography_column, weight_variable, variables, output_table)

Description

This procedure enriches a query containing geographic polygons with custom data provided by the user, using a separate weight dataset to control how values are distributed across spatial intersections.

As a result of this process, each input polygon will be enriched with the custom data that spatially intersects it, weighted according to a specified variable from the weight dataset. The weight variable determines how to proportionally attribute values from the data query to the input geometries. For example, when using population as a weight variable, areas with higher population will receive proportionally more of the enriched values.

Valid aggregation methods are:

• SUM: It assumes the aggregated variable is an extensive property (e.g. population, total income). The value is weighted by the fraction of the weight variable in the intersection relative to the total weight in the source geometry.

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

Input parameters

• input_query: STRING query to be enriched. A qualified table name can be given as well, e.g. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• weight_query: STRING query that contains the weight variable and a geometry column. This parameter is mandatory and cannot be NULL.

• weight_geography_column: STRING name of the geometry column in the weight_query. This parameter is mandatory and cannot be NULL.

• weight_variable: STRING name of the column in weight_query that contains the weight values to be used for proportional attribution. This parameter is mandatory and cannot be NULL.

• variables: STRING a JSON array of objects with the columns that will be used to enrich the input polygons and their corresponding aggregation method. Only SUM and AVG aggregations are supported. At least one variable must be specified. E.g. '[{"column": "population", "aggregation": "sum"}, {"column": "income_avg", "aggregation": "avg"}]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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.

Examples

CALL carto.ENRICH_POLYGONS_WEIGHTED(
    'SELECT id, geom FROM <my-catalog>.<my-schema>.<my-table>',
    'geom',
    'SELECT id, population, income_avg, geom FROM <my-catalog>.<my-schema>.<my-data-table>',
    'geom',
    'SELECT id, weight_value, geom FROM <my-catalog>.<my-schema>.<my-weight-table>',
    'geom',
    'weight_value',
    '[{"column": "population", "aggregation": "sum"}, {"column": "income_avg", "aggregation": "avg"}]',
    '<my-catalog>.<my-schema>.<my-output-table>'
);
-- The table <my-catalog>.<my-schema>.<my-output-table> will be created
-- with columns: id, geom, population_sum, income_avg_avg

CALL carto.ENRICH_POLYGONS_WEIGHTED(
    'SELECT id, geom FROM <my-catalog>.<my-schema>.<my-table>',
    'geom',
    'SELECT improvval, geom FROM <my-catalog>.<my-schema>.<my-data-table>',
    'geom',
    'SELECT POPCY, geom FROM <my-catalog>.<my-schema>.<my-weight-table>',
    'geom',
    'POPCY',
    '[{"column": "improvval", "aggregation": "sum"}]',
    '<my-catalog>.<my-schema>.<my-output-table>'
);
-- The table <my-catalog>.<my-schema>.<my-output-table> will be created
-- with column: improvval_sum

ENRICH_GRID

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

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 a 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 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_GRID_RAW procedure can be used to obtain non-aggregated data that can be later applied to any desired custom aggregation.

Input parameters

• grid_type: STRING type of grid: 'h3' or 'quadbin'.

• input_query: STRING query to be enriched; this query must produce valid grid indexes for the selected grid type in a column of the proper type (STRING for H3, and BIGINT 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. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• variables: STRING a JSON array of objects with the columns that will be used to enrich the input cells and their corresponding aggregation method (SUM, AVG, MAX, MIN, COUNT), e.g. '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "avg"}]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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.

Example

CALL carto.ENRICH_GRID(
    'h3',
    'SELECT index FROM <my-catalog>.<my-schema>.<my-h3-table>',
    'index',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '[{"column": "var1", "aggregation": "sum"}, {"column": "var2", "aggregation": "max"}]',
    '<my-catalog>.<my-schema>.<my-enriched-table>'
);
-- The table <my-catalog>.<my-schema>.<my-enriched-table> will be created
-- with columns: index, var1_sum, var2_max

ENRICH_GRID_RAW

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

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 a 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, and __carto_dimension. See the output information for more details.

Input parameters

• grid_type: STRING type of grid: 'h3' or 'quadbin'.

• input_query: STRING query to be enriched; this query must produce valid grid indexes for the selected grid type in a column of the proper type (STRING for H3, and BIGINT 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. '<my-catalog>.<my-schema>.<my-table>'.

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

• data_query: STRING query that contains both a geometry 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 geometry column provided in the data_query.

• variables: STRING a JSON array of strings with the names of the columns in the enrichment query that will be added to the enriched results, e.g. '["var1", "var2"]'.

• output_table: STRING qualified name of the output table to store the results, e.g. '<my-catalog>.<my-schema>.<my-output-table>'. When the output table is the same as the 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 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.

Example

CALL carto.ENRICH_GRID_RAW(
    'h3',
    'SELECT index FROM <my-catalog>.<my-schema>.<my-h3-table>',
    'index',
    'SELECT geom, var1, var2 FROM <my-catalog>.<my-schema>.<my-data>',
    'geom',
    '["var1", "var2"]',
    '<my-catalog>.<my-schema>.<my-enriched-table>'
);
-- The table <my-catalog>.<my-schema>.<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.