<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=ProgId content=Word.Document>
<meta name=Generator content="Microsoft Word 10">
<meta name=Originator content="Microsoft Word 10">
<link rel=File-List href="cid:filelist.xml@01C63DD7.296D4DD0">
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:DoNotRelyOnCSS/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:SpellingState>Clean</w:SpellingState>
<w:GrammarState>Clean</w:GrammarState>
<w:DocumentKind>DocumentEmail</w:DocumentKind>
<w:EnvelopeVis/>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
</w:WordDocument>
</xml><![endif]-->
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:"";
margin:0in;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;
text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;
text-underline:single;}
pre
{margin:0in;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Courier New";
mso-fareast-font-family:"Times New Roman";}
span.EmailStyle17
{mso-style-type:personal-compose;
mso-style-noshow:yes;
mso-ansi-font-size:10.0pt;
mso-bidi-font-size:10.0pt;
font-family:Arial;
mso-ascii-font-family:Arial;
mso-hansi-font-family:Arial;
mso-bidi-font-family:Arial;
color:windowtext;}
span.SpellE
{mso-style-name:"";
mso-spl-e:yes;}
span.GramE
{mso-style-name:"";
mso-gram-e:yes;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
mso-paper-source:0;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";}
</style>
<![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple style='tab-interval:.5in'>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi Franck:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<pre><font size=2 face="Courier New"><span style='font-size:10.0pt'>Hi Franck:<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>Thank you very much. It does help me lot.<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>The node ID is pre-assigned to each town. I have dynamic points, which are from GPS mounted in cars. They must get node ID on the fly. It is why I want some quick method. I wonder except <span
class=SpellE>pgdijkstra</span> module, is there any other open source routing program exist? In my application, we must use our own data as <span
class=GramE>clients</span> requirement. <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>Fay<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'><o:p> </o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>Hi Fay,<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>I think that this is the case for the <span
class=SpellE>demo_routing</span> data: the towns (town table) geometry are ><span
class=SpellE>independant</span> from the roads (<span class=SpellE>roads_<span
class=GramE>europe</span></span><span class=GramE><span style='mso-spacerun:yes'> </span>table</span>) ones.<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>On my side, I tried to replay the procedure given in the manual, section 1.2.4.2. Some steps are not ><span
class=SpellE>detailled</span> and I had to search the nearest road start geometry for each town in order to create the >txt field of the <span
class=SpellE>roads_europe_vertices</span> table. To get most of the town listed, I had to go up to >2000m, and then get the lowest distance for a given town.<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>To make it short, the SQL script looks <span
class=GramE>like :</span><o:p></o:p></span></font></pre><pre><font size=2
face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>...<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>CREATE TABLE <span
class=SpellE>roads_source_town</span> AS<o:p></o:p></span></font></pre><pre><span
class=GramE><font size=2 face="Courier New"><span style='font-size:10.0pt'>><span style='mso-spacerun:yes'> </span>select</span></font></span> distinct t.txt, <span
class=SpellE>source_id</span>, distance(<span class=SpellE>PointN</span>(r.the_geom,1) , <span
class=SpellE>t.the_geom</span>) as d from ><span class=SpellE>roads_europe</span> r, town t, <span
class=SpellE>roads_europe_vertices</span> <o:p></o:p></pre><pre><font size=2
face="Courier New"><span style='font-size:10.0pt'>><span style='mso-spacerun:yes'> </span><span
class=GramE>where</span> t.txt != 'UNK' AND distance(<span class=SpellE>PointN</span>(r.the_geom,1) , <span
class=SpellE>t.the_geom</span>) < 2000 AND <span class=SpellE>geom_id</span> = ><span
class=SpellE>source_id</span> ORDER BY t.txt , d ; <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>CREATE TABLE <span
class=SpellE>roads_source_town_uniq</span> AS<o:p></o:p></span></font></pre><pre><span
class=GramE><font size=2 face="Courier New"><span style='font-size:10.0pt'>><span style='mso-spacerun:yes'> </span>select</span></font></span> * from <span
class=SpellE>roads_source_town</span> a where a.txt IN (select b.txt from <span
class=SpellE>roads_source_town</span> b where >a.txt = b.txt<span style='mso-spacerun:yes'> </span>limit 1) AND <span
class=SpellE>a.d</span> IN (select <span class=SpellE>b.d</span> from <span
class=SpellE>roads_source_town</span> b where a.txt = b.txt<span style='mso-spacerun:yes'> </span>limit >1);<o:p></o:p></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>UPDATE <span
class=SpellE>roads_europe_vertices</span> set txt = (select distinct <span
class=GramE>txt<span style='mso-spacerun:yes'> </span>from</span> <span
class=SpellE>roads_source_town_uniq</span><span style='mso-spacerun:yes'> </span>where ><span
class=SpellE>roads_europe_vertices.geom_id</span> = <span class=SpellE>roads_source_town_uniq.source_id</span>);<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>...<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>I am not sure this is the solution done to build the <span
class=SpellE>demo_routing.sql</span> database, and it is not >optimized, but it works. <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'><span style='mso-spacerun:yes'> </span><o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>Also it should be interesting to take in account the whole line geometry of the roads instead of >just the initial point (<span
class=SpellE><span class=GramE>PointN</span></span><span class=GramE>(</span>r.the_geom,1))...<span style='mso-spacerun:yes'> </span>but this may imply to cut some roads in several ><span
class=SpellE>source_id/target_id</span>...<o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'> <o:p></o:p></span></font></pre><pre><font
size=2 face="Courier New"><span style='font-size:10.0pt'>>Franck<o:p></o:p></span></font></pre>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
</div>
</body>
</html>