Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Now with postgis2 which supports rasters by default is it possible to do a zonal statistics analysis?

I've google it but I haven't found anything solid? Is there a tutorial outthere to get me started?

Can anyone give me an sql example on how to do it?

EDIT :

An updated (simplified) query according the blog Aragon mention :

CREATE TABLE sum_pop3 AS 
 SELECT gid, SUM((ST_SummaryStats(ST_Clip(rast,1,geom))).sum)
 FROM perez_grid, ls_den
 WHERE ST_Intersects(geom,rast) 
GROUP BY gid;
share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted
+50

you should check out Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database document by Pierre Racine and Steve Cumming which was presented at FOSS, here. There are lots of function defined as raster statistics to solve your problem. i think ST_SummaryStats will help you about zonal statics, of course, not enough.

ST_SummaryStats(raster) will turn you a set of (min, max, sum, mean, stddev, count (of withdata pixels)) records.

Postgis 2.0 supports zonal statistics analysis with some sql queries which you have work on. i have came across a good documentation on net about zonal statitics with esri vs postgis 2.0. you should check out for some information at Moving Spatial here. both of them was given the same statistics result with some hard querying.

example query which was taken from Anthony Lopez site i have mentioned before...

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

Esri Zonal Statistics result: Population: 207,578 Cell Count: 14,400

Postgis Method result: Population: 207,578 Cell Count: 14,400 .

Population Percent Difference: 0%

i havent tried it yet but Anthony mentioned that there was some performance problem with raster analysis with postgis.

i hope it helps you...

share|improve this answer
    
It is a good starting point! –  nickves Oct 8 '12 at 10:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.