[postgis-users] Effectively combining rasters
Kim Bisgaard
kib at dmi.dk
Thu Nov 29 00:37:39 PST 2012
Hi Bborie,
I just had an idea - if I use the two single raster queries as sub-select and join them I get the same result (time-wise) but using a "with"
construct like this:
with bar as (select st_x(st_centroid((d52_2012112701_4).geom)) as lon,
st_y(st_centroid((d52_2012112701_4).geom)) as lat,
(d52_2012112701_4).val as "current_dir"
from (select st_intersection(d52_2012112701_4.rast,1,pos.geom) as d52_2012112701_4
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
d52_2012112701_4 on st_intersects(d52_2012112701_4.rast,pos.geom) )bar),
foo as (select st_x(st_centroid((d52_2012112701_5).geom)) as lon,
st_y(st_centroid((d52_2012112701_5).geom)) as lat,
(d52_2012112701_5).val as "current_speed"
from (select st_intersection(d52_2012112701_5.rast,1,pos.geom) as d52_2012112701_5
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
d52_2012112701_5 on st_intersects(d52_2012112701_5.rast,pos.geom) ) foo)
select bar.lon, bar.lat, bar."current_dir", foo."current_speed"
from bar join foo using (lat,lon);
the explain looks like this (at depesz 2012-11-29 as zV6):
Nested Loop (cost=22.15..22.21 rows=1 width=32) (actual time=45.005..45.400 rows=11 loops=1)
Join Filter: ((bar.lat = foo.lat) AND (bar.lon = foo.lon))
CTE bar
-> Subquery Scan on bar (cost=0.00..11.08 rows=1 width=32) (actual time=23.099..23.303 rows=15 loops=1)
-> Nested Loop (cost=0.00..11.06 rows=1 width=64) (actual time=23.068..23.097 rows=15 loops=1)
Join Filter: _st_intersects(d52_2012112701_4.rast,
('0106000020E610000001000000010300000001000000050000000000000000002E406666666666664B400000000000
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
-> Index Scan using d52_2012112701_4_rast_gist on d52_2012112701_4 (cost=0.00..8.27 rows=1 width=32) (actual
time=0.048..0.050 rows=1 loops=1)
Index Cond: ((rast)::geometry &&
('0106000020E610000001000000010300000001000000050000000000000000002E406666666666664B400000000000002E40000000
CTE foo
-> Subquery Scan on foo (cost=0.00..11.08 rows=1 width=32) (actual time=21.893..22.004 rows=13 loops=1)
-> Nested Loop (cost=0.00..11.06 rows=1 width=64) (actual time=21.861..21.886 rows=13 loops=1)
Join Filter: _st_intersects(d52_2012112701_5.rast,
('0106000020E610000001000000010300000001000000050000000000000000002E406666666666664B400000000000
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
-> Index Scan using d52_2012112701_5_rast_gist on d52_2012112701_5 (cost=0.00..8.27 rows=1 width=32) (actual
time=0.031..0.035 rows=1 loops=1)
Index Cond: ((rast)::geometry &&
('0106000020E610000001000000010300000001000000050000000000000000002E406666666666664B400000000000002E40000000
-> CTE Scan on bar (cost=0.00..0.02 rows=1 width=24) (actual time=23.103..23.315 rows=15 loops=1)
-> CTE Scan on foo (cost=0.00..0.02 rows=1 width=24) (actual time=1.460..1.469 rows=13 loops=15)
Total runtime: 45.477 ms
Look at the beautiful 45 ms at the bottom!!
Thanks for helping me through this!
Best Regards,
Kim
On 2012-11-28 16:50, Bborie Park wrote:
> 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
>
>
--
Kim Bisgaard
Application Development Division Phone: +45 3915 7562 (direct)
Danish Meteorological Institute Fax: +45 3915 7460 (division)
More information about the postgis-users
mailing list