2

I need some help writing the sql for returning the "MEAN" value from a raster(in postgis db) that intersects a defined(inquery) polygon(in postgis db).

The polygon layer is called: public.farmpaddocks The raster layer is called: public.l8_9385_20131118ndvi_vg94 Both are SRID :3111

I have a field in the public.farmpaddocks table that I would like to filter the result on. Field: farmpaddocks.farmid = '511'.

I assume i need to use the ST_Summarystats function? along with intersection and clip ?? This is my first attempt at an intersect. I get no error or result from the pgadmin output pane?? Not sure whats going on.

sql:

SELECT 
l8_9385_20131118ndvi_vg94.rast, 
farmpaddocks.farmid 
FROM 
public.l8_9385_20131118ndvi_vg94, 
public.farmpaddocks
WHERE
farmpaddocks.farmid='511' AND ST_Intersects(l8_9385_20131118ndvi_vg94.rast,
farmpaddocks.the_geom);
Haydlew
  • 417
  • 1
  • 4
  • 13

1 Answers1

2

One way is to read this http://www.gisintersect.com/?p=154 and understand how aggregate functions work, or this from postgresql site

SQL from link:

SELECT gid, CAST(AVG(((foo.geomval).val)) AS decimal(9,3)) as avgimr FROM (SELECT p.gid, ST_Intersection(r.rast, p.cell) AS geomval FROM imrgrid2p5m r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast)) AS foo WHERE (foo.geomval).val >=0 GROUP BY gid ORDER BY gid;

(see that AVG on start, replace that with your selected median aggregate function and you get median raster value inside polygon)

Then you need add Median support from postgresql wiki or from install contrib package ( i dont know if there is already "default" package in contrib dir) or http://poststat.projects.pgfoundry.org/ or if you really need to math in postgis you can try PL/R from http://www.joeconway.com/plr/.

(disclaimer, i have newer actually used median function , poststat or PL/R and my experience with postgis raster is limited to import/export to db , but all given option should work )

simpleuser001
  • 3,864
  • 18
  • 21
  • Boston GIS has a nice, short PL/R tutorial and it includes how to build a median function for Postgres. http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 – HeyOverThere Jan 21 '14 at 15:59
  • Very coincidental "heyOverThere". Actually looking at an R script right now.....I was trying to figure out how to integrate R into my web application. You may have solved that problem for me with this method. Fantastic!! – Haydlew Jan 21 '14 at 22:23