ST_PointValues function to obtain pixel values of all bands - ApsaraDB RDS

ST_PointValues returns the pixel values of all bands at a given location in a raster object. Specify the location using either pixel column/row coordinates or geographic x/y coordinates.

Syntax

Overload 1: column and row coordinates

setof record ST_PointValues(raster raster_obj,
        integer column_sn,
        integer row_sn,
        boolean exclude_nodata default true,
        out integer band,
        out float8 value);

Overload 2: x/y geographic coordinates

setof record ST_PointValues(raster raster_obj,
        float8  x,
        float8  y,
        boolean exclude_nodata default true,
        out integer band,
        out float8 value);

Parameters

Parameter	Description
`raster_obj`	The raster object that you want to clip.
`column_sn`	The column number of the pixel, counted from the upper-left corner of the raster object.
`row_sn`	The row number of the pixel, counted from the upper-left corner of the raster object.
`x`	The x coordinate of the pixel.
`y`	The y coordinate of the pixel.
`exclude_nodata`	Specifies whether to exclude NoData pixels from the results. When set to `true` (default), pixels with a NoData value are not returned.
`band`	Output: the band number of the returned pixel.
`value`	Output: the pixel value for the band.

Description

ST_PointValues returns a set of (band, value) record pairs, one per band, for the pixel at the specified location. Use the column/row overload when you know the pixel's grid position, and the x/y overload when you have geographic coordinates.

When exclude_nodata is true, bands where the pixel value equals the raster's NoData value are omitted from the result set.

To access individual columns from the setof record return type, use a subquery with the .* expansion pattern, as shown in the examples below.

Examples

Example 1: Retrieve all band values at geographic coordinates (including NoData)

select * from
    ( select (st_pointValues(rast, 125.84382034243441 , 47.67709555783107, false)).*
      from t_pixel where id = 3) a
ORDER by band;

Result:

 band | value
------+-------
    0 |    66
    1 |    87
    2 |    28

Example 2: Retrieve all band values at x/y coordinates (excluding NoData)

select * from
    ( select (st_pointValues(rast, 125 , 47)).*
      from t_pixel where id = 3) a
ORDER by band;

Result:

 band | value
------+-------
    0 |    39
    1 |    66
    2 |    11