[postgis-users] Finding highest cell value in raster using ST_Value

[e.j.h.polle]

Hi Group,

I think I want your opinion on a query I have written.

The goal is to find the highest value in a DEM, a raster table. The 
solution I found brings me where I want to go, but it takes 45 seconds on a 
relatively small (225*259) grid. It looks like it is especially the nested 
loop on the ST_Value function in the subquery that does take some time.

Can you have a look at the query below and tell me if I am on the right 
track. Or is there an other route I should follow?

A full description of my workflow (and the origin of the table used) can be 
found in 2 blog posts:

   - 
   http://twiav-tt.blogspot.nl/2012/06/postgis-raster-analysis-find-highest.html
   - 
   http://twiav-tt.blogspot.nl/2012/06/postgis-raster-analysis-find-highest_24.html

This is the query:

INSERT INTO france.highest_peak_martinique(height, geom)
SELECT  height, ST_SetSRID(ST_MakePoint(ST_Raster2WorldCoordX(rast,x),
ST_Raster2WorldCoordY(rast,y)),32620) as geom FROM
(SELECT ST_Value(rast, hor.n, ver.n) AS height, hor.n AS x, ver.n AS y
FROM generate_series(1,225) as hor(n), generate_series(1,259) as ver(n), 
france.mnt_martinique
ORDER BY height DESC LIMIT 1) AS highest_cell_value, france.mnt_martinique;

Please let me know what you think.

TIA,

Egge-Jan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120624/02e3d817/attachment.html>