Returns a point geometry for each pixel of a raster, along with the pixel value, row, column, and band. By default, the point coordinates correspond to the upper-left corner of each pixel.
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 | Specifies whether to exclude NoData pixels. |
ref_point | cstring | 'UPPERLEFT' | The pixel corner that the returned point geometry represents. Valid values: UPPERLEFT (upper-left corner) and CENTER (center). |
rowsn | integer | — | Output. The row number of the pixel. |
columnsn | integer | — | Output. The column number of the pixel. |
bandsn | integer | — | Output. The band ID of the pixel. |
value | double | — | Output. The pixel value. |
geom | geometry | — | Output. The spatial extent of the pixel. |
Description
ST_PixelAsPoints converts each pixel in a raster into a record containing rowsn (row), columnsn (column), bandsn (band ID), value (pixel value), and geom (point geometry). The ref_point parameter controls which corner of the pixel the point geometry represents — UPPERLEFT or CENTER.
Examples
The following example retrieves all pixels from band 1 of the raster with id = 10, then filters for 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;
----------------------------------------------------
100 | 100 | 0 | 43 | SRID=4326;POINT(-90 0)The (ST_PixelAsPoints(...)).* pattern expands the returned setof record into individual columns, which you can then filter and select from in the outer query.