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: STRING the qualified table name of the raster table, e.g. 'project.dataset.raster_table'.
vector_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 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 'project.dataset.output_table'. 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:
Option
Description
include_pixel
BOOLEAN whether to include the pixel column of the extracted pixel values, in quadbin format. Default is false.
intersect_center
BOOLEAN whether to extract the pixel values by intersecting the pixel center or instead the pixel boundary. This operation is recommended only for big polygons, relative to the pixel size. It will run faster but provide less accurate results. It does not take effect when vector_query is NULL. Default is false.
When intersecting large polygons with high-resolution pixels, it may encounter BigQuery'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 BigQuery's quotas.
Limitations
In some cases, Big compressed rasters might fail to be decompressed. A "Resources exceeded during query execution: UDF out of memory." will be thrown. In this case, try to upload the raster to BigQuery as uncompressed.
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.
CALL `carto-un`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'''
SELECT point AS geom
FROM `project.dataset.vector_table`
''',
'band_1',
NULL,
NULL
);
-- The (last) result will return the columns:
-- band_1 (one value per pixel boundary intersecting each point)
CALL `carto-un-eu`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'''
SELECT point AS geom
FROM `project.dataset.vector_table`
''',
'band_1',
NULL,
NULL
);
-- The (last) result will return the columns:
-- band_1 (one value per pixel boundary intersecting each point)
CALL carto.RASTER_VALUES(
'project.dataset.raster_table',
'''
SELECT point AS geom
FROM `project.dataset.vector_table`
''',
'band_1',
NULL,
NULL
);
-- The (last) result will return the columns:
-- 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.
CALL `carto-un`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'''
SELECT polygon AS geom
FROM `project.dataset.vector_table`
''',
'band_1',
NULL,
NULL
);
-- The (last) result will return the columns:
-- band_1 (one value per pixel boundary intersecting each polygon)
CALL `carto-un-eu`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'''
SELECT polygon AS geom
FROM `project.dataset.vector_table`
''',
'band_1',
NULL,
NULL
);
-- The (last) result will return the columns:
-- band_1 (one value per pixel boundary intersecting each polygon)
CALL carto.RASTER_VALUES(
'project.dataset.raster_table',
'''
SELECT polygon AS geom
FROM `project.dataset.vector_table`
''',
'band_1',
NULL,
NULL
);
-- The (last) result will return the columns:
-- 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-un`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (point)
'''
band_1 AS alias_1,
band_2 AS alias_2
''',
'project.dataset.output_table',
NULL
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, alias_1, alias_2 (one value per pixel boundary intersecting each point)
CALL `carto-un-eu`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (point)
'''
band_1 AS alias_1,
band_2 AS alias_2
''',
'project.dataset.output_table',
NULL
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, alias_1, alias_2 (one value per pixel boundary intersecting each point)
CALL carto.RASTER_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (point)
'''
band_1 AS alias_1,
band_2 AS alias_2
''',
'project.dataset.output_table',
NULL
);
-- The table project.dataset.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-un`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (point)
'band_1',
'project.dataset.output_table',
'{"include_pixel": true}'
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, pixel, band_1 (one value per pixel boundary intersecting each point)
CALL `carto-un-eu`.carto.RASTER_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (point)
'band_1',
'project.dataset.output_table',
'{"include_pixel": true}'
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, pixel, band_1 (one value per pixel boundary intersecting each point)
CALL carto.RASTER_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (point)
'band_1',
'project.dataset.output_table',
'{"include_pixel": true}'
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, pixel, band_1 (one value per pixel boundary intersecting each point)
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. 'project.dataset.raster_table'.
vector_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_table: STRING where the resulting table will be stored. It must be a STRING of the form 'project.dataset.output_table'. 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.
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.
intersect_center
BOOLEAN whether to extract the pixel values by intersecting the pixel center or instead the pixel boundary. This operation is recommended only for big polygons, relative to the pixel size. It will run faster but provide less accurate results. It does not take effect when vector_query is NULL. Default is false.
When intersecting large polygons with high-resolution pixels, it may encounter BigQuery'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 BigQuery'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.
Limitations
In some cases, Big compressed rasters might fail to be decompressed. A "Resources exceeded during query execution: UDF out of memory." will be thrown. In this case, try to upload the raster to BigQuery as uncompressed.
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.
CALL `carto-un`.carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'''
SELECT polygon AS geom
FROM `project.dataset.vector_table`
''',
'AVG(band_1) AS band_1_avg',
NULL,
NULL
);
-- The (last) result will return the columns:
-- band_1_avg (one value per pixel boundary intersecting each polygon)
CALL `carto-un-eu`.carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'''
SELECT polygon AS geom
FROM `project.dataset.vector_table`
''',
'AVG(band_1) AS band_1_avg',
NULL,
NULL
);
-- The (last) result will return the columns:
-- band_1_avg (one value per pixel boundary intersecting each polygon)
CALL carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'''
SELECT polygon AS geom
FROM `project.dataset.vector_table`
''',
'AVG(band_1) AS band_1_avg',
NULL,
NULL
);
-- The (last) result will return the columns:
-- 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-un`.carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'project.dataset.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,
''',
'project.dataset.output_table',
NULL
);
-- The table project.dataset.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)
CALL `carto-un-eu`.carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'project.dataset.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,
''',
'project.dataset.output_table',
NULL
);
-- The table project.dataset.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)
CALL carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'project.dataset.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,
''',
'project.dataset.output_table',
NULL
);
-- The table project.dataset.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-un`.carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (polygon)
'AVG(band_1) AS band_1_avg',
'project.dataset.output_table',
'{"groupby_vector_columns": ["id"]}'
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, band_1_avg (one value per pixel boundary intersecting each polygon, and grouped by id)
CALL `carto-un-eu`.carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (polygon)
'AVG(band_1) AS band_1_avg',
'project.dataset.output_table',
'{"groupby_vector_columns": ["id"]}'
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, band_1_avg (one value per pixel boundary intersecting each polygon, and grouped by id)
CALL carto.RASTER_AGG_VALUES(
'project.dataset.raster_table',
'project.dataset.vector_table', -- columns: id, name, geom (polygon)
'AVG(band_1) AS band_1_avg',
'project.dataset.output_table',
'{"groupby_vector_columns": ["id"]}'
);
-- The table project.dataset.output_table will be created
-- with columns: id, name, band_1_avg (one value per pixel boundary intersecting each polygon, and grouped by id)
output_expression: STRING contains the aggregated values to be computed from the raster. For extracting non-aggregated values, use the function. This expression cannot be NULL.
This project has received funding from the research and innovation programme under grant agreement No 960401.
