Use ST_SubRaster to convert pyramid levels or bands of a raster object - ApsaraDB RDS

Extracts a specified pyramid level or band subset from a raster object and returns the result as a new raster object.

Syntax

raster ST_SubRaster(raster raster_obj,
                    integer pyramidLevel default 0,
                    cstring bands default '',
                    cstring storageOption default '',
                    cstring options default '{}')

Parameters

Parameter	Type	Default	Description
`raster_obj`	raster	—	The source raster object.
`pyramidLevel`	integer	`0`	The pyramid level to extract.
`bands`	cstring	`''`	The bands to extract. Accepts an integer range (`'0-2'`) or a comma-separated list (`'1,2,3'`). Band indexes start at `0`. The default value (`''`) extracts all bands.
`storageOption`	cstring	`''`	A JSON string that controls how the new raster object is stored. See storageOption parameters.
`options`	cstring	`'{}'`	A JSON string that controls how the new raster object is generated. See options parameters.

storageOption parameters

All storageOption sub-parameters are optional. Parameters not specified inherit the value from the source raster object.

Commonly used parameters

Parameter	Type	Default	Description
`chunktable`	string	`''`	The name of the chunk table for the new raster object. If set to `''` or `NULL`, a temporary chunk table with a random name is created and is valid only for the current session. Specify a name to create a permanent chunk table.
`compression`	string	Same as source	The compression format. Valid values: `NONE`, `JPEG`, `ZLIB`, `PNG`, `LZO`, `LZ4`.
`celltype`	string	Same as source	The pixel type. Valid values: `1bb`, `2bui`, `4bui`, `8bsi`, `8bui`, `16bsi`, `16bui`, `32bsi`, `32bui`, `64bsi`, `64bui`, `32bf`, `64bf`.
`chunking`	boolean	Same as source	Specifies whether to store the new raster object in chunks.

Advanced parameters

Parameter	Type	Default	Description
`chunkdim`	string	Same as source	The chunk dimensions. Valid only when `chunking` is `true`.
`quality`	integer	Same as source	The image quality after compression (1–99). Valid only when `compression` is `JPEG`.
`interleaving`	string	Same as source	The interleaving type. Valid values: `bip` (band interleaved by pixel, BIP), `bil` (band interleaved by line, BIL), `bsq` (band sequential, BSQ).
`endian`	string	Same as source	The byte order. Valid values: `NDR` (little-endian), `XDR` (big-endian).

options parameters

Parameter	Type	Default	Description
`parallel`	integer	`ganos.parallel.degree`	The degree of parallelism. Valid values: 1–64.
`stretch`	string	`none`	The pixel value stretch method. Valid values: `none` (replaces out-of-range values with the nearest extremum), `stats` (stretches based on raster statistics — requires precomputed statistics; see note below), `data_type` (stretches based on the pixel type's value range).

When stretch is set to stats, the source raster object must have precomputed statistics. Call ST_SummaryStats on the source raster object before calling ST_SubRaster.

Examples

Extract bands from a raster object

Extract bands 0 through 2 from pyramid level 1 and store the result in a permanent chunk table.

SELECT ST_SubRaster(rast, 1, '0-2', '{"chunktable":"chunk_table", "chunking":true}')
FROM raster_sub
WHERE id = 1;

Convert pixel type and stretch pixel values in parallel

Convert to 8-bit unsigned integer (8BUI) using data_type stretch with a parallelism of 4.

SELECT ST_SubRaster(rast, 1, '0-2', '{"chunktable":"chunk_table", "chunking":true, "celltype": "8BUI"}', '{"stretch": "data_type", "parallel": 4}')
FROM raster_sub
WHERE id = 1;