ST_PointValues Overview: Raster Pixel Data Across Bands - PolarDB

Returns pixel values for all bands at a specific location, identified by pixel coordinates or longitude/latitude.

Syntax

setof record ST_PointValues(raster raster_obj, integer column_sn, integer row_sn, boolean exclude_nodata default true, out integer* band, out float8 value);
setof record ST_PointValues(raster raster_obj, float8 x, float8 y, boolean exclude_nodata default true, text options default '{}', out integer band, out float8 value);

Parameters

Parameter	Description
`raster_obj`	The raster object.
`column_sn`	The column index of the pixel, starting from the upper-left corner.
`row_sn`	The row index of the pixel, starting from the upper-left corner.
`x`	The longitude of the pixel.
`y`	The latitude of the pixel.
`exclude_nodata`	Specifies whether to exclude NoData values. If set to `true`, pixels with NoData values are not returned. Default value: `true`.
`options`	The resampling method, specified as a JSON string. Default value: `'{}'`.
`band`	The band number of the pixel (output parameter).
`value`	The pixel value (output parameter).

options parameter

The options parameter accepts a JSON string with the following field:

resample: The resampling method. Valid values:

none: no processing.
idw: inverse distance weighting (IDW) resampling.
mean: mean value resampling.
max: maximum value resampling.
min: minimum value resampling.

Return values

Return value	Description
`band`	The band number.
`value`	The pixel value.

Usage notes

Both overloads return pixel values for all bands. The first overload uses pixel column and row indices; the second uses longitude and latitude coordinates.
If you configure the resample parameter, the four neighboring pixel values are involved in the calculation.

Examples

Example 1: Query pixel values by longitude and latitude, including NoData pixels.

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: Query pixel values by integer longitude and latitude, using default settings.

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

Example 3: Query pixel values with IDW resampling.

SELECT * FROM
    (SELECT (st_pointValues(rast, 125.84382034243441, 47.67709555783107, false, '{"resample" : "idw"}')).*
     FROM t_pixel WHERE id = 3) a
ORDER BY band;

Result:

 band | value
------+--------------------
    0 |  60.81401293159196
    1 |  85.23822259113626
    2 | 26.300926205142165