[postgis-users] Effectively combining rasters

[Bborie Park]

Hi Kim,

Can you post the query for the single raster and its EXPLAIN ANALYZE?
When posting an EXPLAIN ANALYZE, I've found that using
http://explain.depesz.com/ makes it more readable.

Looking at the EXPLAIN ANALYZE for the WITH query, most of the time is
spent on the "foo" query.  I'm guessing on the two ST_Intersections().

-bborie

On Wed, Nov 28, 2012 at 12:01 AM, Kim Bisgaard <kib at dmi.dk> wrote:
> 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)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



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