Use ST_Values to get pixel values from a raster by intersecting a geometry - ApsaraDB RDS

Returns the geographic coordinates and pixel values of all cells in a raster object that intersect with a geometry object. Bands are numbered starting from 0. By default, all bands are returned. Both objects must share the same spatial reference system identifier (SRID).

Syntax

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

The function returns a set of record. Each row contains three output fields: cords (geographic coordinates), band (band sequence number), and value (pixel value). To expand all output fields in a query, use the .* syntax:

SELECT (ST_Values(rast, geom)).*
FROM your_table;

To access a specific field, use dot notation:

SELECT (ST_Values(rast, geom)).cords,
       (ST_Values(rast, geom)).value
FROM your_table;

Parameters

Input parameters

Parameter	Type	Default	Description
`raster_obj`	raster	—	The raster object to query.
`geom`	geometry	—	The geometry object used for clipping. Must have the same SRID as `raster_obj`.
`pyramidLevel`	integer	`0`	The pyramid level of the raster object to use.
`bands`	cstring	`''`	The band sequence numbers to return, in the format `'0-2'` or `'1,2,3'`. Band numbers start from 0. The default empty string `''` returns all bands.
`clipOption`	cstring	`''`	Clipping options as a JSON-formatted string. See clipOption parameters.

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.

clipOption parameters

Parameter	Type	Default	Description
`window_clip`	Boolean	`false`	Controls whether to clip by the geometry's bounding box or by the exact geometry shape. Set to `true` to clip by the minimum bounding rectangle (MBR) of the geometry object. Set to `false` to clip by the exact geometry shape.

When to use `window_clip`:

Use window_clip: true when you want to retrieve all cells within the rectangular envelope of a geometry — for example, when clipping with a point and you want to capture the surrounding pixel grid rather than just the exact intersection.
Use window_clip: false (default) when you need precise intersection with the actual geometry shape, such as clipping along a linestring or polygon boundary.

Usage notes

Both the geometry object and the raster object must have a valid SRID, and their SRIDs must match.
By default, each call returns up to 100 MB of data. To adjust this limit, set the ganos.raster.clip_max_buffer_size parameter.

Examples

All examples call ST_Values with the .* syntax to expand the output into individual columns.

Clip with a point geometry

Setting window_clip to true retrieves the pixel grid within the MBR of the point, rather than a single exact-intersection cell.

SELECT (ST_Values(rast, ST_geomfromtext('POINT(128.135 29.774)', 4326)::geometry, 0,'{"window_clip":"true"}')).*
FROM rat_clip WHERE id=1;
    coord     | band | value
--------------+------+-------
 (127.8,29.7) |    0 |    11
 (127.8,29.7) |    1 |    10
 (127.8,29.7) |    2 |    50
(3 rows)

Clip with a linestring geometry

This example clips the raster using the exact shape of a linestring, returning up to 10 rows across all bands.

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;
    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)

What's next

ST_Value — Returns the value of a single cell at a specified point or pixel coordinate.
ST_ClipByMask — Clips a raster object using a geometry mask and returns the clipped raster.
ST_Clip — Clips a raster object by a geometry object and returns the clipped raster.
ST_Intersects — Tests whether a raster object and a geometry object intersect.