Query Raster Cell Values by Geometry Using ST_Values - PolarDB

Returns the geographic coordinates and pixel values of all cells in a raster object that intersect with a geometry object. Each matching cell is returned as a record with three fields: coordinates, band sequence number, and pixel value.

Syntax

set of record ST_Values(raster raster_obj,
                        geometry geom,
                        integer pyramidLevel default 0,
                        cstring bands default '',
                        cstring clipOption default '',
                        out point cords,
                        out integer band,
                        out float8 value)

Parameters

Parameter	Type	Default	Description
`raster_obj`	raster	—	The raster object.
`geom`	geometry	—	The geometry object used for clipping.
`pyramidLevel`	integer	0	The pyramid level.
`bands`	cstring	`''`	The band sequence numbers to include. Use the format `'0-2'` or `'1,2,3'`. Sequence numbers start at 0. Leave blank to include all bands.
`clipOption`	cstring	`''`	Clipping options, as a JSON-formatted string. See the `clipOption` sub-parameters below.

clipOption sub-parameters

Parameter	Type	Default	Description
`window_clip`	Boolean	`false`	Specifies whether to use the bounding box of the geometry object instead of the geometry itself. `true` clips using the minimum bounding rectangle (MBR) of the geometry; `false` clips using the geometry object directly.

Return record format

Each returned record contains the following output fields:

Field	Type	Description
`cords`	point	The geographic coordinates of the pixel.
`band`	integer	The band sequence number of the pixel.
`value`	float8	The pixel value.

Usage notes

Both the geometry object and the raster object must have a valid spatial reference system identifier (SRID), and their SRIDs must match.
The default output size limit is 100 MB. To adjust this limit, set the ganos.raster.clip_max_buffer_size parameter.

Examples

Query pixel values using a point geometry

window_clip is set to true, so the function clips using the MBR of the point.

SELECT (ST_Values(rast, ST_geomfromtext('POINT(128.135 29.774)', 4326)::geometry, 0,'{"window_clip":"true"}')).*
FROM rat_clip WHERE id=1;

Result:

    coord     | band | value
--------------+------+-------
 (127.8,29.7) |    0 |    11
 (127.8,29.7) |    1 |    10
 (127.8,29.7) |    2 |    50
(3 rows)

Query pixel values using a linestring geometry

The function returns one record per intersecting pixel per band.

SELECT (ST_Values(rast, ST_geomfromtext('LINESTRING(0 0, 45 45, 90 0, 135 45)', 4326)::geometry, 0)).*
FROM rat_clip WHERE id=1 LIMIT 10;

Result:

    coord    | band | value
-------------+------+-------
 (44.1,45)   |    0 |   115
 (44.1,45)   |    1 |   112
 (44.1,45)   |    2 |    69
 (45,45)     |    0 |   122
 (45,45)     |    1 |   117
 (45,45)     |    2 |    75
 (134.1,45)  |    0 |    37
 (134.1,45)  |    1 |    64
 (134.1,45)  |    2 |    13
 (43.2,44.1) |    0 |    66
(10 rows)