[postgis-users] Effectively combining rasters

[Kim Bisgaard]

Hi,

I am trying to combine rasters, or in other words pick a value at a position in one raster and at the same time get the value at same the 
same position in the other raster. The way I'm doing it, takes a long time compare to only picking values from one raster, which makes me 
think that there ought to be a more efficient way?

I am currently using this SQL:
select st_x(st_centroid((AA).geom)) as lon,
        st_y(st_centroid((AA).geom)) as lat,
        (AA).val as "AAA",
        (BB).val as "BBB"
from (select st_intersection(A.rast,1,pos.geom) as AA,
              st_intersection(B.rast,1,pos.geom) as BB
       from (select ST_GeomFromText('MULTIPOLYGON(((15.000 54.800,15.000 55.000,14.800 55.000,14.800 54.800,15.000 54.800)))',4326) as geom) 
as pos join
            A on st_intersects(A.rast,pos.geom) join
            B on st_intersects(B.rast,pos.geom) ) as foo
where (AA).geom = (BB).geom
order by lon, lat;

As a side note I can tell that both rasters are equal in geographical sense (different bands from the same data).

Looking in 2 rasters takes 1.4 sec. and in one raster is 60ms

This is PostgreSQL 9.1.6, PostGIS 2.0.1

Thanks in advance!

Regards,

-- 
Kim Bisgaard

Application Development Division     Phone: +45 3915 7562 (direct)
Danish Meteorological Institute      Fax: +45 3915 7460 (division)