Use ST_AsImage to convert a raster to a BYTEA image - PolarDB

Converts a raster to a bytea image by cropping it to the specified extent and returns the result as a PNG or JPEG byte array. If no bands are specified, the first three bands are used. PNG supports 1, 2, 3, or 4 bands; JPEG supports 1 or 3 bands.

Syntax

bytea ST_AsImage(raster raster_obj,
        box extent,
        integer pyramidLevel default 0,
        cstring bands default '',
        cstring format default 'PNG',
        cstring option default '');

Parameters

Parameter	Description
`raster_obj`	The raster to convert.
`extent`	The cropping extent. Uses geographic coordinates by default. To use pixel coordinates instead, set `rast_coord` to `true` in the `option` parameter.
`pyramidLevel`	The pyramid level to read from. Valid values start from 0. Default: `0`.
`bands`	The bands to include in the output image. Valid values start from 0. Specify a range (`'0-2'`) or a list (`'1,2,3'`). Default: the first three bands. For JPEG, specify 1 or 3 bands. For PNG, specify 1, 2, 3, or 4 bands.
`format`	The output image format. Valid values: `PNG` (default), `JPEG`.
`option`	A JSON string that controls NoData handling, coordinate mode, display enhancement, and compression quality. See Option fields.

Option fields

Field	Type	Default	Description
`nodata`	Boolean	`false`	Whether to treat NoData values specially. `true`: process NoData values as NoData. `false`: treat NoData values as normal values.
`nodataValue`	integer	`0`	The NoData value of the raster. Required when `nodata` is `true`.
`rast_coord`	Boolean	`false`	Whether the `extent` box uses pixel coordinates instead of geographic coordinates. When `true`, `x` is the column number and `y` is the row number, both starting from 0.
`strength`	string	`none`	Display enhancement mode. `none`: no enhancement. `stats`: contrast stretching based on statistical values.
`quality`	integer	`75`	Compression quality. Valid values: 1–100.

Usage notes

The function returns a bytea byte array containing the image data.
By default, up to 100 MB of cropped data can be cached, which indicates that up to 100 MB of data can be returned. To adjust this limit, set the ganos.raster.clip_max_buffer_size parameter.
Band count determines the image color mode:
- 1 band: grayscale image
- 2 bands: grayscale image with Alpha channel
- 3 bands: RGB (R band, G band, B band)
- 4 bands: RGBA (R band, G band, B band, Alpha band)

Examples

All examples query raster_table and filter by id = 1.

Crop to a geographic extent

SELECT ST_AsImage(raster_obj,
                  '(-180,-90), (0,0)'::Box)
FROM raster_table
WHERE id = 1;

Read from a specific pyramid level

SELECT ST_AsImage(raster_obj,
                  '(-180,-90), (0,0)'::Box,
                  1)
FROM raster_table
WHERE id = 1;

Select a band range

SELECT ST_AsImage(raster_obj,
                  '(-180,-90), (0,0)'::Box,
                  1,
                  '0-2')
FROM raster_table
WHERE id = 1;

Output as PNG

SELECT ST_AsImage(raster_obj,
                  '(-180,-90), (0,0)'::Box,
                  1,
                  '0-2',
                  'PNG')
FROM raster_table
WHERE id = 1;

Apply contrast stretching

SELECT ST_AsImage(rast,
                  '(-180,-90), (0,0)'::Box,
                  0,
                  '',
                  'PNG',
                  '{"nodata":"false", "nodatavalue":"0","rast_coord":"false", "strength":"stats", "quality":"75"}')
FROM raster_table
WHERE id = 1;

Crop using pixel coordinates with contrast stretching

SELECT ST_AsImage(rast,
                  '(0,0), (200,100)'::Box,
                  0,
                  '',
                  'PNG',
                  '{"nodata":"false", "nodatavalue":"0","rast_coord":"true", "strength":"stats", "quality":"75"}')
FROM raster_table
WHERE id = 1;