Ask or search…
K
Links

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_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: STRING the qualified table name of the raster table, e.g. 'your_project.your_dataset.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: STRING 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, COUNTIF 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: 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.
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
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'band_1',
NULL
);
-- The (last) result will return the columns:
-- pixel, band_1 (one pixel intersecting the point)
CALL `carto-un-eu`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'band_1',
NULL
);
-- The (last) result will return the columns:
-- pixel, band_1 (one pixel intersecting the point)
CALL carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'band_1',
NULL
);
-- The (last) result will return the columns:
-- pixel, band_1 (one pixel intersecting the point)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'band_1',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (one pixel intersecting the point)
CALL `carto-un-eu`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'band_1',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (one pixel intersecting the point)
CALL carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'band_1',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (one pixel intersecting the point)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'''
band_1 AS alias_1,
band_3 AS alias_2
''',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, alias_1, alias_2 (one pixel intersecting the point)
CALL `carto-un-eu`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'''
band_1 AS alias_1,
band_3 AS alias_2
''',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, alias_1, alias_2 (one pixel intersecting the point)
CALL carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
ST_GEOGPOINT(-6.61403, 37.47571),
'''
band_1 AS alias_1,
band_3 AS alias_2
''',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, alias_1, alias_2 (one pixel intersecting the point)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
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-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (all the pixels intersecting the polygon)
CALL `carto-un-eu`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
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-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (all the pixels intersecting the polygon)
CALL carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
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-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (all the pixels intersecting the polygon)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
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-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.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-un-eu`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
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-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.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.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
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-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.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)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
NULL,
'band_1',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (all the pixels of the raster table)
CALL `carto-un-eu`.carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
NULL,
'band_1',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (all the pixels of the raster table)
CALL carto.RASTER_VALUE(
'my-project.my-dataset.my-raster-table',
NULL,
'band_1',
'my-project.my-dataset.my-output-table'
);
-- The table `my-project.my-dataset.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: 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.
  • output_expression: STRING 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, COUNTIF 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: 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.
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
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE_TABLE(
'my-project.my-dataset.my-raster-table',
'''
SELECT point AS geom
FROM `my-project.my-dataset.my-geom-table`
''',
'band_1',
NULL
);
-- The (last) result will return the columns:
-- pixel, band_1 (one pixel intersecting each point)
CALL `carto-un-eu`.carto.RASTER_VALUE_TABLE(
'my-project.my-dataset.my-raster-table',
'''
SELECT point AS geom
FROM `my-project.my-dataset.my-geom-table`
''',
'band_1',
NULL
);
-- The (last) result will return the columns:
-- pixel, band_1 (one pixel intersecting each point)
CALL carto.RASTER_VALUE_TABLE(
'my-project.my-dataset.my-raster-table',
'''
SELECT point AS geom
FROM `my-project.my-dataset.my-geom-table`
''',
'band_1',
NULL
);
-- The (last) result will return the columns:
-- pixel, band_1 (one pixel intersecting each point)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE_TABLE(
'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'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: id, pixel, alias_1, alias_2 (one pixel intersecting each point)
CALL `carto-un-eu`.carto.RASTER_VALUE_TABLE(
'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'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: id, pixel, alias_1, alias_2 (one pixel intersecting each point)
CALL carto.RASTER_VALUE_TABLE(
'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'
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: id, pixel, alias_1, alias_2 (one pixel intersecting each point)
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_VALUE_TABLE(
'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'
);
-- 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)
CALL `carto-un-eu`.carto.RASTER_VALUE_TABLE(
'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'
);
-- 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)
CALL carto.RASTER_VALUE_TABLE(
'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'
);
-- 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)
EU flag
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.
Last modified 16d ago