HomeAcademyLoginTry for free
Search…
⌃K
Links

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_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 band
  • raster_boundary GEOGRAPHY boundary of the whole raster table
  • raster_area FLOAT area of the whole raster table boundary
  • nb_pixel INT64 total number of pixels in the raster table
  • avg_pixel_area FLOAT average area of pixels
  • width_in_pixel FLOAT total width in pixels of the raster table
  • height_in_pixel FLOAT total height in pixels of the raster table
  • nb_pixel_blocks INT64 number of pixel blocks in the table
  • min_pixel_block_height_in_pixel FLOAT minimum height in pixels of a pixel block
  • max_pixel_block_height_in_pixel FLOAT maximum height in pixels of a pixel block
  • min_pixel_block_width_in_pixel FLOAT minimum width in pixels of a pixel block
  • max_pixel_block_width_in_pixel FLOAT maximum width in pixels of a pixel block
  • irregular_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 table
  • height_in_pixel_block INT64 number of rows of pixel blocks present in the raster table
  • sparse_pixel_block BOOLEAN if true, the pixel blocks cover completely the whole raster area
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_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 be NULL, 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 a STRING of the form projectID.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 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_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_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.
    Option
    Description
    geography_column
    Default: geom. A STRING that specifies the name of the column of the geog_table that contains the geographies for which to extract raster values.
  • output_table: STRING Where the resulting table will be stored. It must be a STRING of the form projectID.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 be NULL, 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
​
EU flag
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.
Previous
random
Next
retail
Last modified 14d ago