[postgis-users] Effectively combining rasters

[Bborie Park]

You may want to try pulling out the nested SELECTs into WITH blocks

WITH pos AS (
	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
), foo AS (
	SELECT	
		st_intersection(A.rast,1,pos.geom) AS AA,
		st_intersection(B.rast,1,pos.geom) AS BB
	FROM pos
	JOIN A
		ON st_intersects(A.rast,pos.geom)
	JOIN B
		ON st_intersects(B.rast,pos.geom)
)
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 foo
WHERE (AA).geom = (BB).geom
ORDER BY lon, lat;

That should eliminate the number of times the inner SELECTs are run.

-bborie

On 11/27/2012 05:16 AM, Kim Bisgaard wrote:
> 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,
> 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu