> For the complete documentation index, see [llms.txt](https://docs.carto.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-snowflake/sql-reference/raster.md). # raster This module contains procedures to access and operate with raster tables in Snowflake: rasters loaded using [Raster Loader](https://raster-loader.readthedocs.io/en/latest/), imported with CARTO, or written in the [RaQuet](https://github.com/CartoDB/raquet) v0.5.0 format. `RASTER_VALUES` and `RASTER_AGG_VALUES` read all of them and return the values as stored in the table (the `scale` and `offset` of the bands are not applied). Pixels equal to the band's nodata value, and NaN pixels of floating point bands, are ignored. `RASTER_ALGEBRA` requires RaQuet v0.5.0 inputs. 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 ```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. `'..'`. * `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 `'..'`. 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( '..', ' SELECT point AS geom FROM .. ', 'band_1', '..', NULL ); -- The 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( '..', ' SELECT polygon AS geom FROM .. ', 'band_1', '..', NULL ); -- The 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( '..', '..', -- columns: id, name, geom (point) ' band_1 AS alias_1, band_2 AS alias_2 ', '..', NULL ); -- The 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( '..', '..', -- columns: id, name, geom (point) 'band_1', '..', '{"include_pixel": true}' ); -- The table .. will be created -- with columns: id, name, pixel, band_1 (one value per pixel boundary intersecting each point) ``` ## RASTER\_AGG\_VALUES ```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. `'..'`. * `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 `'..
'`. 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( '..', ' SELECT polygon AS geom FROM .. ', 'AVG(band_1) AS band_1_avg', '..', NULL ); -- The 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( '..', '..', -- 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, ', '..', NULL ); -- The 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( '..', '..', -- columns: id, name, geom (polygon) 'AVG(band_1) AS band_1_avg', '..', '{"groupby_vector_columns": ["id"]}' ); -- The table .. will be created -- with columns: id, name, band_1_avg (one value per pixel boundary intersecting each polygon, and grouped by id) ``` ## RASTER\_ALGEBRA ```sql RASTER_ALGEBRA(input_rasters, expression, output_table, options) ``` **Description** Computes a new raster from one or more input rasters by evaluating a user-defined expression pixel by pixel. The result is written as a new [RaQuet](https://github.com/CartoDB/raquet) v0.5.0 raster table. Inputs are referenced in the expression as `$a`, `$b`, `$c`, … in the order in which they are passed. Their bands are referenced as `$a.band_1` (by band name) or `$a[1]` (by 1-based band index). A single-band input can be referenced simply as `$a`. **Input parameters** * `input_rasters`: `ARRAY` the qualified names of the input RaQuet raster tables, e.g. `ARRAY_CONSTRUCT('..', '..')`. The first one is `$a`, the second one `$b`, and so on. All inputs must be referenced in the expression. * `expression`: `VARCHAR` the expression to compute. Several output bands can be computed at once by separating expressions with `;` and optionally naming them: `'ndvi = ($a.band_4 - $a.band_3) / ($a.band_4 + $a.band_3); vegetation = $a.band_4 > 1000'`. Unnamed outputs are called `band_1`, `band_2`, … The expression supports: | Syntax | Description | | ----------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | `+`, `-`, `*`, `/`, `%`, `^` (or `**`) | Arithmetic. `^` is right-associative and binds tighter than unary minus: `-$a ^ 2` is `-($a ^ 2)`. | | `<`, `<=`, `>`, `>=`, `==`, `!=` | Comparisons, returning `1` or `0`. | | `and`, `or`, `not` (or `&&`, `\|\|`, `!`) | Logical operators, returning `1` or `0`. | | `if(condition, a, b)` | `a` where `condition` is not `0`, `b` otherwise. | | `abs`, `sqrt`, `exp`, `log`, `log10`, `log2`, `pow`, `min`, `max`, `floor`, `ceil`, `round`, `clamp(x, lo, hi)`, `sin`, `cos`, `tan`, `atan`, `atan2` | Math functions. | | `pi`, `e` | Constants. | `log` is the natural logarithm. `round` rounds halves up (`round(-2.5)` is `-2`), also when results are rounded to an integer `output_type`. * `output_table`: `VARCHAR` the table where the resulting raster will be stored, of the form `'..'`. The schema must exist and the caller needs permissions to create a new table on it. The process will fail if the target table already exists or if it is one of the inputs. * `options`: `VARCHAR` a JSON string with additional options, or `NULL`: | Option | Description | | -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `output_type` | `STRING` data type of the output bands: `uint8`, `int8`, `uint16`, `int16`, `uint32`, `int32`, `float32` or `float64`. Default is `float32`. Integer results are rounded; values that do not fit the type become nodata. | | `output_nodata` | `NUMBER` or `STRING` nodata value of the output bands. Default is `"NaN"` for floating point types and the minimum (signed) or maximum (unsigned) value for integer types, e.g. `255` for `uint8`: set it explicitly when that value is a valid result, e.g. for 0/255 masks. | | `overviews` | `STRING` `evaluate` (default) computes the expression on every zoom level present in all the inputs, so the result can be visualized at any zoom right away. `none` computes the native resolution only. Note that for non-linear expressions, overview pixels are computed from the inputs' overview pixels. | | `apply_scale_offset` | `BOOL` whether band values are converted to physical values (`value * scale + offset`, from the band metadata) before evaluating the expression. Default is `true`; bands without scale and offset are used as they are. Set it to `false` to work on the stored values. | | `compression` | `STRING` compression of the output raster tiles: `gzip` (default) or `none`. The compression of the input rasters is read from their metadata. | | `compression_level` | `INTEGER` gzip level of the output raster tiles, between 1 and 9. Default is `1`, which is noticeably cheaper to compute than higher levels while producing tiles of almost the same size. It is ignored when `compression` is `none`. | **Nodata** An output pixel is nodata when any of the input pixels referenced by its expression is nodata, or when the result is not a finite number (e.g. a division by zero or the logarithm of a negative number). Blocks where every output pixel is nodata are not written. **Grid alignment** All inputs must use the same block size and the same native resolution (`max_zoom` in their metadata). Their extents do not need to match: each output band covers the area where all the inputs referenced by its expression have data. Rasters with a different resolution must be re-imported with a common resolution; they are not resampled automatically. **Output** The output table is a RaQuet v0.5.0 raster with one column per output band, per-tile statistics columns (`_COUNT`, `_MIN`, `_MAX`, `_SUM`, `_MEAN`, `_STDDEV`) and a metadata row (`BLOCK = 0`) with the band statistics and the expression used. Column names are upper case, following the Snowflake convention for RaQuet tables; band names in the metadata keep the case used in the expression. Input band columns are matched to the metadata band names case-insensitively. {% hint style="warning" %} **Limitations** * Input rasters must be RaQuet v0.5.0 rasters, such as those written by the [raquet](https://github.com/CartoDB/raquet) tools. Rasters with a time dimension and rasters with lossy (JPEG/WebP) compression are not supported. * Inputs must share block size and native resolution. Automatic resampling or reprojection is not supported. {% endhint %} **Examples** Difference between two rasters, e.g. signal coverage before and after deploying a new site: ```sql CALL CARTO.CARTO.RASTER_ALGEBRA( ARRAY_CONSTRUCT('..coverage_before', '..coverage_after'), '$b - $a', '..coverage_delta', NULL ); ``` NDVI and a vegetation mask from a single multi-band raster: ```sql CALL CARTO.CARTO.RASTER_ALGEBRA( ARRAY_CONSTRUCT('..sentinel2'), 'ndvi = ($a.band_4 - $a.band_3) / ($a.band_4 + $a.band_3); vegetation = $a.band_4 > 1000', '..sentinel2_ndvi', '{"overviews": "none"}' ); ``` Solar potential (in hundredths) where the elevation is above 1,000 m and `0` elsewhere, as 16-bit integers: ```sql CALL CARTO.CARTO.RASTER_ALGEBRA( ARRAY_CONSTRUCT('..elevation', '..solar_pvout'), 'if($a > 1000, round($b * 100), 0)', '..solar_highlands', '{"output_type": "int16", "output_nodata": -1}' ); ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.carto.com/data-and-analysis/analytics-toolbox-for-snowflake/sql-reference/raster.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.