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

[Michael Treglia]

Hi All,

Just to follow-up - looks like I've figured out the issue:
In the count of total number of pixels per building, it seems I was 
calculating the total number of pixels per tile that intersected with the 
polygons, not the total number of pixels that actually intersected the 
polygon. For now, I used a reclassification step, which gets around this 
issue, but I suspect an ST_Intersection approach would also work. 

Here's the working sql:

--Begin Code
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.greenroofs_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,
as count_undevel,
--Count total number of pixels
ST_CountAgg(
ST_Clip(
ST_Reclass(rast,1,'[0-5]:1','8BUI',1), --reclassify all pixels to 
non-nodata; when this is clipped, all pixels within polygons are kept as 1, 
while pixels outside of buildings are set to nodata
1, p.geom_2263,NULL,TRUE),
1,TRUE) --exclude nodata from countAgg
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;
--End Code

On Tuesday, July 12, 2016 at 4:56:11 PM UTC-4, Michael Treglia wrote:
>
> 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/20160713/2410720a/attachment-0001.html>