raster

ADVANCED

This module contains procedures to access and operate with raster data that has been loaded into BigQuery with our Raster Loader, built in collaboration with Makepath.

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

warning

The current version of the raster module in the Analytics Toolbox only works with raster-loader>=0.4.0. Raster tables generated with older versions are not compatible with the current version.

RASTER_VALUES

RASTER_VALUES(raster_table, input_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 input_query as well as from all bands, corresponding to the pixels that intersect each geography.

• raster_table: STRING the qualified table name of the raster table, e.g. 'your_project.your_dataset.your_tablename'.

• input_query: STRING 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: STRING 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: STRING where the resulting table will be stored. It must be a STRING of the form 'your_project.your_dataset.your_tablename'. The project can be omitted (in which case the default one will be used). The dataset 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. May be NULL, in which case the table is directly returned as the last result of the procedure.

• options: STRING a JSON string with additional options. So far, the only supported option is include_pixel. If true, a pixel column with the quadbin index corresponding to each extracted value will be added to the output table. By default, no pixel column is added.

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

carto-un

CALL `carto-un`.carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    'band_1',
    NULL,
    NULL
);
-- The (last) result will return the columns:
-- band_1 (one pixel intersecting each point)

carto-un-eu

CALL `carto-un-eu`.carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    'band_1',
    NULL,
    NULL
);
-- The (last) result will return the columns:
-- band_1 (one pixel intersecting each point)

manual

CALL carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    'band_1',
    NULL,
    NULL
);
-- The (last) result will return the columns:
-- band_1 (one pixel intersecting each point)

carto-un

CALL `carto-un`.carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT id, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    '''
    band_1 AS alias_1,
    band_3 AS alias_2
    ''',
    'my-project.my-dataset.my-output-table',
    NULL
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: alias_1, alias_2 (one pixel intersecting each point)

carto-un-eu

CALL `carto-un-eu`.carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT id, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    '''
    band_1 AS alias_1,
    band_3 AS alias_2
    ''',
    'my-project.my-dataset.my-output-table',
    NULL
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: alias_1, alias_2 (one pixel intersecting each point)

manual

CALL carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT id, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    '''
    band_1 AS alias_1,
    band_3 AS alias_2
    ''',
    'my-project.my-dataset.my-output-table',
    NULL
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: alias_1, alias_2 (one pixel intersecting each point)

carto-un

CALL `carto-un`.carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT name, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    'band_1',
    'my-project.my-dataset.my-output-table',
    '{"include_pixel": true}'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1

carto-un-eu

CALL `carto-un-eu`.carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT name, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    'band_1',
    'my-project.my-dataset.my-output-table',
    '{"include_pixel": true}'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1

manual

CALL carto.RASTER_VALUES(
    'my-project.my-dataset.my-raster-table',
    '''
    SELECT name, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    'band_1',
    'my-project.my-dataset.my-output-table',
    '{"include_pixel": true}'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1

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: STRING the qualified table name of the raster table, e.g. 'your_project.your_dataset.your_tablename'.

• input_query: STRING 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: STRING 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: STRING where the resulting table will be stored. It must be a STRING of the form 'your_project.your_dataset.your_tablename'. The project can be omitted (in which case the default one will be used). The dataset 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. May be NULL, in which case the table is directly returned as the last result of the procedure.

• options: STRING a JSON string with additional options. So far, the only supported option is groupby_column, which should contain an array of names of columns to use to group by. If this option is not provided, one single row will be added to the output table for each row in the input table. Otherwise, multiple rows will be added for each original row.

Result

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

Examples

carto-un

CALL `carto-un`.carto.RASTER_AGG_VALUES(
    'my-project.my-dataset.my-raster-table',
    'my-project.my-dataset.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-project.my-dataset.my-output-table',
    NULL
);
-- The table `my-project.my-dataset.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)

carto-un-eu

CALL `carto-un-eu`.carto.RASTER_AGG_VALUES(
    'my-project.my-dataset.my-raster-table',
    'my-project.my-dataset.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-project.my-dataset.my-output-table',
    NULL
);
-- The table `my-project.my-dataset.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)

manual

CALL carto.RASTER_AGG_VALUES(
    'my-project.my-dataset.my-raster-table',
    'my-project.my-dataset.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-project.my-dataset.my-output-table',
    NULL
);
-- The table `my-project.my-dataset.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)

carto-un

CALL `carto-un`.carto.RASTER_AGG_VALUES(
    'my-project.my-dataset.my-raster-table', -- columns: band_1, band_2, category
    '''
    SELECT name, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    '''
    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,
    category
    ''',
    'my-project.my-dataset.my-output-table',
    '{"groupby_columns": ["category"]}'
);
-- The table `my-project.my-dataset.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 and belonging
-- to the same category according to the "category" column)

carto-un-eu

CALL `carto-un-eu`.carto.RASTER_AGG_VALUES(
    'my-project.my-dataset.my-raster-table', -- columns: band_1, band_2, category
    '''
    SELECT name, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    '''
    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,
    category
    ''',
    'my-project.my-dataset.my-output-table',
    '{"groupby_columns": ["category"]}'
);
-- The table `my-project.my-dataset.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 and belonging
-- to the same category according to the "category" column)

manual

CALL carto.RASTER_AGG_VALUES(
    'my-project.my-dataset.my-raster-table', -- columns: band_1, band_2, category
    '''
    SELECT name, point AS geom
    FROM `my-project.my-dataset.my-geom-table`
    ''',
    '''
    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,
    category
    ''',
    'my-project.my-dataset.my-output-table',
    '{"groupby_columns": ["category"]}'
);
-- The table `my-project.my-dataset.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 and belonging
-- to the same category according to the "category" column)

This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.