ST_PixelAsPolygons Raster-to-Polygon Geometry Function - PolarDB

Returns every pixel in a raster band as a polygon geometry, along with each pixel's row, column, band, and value.

Syntax

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

Each returned record has the format: rowsn integer, columnsn integer, bandsn integer, value double, geom geometry.

When exclude_nodata_value is true (the default), pixels with NoData values are excluded from the result. Set it to false to include all pixels regardless of their value.

Parameters

Parameter	Description
`raster_obj`	The raster to query.
`band`	The band ID to query. Valid band IDs start from `0`. Default: `0`.
`pyramid`	The pyramid level ID to query. Default: `0`.
`exclude_nodata_value`	Specifies whether to exclude NoData pixels from the result. Default: `true`.
`rowsn`	Output. The row number of the pixel.
`columnsn`	Output. The column number of the pixel.
`bandsn`	Output. The band ID of the pixel.
`value`	Output. The pixel value.
`geom`	Output. The spatial extent of the pixel as a polygon geometry.

Examples

The following example retrieves pixels from band 1 of the raster with id = 10, then filters for pixels with a value greater than 38.0. The (ST_PixelAsPolygons(...)).* syntax expands the returned records into individual columns.

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

Output:

    100 |      100 |      0 |    43 | SRID=4326;POLYGON((-180 90,-180 89.1,-179.1 89.1,-179.1 90,-180 90))