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;
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users
