Navigation :

ST_ValueCount

ST_ValueCount
Prev   12.9. Raster Band Statistics and Analytics   Next

Name

ST_ValueCount — Returns a set of records containing a pixel band value and count of the number of pixels in a given band of a raster (or a raster coverage) that have a given set of values. If no band is specified defaults to band 1. By default nodata value pixels are not counted. and all other values in the pixel are output and pixel band values are rounded to the nearest integer.

Synopsis

SETOF record ST_ValueCount ( raster rast , integer nband=1 , boolean exclude_nodata_value=true , double precision[] searchvalues=NULL , double precision roundto=0 , double precision OUT value , integer OUT count ) ;

SETOF record ST_ValueCount ( raster rast , integer nband , double precision[] searchvalues , double precision roundto=0 , double precision OUT value , integer OUT count ) ;

SETOF record ST_ValueCount ( raster rast , double precision[] searchvalues , double precision roundto=0 , double precision OUT value , integer OUT count ) ;

bigint ST_ValueCount ( raster rast , double precision searchvalue , double precision roundto=0 ) ;

bigint ST_ValueCount ( raster rast , integer nband , boolean exclude_nodata_value , double precision searchvalue , double precision roundto=0 ) ;

bigint ST_ValueCount ( raster rast , integer nband , double precision searchvalue , double precision roundto=0 ) ;

SETOF record ST_ValueCount ( text rastertable , text rastercolumn , integer nband=1 , boolean exclude_nodata_value=true , double precision[] searchvalues=NULL , double precision roundto=0 , double precision OUT value , integer OUT count ) ;

SETOF record ST_ValueCount ( text rastertable , text rastercolumn , double precision[] searchvalues , double precision roundto=0 , double precision OUT value , integer OUT count ) ;

SETOF record ST_ValueCount ( text rastertable , text rastercolumn , integer nband , double precision[] searchvalues , double precision roundto=0 , double precision OUT value , integer OUT count ) ;

bigint ST_ValueCount ( text rastertable , text rastercolumn , integer nband , boolean exclude_nodata_value , double precision searchvalue , double precision roundto=0 ) ;

bigint ST_ValueCount ( text rastertable , text rastercolumn , double precision searchvalue , double precision roundto=0 ) ;

bigint ST_ValueCount ( text rastertable , text rastercolumn , integer nband , double precision searchvalue , double precision roundto=0 ) ;

Description

Returns a set of records with columns value count which contain the pixel band value and count of pixels in the raster tile or raster coverage of selected band.

If no band is specified nband defaults to 1. If no searchvalues are specified, will return all pixel values found in the raster or raster coverage. If one searchvalue is given, will return an integer instead of records denoting the count of pixels having that pixel band value

[Note]

If exclude_nodata_value is set to false, will also count pixels with no data.

Availability: 2.0.0

Examples 

UPDATE dummy_rast SET rast = ST_SetBandNoDataValue(rast,249) WHERE rid=2;
--Example will count only pixels of band 1 that are not 249. --

SELECT (pvc).*
FROM (SELECT ST_ValueCount(rast) As pvc
    FROM dummy_rast WHERE rid=2) As foo
    ORDER BY (pvc).value;

 value | count
-------+-------
   250 |     2
   251 |     1
   252 |     2
   253 |     6
   254 |    12

-- Example will coount all pixels of band 1 including 249 --
SELECT (pvc).*
FROM (SELECT ST_ValueCount(rast,1,false) As pvc
    FROM dummy_rast WHERE rid=2) As foo
    ORDER BY (pvc).value;

 value | count
-------+-------
   249 |     2
   250 |     2
   251 |     1
   252 |     2
   253 |     6
   254 |    12

-- Example will count only non-nodata value pixels of band 2
SELECT (pvc).*
FROM (SELECT ST_ValueCount(rast,2) As pvc
    FROM dummy_rast WHERE rid=2) As foo
    ORDER BY (pvc).value;
 value | count
-------+-------
    78 |     1
    79 |     1
    88 |     1
    89 |     1
    96 |     1
    97 |     1
    98 |     1
    99 |     2
   112 |     2
:

                
--real live example.  Count all the pixels in an aerial raster tile band 2 intersecting a geometry
-- and return only the pixel band values that have a count > 500
SELECT (pvc).value, SUM((pvc).count) As total
FROM (SELECT ST_ValueCount(rast,2) As pvc
    FROM o_4_boston
        WHERE ST_Intersects(rast,
            ST_GeomFromText('POLYGON((224486 892151,224486 892200,224706 892200,224706 892151,224486 892151))',26986)
             )
        ) As foo
    GROUP BY (pvc).value
    HAVING SUM((pvc).count) > 500
    ORDER BY (pvc).value;

 value | total
-------+-----
    51 | 502
    54 | 521

-- Just return count of pixels in each raster tile that have value of 100 of tiles that intersect  a specific geometry --
SELECT rid, ST_ValueCount(rast,2,100) As count
    FROM o_4_boston
        WHERE ST_Intersects(rast,
            ST_GeomFromText('POLYGON((224486 892151,224486 892200,224706 892200,224706 892151,224486 892151))',26986)
             ) ;

 rid | count
-----+-------
   1 |    56
   2 |    95
  14 |    37
  15 |    64

See Also

ST_Count , ST_SetBandNoDataValue

Prev   Up   Next
ST_SummaryStatsAgg  Home  ST_RastFromWKB