[postgis-users] Understanding ST_SummaryStats output

[David Quinn]

Hello List,

I'm using ST_SummaryStats() to calculate the total value of the pixels
selected using ST_Intersect. The number I want is the weighted sum of this
area. e.g. SUM (number of times value occurs * value)
I'm doing this using the following query where I intersect a raster with
zip code boundaries. The pixel values contain the number of people, so I
want to sum up all of the selected rasters.

SELECT
    (stats).count,
    (stats).sum
FROM (
    SELECT
        ST_SummaryStats(r.rast) as stats
    from
        new_york as r,
        ny_zip as e
    where
        ST_Intersects(r.rast, e.geom)
    and
        e.zipcode = '10456'
    ) As foo

However, I don't understand the values from ST_SummaryStats. I've
summarized the results below and the PostGIS results are quite different
from each other, and from my ArcGIS result. I didn't think that the chunk
size influenced the accuracy of the result, and I'm not sure why the 'sum'
between ArcGIS and PostGIS is so different.

--                    Count         Sum
-- ArcGIS:           499       149881 (100m cell-size)
-- PostGIS:        1200      297167 (10 x 10 chunks)
-- PostGIS:        4926     1074925 (50 x 50 chunks)
-- PostGIS:        7872     1361671 (100 x 100 chunks)
-- PostGIS:     348722   10572532 (1000 x 1000 chunks)


The original raster is 100m in cell-size and I am loading it into
postgresql using the following command:

raster2pgsql -I -C -F -s 900913 -t 50x50  \new_york_pop.tif new_york_50 |
psql.exe -U postgres -d raster_analysis -h localhost -p 5434

I'm using PostGIS 2.0.0 on Win 7.

Thanks,
David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120205/ef7838bc/attachment.html>