[postgis-users] Effectively combining rasters
Bborie Park
bkpark at ucdavis.edu
Thu Nov 29 07:34:58 PST 2012
Wow. How strange. I wonder what was the bottleneck that somehow an
"uglier" query goes around...
Yay to faster queries!
-bborie
On Thu, Nov 29, 2012 at 12:37 AM, Kim Bisgaard <kib at dmi.dk> wrote:
> 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)
> _______________________________________________
> 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
More information about the postgis-users
mailing list