I'm using PostgreSQL 9.2.4 and PostGIS 2.0.3.
I have some raster layer and I'm trying to get the count of all pixels (including nodata), cliped by polygon.
Raster was imported by raster2sql:
raster2pgsql -I -d -C -M -s ESPG_code -t 80x80 input_source > output.sql psql -h localhost -U user_name -d database -f output.sql
So my code is:
polygon As (
SELECT ST_GeometryFromText('wtk_polygon_geometry))', epsg) As geom
SELECT ST_Count(ST_Clip(r.rast,p.geom),1,false) As val FROM raster As r, polygon As p WHERE ST_Intersects(r.rast,p.geom)