Working with Raster data

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 the complete SQL reference for 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)