[postgis-users] Incorrect Results for ST_ValueCount with large polygons

[Michael Treglia]

Hi all,

I'm basically trying to compute # of pixels of land cover type by polygon,
for lots of polygons.

I'm working with a query a query like this:
SELECT  bbl, (pvc).VALUE, SUM((pvc).COUNT) AS tot_pix
 FROM base_rasters.landcover6in
  INNER join  results_scratch.polys_test
  ON ST_Intersects(landcover6in.rast, polys_test.geom_2263),
    ST_ValueCount(ST_Clip(landcover6in.rast,polys_test.geom_2263),1) AS pvc
  GROUP BY (pvc).VALUE, bbl
 ORDER BY (pvc).VALUE;

For smaller polygons, spot-checking, it looks like I'm generally getting
the right results. However, for larger polygons, not always. For example,
though a polygon might not have roads, I'm getting non-0 counts for road
pixels within a polygon (polygons have unique identifiers as 'bbl').  The
total area of pixels counted ends up being larger than the polygon (larger
than would be attributable to differences between polygons edges & pixels)

I'm thinking it's an issue when there are multiple raster tiles included...
but not entirely sure. Any suggestions on whether there's something off in
this query that might be causing an issue like I describe?

Thanks in advance for any suggestions!
Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180621/9231910d/attachment.html>