raster

This module contains procedures to access and operate with raster tables in Snowflake: rasters loaded using Raster Loader, imported with CARTO, or written in the 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.

RASTER_VALUES

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

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.

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.

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.

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.

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.

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

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

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.

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

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.

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.

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)

RASTER_ALGEBRA

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 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('<my-database>.<my-schema>.<before>', '<my-database>.<my-schema>.<after>'). 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 '<my-database>.<my-schema>.<my-output-table>'. 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 (<BAND>_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.

Limitations

• Input rasters must be RaQuet v0.5.0 rasters, such as those written by the 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.

Examples

Difference between two rasters, e.g. signal coverage before and after deploying a new site:

CALL CARTO.CARTO.RASTER_ALGEBRA(
    ARRAY_CONSTRUCT('<my-database>.<my-schema>.coverage_before', '<my-database>.<my-schema>.coverage_after'),
    '$b - $a',
    '<my-database>.<my-schema>.coverage_delta',
    NULL
);

NDVI and a vegetation mask from a single multi-band raster:

CALL CARTO.CARTO.RASTER_ALGEBRA(
    ARRAY_CONSTRUCT('<my-database>.<my-schema>.sentinel2'),
    'ndvi = ($a.band_4 - $a.band_3) / ($a.band_4 + $a.band_3); vegetation = $a.band_4 > 1000',
    '<my-database>.<my-schema>.sentinel2_ndvi',
    '{"overviews": "none"}'
);

Solar potential (in hundredths) where the elevation is above 1,000 m and 0 elsewhere, as 16-bit integers:

CALL CARTO.CARTO.RASTER_ALGEBRA(
    ARRAY_CONSTRUCT('<my-database>.<my-schema>.elevation', '<my-database>.<my-schema>.solar_pvout'),
    'if($a > 1000, round($b * 100), 0)',
    '<my-database>.<my-schema>.solar_highlands',
    '{"output_type": "int16", "output_nodata": -1}'
);