Hi,<div><br></div><div>I am testing various summary stat queries, to summarize raster pixel values within polygons.</div><div><br></div><div>After some testing i ended up with this query (source: <a href="http://movingspatial.blogspot.com/2012/07/postgis-20-intersect-raster-and-polygon.html" style="font-family:Calibri,sans-serif;font-size:11pt">http://movingspatial.blogspot.com/2012/07/postgis-20-intersect-raster-and-polygon.html</a>)</div>
<div><br></div><div><div>CREATE TABLE borderpop AS</div><div>WITH </div><div> feat AS (SELECT tribe_code, geom FROM border_tribes AS b ),</div><div> b_stats AS</div><div> (SELECT tribe_code, (stats).*</div><div> FROM (</div>
<div> SELECT tribe_code, ST_SummaryStats(ST_Clip(rast,1,geom),true) AS stats</div><div> FROM gpw1990</div><div> INNER JOIN feat</div><div> ON ST_Intersects(feat.geom,rast) ) AS foo )</div><div>SELECT tribe_code, SUM(count) AS cell_count</div>
<div> ,SUM(sum) AS population</div><div> FROM b_stats</div><div> WHERE count > 0</div><div> GROUP BY tribe_code</div><div> ORDER BY tribe_code;</div><div><br></div></div><div>This works, but the result deviates a little from the ArcGIS Summary Stats result.</div>
<div><br></div><div>An example using only the first ten polygons returns:</div><div><br></div><div><p class="MsoPlainText">Groupid arc_sum q1_sum</p>
<p class="MsoPlainText">0 2637180.00 2654443.559</p>
<p class="MsoPlainText">1 546136.00 454462.6017</p>
<p class="MsoPlainText">2 26730.30 26414.74116</p>
<p class="MsoPlainText">3 217604.00 211572.4562</p>
<p class="MsoPlainText">4 773476.00 776741.9507</p>
<p class="MsoPlainText">5 36266.00 543900.1659</p>
<p class="MsoPlainText">6 404880.00 414704.143</p>
<p class="MsoPlainText">7 973381.00 977798.4238</p>
<p class="MsoPlainText">8 38558.90 40807.53601</p>
<p class="MsoPlainText">9 1531430.00 1523654.018</p>
<p class="MsoPlainText">10 44975.40 47802.05481</p>
<p class="MsoPlainText"> </p>
<p class="MsoPlainText">The total sum in arc was 7730617.60, while the total sum
in postgis was 7672301.651</p>
<p class="MsoPlainText">It is not a lot, but some 0.7543505 percent less in the
postgis calculations.</p><p class="MsoPlainText"><br></p><p class="MsoPlainText">Any suggestions why the results are not the same?</p><p class="MsoPlainText">Any improvements to the query maybe?</p><p class="MsoPlainText">
<br></p><p class="MsoPlainText">My guess is that the two queries handles pixels at the border of two polygons differently.</p><p class="MsoPlainText"><br></p><p class="MsoPlainText">Best,</p><p class="MsoPlainText">Andreas</p>
</div>