ST_SubRaster function to transform the pyramid level or band of a raster - PolarDB

Transforms a pyramid level or specific bands 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 '',  /* All bands */
           cstring storageOption DEFAULT '')

Parameters

Parameter	Type	Default	Description
`raster_obj`	raster	—	The source raster object to extract from.
`pyramidLevel`	integer	0	The pyramid level to transform.
`bands`	cstring	`''`	The bands to extract. Accepts an integer range (e.g., `'0-2'`) or a comma-separated list (e.g., `'1,2,3'`). Band indexes start at 0. An empty string extracts all bands.
`storageOption`	cstring	`''`	A JSON string that controls how the new raster object is stored.

storageOption fields

Field	Type	Default	Description
`chunking`	Boolean	Same as source	Whether to store the new raster object in chunks.
`chunkdim`	String	Same as source	Chunk dimensions. Takes effect only when `chunking` is `true`.
`chunktable`	String	`''` (empty)	Name of the chunk table. By default, a temporary chunk table with a random name is created and is only valid for the current session. Specify a table name to save the raster object permanently.
`compression`	String	Same as source	Compression format. Supported values: `None`, `JPEG`, `Zlib`, `PNG`, `LZO`, `LZ4`.
`quality`	Integer	Same as source	JPEG image quality. Takes effect only when `compression` is `JPEG`.
`interleaving`	String	Same as source	Interleaving type. Supported values: `bip` (band interleaved by pixel, BIP), `bil` (band interleaved by line, BIL), `bsq` (band sequential, BSQ).
`endian`	String	Same as source	Byte order. Supported values: `NDR` (little endian format), `XDR` (big endian format).

Usage notes

Persistent storage: By default, the output raster is stored in a temporary chunk table that expires at the end of the session. To save the output permanently, specify a chunktable name in storageOption.
Band index: Band indexes start at 0. Use the range syntax ('0-2') or the list syntax ('1,2,3') to select multiple bands.

Example

Extract pyramid level 1 and bands 0 through 2 from a raster, saving the result to a permanent chunk table:

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