[postgis-users] Effectively combining rasters
kib at dmi.dk
Tue Nov 27 05:16:31 PST 2012
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!
Application Development Division Phone: +45 3915 7562 (direct)
Danish Meteorological Institute Fax: +45 3915 7460 (division)
More information about the postgis-users