Hello list
I have a question about how QGIS (specifically the pgRouting Layer plugin) uses
psycopg2 and a connection to PostgreSQL to run a query and return a result.
I am using pgRouting with Ordnance Survey's Open Roads network for the UK. It
is a large network with over 3 million links and 2.6 million nodes. Using the
pgRouting Layer plugin in QGIS 2.6.1 and 2.8.2 to interact with the network.
It is really slow to solve routes ~ 1m20s per solution. I am also using
PgAdmin3 to run the same queries against the same network and they are also
slow (~1m20s) but I can speed them up using a bounding box in the query (in
some cases by 400x!). In the pgRouting Layer plugin I have updated the
djikstra.py file to use the query with a bounding box but the result is still
returned in ~ 1m20s. Running the same query in the SQL pane in DB Manager
returns the result in ~ 100ms.
So, I guess the question is "Is psycopg2 (or python) the bottleneck in running
queries in the pgRouting Layer plugin?" and maybe a secondary question is "how
does DB Manager connect to the database and run the query so quickly?"
The queries
Without bounding box ~ 1m20s
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
SELECT gid AS id,
source::integer,
target::integer,
cost_len::double precision AS cost,
rcost_len::double precision AS reverse_cost
FROM or_network',
1190869, 586365, false, true);
With a bounding box ~ 200ms
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
SELECT gid AS id,
source::integer,
target::integer,
cost_len::double precision AS cost,
rcost_len::double precision AS reverse_cost
FROM or_network
WHERE geometry && ST_Expand(
(SELECT ST_Collect(the_geom) FROM
or_network_vertices_pgr WHERE id IN (1190869, 586365)),2000)',
1190869, 586365, false, true);
Thanks in advance
Ross
Ross McDonald | GIS Data Coordinator | Resources Department, IT Division |
Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT
T: 01307 476419 | F: 01307 476401 | E:
mcdona...@angus.gov.uk<mailto:mcdona...@angus.gov.uk>
This message is strictly confidential. If you have received this in error,
please inform the sender and remove it from your system. If received in error
you may not copy, print, forward or use it or any attachment in any way. This
message is not capable of creating a legal contract or a binding representation
and does not represent the views of Angus Council. Emails may be monitored for
security and network management reasons. Messages containing inappropriate
content may be intercepted. Angus Council does not accept any liability for any
harm that may be caused to the recipient system or data on it by this message
or any attachment.
_______________________________________________
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer
