Once your raster data is stored as a BigQuery table, you can leverage the power of CARTO's Analytics Toolbox to perform advanced spatial analyses involving raster and vector data sources.
This section covers some key use cases for analyzing raster data, demonstrating how to extract raster values, perform spatial intersections, and aggregate raster data values using SQL and the procedures provided by the raster module in the CARTO Analytics Toolbox.
Check thefor the raster module.
Get values from the pixels that intersect with a specific point
CALL `carto-un`.carto.RASTER_VALUES(
'my-project.my-dataset.my-raster-table',
'''SELECT ST_GEOGPOINT(-6.61403, 37.47571) AS geom'''
'band_1',
NULL,
NULL
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1 (one pixel intersecting the point)
Get values from the pixels that intersect with points from a table
CALL `carto-un`.carto.RASTER_VALUES(
'my-project.my-dataset.my-raster-table',
'''
SELECT id, point AS geom
FROM `my-project.my-dataset.my-point-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: id, pixel, alias_1, alias_2 (one pixel intersecting each point)
Get a calculated value from different bands for each pixel that intersects with points on table
CALL `carto-un`.carto.RASTER_VALUES(
'my-project.my-dataset.my-raster-table',
'''
SELECT id, point AS geom
FROM `my-project.my-dataset.my-point-table`
''',
'''
band_1 as nir,
band_2 as red,
(band_1-band_2)/(band_1+band_2) as nvdi
''',
'my-project.my-dataset.my-output-table',
NULL
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, nir, red, nvdi
Get all pixels that intersect with a polygon and their band's values
CALL `carto-un`.carto.RASTER_VALUES(
'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, band_2',
'my-project.my-dataset.my-output-table',
NULL
);
-- The table `my-project.my-dataset.my-output-table` will be created
-- with columns: pixel, band_1, band_2 (all the pixels intersecting the polygon)
Intersect with a polygon table and get aggregated data
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)
