# data

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

## ENRICH\_GRID <a href="#enrich_grid" id="enrich_grid"></a>

```sql
ENRICH_GRID(grid_type, input_query, input_index_column, data_query, data_geography_column, aggregations, 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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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](https://en.wikipedia.org/wiki/Weighted_arithmetic_mean) 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`](#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`: Type of grid: "h3" or "quadbin".
* `input_query`: `VARCHAR` query to be enriched; this query must produce valid grid indexes for the selected grid type in a column of the proper type (VARCHAR for H3 and INT 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.
* `input_index_column`: `VARCHAR` name of a column in the query that contains the grid indexes.
* `data_query`: `VARCHAR` 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. All rows in the table must contain the same kind of geometry (points/lines/polygons) in the geography column.
* `data_geography_column`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `aggregations`: `ARRAY` with the columns that will be used to enrich the input polygons and their corresponding aggregation method (`SUM`, `AVG`, `MAX`, `MIN`, `COUNT`). Each element in this array should be an `OBJECT` with fields `column` and `aggregation`.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. When the output table is the same than then input, the input table will be enriched in place.

Note that GeometryCollection/FeatureCollection geographies are not supported at the moment.

**Output**

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

If a new output table is created, it 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**

```sql
CALL CARTO.CARTO.ENRICH_GRID(
   'h3',
   $
   SELECT value AS index FROM TABLE(FLATTEN(ARRAY_CONSTRUCT(
     '8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff'
    )))
   $,
   'index',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT(
     OBJECT_CONSTRUCT('column', 'VAR1', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'max')
   ),
   TO_ARRAY('MYENRICHEDTABLE')
);
-- The table `MYENRICHEDTABLE` will be created
-- with columns: INDEX, VAR1_SUM, VAR2_SUM, VAR2_MAX
```

```sql
CALL CARTO.CARTO.ENRICH_GRID(
   'h3',
   '<my-database>.<my-schema>.<my-table>',
   'index',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT(
     OBJECT_CONSTRUCT('column', 'VAR1', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'max')
   ),
   TO_ARRAY('<my-database>.<my-schema>.<my-table>')
);
-- The table `<my-database>.<my-schema>.<my-table>` will be augmented
-- with columns: VAR1_SUM, VAR2_SUM, VAR2_MAX
```

## ENRICH\_GRID\_RAW <a href="#enrich_grid_raw" id="enrich_grid_raw"></a>

```sql
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.

**Input parameters**

* `grid_type`: Type of grid: "h3" or "quadbin".
* `input_query`: `VARCHAR` query to be enriched; this query must produce valid grid indexes for the selected grid type in a column of the proper type (VARCHAR for H3 and INT 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.
* `input_index_column`: `VARCHAR` name of a column in the query that contains the grid indexes.
* `data_query`: `VARCHAR` 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. All rows in the table must contain the same kind of geometry (points/lines/polygons) in the geography column.
* `data_geography_column`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `variables`: `ARRAY` of `VARCHAR` elements with names of the columns in the enrichment query that will be added to the enriched results.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. When the output table is the same than then input, the input table will be enriched in place.

Note that GeometryCollection/FeatureCollection geographies are not supported at the moment.

**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 OBJECTs 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.

If a new output table is created, it 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**

```sql
CALL CARTO.CARTO.ENRICH_GRID_RAW(
   'h3',
   $
   SELECT value AS index FROM TABLE(FLATTEN(ARRAY_CONSTRUCT(
     '8718496d8ffffff','873974865ffffff','87397486cffffff','8718496daffffff','873974861ffffff','8718496dbffffff','87397494bffffff','8718496ddffffff','873974864ffffff'
    )))
   $,
   'index',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT('VAR1', 'VAR2'),
   TO_ARRAY('MYENRICHEDTABLE')
);
-- The table `MYENRICHEDTABLE` will be created
-- with columns: INDEX, __CARTO_ENRICHMENT. The latter will contain OBJECTS with the fields VAR1, VAR2, __carto_intersection, __carto_total and __carto_dimension.
```

```sql
CALL CARTO.CARTO.ENRICH_GRID_RAW(
   'h3',
   '<my-database>.<my-schema>.<my-table>',
   'index',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT('VAR1', 'VAR2'),
   TO_ARRAY('<my-database>.<my-schema>.<my-table>')
);
-- The table `<my-database>.<my-schema>.<my-table>` will be augmented
-- with column __CARTO_ENRICHMENT which will contain OBJECTS with the fields VAR1, VAR2, __carto_intersection, __carto_total and __carto_dimension.
```

## ENRICH\_POINTS <a href="#enrich_points" id="enrich_points"></a>

```sql
ENRICH_POINTS(input_query, input_geography_column, data_query, data_geography_column, aggregations, 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`](#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`: `VARCHAR` query to be enriched. A qualified table name can be given as well.
* `input_geography_column`: `VARCHAR` name of the GEOGRAPHY column in the query containing the points to be enriched.
* `data_query`: `VARCHAR` 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. All rows in the table must contain the same kind of geometry (points/lines/polygons) in the geography column.
* `data_geography_column`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `aggregations`: `ARRAY` with the columns that will be used to enrich the input polygons and their corresponding aggregation method (`SUM`, `AVG`, `MAX`, `MIN`, `COUNT`). Each element in this array should be an `OBJECT` with fields `column` and `aggregation`.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. 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 `aggregations`, named after its corresponding enrichment column and including a suffix indicating the aggregation method used.

If a new output table is created, it will be ordered by the x and y coordinates optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently.

**Examples**

```sql
CALL CARTO.CARTO.ENRICH_POINTS(
   'SELECT ID, GEOM FROM MYTABLE', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT(
     OBJECT_CONSTRUCT('column', 'VAR1', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'max')
   ),
   TO_ARRAY('MYENRICHEDTABLE')
);
-- The table `MYENRICHEDTABLE` will be created
-- with columns: ID, GEOM, VAR1_SUM, VAR2_SUM, VAR2_MAX
```

```sql
CALL CARTO.CARTO.ENRICH_POINTS(
   '<my-database>.<my-schema>.<my-table>', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT(
     OBJECT_CONSTRUCT('column', 'VAR1', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'max')
   ),
   TO_ARRAY('<my-database>.<my-schema>.<my-table>')
);
-- The table `<my-database>.<my-schema>.<my-table>` will be created
-- with columns: VAR1_SUM, VAR2_SUM, VAR2_MAX
```

## ENRICH\_POINTS\_RAW <a href="#enrich_points_raw" id="enrich_points_raw"></a>

```sql
ENRICH_POINTS_RAW(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.

As a result of this process, each input polygon will be enriched with the data from 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 OBJECTS 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`: `VARCHAR` query to be enriched. A qualified table name can be given as well.
* `input_geography_column`: `VARCHAR` name of the GEOGRAPHY column in the query containing the points to be enriched.
* `data_query`: `VARCHAR` 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. All rows in the table must contain the same kind of geometry (points/lines/polygons) in the geography column.
* `data_geography_column`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `variables`: `ARRAY` of `VARCHAR` elements with names of the columns in the enrichment query that will be added to the enriched results.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. 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 OBJECTs 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.

If a new output table is created, it will be ordered by the x and y coordinates optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently.

**Examples**

```sql
CALL CARTO.CARTO.ENRICH_POINTS_RAW(
   'SELECT ID, GEOM FROM MYTABLE', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT('VAR1', 'VAR2'),
   TO_ARRAY('MYENRICHEDTABLE')
);
-- The table `MYENRICHEDTABLE` will be created
-- with columns: ID, GEOM, __CARTO_ENRICHMENT. The latter will contain OBJECTS with the fields VAR1, VAR2, __carto_total and __carto_dimension.
```

```sql
CALL CARTO.CARTO.ENRICH_POINTS_RAW(
   '<my-database>.<my-schema>.<my-table>', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT('VAR1', 'VAR2'),
   TO_ARRAY('<my-database>.<my-schema>.<my-table>')
);
-- The table `<my-database>.<my-schema>.<my-table>` will be augmented
-- with column __CARTO_ENRICHMENT which will contain OBJECTS with the fields VAR1, VAR2, __carto_total and __carto_dimension.
```

## ENRICH\_POLYGONS <a href="#enrich_polygons" id="enrich_polygons"></a>

```sql
ENRICH_POLYGONS(input_query, input_geography_column, data_query, data_geography_column, aggregations, 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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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](https://en.wikipedia.org/wiki/Weighted_arithmetic_mean) 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`](#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`: `VARCHAR` query to be enriched. A qualified table name can be given as well.
* `input_geography_column`: `VARCHAR` name of the GEOGRAPHY column in the query containing the polygons to be enriched.
* `data_query`: `VARCHAR` 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. All rows in the table must contain the same kind of geometry (points/lines/polygons) in the geography column.
* `data_geography_column`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `aggregations`: `ARRAY` with the columns that will be used to enrich the input polygons and their corresponding aggregation method (`SUM`, `AVG`, `MAX`, `MIN`, `COUNT`). Each element in this array should be an `OBJECT` with fields `column` and `aggregation`.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. When the output table is the same than then input, the input table will be enriched in place.

Note that GeometryCollection/FeatureCollection geographies are not supported at the moment.

**Output**

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

If a new output table is created, it will be ordered by the minimum x and y coordinates of each polygon to optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently.

**Examples**

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS(
   'SELECT ID, GEOM FROM MYTABLE', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT(
     OBJECT_CONSTRUCT('column', 'VAR1', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'max')
   ),
   TO_ARRAY('MYENRICHEDTABLE')
);
-- The table `MYENRICHEDTABLE` will be created
-- with columns: ID, GEOM, VAR1_SUM, VAR2_SUM, VAR2_MAX
```

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS(
   '<my-database>.<my-schema>.<my-table>', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT(
     OBJECT_CONSTRUCT('column', 'VAR1', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'sum'),
     OBJECT_CONSTRUCT('column', 'VAR2', 'aggregation', 'max')
   ),
   TO_ARRAY('<my-database>.<my-schema>.<my-table>')
);
-- The table `<my-database>.<my-schema>.<my-table>` will be augmented
-- with columns: VAR1_SUM, VAR2_SUM, VAR2_MAX
```

## ENRICH\_POLYGONS\_RAW <a href="#enrich_polygons_raw" id="enrich_polygons_raw"></a>

```sql
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 OBJECTS 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`: `VARCHAR` query to be enriched. A qualified table name can be given as well.
* `input_geography_column`: `VARCHAR` name of the GEOGRAPHY column in the query containing the polygons to be enriched.
* `data_query`: `VARCHAR` 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. All rows in the table must contain the same kind of geometry (points/lines/polygons) in the geography column.
* `data_geography_column`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `variables`: `ARRAY` of `VARCHAR` elements with names of the columns in the enrichment query that will be added to the enriched results.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. When the output table is the same than then input, the input table will be enriched in place.

Note that GeometryCollection/FeatureCollection geographies are not supported at the moment.

**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 OBJECTs 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 field named `__carto_input_area` will be included in `__CARTO_ENRICHMENT`, containing the area of the input polygon in square meters.

If a new output table is created, it will be ordered by the minimum x and y coordinates of each polygon to optimize the performance of spatial filters and joins. This is important to visualize the results in a map efficiently.

**Examples**

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS_RAW(
   'SELECT ID, GEOM FROM MYTABLE', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT('VAR1', 'VAR2'),
   TO_ARRAY('MYENRICHEDTABLE')
);
-- The table `MYENRICHEDTABLE` will be created
-- with columns: ID, GEOM, __CARTO_ENRICHMENT. The latter will contain OBJECTS with the fields VAR1, VAR2, __carto_intersection, __carto_total, dimension and __carto_input_area.
```

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS_RAW(
   '<my-database>.<my-schema>.<my-table>', 'GEOM',
   'SELECT GEOM, VAR1, VAR2 FROM MYDATATABLE', 'GEOM',
   ARRAY_CONSTRUCT('VAR1', 'VAR2'),
   TO_ARRAY('<my-database>.<my-schema>.<my-table>')
);
-- The table `<my-database>.<my-schema>.<my-table>` will be augmented
-- with columns __CARTO_ENRICHMENT which will contain OBJECTS with the fields VAR1, VAR2, __carto_intersection, __carto_total, dimension and __carto_input_area.
```

## ENRICH\_POLYGONS\_WEIGHTED <a href="#enrich_polygons_weighted" id="enrich_polygons_weighted"></a>

```sql
ENRICH_POLYGONS_WEIGHTED(input_query, input_geography_column, data_query, data_geography_column, weight_query, weight_geography_column, weight_variable, variables, output)
```

**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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (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*](https://en.wikipedia.org/wiki/Intensive_and_extensive_properties) (e.g. temperature, density, median age). A [weighted average](https://en.wikipedia.org/wiki/Weighted_arithmetic_mean) is computed, using the weight variable as weights.

**Input parameters**

* `input_query`: `VARCHAR` query to be enriched. A qualified table name can be given as well, e.g. `'<my-database>.<my-schema>.<my-table>'`.
* `input_geography_column`: `VARCHAR` name of the GEOGRAPHY column in the query containing the polygons to be enriched.
* `data_query`: `VARCHAR` 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`: `VARCHAR` name of the GEOGRAPHY column provided in the `data_query`.
* `weight_query`: `VARCHAR` query that contains the weight variable and a geography column. This parameter is mandatory and cannot be `NULL`.
* `weight_geography_column`: `VARCHAR` name of the GEOGRAPHY column in the `weight_query`. This parameter is mandatory and cannot be `NULL`.
* `weight_variable`: `VARCHAR` 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`: `ARRAY` with the columns that will be used to enrich the input polygons and their corresponding aggregation method. Each element should be an `OBJECT` with fields `column` and `aggregation`. Only `SUM` and `AVG` aggregations are supported. At least one variable must be specified.
* `output`: `ARRAY` of `VARCHAR` containing the name of an output table to store the results and optionally an SQL clause that can be used to partition it, e.g. `'CLUSTER BY number'`. 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.

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**

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS_WEIGHTED(
  $
  SELECT id, geom FROM <my-database>.<my-schema>.<my-input-table>
  $,
  'geom',
  $
  SELECT id, population, income_avg, geom FROM <my-database>.<my-schema>.<my-data-table>
  $,
  'geom',
  $
  SELECT id, weight_value, geom FROM <my-database>.<my-schema>.<my-weight-table>
  $,
  'geom',
  'weight_value',
  ARRAY_CONSTRUCT(
    OBJECT_CONSTRUCT('column', 'population', 'aggregation', 'sum'),
    OBJECT_CONSTRUCT('column', 'income_avg', 'aggregation', 'avg')
  ),
  TO_ARRAY('<my-database>.<my-schema>.<my-output-table>')
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with columns: id, geom, population_sum, income_avg_avg
```

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS_WEIGHTED(
  $
  SELECT id, geom FROM <my-database>.<my-schema>.<my-input-table>
  $,
  'geom',
  $
  SELECT improvval, geom FROM <my-database>.<my-schema>.<my-data-table>
  $,
  'geom',
  $
  SELECT POPCY, geom FROM <my-database>.<my-schema>.<my-weight-table>
  $,
  'geom',
  'POPCY',
  ARRAY_CONSTRUCT(
    OBJECT_CONSTRUCT('column', 'improvval', 'aggregation', 'sum')
  ),
  TO_ARRAY('<my-database>.<my-schema>.<my-input-table>')
);
-- The table <my-database>.<my-schema>.<my-input-table> will be enriched in place
-- with a new column: improvval_sum
```

```sql
CALL CARTO.CARTO.ENRICH_POLYGONS_WEIGHTED(
  $
  SELECT id, geom FROM <my-database>.<my-schema>.<my-input-table>
  $,
  'geom',
  $
  SELECT total_value, avg_temperature, geom FROM <my-database>.<my-schema>.<my-data-table>
  $,
  'geom',
  $
  SELECT weight, geom FROM <my-database>.<my-schema>.<my-weight-table>
  $,
  'geom',
  'weight',
  ARRAY_CONSTRUCT(
    OBJECT_CONSTRUCT('column', 'total_value', 'aggregation', 'sum'),
    OBJECT_CONSTRUCT('column', 'avg_temperature', 'aggregation', 'avg')
  ),
  TO_ARRAY('<my-database>.<my-schema>.<my-output-table>')
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with columns: id, geom, total_value_sum, avg_temperature_avg
```