[postgis-users] Effectively combining rasters

[Kim Bisgaard]

Hi Pierre,

That is a good question! I haven tried because of technicalities like keeping track of band number and content.

I just found that I get good performance by using a query structured like:
with raster1 as (single raster select),
     raster2 as (single raster select)
select lat, lon, raster1.val, raster2.val
from raster1 join raster2 using (lat,lon)

Thanks!

Best regards,
Kim


On 2012-11-28 19:33, Pierre Racine wrote:
> What if you store the two bands in the same raster?
>
> Pierre
>
>> -----Original Message-----
>> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
>> bounces at lists.osgeo.org] On Behalf Of Kim Bisgaard
>> Sent: Tuesday, November 27, 2012 8:17 AM
>> To: postgis-users at postgis.refractions.net
>> Subject: [postgis-users] Effectively combining rasters
>>
>> 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)
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>


-- 
Kim Bisgaard

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