[postgis-users] Summarystats (Raster, Polygon)

[Andreas Forø Tollefsen]

Hi,

I am testing various summary stat queries, to summarize raster pixel values
within polygons.

After some testing i ended up with this query (source:
http://movingspatial.blogspot.com/2012/07/postgis-20-intersect-raster-and-polygon.html
)

CREATE TABLE borderpop AS
WITH
   feat AS (SELECT tribe_code, geom FROM border_tribes AS b ),
   b_stats AS
 (SELECT  tribe_code, (stats).*
  FROM (
  SELECT tribe_code, ST_SummaryStats(ST_Clip(rast,1,geom),true) AS stats
  FROM gpw1990
  INNER JOIN feat
  ON ST_Intersects(feat.geom,rast) ) AS foo )
SELECT tribe_code, SUM(count) AS cell_count
  ,SUM(sum) AS population
 FROM b_stats
 WHERE count > 0
 GROUP BY tribe_code
 ORDER BY tribe_code;

This works, but the result deviates a little from the ArcGIS Summary Stats
result.

An example using only the first ten polygons returns:

Groupid     arc_sum                      q1_sum

0           2637180.00                   2654443.559

1           546136.00                    454462.6017

2           26730.30                     26414.74116

3           217604.00                    211572.4562

4           773476.00                    776741.9507

5           36266.00                     543900.1659

6           404880.00                    414704.143

7           973381.00                    977798.4238

8           38558.90                     40807.53601

9           1531430.00                   1523654.018

10          44975.40                     47802.05481



The total sum in arc was 7730617.60, while the total sum in postgis was
7672301.651

It is not a lot, but some 0.7543505 percent less in the postgis
calculations.


Any suggestions why the results are not the same?

Any improvements to the query maybe?


My guess is that the two queries handles pixels at the border of two
polygons differently.


Best,

Andreas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121115/a0c31bed/attachment.html>