<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=utf-8">
<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:Tahoma;
panose-1:2 11 6 4 3 5 4 4 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:12.0pt;
font-family:"Times New Roman","serif";}
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;}
code
{mso-style-priority:99;
font-family:"Courier New";}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Century Gothic","sans-serif";
color:#1F497D;
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";color:#1F497D">Hello Yang Zhang<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D">There are some good resources on anitagraser.com and this post shows how to associate a point (in this case airports) to the nearest network node and
then solve the routing problem: <a href="http://anitagraser.com/2011/02/12/drive-time-isochrones/">
http://anitagraser.com/2011/02/12/drive-time-isochrones/</a><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D">The first bit of SQL will do what you want, I think.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"">ALTER</span>
<span style="font-size:10.0pt;font-family:"Courier New"">TABLE</span> <span style="font-size:10.0pt;font-family:"Courier New"">
airport</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> ADD</span>
<span style="font-size:10.0pt;font-family:"Courier New"">COLUMN</span> <span style="font-size:10.0pt;font-family:"Courier New"">
nearest_node integer;</span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"">CREATE</span>
<span style="font-size:10.0pt;font-family:"Courier New"">TABLE</span> <span style="font-size:10.0pt;font-family:"Courier New"">
temp</span> <span style="font-size:10.0pt;font-family:"Courier New"">AS</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> SELECT</span>
<span style="font-size:10.0pt;font-family:"Courier New"">a.gid, b.id, min(a.dist)</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> FROM</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> (SELECT</span>
<span style="font-size:10.0pt;font-family:"Courier New"">airport.gid, </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> min(distance(airport.the_geom, node.the_geom)) AS</span>
<span style="font-size:10.0pt;font-family:"Courier New"">dist</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> FROM</span>
<span style="font-size:10.0pt;font-family:"Courier New"">airport, node</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> GROUP</span>
<span style="font-size:10.0pt;font-family:"Courier New"">BY</span> <span style="font-size:10.0pt;font-family:"Courier New"">
airport.gid) AS</span> <span style="font-size:10.0pt;font-family:"Courier New"">a,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> (SELECT</span>
<span style="font-size:10.0pt;font-family:"Courier New"">airport.gid, node.id, </span>
<o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> distance(airport.the_geom, node.the_geom) AS</span>
<span style="font-size:10.0pt;font-family:"Courier New"">dist</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> FROM</span>
<span style="font-size:10.0pt;font-family:"Courier New"">airport, node) AS</span>
<span style="font-size:10.0pt;font-family:"Courier New"">b</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> WHERE</span>
<span style="font-size:10.0pt;font-family:"Courier New"">a.dist = b. dist</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> AND</span>
<span style="font-size:10.0pt;font-family:"Courier New"">a.gid = b.gid</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> GROUP</span>
<span style="font-size:10.0pt;font-family:"Courier New"">BY</span> <span style="font-size:10.0pt;font-family:"Courier New"">
a.gid, b.id;</span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"">UPDATE</span>
<span style="font-size:10.0pt;font-family:"Courier New"">airport</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"">SET</span>
<span style="font-size:10.0pt;font-family:"Courier New"">nearest_node = </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> (SELECT</span>
<span style="font-size:10.0pt;font-family:"Courier New"">id </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> FROM</span>
<span style="font-size:10.0pt;font-family:"Courier New"">temp</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> WHERE</span>
<span style="font-size:10.0pt;font-family:"Courier New"">temp.gid = airport.gid);</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D">Hope that helps<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D">Ross<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228">Ross McDonald
</span></b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228">|<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">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"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> pgrouting-users-bounces@lists.osgeo.org [mailto:pgrouting-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Yang Zhang<br>
<b>Sent:</b> 22 July 2015 23:51<br>
<b>To:</b> pgrouting-users@lists.osgeo.org<br>
<b>Subject:</b> [pgrouting-users] pgrouting Dijkstra: non-vertex data routing<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt">Hello everyone,</span><o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I have some data samples, they are some traffic accident locations (have shape files) I really want to use the dijkstra function of pgrouting to look into their shortest distances between each other. However, I noticed that dijkstra requires
all the points need to be the vertices of a topology. But obviously my data points are not the vertices of the road network. Is there any way to integrate my data points to the road network and make them vertices too? What is the good approach to route non-vertex
data like this? Thank you. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#888888">-- <o:p></o:p></span></p>
<div>
<div>
<p class="MsoNormal"><span style="color:#888888">Yang Zhang<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="color:#888888"><o:p> </o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</div>
<div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
</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>