Use ST_SetValue to change pixel values in a raster - PolarDB

Sets the pixel value of a raster object at a specified location, defined by either row and column coordinates or a geometry object.

Syntax

Two overloads are available. Use the row/column overload to target a single pixel by position, and the geometry overload to set pixel values across an area defined by a geometry shape.

-- Overload 1: set by row and column
raster ST_SetValue(raster raster_obj,
                   integer row,
                   integer column,
                   double value,
                   integer band);

-- Overload 2: set by geometry
raster ST_SetValue(raster raster_obj,
                   geometry geom,
                   double value,
                   integer band default 0,
                   boolean rebuild_pyd default true,
                   cstring setvalueOptions default '');

Parameters

Parameter	Description
`raster_obj`	The raster object to modify.
`row`	The row index of the target pixel.
`column`	The column index of the target pixel.
`value`	The new pixel value to assign.
`band`	The band index to update. Default value: `0`.
`rebuild_pyd`	Specifies whether to rebuild the pyramid after the update. Default value: `true`.
`setvalueOptions`	Additional options for the geometry-based overload, passed as a JSON string. See setvalueOptions fields.

setvalueOptions fields

Field	Type	Default	Description
`window_clip`	bool	`false`	Specifies whether to use the bounding box of the geometry object as the update region. If `true`, uses the minimum bounding rectangle (MBR) of the geometry as the update region. If `false`, uses the geometry shape itself.
`rast_coord`	bool	`false`	Specifies whether the geometry coordinates are pixel coordinates. If `true`, the x-coordinate maps to the column index and the y-coordinate maps to the row index, with both starting at `0`. If `false`, the geometry uses spatial (geographic) coordinates.

Examples

Read a pixel value before updating

SELECT st_value(rast, 0, 2, 1) FROM raster_table ORDER BY id;

Set pixel values at multiple points using a MULTIPOINT geometry

UPDATE raster_table
SET rast = ST_SetValue(
    rast,
    ST_GeomFromText('MULTIPOINT(0 0, 2 2, 10 10)', 4326),
    250.0,
    0,
    true
);

Set pixel values along a line using pixel coordinates

The rast_coord option treats the geometry coordinates as pixel column/row indices rather than spatial coordinates.

UPDATE raster_table
SET rast = ST_SetValue(
    rast,
    ST_GeomFromText('LINESTRING(211 77, 233 100)', 4326),
    10.0,
    0,
    false,
    '{"rast_coord":true}'
);