Obtain the percentile of a raster band with ST_Percentile - PolarDB

Returns the pixel value at which a given percentage of pixels in a raster band fall below.

Prerequisites

Before calling ST_Percentile, compute the percentile statistics for the raster object using either ST_BuildPercentiles or ST_ComputeStatistics.

Syntax

float8 ST_Percentile(raster raster_obj, integer band, integer percentage)

Parameters

Parameter	Description
`raster_obj`	The raster object.
`band`	The band index. Valid values start from `0`. Specify a single band (`0`), a range (`1-3`), or a combination (`'0,1-3,6,8'`). To select all bands, pass an empty string (`''`).
`percentage`	The percentage of the specified bands for the raster object. Pre-compute this data using ST_BuildPercentiles or ST_ComputeStatistics.

Examples

Get the pixel value at the 10th percentile

SELECT st_percentile(rast_obj, 0, 10)
FROM raster_table
WHERE id = 1;

Output:

 st_percentile
---------------
          29.7

Get multiple percentiles in one query

To retrieve several percentile values at once, call ST_Percentile multiple times in a single SELECT statement. This is equivalent to querying quantiles with ST_Quantile.

SELECT st_percentile(rast_obj, 0, 25), st_percentile(rast_obj, 0, 50)
FROM raster_table
WHERE id = 1;

Related functions

Function	Description
ST_BuildPercentiles	Computes and stores percentile statistics for the specified bands of a raster. Run this before calling ST_Percentile.
ST_ComputeStatistics	Computes all statistics for a raster, including histograms and percentiles.
ST_Quantile	Queries pixel values at specified quantile positions for a raster object.