[postgis-users] Effectively combining rasters

[Kim Bisgaard]

Thanks Bborie,

That is an alternative solution, but it gives about the same runtime here, perhaps because I use PostgreSQL 9.1 and not 9.2.

"Sort  (cost=22.14..22.15 rows=1 width=64) (actual time=1402.745..1402.746 rows=14 loops=1)"
"  Sort Key: (st_x(st_centroid((foo.AAA).geom))), (st_y(st_centroid((foo.AAA).geom)))"
"  Sort Method: quicksort  Memory: 26kB"
"  CTE pos"
"    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)"
"  CTE foo"
"    ->  Nested Loop  (cost=0.01..22.09 rows=1 width=96) (actual time=101.003..1402.181 rows=210 loops=1)"
"          Join Filter: _st_intersects(B.rast, pos.geom, NULL::integer)"
"          ->  Nested Loop  (cost=0.00..10.81 rows=1 width=64) (actual time=0.198..0.203 rows=1 loops=1)"
"                Join Filter: _st_intersects(A.rast, pos.geom, NULL::integer)"
"                ->  CTE Scan on pos  (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1)"
"                ->  Index Scan using A_rast_gist on A (cost=0.00..8.27 rows=1 width=32) (actual time=0.022..0.024 rows=1 loops=1)"
"                      Index Cond: ((rast)::geometry && pos.geom)"
"          ->  Index Scan using B_rast_gist on B (cost=0.00..8.27 rows=1 width=32) (actual time=0.014..0.021 rows=1 loops=1)"
"                Index Cond: ((rast)::geometry && pos.geom)"
"  ->  CTE Scan on foo  (cost=0.00..0.03 rows=1 width=64) (actual time=101.041..1402.723 rows=14 loops=1)"
"        Filter: ((AA).geom = (BB).geom)"
"Total runtime: 1402.833 ms"

I will see if I can find a way to try it on a 9.2 ...

If there is no better idea as to reformulate the query? I will have to merge results in the program - with 60ms per raster it's faster to do 
in the program and not in the DB.

Thanks again,
Kim

On 2012-11-27 19:04, Bborie Park wrote:
> 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;


-- 
Kim Bisgaard

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