raster
ADVANCED BETA
Beta
The raster module is currently available in beta and it might be subject to changes.
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.
We currently support two types of raster tables, generic raster and quadbin raster. Please note that we recommend using the current beta version of this module with quadbin raster, as our support for generic raster is still in a very experimental phase. To learn more about the raster tables and how to upload them, please read the Raster Loader documentation.
Generic raster tables maintain the original pixel data of a raster file, in the original projection. Quadbin raster tables use quadbin cells as pixels, so they require resampling the data into the Web Mercator projection used by the quadbin spatial index. Support for generic rasters will soon be improved, but at the moment we recommend the use of quadbin rasters.
To learn more about Quadbin, please read the section on Spatial Indexes in our documentation and/or download CARTO's recent report on Spatial Indexes 101.
To upload a raster file as a BigQuery quadbin raster table it must be first converted into a suitable raster file (in the WebMercator projection). You need to have installed gdal (we recommend version 3.2.0 or later). Then you can make the transformation with the following command:
gdalwarp {YOURRASTERFILE} \
-of COG \
-co TILING_SCHEME=GoogleMapsCompatible -co COMPRESS=DEFLATE \
{THERESULTINGPREPAREDFILE}
After doing this you can upload the prepared file normally using
raster-loader
with the --output_quadbin
option to create a quadbin raster table:carto bigquery upload \
--file_path {THERESULTINGPREPAREDFILE}\
--project {YOURBQPROJECT} \
--dataset {YOURBQDATASET} \
--table {YOURRASTERTABLENAME}
--output_quadbin
warning
The current version of the raster module in the Analytics Toolbox only works with
raster-loader>=0.3.0
. Raster tables generated with older versions are not compatible with the current version.RASTER_GETMETADATA(raster_table)
Description
Returns the metadata of a raster table.
raster_table
:STRING
The qualified table name of the raster table, e.g.'your_project.your_dataset.your_tablename'
.
Return type
A row with the following columns:
bands ARRAY<STRING>
names of the binary columns containing the raster pixels for each bandraster_boundary GEOGRAPHY
boundary of the whole raster tableraster_area FLOAT
area of the whole raster table boundarynb_pixel INT64
total number of pixels in the raster tableavg_pixel_area FLOAT
average area of pixelswidth_in_pixel FLOAT
total width in pixels of the raster tableheight_in_pixel FLOAT
total height in pixels of the raster tablenb_pixel_blocks INT64
number of pixel blocks in the tablemin_pixel_block_height_in_pixel FLOAT
minimum height in pixels of a pixel blockmax_pixel_block_height_in_pixel FLOAT
maximum height in pixels of a pixel blockmin_pixel_block_width_in_pixel FLOAT
minimum width in pixels of a pixel blockmax_pixel_block_width_in_pixel FLOAT
maximum width in pixels of a pixel blockirregular_pixel_block_shape BOOLEAN
if false, all pixel blocks have the same dimensions (number of pixel columns and rows)width_in_pixel_block INT64
number of columns of pixel blocks present in the raster tableheight_in_pixel_block INT64
number of rows of pixel blocks present in the raster tablesparse_pixel_block BOOLEAN
if true, the pixel blocks cover completely the whole raster arearesolution INT64
raster resolution (only forquadbin
rasters)
Each row of the pixel table corresponds to a block of pixels, except the one with a NULL value in
geog
(for generic rasters) or quadbin
(for quadbin rasters), which contains this metadata in JSON format.warning
In some cases, depending on the original tiff orientation and projection, the
raster_boundary
may not be a valid geography and both ther raster_boundary
and raster_area
columns will have NULL values.Example
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_GETMETADATA(
"your_project.your_dataset.your_raster_table",
)
-- the (last) result will be a single row with the columns described above
CALL `carto-un-eu`.carto.RASTER_GETMETADATA(
"your_project.your_dataset.your_raster_table",
)
-- the (last) result will be a single row with the columns described above
CALL carto.RASTER_GETMETADATA(
"your_project.your_dataset.your_raster_table",
)
-- the (last) result will be a single row with the columns described above
RASTER_ST_GETVALUE(raster_table, area_of_interest, options, output_table)
Description
Returns each pixel and associated values 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'
.area_of_interest
:GEOGRAPHY
The area of interest in which to perform the extraction. May beNULL
, in which case no spatial filter will be applied (the whole raster table will be extracted).options
:STRING
Parameter left for future development.output_table
:STRING
Where the resulting table will be stored. It must be aSTRING
of the formprojectID.dataset.tablename
. The projectID 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 beNULL
, 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_geog
contaning the geography of the pixels and one numeric column for each band in the raster table named after the band. You can obtain the names of the bands in the raster tableusing the RASTER_GETMETADATA
procedure (in column bands
).If the raster is of type quadbin, the output will contain a column named
quadbin
with the quadbin index of the pixels instead of pixel_geog
.Examples
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_raster_table",
NULL,
NULL,
NULL
)
-- the (last) result will be a table with columns `pixel_geog` and one column per band (e.g. band_1_uint8)
-- and one row per each pixel of the raster
CALL `carto-un-eu`.carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_raster_table",
NULL,
NULL,
NULL
)
-- the (last) result will be a table with columns `pixel_geog` and one column per band (e.g. band_1_uint8)
-- and one row per each pixel of the raster
CALL carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_raster_table",
NULL,
NULL,
NULL
)
-- the (last) result will be a table with columns `pixel_geog` and one column per band (e.g. band_1_uint8)
-- and one row per each pixel of the raster
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_raster_table",
ST_GEOGFROMTEXT(
"""
MULTIPOINT (
0.1543467645281016 -0.2054174264633717,
0.1592152075554587 -0.2088337877042927,
0.1647083716179587 -0.20488560176732282,
0.15595364139334933 -0.20814714674248627,
0.15698360965506808 -0.21295363284185725
)
"""),
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns: geog_column, band_1_uint8 (one column per raster band)
-- and one row for each raster pixel that intersects any of points in the multipoint geography.
CALL `carto-un-eu`.carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_raster_table",
ST_GEOGFROMTEXT(
"""
MULTIPOINT (
0.1543467645281016 -0.2054174264633717,
0.1592152075554587 -0.2088337877042927,
0.1647083716179587 -0.20488560176732282,
0.15595364139334933 -0.20814714674248627,
0.15698360965506808 -0.21295363284185725
)
"""),
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns: geog_column, band_1_uint8 (one column per raster band)
-- and one row for each raster pixel that intersects any of points in the multipoint geography.
CALL carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_raster_table",
ST_GEOGFROMTEXT(
"""
MULTIPOINT (
0.1543467645281016 -0.2054174264633717,
0.1592152075554587 -0.2088337877042927,
0.1647083716179587 -0.20488560176732282,
0.15595364139334933 -0.20814714674248627,
0.15698360965506808 -0.21295363284185725
)
"""),
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns: geog_column, band_1_uint8 (one column per raster band)
-- and one row for each raster pixel that intersects any of points in the multipoint geography.
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_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
))
"""),
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns: geog_column, band_1_uint8 (one column per raster band)
-- and one row for each raster pixel that intersects the polygon.
CALL `carto-un-eu`.carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_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
))
"""),
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns: geog_column, band_1_uint8 (one column per raster band)
-- and one row for each raster pixel that intersects the polygon.
CALL carto.RASTER_ST_GETVALUE(
"your_project.your_dataset.your_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
))
"""),
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns: geog_column, band_1_uint8 (one column per raster band)
-- and one row for each raster pixel that intersects the polygon.
RASTER_ST_GETVALUE_FROM_TABLE(raster_table, geog_table, options, output_table)
Description
Enrich a table containing a geography column by joining with a raster table. The result includes the raster values of all bands, corresponding to the pixels that intersect the centroid of each geography.
raster_table
:STRING
The qualified table name of the raster table, e.g.'your_project.your_dataset.your_tablename'
.geog_table
:STRING
The name of the table with a geography column to match with the raster table. The name as to be qualified e.g.'your_project.your_dataset.your_tablename'
.options
:STRING
|NULL
containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.OptionDescriptiongeography_column
Default:geom
. ASTRING
that specifies the name of the column of thegeog_table
that contains the geographies for which to extract raster values.output_table
:STRING
Where the resulting table will be stored. It must be aSTRING
of the formprojectID.dataset.tablename
. The projectID 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 beNULL
, in which case the table is directly returned.
Result
The result, for generic rasters, is a table with all the columns of the input
geog_table
, a column named pixel_geog
containing the geography of the pixels and one column for each band in the raster table named after the band. You can obtain the names of the bands in the raster table using the RASTER_GETMETADATA
procedure (in column bands
).For quadbin rasters, instead of the
pixel_geog
, the result will include the following columns:block_quadbin
quadbin of the containing pixel block.quadbin
quadbin of the pixel.
Example
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.RASTER_ST_GETVALUE_FROM_TABLE(
"your_project.your_dataset.your_raster_table",
"your_project.your_dataset.your_geog_table",
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns (assuming a generic raster): geog_column, band_1_uint8 (one column per raster band)
-- in addition to the existing columns in your_project.your_dataset.your_geog_table
CALL `carto-un-eu`.carto.RASTER_ST_GETVALUE_FROM_TABLE(
"your_project.your_dataset.your_raster_table",
"your_project.your_dataset.your_geog_table",
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns (assuming a generic raster): geog_column, band_1_uint8 (one column per raster band)
-- in addition to the existing columns in your_project.your_dataset.your_geog_table
CALL carto.RASTER_ST_GETVALUE_FROM_TABLE(
"your_project.your_dataset.your_raster_table",
"your_project.your_dataset.your_geog_table",
NULL,
"your_project.your_dataset.your_output_table"
)
-- The table `your_project.your_dataset.your_output_table` will be created
-- with columns (assuming a generic raster): geog_column, band_1_uint8 (one column per raster band)
-- in addition to the existing columns in your_project.your_dataset.your_geog_table
​
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.

Last modified 3mo ago