ST_PixelAsPoints Raster-to-Geometry Conversion Reference - PolarDB

Converts every pixel in a raster band into a point geometry. The point's position within each pixel is controlled by the ref_point parameter.

Syntax

setof record ST_PixelAsPoints(raster raster_obj,
    integer band default  0,
    integer pyramid default 0,
    boolean exclude_nodata_value default true,
    cstring ref_point default 'UPPERLEFT',
    out integer rowsn,
    out integer columnsn,
    out integer bandsn,
    out double value,
    out geometry geom);

Parameters

Parameter	Type	Default	Description
`raster_obj`	raster	—	The raster to query.
`band`	integer	0	The band to query. Band IDs start from 0.
`pyramid`	integer	0	The pyramid level to query.
`exclude_nodata_value`	boolean	true	Whether to exclude NoData pixels from the results.
`ref_point`	cstring	`'UPPERLEFT'`	The position of the point within each pixel's bounding geometry. Valid values: `UPPERLEFT` (upper-left corner) and `CENTER` (centroid).

Return value

Returns a set of records. Each record corresponds to one pixel and contains the following columns:

Column	Type	Description
`rowsn`	integer	The row number of the pixel in the raster.
`columnsn`	integer	The column number of the pixel in the raster.
`bandsn`	integer	The band ID of the pixel.
`value`	double	The pixel value.
`geom`	geometry	The point geometry representing the pixel's position.

Description

ST_PixelAsPoints iterates over every pixel in the specified band and pyramid level and returns one point geometry per pixel. The point's position is set by ref_point:

UPPERLEFT — places the point at the upper-left corner of the pixel. Use this when you need coordinates that align with raster grid edges or tile boundaries.
CENTER — places the point at the centroid of the pixel. Use this when the point should represent the pixel's geographic center, such as for interpolation or sampling.

When exclude_nodata_value is true (the default), pixels whose values equal the raster's NoData value are not returned. Set it to false to include all pixels regardless of their value.

Example

The following query expands ST_PixelAsPoints results for band 1 of the raster with id = 10, then filters to pixels with a value greater than 38.0.

WITH tmp AS (
SELECT (ST_PixelAsPoints(rast, 1)).*
FROM rast_table
WHERE id = 10 )
SELECT rowsn, columnsn, bandsn, value, ST_AsEWKT(geom)  FROM tmp WHERE value > 38.0;

Example output:

 rowsn | columnsn | bandsn | value |        st_asewkt
-------+----------+--------+-------+--------------------------
   100 |      100 |      0 |    43 | SRID=4326;POINT(-90 0)

Use (ST_PixelAsPoints(...)).* in the FROM clause to expand the returned set of records into individual columns before filtering or joining.