Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


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

 SELECT gid, SUM((ST_SummaryStats(ST_Clip(rast,1,geom))).sum)
 FROM perez_grid, ls_den
 WHERE ST_Intersects(geom,rast) 
share|improve this question
Hi @nickves, did you ever manage to get zonal stats working? I can't seem to get the updated query working. – Cliff Patterson Dec 4 '15 at 20:33
@CliffPatterson remember I did work out - it's been three years since then, a lot have changed. I am going try out the querry later and confirm or update. – nickves Dec 4 '15 at 21:24
something must have changed from the last time I used that; not enough time at the moment to see where the Query kicks. if you manage to fix it please free to update – nickves Dec 7 '15 at 2:04
Check out my recent question on the subject. – Cliff Patterson Dec 7 '15 at 13:53
up vote 6 down vote accepted

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...

   feat AS (SELECT gid, geom FROM perez_grid AS b ),
   b_stats AS
(SELECT  gid, (stats).*
SELECT gid, ST_SummaryStats(ST_Clip(rast,1,geom)) AS stats
FROM ls_den
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

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

Your Answer


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.