[postgis-users] Incorrect Result for ST_CountAgg with nodata pixels?

[Michael Treglia]

Hi All,

I'm working to do some overlay analyses between raster and vector layers,
and having an issue getting an accurate count of pixels per polygon.

My query is below - basically my raster layer has values ranging 1-5, with
some nodata pixels (defined as such, with values of 0). I want to
calculate, for each polygon, the proportion of pixels, of the total
(including nodata), with values of 1 and 2.

The first ST_CountAgg statement (yielding 'count_undevel') seems to be
working as expected, and reports an accurate number. However, the second
one, to create the 'count_total' field consistently gives an over-count.

Any guess as to what might be going on/how to correct? I'll gladly share
the properties of the data if that would be useful.

Thanks!
Mike



SELECT *,
 count_undevel/(count_total)::float as PropVeg --Calculates proportion of
pixels within each building that are class 1 or 2 based on count_undevel
and count_total
INTO resultsvectorlayers.pctgrn
FROM(
SELECT
p.*,
--Count number of pixels per building with values 1 or 2
ST_CountAgg(
ST_Reclass(
ST_Clip(rast, p.geom_2263),
1,'[1-2]:1, [3-5]:0', '8BUI',0), 1,TRUE) --reclassifies classes 1 and 2 as
1, reclassifies values 3-5 as 0, and designates 0 as nodata
as count_undevel,
--Count total number of pixels
ST_CountAgg(
ST_Clip(rast, p.geom_2263),
1,FALSE)
as count_total
FROM staging.naip_ndvi_classified_2013 r, staging.nycbldgs p
WHERE ST_INTERSECTS(r.rast, p.geom_2263) --and rid = 2
group by p.geom_2263, p.gid
) AS FOO;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160712/9aa9cd14/attachment.html>