raster
This module contains procedures to access and operate with raster data that has been loaded into Snowflake with using Raster Loader.
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.
raster_table
:VARCHAR
the qualified table name of the raster table, e.g.'database.schema.raster_table'
.vector_query
:VARCHAR
query containing the area of interest in which to perform the extraction, stored in a column namedgeom
. Additional columns can be included into this query in order to be referenced from theoutput_expression
. It can beNULL
, 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 theRASTER_AGG_VALUES
function. This expression can beNULL
if apixel
column is added using theinclude_pixel
option.output_table
:VARCHAR
where the resulting table will be stored. It must be aVARCHAR
of the form'database.schema.output_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:OptionDescriptioninclude_pixel
BOOLEAN
whether to include thepixel
column of the extracted pixel values, in quadbin format. Default isfalse
.
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(
'database.schema.raster_table',
'
SELECT point AS geom
FROM database.schema.vector_table
',
'band_1',
'database.schema.output_table',
NULL
);
-- The table database.schema.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(
'database.schema.raster_table',
'
SELECT polygon AS geom
FROM database.schema.vector_table
',
'band_1',
'database.schema.output_table',
NULL
);
-- The table database.schema.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(
'database.schema.raster_table',
'database.schema.vector_table', -- columns: id, name, geom (point)
'
band_1 AS alias_1,
band_2 AS alias_2
',
'database.schema.output_table',
NULL
);
-- The table database.schema.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(
'database.schema.raster_table',
'database.schema.vector_table', -- columns: id, name, geom (point)
'band_1',
'database.schema.output_table',
'{"include_pixel": true}'
);
-- The table database.schema.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
.
raster_table
:VARCHAR
the qualified table name of the raster table, e.g.'database.schema.raster_table'
.input_query
:VARCHAR
query containing the area of interest in which to perform the aggregation, stored in a column namedgeom
. Additional columns can be included into this query in order to be referenced from theoutput_expression
. It can beNULL
, 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 theRASTER_VALUES
function. This expression cannot beNULL
.output_table
:VARCHAR
where the resulting table will be stored. It must be aVARCHAR
of the form'database.schema.output_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.OptionDescriptiongroupby_vector_columns
ARRAY
group by extra columns included in thevector_query
, in addition to thegeom
column. It does not take effect whenvector_query
isNULL
.groupby_raster_columns
ARRAY
group by extra columns included in theraster_table
, in addition to thegeom
column.
**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(
'database.schema.raster_table',
'
SELECT polygon AS geom
FROM database.schema.vector_table
',
'AVG(band_1) AS band_1_avg',
'database.schema.output_table',
NULL
);
-- The table database.schema.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(
'database.schema.raster_table',
'database.schema.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,
',
'database.schema.output_table',
NULL
);
-- The table database.schema.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(
'database.schema.raster_table',
'database.schema.vector_table', -- columns: id, name, geom (polygon)
'AVG(band_1) AS band_1_avg',
'database.schema.output_table',
'{"groupby_vector_columns": ["id"]}'
);
-- The table database.schema.output_table will be created
-- with columns: id, name, band_1_avg (one value per pixel boundary intersecting each polygon, and grouped by id)
Last updated
Was this helpful?