I am trying to do a seemingly simple task -- extract mean pixel values from a raster based on a polygon overlay in PostGIS. I consulted several sources, including this blog, this blog, and this post). I loaded a Landsat tile into my database (in-db) and a polygon overlay consisting of three polygons (ID 1,2,3). The Landsat tile is a pansharpened image with a 15m spatial resolution and 4 bands.
Based on help files and the aforementioned posts/questions, I arrived at the following PostGIS query:
SELECT id, (SUM((ST_SummaryStats(a.rast, 1, true)).sum)/SUM((ST_SummaryStats(a.rast, 1, true)).count)) as meanFROM imagery.l8_2015_09_12 AS a, analysis_results.zonal_stats_test as bWHERE ST_Intersects(b.geom,a.rast)GROUP BY id;
The results are:
1: 9902.492: 10079.683: 12355.90However, when I test the results against ArcGIS's "zonal statistics to table" tool, I get the following results:
1: 8089.612: 7527.623: 12290.05Close, but not identical. I am almost there but I was wondering if someone could help me figure out the source of the difference between both techniques. I suspect that there is something wrong with my ST_summaryStats method.
أكثر...
Based on help files and the aforementioned posts/questions, I arrived at the following PostGIS query:
SELECT id, (SUM((ST_SummaryStats(a.rast, 1, true)).sum)/SUM((ST_SummaryStats(a.rast, 1, true)).count)) as meanFROM imagery.l8_2015_09_12 AS a, analysis_results.zonal_stats_test as bWHERE ST_Intersects(b.geom,a.rast)GROUP BY id;
The results are:
1: 9902.492: 10079.683: 12355.90However, when I test the results against ArcGIS's "zonal statistics to table" tool, I get the following results:
1: 8089.612: 7527.623: 12290.05Close, but not identical. I am almost there but I was wondering if someone could help me figure out the source of the difference between both techniques. I suspect that there is something wrong with my ST_summaryStats method.
أكثر...