<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Century Gothic";
panose-1:2 11 5 2 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Century Gothic","sans-serif";
color:windowtext;
font-weight:normal;
font-style:normal;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Hello list<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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?”<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">The queries<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Without bounding box ~ 1m20s<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT gid AS id,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> source::integer,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> target::integer,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> cost_len::double precision AS cost,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> rcost_len::double precision AS reverse_cost<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM or_network',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 1190869, 586365, false, true);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">With a bounding box ~ 200ms<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> SELECT gid AS id,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> source::integer,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> target::integer,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> cost_len::double precision AS cost,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> rcost_len::double precision AS reverse_cost<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FROM or_network<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> WHERE geometry && ST_Expand(<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> (SELECT ST_Collect(the_geom) FROM or_network_vertices_pgr WHERE id IN (1190869, 586365)),2000)',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 1190869, 586365, false, true);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Thanks in advance<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Ross<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">Ross McDonald
</span></b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">|<b>
</b>GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">T:
<b>01307 476419</b> | F: 01307 476401 | E: <a href="mailto:mcdonaldr@angus.gov.uk">
<span style="color:#4F6228">mcdonaldr@angus.gov.uk</span></a> <b><o:p></o:p></b></span></p>
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div> </div>
<div>
<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><span style="FONT-SIZE: 7.5pt; FONT-FAMILY: 'Arial','sans-serif'; mso-bidi-font-size: 8.0pt; mso-bidi-font-family: 'Times New Roman'"><font size="2">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.<span style="mso-spacerun: yes">
</span>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.
</font></span></p>
</div>
</body>
</html>