ST_NearestValue

Name

ST_NearestValue — Returns the nearest non- NODATA value of a given band's pixel specified by a columnx and rowy or a geometric point expressed in the same spatial reference coordinate system as the raster.

Synopsis

double precision ST_NearestValue ( raster rast , integer bandnum , geometry pt , boolean exclude_nodata_value=true ) ;

double precision ST_NearestValue ( raster rast , geometry pt , boolean exclude_nodata_value=true ) ;

double precision ST_NearestValue ( raster rast , integer bandnum , integer columnx , integer rowy , boolean exclude_nodata_value=true ) ;

double precision ST_NearestValue ( raster rast , integer columnx , integer rowy , boolean exclude_nodata_value=true ) ;

Description

Returns the nearest non- NODATA value of a given band in a given columnx, rowy pixel or at a specific geometric point. If the columnx, rowy pixel or the pixel at the specified geometric point is NODATA , the function will find the nearest pixel to the columnx, rowy pixel or geometric point whose value is not NODATA .

Band numbers start at 1 and bandnum is assumed to be 1 if not specified. If exclude_nodata_value is set to false, then all pixels include nodata pixels are considered to intersect and return value. If exclude_nodata_value is not passed in then reads it from metadata of raster.

Availability: 2.1.0

[Note]

ST_NearestValue is a drop-in replacement for ST_Value.

Examples

This example reads a value from pixel 2x2.

Code
WITH source AS (
    SELECT ST_SetValues(
        ST_AddBand(
            ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
            1, '8BUI', 1, 0
        ),
        1, 1, 1,
        ARRAY[
            [0, 1, 1, 1, 1],
            [1, 1, 1, 0, 1],
            [1, 0, 1, 1, 1],
            [1, 1, 1, 1, 0],
            [1, 1, 0, 1, 1]
        ]::double precision[][]
    ) AS rast
)
SELECT
    ST_Value(rast, 2, 2) AS value,
    ST_NearestValue(rast, 2, 2) AS nearestvalue
FROM source
Output
 value | nearestvalue
-------+--------------
     1 |            1

This example reads a NODATA value from pixel 2x3.

Code
WITH source AS (
    SELECT ST_SetValues(
        ST_AddBand(
            ST_MakeEmptyRaster(5, 5, -2, 2, 1, -1, 0, 0, 0),
            1, '8BUI', 1, 0
        ),
        1, 1, 1,
        ARRAY[
            [0, 1, 1, 1, 1],
            [1, 1, 1, 0, 1],
            [1, 0, 1, 1, 1],
            [1, 1, 1, 1, 0],
            [1, 1, 0, 1, 1]
        ]::double precision[][]
    ) AS rast
)
SELECT
    ST_Value(rast, 2, 3) AS value,
    ST_NearestValue(rast, 2, 3) AS nearestvalue
FROM source
Output
 value | nearestvalue
-------+--------------
       |            1