# raster

This module contains procedures to access and operate with raster data that has been loaded into Snowflake with using [Raster Loader](https://raster-loader.readthedocs.io/en/latest/).

Learn more about loading raster data as raster tables in Snowflake following this [guide](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-snowflake/guides/working-with-raster-data).

## RASTER\_VALUES <a href="#raster_values" id="raster_values"></a>

```sql
RASTER_VALUES(raster_table, vector_query, output_expression, output_table, options)
```

**Description**

Returns each pixel and associated values from a `output_expression` across all bands in a given area of interest of a raster table. The result will include the data from the `vector_query` as well as from all bands, corresponding to the pixels that intersect each geography.

**Input parameters**

* `raster_table`: `VARCHAR` the qualified table name of the raster table, e.g. `'<my-database>.<my-schema>.<my-raster-table>'`.
* `vector_query`: `VARCHAR` query containing the area of interest in which to perform the extraction, stored in a column named `geom`. Additional columns can be included into this query in order to be referenced from the `output_expression`. It can be `NULL`, in which case all values stored in the raster table will be extracted.
* `output_expression`: `VARCHAR` contains the bands and values to be extracted from the raster. This expression support alias. It does not support aggregations. If you need to use aggregations, use the [`RASTER_AGG_VALUES`](#raster_agg_values) function. This expression can be `NULL` if a `pixel` column is added using the `include_pixel` option.
* `output_table`: `VARCHAR` where the resulting table will be stored. It must be a `VARCHAR` of the form `'<database>.<schema>.<table>'`. The schema must exist and the caller needs to have permissions to create a new table on it. The process will fail if the target table already exists.
* `options`: `VARCHAR` a JSON string with additional options:

  | Option          | Description                                                                                                           |
  | --------------- | --------------------------------------------------------------------------------------------------------------------- |
  | `include_pixel` | `BOOLEAN` whether to include the `pixel` column of the extracted pixel values, in quadbin format. Default is `false`. |

{% hint style="info" %}
When intersecting large polygons with high-resolution pixels, it may encounter Snowflake's computational and memory limits due to the extensive data generated. To mitigate this, we recommend simplifying polygon geometries, reducing pixel resolution, or partitioning data into manageable subsets. These strategies help manage data efficiently and prevent exceeding Snowflake's quotas.
{% endhint %}

**Result**

The result is a table with the corresponding values extracted from the `output_expression` and, if selected, a `pixel` column with quadbin indexes.

**Examples**

Extract `band_1` values for raster pixels intersected by each point in the vector query. The output will be stored in a table.

```sql
CALL CARTO.CARTO.RASTER_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '
  SELECT point AS geom
  FROM <my-database>.<my-schema>.<my-vector-table>
  ',
  'band_1',
  '<my-database>.<my-schema>.<my-output-table>',
  NULL
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with column: band_1 (one value per pixel boundary intersecting each point)
```

Extract `band_1` values for the raster pixels intersected by each polygon in the vector query. The output will be stored in a table.

```sql
CALL CARTO.CARTO.RASTER_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '
  SELECT polygon AS geom
  FROM <my-database>.<my-schema>.<my-vector-table>
  ',
  'band_1',
  '<my-database>.<my-schema>.<my-output-table>',
  NULL
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with column: band_1 (one value per pixel boundary intersecting each polygon)
```

Extract `band_1`, `band_2` values wih alias names for the raster pixels intersected by all the geographies in the vector table. It will include the input columns except `geom`. The output will be stored in a table.

```sql
CALL CARTO.CARTO.RASTER_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '<my-database>.<my-schema>.<my-vector-table>', -- columns: id, name, geom (point)
  '
  band_1 AS alias_1,
  band_2 AS alias_2
  ',
  '<my-database>.<my-schema>.<my-output-table>',
  NULL
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with columns: id, name, alias_1, alias_2 (one value per pixel boundary intersecting each point)
```

Extract `band_1` values for raster pixels intersected by each point in the vector table. It will include the input columns except `geom`. The output will be stored in a table. The option `"include_pixel"` can be set to `true` to include the pixel value in quadbin format.

```sql
CALL CARTO.CARTO.RASTER_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '<my-database>.<my-schema>.<my-vector-table>', -- columns: id, name, geom (point)
  'band_1',
  '<my-database>.<my-schema>.<my-output-table>',
  '{"include_pixel": true}'
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with columns: id, name, pixel, band_1 (one value per pixel boundary intersecting each point)
```

## RASTER\_AGG\_VALUES <a href="#raster_agg_values" id="raster_agg_values"></a>

```sql
RASTER_AGG_VALUES(raster_table, input_query, output_expression, output_table, options)
```

**Description**

Returns aggregated values for all pixels intersecting the specified geometries, according to an `output_expression`.

**Input parameters**

* `raster_table`: `VARCHAR` the qualified table name of the raster table, e.g. `'<my-database>.<my-schema>.<my-raster-table>'`.
* `input_query`: `VARCHAR` query containing the area of interest in which to perform the aggregation, stored in a column named `geom`. Additional columns can be included into this query in order to be referenced from the `output_expression`. It can be `NULL`, in which case all values stored in the raster table will be used.
* `output_expression`: `VARCHAR` contains the aggregated values to be computed from the raster. For extracting non-aggregated values, use the [`RASTER_VALUES`](#raster_values) function. This expression cannot be `NULL`.
* `output_table`: `VARCHAR` where the resulting table will be stored. It must be a `VARCHAR` of the form `'<database>.<schema>.<table>'`. The schema must exist and the caller needs to have permissions to create a new table on it. The process will fail if the target table already exists.
* `options`: `VARCHAR` a JSON string with additional options.

  | Option                   | Description                                                                                                                                             |
  | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | `groupby_vector_columns` | `ARRAY` group by extra columns included in the `vector_query`, in addition to the `geom` column. It does not take effect when `vector_query` is `NULL`. |
  | `groupby_raster_columns` | `ARRAY` group by extra columns included in the `raster_table`, in addition to the `geom` column.                                                        |

{% hint style="info" %}
When intersecting large polygons with high-resolution pixels, it may encounter Snowflake's computational and memory limits due to the extensive data generated. To mitigate this, we recommend simplifying polygon geometries, reducing pixel resolution, or partitioning data into manageable subsets. These strategies help manage data efficiently and prevent exceeding Snowflake's quotas.
{% endhint %}

```hint:warning
**warning**
The aggregation of duplicated geographies may produce unexpected results. If the data provided in the `vector_query` contains duplicated geographies, either clean duplicates previously or use the option `groupby_vector_columns` with an existing unique-id column.
```

**Result**

The result is a table with the corresponding aggregated values from the `output_expression`.

**Examples**

Aggregate `band_1` values for the raster pixels intersected by each polygon in the vector query. The output will be stored in a table.

```sql
CALL CARTO.CARTO.RASTER_AGG_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '
  SELECT polygon AS geom
  FROM <my-database>.<my-schema>.<my-vector-table>
  ',
  'AVG(band_1) AS band_1_avg',
  '<my-database>.<my-schema>.<my-output-table>',
  NULL
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with column: band_1_avg (one value per pixel boundary intersecting each polygon)
```

Aggregate `band_1`, `band_2` values wih different aggregations for the raster pixels intersected by all the geographies in the vector table. It will include the input columns. The output will be stored in a table.

```sql
CALL CARTO.CARTO.RASTER_AGG_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '<my-database>.<my-schema>.<my-vector-table>', -- columns: id, name, geom (polygon)
  '
  AVG(band_1) AS band_1_avg,
  SUM(band_2) AS band_2_avg,
  SUM(band_1 + band_2) AS total_sum,
  COUNT(band_1) AS count,
  ',
  '<my-database>.<my-schema>.<my-output-table>',
  NULL
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with columns: id, name, band_1_avg, band_2_avg, total_sum, count (one value per each pixel center intersecting each polygon)
```

Aggregate `band_1` values for the raster pixels intersected by each polygon in the vector table. It will include the input columns. The option `groupby_vector_columns` will be set to `id` (unique-id) to aggregate with duplicated geographies. The output will be stored in a table.

```sql
CALL CARTO.CARTO.RASTER_AGG_VALUES(
  '<my-database>.<my-schema>.<my-raster-table>',
  '<my-database>.<my-schema>.<my-vector-table>', -- columns: id, name, geom (polygon)
  'AVG(band_1) AS band_1_avg',
  '<my-database>.<my-schema>.<my-output-table>',
  '{"groupby_vector_columns": ["id"]}'
);
-- The table <my-database>.<my-schema>.<my-output-table> will be created
-- with columns: id, name, band_1_avg (one value per pixel boundary intersecting each polygon, and grouped by id)
```