[postgis-users] raster, stats conditioned to a set of values

[Pierre Racine]

> But my main problem is that I would like to do this (e.g., the query above),
> but only for the pixels in which Band2 = 0.  Any idea? any clue?

1) isolate the pixels with 0. For this you can use ST_MapAlgebra() or ST_Reclass(). I use ST_MapAlgebra() in the following query
2) compute the intersection with band 1
3) compute the stats as you did

SELECT region_cod, (res).* 
FROM 
  (SELECT p.region_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS res
    FROM gis_wd.wd_regiones AS p, rastertmp.ndvitmp AS r
    WHERE ST_Intersects(r.rast, p.geom) 
    AND p.region_cod = 'PA1214'
   ) AS foo WHERE (res).value  > 0;


WITH bands AS ( -- reclass the second band to 0 and 1
SELECT ST_MapAlgebra(ST_Band(rast, 2), '16BSI'::text, 'CASE WHEN [rast] < 0 or [rast] > 0 THEN NULL ELSE 1 END') band2,
             ST_Band(rast, 1) band1
) rastintersect AS ( -- compute the intersection of band 1 and band 2
  SELECT ST_Intersection(band1, band2, 'BAND1') rast FROM bands
)
SELECT region_cod, (res).* 
FROM 
  (SELECT p.region_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS res
    FROM gis_wd.wd_regiones AS p, rastintersect AS r
    WHERE ST_Intersects(r.rast, p.geom) 
    AND p.region_cod = 'PA1214'
   ) AS foo;