raster

ADVANCED

This module contains procedures to access and operate with raster data that has been loaded into Snowflake with our Raster Loader.

Learn more about loading raster data as raster tables in Snowflake following this guide.

warning

The current version of the raster module in the Analytics Toolbox only works with raster-loader>=0.5.0.

RASTER_VALUE

RASTER_VALUE(raster_table, input_geog, output_expression, output_table)

Description

Returns each pixel and associated values from a output_expression across all bands in a given area of interest of a raster table.

  • raster_table: VARCHAR the qualified table name of the raster table, e.g. 'your_database.your_schema.your_tablename'.

  • input_geog: GEOGRAPHY the area of interest in which to perform the extraction. May be NULL, in which case no spatial filter will be applied so the whole raster table will be extracted.

  • output_expression: VARCHAR contains the bands and values to be extracted from the raster. This expression support alias as well as aggregations (the supported aggregations are SUM, MIN, MAX, AVG, COUNT, COUNT_IF and ANY_VALUE). When using aggregations the column pixel will not be in the result. If NULL only the pixel column is returned.

  • output_table: VARCHAR where the resulting table will be stored. It must be a VARCHAR of the form 'your_database.your_schema.your_tablename'. 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.

Result

The result is a table with a column named pixel with the quadbin index of the pixels and the corresponding values of the bands extracted from the output_expression.

Examples

CALL CARTO.CARTO.RASTER_VALUE(
    'my_database.my_schema.my_tablename',
    ST_POINT(-6.61403, 37.47571),
    'band_1',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: pixel, band_1 (one pixel intersecting the point)
CALL CARTO.CARTO.RASTER_VALUE(
    'my_database.my_schema.my_tablename',
    ST_POINT(-6.61403, 37.47571),
    '
    band_1 AS alias_1,
    band_3 AS alias_2
    ',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: pixel, alias_1, alias_2 (one pixel intersecting the point)
CALL CARTO.CARTO.RASTER_VALUE(
    'my_database.my_schema.my_tablename',
    ST_GEOGFROMTEXT('
        POLYGON((
            0.14538491988787428 -0.19997006321490718,
            0.1595469834865071 -0.22803650229192843,
            0.14881814742693678 -0.20391825036028427,
            0.1734515550197102 -0.22898063235646787,
            0.15165056014666334 -0.19885427102265768,
            0.16169275069842115 -0.21413203430647013,
            0.14538491988787428 -0.19997006321490718
        ))
    '),
    'band_1',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: pixel, band_1 (all the pixels intersecting the polygon)
CALL CARTO.CARTO.RASTER_VALUE(
    'my_database.my_schema.my_tablename',
    ST_GEOGFROMTEXT('
        POLYGON((
            0.14538491988787428 -0.19997006321490718,
            0.1595469834865071 -0.22803650229192843,
            0.14881814742693678 -0.20391825036028427,
            0.1734515550197102 -0.22898063235646787,
            0.15165056014666334 -0.19885427102265768,
            0.16169275069842115 -0.21413203430647013,
            0.14538491988787428 -0.19997006321490718
        ))
    '),
    '
    AVG(band_1) AS band_1_avg,
    MAX(band_2) AS band_2_max,
    SUM(band_1 + band_2) AS total_sum,
    COUNT(band_1) AS count
    ',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: band_1_avg, band_2_max, total_sum, count
-- (aggregation of all the pixels intersecting the polygon)
CALL CARTO.CARTO.RASTER_VALUE(
    'my_database.my_schema.my_tablename',
    NULL,
    'band_1',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: pixel, band_1 (all the pixels of the raster table)

RASTER_VALUE_TABLE

RASTER_VALUE_TABLE(raster_table, input_query, output_expression, output_table)

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 input_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. 'your_database.your_schema.your_tablename'.

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

  • output_expression: VARCHAR contains the bands and values to be extracted from the raster. This expression support alias as well as aggregations (the supported aggregations are SUM, MIN, MAX, AVG, COUNT, COUNT_IF and ANY_VALUE). When using aggregations the column pixel will not be in the result. If NULL only the pixel column is returned.

  • output_table: VARCHAR where the resulting table will be stored. It must be a VARCHAR of the form 'your_database.your_schema.your_tablename'. 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.

Result

The result is a table with a column named pixel with the quadbin index of the pixels and the corresponding values extracted from the output_expression.

Examples

CALL CARTO.CARTO.RASTER_VALUE_TABLE(
    'my_database.my_schema.my_tablename',
    '
    SELECT point AS geom
    FROM `my_database.my_schema.my_geom_table`
    ',
    'band_1',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: pixel, band_1 (one pixel intersecting each point)
CALL CARTO.CARTO.RASTER_VALUE_TABLE(
    'my_database.my_schema.my_tablename',
    '
    SELECT id, point AS geom
    FROM my_database.my_schema.my_geom_table
    ',
    '
    band_1 AS alias_1,
    band_3 AS alias_2
    ',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: id, pixel, alias_1, alias_2 (one pixel intersecting each point)
CALL CARTO.CARTO.RASTER_VALUE_TABLE(
    'my_database.my_schema.my_tablename',
    'my_database.my_schema.my_geom_table', -- columns: name, geom
    '
    AVG(band_1) AS band_1_avg,
    MAX(band_2) AS band_2_max,
    SUM(band_1 + band_2) AS total_sum,
    COUNT(band_1) AS count
    ',
    'my_database.my_schema.my_output_table'
);
-- The table `my_database.my_schema.my_output_table` will be created
-- with columns: name, band_1_avg, band_2_max, total_sum, count
-- (aggregation of all the pixels intersecting each polygon)

Last updated