G'day all,
I am trying to pre-populate a routing table by creating a route from
the road segment nearest the centroid of each of 520 shires to any
other shire... oops, sounds awful, but potentially better than
creating too many on the fly...
So I thought I could create a multilinestring by getting all the line
segments, dumping and collecting them
so I created a query to find the start and end of the route, and
create the desired geometry.
Then I was going to run it on the entire table....
but looking at the query analysis (and I don't think I fully
understand them yet), there is an inexplicable gap between about 100
ms (see the second Limit group):
-> Limit (cost=41.85..41.85 rows=1 width=547)
(actual time=105.348..105.349 rows=1 loops=1)
and the final time of around 1700ms - is this because of time being
taken by the function?
-> Function Scan on dijkstra_sp_delta (cost=83.71..346.21
rows=1000 width=36) (actual time=1748.717..1753.921 rows=154 loops=1)
ie I don't really understand what happened between 105.349 and
1748.717? but I want to update a large number of rows, so any saving
would be great!
cheers
Ben
australia=# EXPLAIN ANALYZE
SELECT st_asEWKT(ST_Collect(rt.the_geom)) AS roadroute
FROM mrwa_net
JOIN
(SELECT gid, (ST_Dump(the_geom)).geom AS the_geom
FROM dijkstra_sp_delta(
'mrwa_net',
(SELECT m.source
FROM mrwa_net m JOIN tailtagarea r
ON st_dwithin(m.the_geom, r.c_geom, 2) AND r.shortcode like 'BE'
ORDER BY st_distance(m.the_geom, r.c_geom) limit 1),
(SELECT n.target
FROM mrwa_net n JOIN tailtagarea a
ON st_dwithin(n.the_geom, a.c_geom, 2) AND a.shortcode like 'GH'
ORDER BY st_distance(n.the_geom, a.c_geom) limit 1),
3000)
) as rt
ON mrwa_net.gid=rt.gid;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6079.39..6079.40 rows=1 width=32) (actual
time=1785.492..1785.492 rows=1 loops=1)
-> Nested Loop (cost=83.71..6076.89 rows=1000 width=32) (actual
time=1748.735..1755.042 rows=154 loops=1)
-> Function Scan on dijkstra_sp_delta (cost=83.71..346.21
rows=1000 width=36) (actual time=1748.717..1753.921 rows=154 loops=1)
InitPlan
-> Limit (cost=41.85..41.85 rows=1 width=547)
(actual time=11.887..11.887 rows=1 loops=1)
-> Sort (cost=41.85..41.85 rows=1 width=547)
(actual time=11.887..11.887 rows=1 loops=1)
Sort Key: (st_distance(m.the_geom,
r.c_geom))
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop (cost=0.00..41.84
rows=1 width=547) (actual time=0.171..11.313 rows=1124 loops=1)
Join Filter:
(_st_dwithin(m.the_geom, r.c_geom, 2::double precision) AND (r.c_geom
&& st_expand(m.the_geom, 2::double precision)))
-> Seq Scan on tailtagarea r
(cost=0.00..33.50 rows=1 width=100) (actual time=0.044..0.400 rows=1
loops=1)
Filter: ((shortcode)::text
~~ 'BE'::text)
-> Index Scan using idx_gist_geom
on mrwa_net m (cost=0.00..8.31 rows=1 width=447) (actual
time=0.104..6.116 rows=1134 loops=1)
Index Cond: (m.the_geom &&
st_expand(r.c_geom, 2::double precision))
Filter: (m.the_geom &&
st_expand(r.c_geom, 2::double precision))
-> Limit (cost=41.85..41.85 rows=1 width=547)
(actual time=105.348..105.349 rows=1 loops=1)
-> Sort (cost=41.85..41.85 rows=1 width=547)
(actual time=105.346..105.346 rows=1 loops=1)
Sort Key: (st_distance(n.the_geom,
a.c_geom))
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop (cost=0.00..41.84
rows=1 width=547) (actual time=0.152..101.115 rows=11536 loops=1)
Join Filter:
(_st_dwithin(n.the_geom, a.c_geom, 2::double precision) AND (a.c_geom
&& st_expand(n.the_geom, 2::double precision)))
-> Seq Scan on tailtagarea a
(cost=0.00..33.50 rows=1 width=100) (actual time=0.023..0.376 rows=2
loops=1)
Filter: ((shortcode)::text
~~ 'GH'::text)
-> Index Scan using idx_gist_geom
on mrwa_net n (cost=0.00..8.31 rows=1 width=447) (actual
time=0.141..30.874 rows=6848 loops=2)
Index Cond: (n.the_geom &&
st_expand(a.c_geom, 2::double precision))
Filter: (n.the_geom &&
st_expand(a.c_geom, 2::double precision))
-> Index Scan using mrwa_net_pkey on mrwa_net
(cost=0.00..5.71 rows=1 width=4) (actual time=0.005..0.006 rows=1
loops=154)
Index Cond: (mrwa_net.gid = dijkstra_sp_delta.gid)
Total runtime: 1786.102 ms
(29 rows)
--
Ben Madin
REMOTE INFORMATION
t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome WA 6725
[email protected]
Out here, it pays to
know...
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
