<div dir="ltr">
<div>Hi, <br></div><div><br></div><div>I am using pgr_dijkstra() to find the shortest path between origin and destination using a query like so: <br></div><div><br></div><div>
<pre class="gmail-m_-8662785027418076932gmail-lang-sql gmail-m_-8662785027418076932gmail-prettyprint gmail-m_-8662785027418076932gmail-prettyprinted"><code><span class="gmail-m_-8662785027418076932gmail-kwd">CREATE</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">OR</span><span class="gmail-m_-8662785027418076932gmail-pln"> REPLACE </span><span class="gmail-m_-8662785027418076932gmail-kwd">FUNCTION</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">public</span><span class="gmail-m_-8662785027418076932gmail-pun">.</span><span class="gmail-m_-8662785027418076932gmail-pln">sp_od</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">
    orig integer</span><span class="gmail-m_-8662785027418076932gmail-pun">,</span><span class="gmail-m_-8662785027418076932gmail-pln">
    dest integer</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln">
    RETURNS </span><span class="gmail-m_-8662785027418076932gmail-kwd">TABLE</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">shortest_path geometry</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> 
LANGUAGE </span><span class="gmail-m_-8662785027418076932gmail-str">'sql'</span><span class="gmail-m_-8662785027418076932gmail-pln">

</span><span class="gmail-m_-8662785027418076932gmail-kwd">AS</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-pun">$</span><span class="gmail-m_-8662785027418076932gmail-pln">BODY</span><span class="gmail-m_-8662785027418076932gmail-pun">$</span><span class="gmail-m_-8662785027418076932gmail-pln">   

</span><span class="gmail-m_-8662785027418076932gmail-kwd">SELECT</span><span class="gmail-m_-8662785027418076932gmail-pln"> st_makeline</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">geom</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">as</span><span class="gmail-m_-8662785027418076932gmail-pln"> shortest_path
</span><span class="gmail-m_-8662785027418076932gmail-kwd">FROM</span><span class="gmail-m_-8662785027418076932gmail-pln"> pgr_dijkstra</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">
    </span><span class="gmail-m_-8662785027418076932gmail-str">'SELECT id, source, target, st_length(geom, true) as cost FROM public."WA_roads"'</span><span class="gmail-m_-8662785027418076932gmail-pun">,</span><span class="gmail-m_-8662785027418076932gmail-pln">
</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-kwd">SELECT</span><span class="gmail-m_-8662785027418076932gmail-pln"> source </span><span class="gmail-m_-8662785027418076932gmail-kwd">FROM</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">public</span><span class="gmail-m_-8662785027418076932gmail-pun">.</span><span class="gmail-m_-8662785027418076932gmail-str">"WA_roads"</span><span class="gmail-m_-8662785027418076932gmail-pln">
 </span><span class="gmail-m_-8662785027418076932gmail-kwd">ORDER</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">BY</span><span class="gmail-m_-8662785027418076932gmail-pln"> ST_StartPoint</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">geom</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-pun"><-></span><span class="gmail-m_-8662785027418076932gmail-pln">
     </span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-kwd">select</span><span class="gmail-m_-8662785027418076932gmail-pln"> ST_SetSRID</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">ST_MakePoint</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">CAST</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">ocentx </span><span class="gmail-m_-8662785027418076932gmail-kwd">as</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">double</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">precision</span><span class="gmail-m_-8662785027418076932gmail-pun">),</span><span class="gmail-m_-8662785027418076932gmail-pln"> CAST</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">ocenty </span><span class="gmail-m_-8662785027418076932gmail-kwd">as</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">double</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">precision</span><span class="gmail-m_-8662785027418076932gmail-pun">)),</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-lit">4326</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">from</span><span class="gmail-m_-8662785027418076932gmail-pln"> all_trips_non_zero </span><span class="gmail-m_-8662785027418076932gmail-kwd">where</span><span class="gmail-m_-8662785027418076932gmail-pln"> origin </span><span class="gmail-m_-8662785027418076932gmail-pun">=</span><span class="gmail-m_-8662785027418076932gmail-pln"> orig LIMIT </span><span class="gmail-m_-8662785027418076932gmail-lit">1</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">ASC</span><span class="gmail-m_-8662785027418076932gmail-pln">
LIMIT </span><span class="gmail-m_-8662785027418076932gmail-lit">1</span><span class="gmail-m_-8662785027418076932gmail-pun">),</span><span class="gmail-m_-8662785027418076932gmail-pln">
</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-kwd">SELECT</span><span class="gmail-m_-8662785027418076932gmail-pln"> source </span><span class="gmail-m_-8662785027418076932gmail-kwd">FROM</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">public</span><span class="gmail-m_-8662785027418076932gmail-pun">.</span><span class="gmail-m_-8662785027418076932gmail-str">"WA_roads"</span><span class="gmail-m_-8662785027418076932gmail-pln">
 </span><span class="gmail-m_-8662785027418076932gmail-kwd">ORDER</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">BY</span><span class="gmail-m_-8662785027418076932gmail-pln"> ST_StartPoint</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">geom</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-pun"><-></span><span class="gmail-m_-8662785027418076932gmail-pln">
     </span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-kwd">select</span><span class="gmail-m_-8662785027418076932gmail-pln"> ST_SetSRID</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">ST_MakePoint</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">CAST</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">dcentx </span><span class="gmail-m_-8662785027418076932gmail-kwd">as</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">double</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">precision</span><span class="gmail-m_-8662785027418076932gmail-pun">),</span><span class="gmail-m_-8662785027418076932gmail-pln"> CAST</span><span class="gmail-m_-8662785027418076932gmail-pun">(</span><span class="gmail-m_-8662785027418076932gmail-pln">dcenty </span><span class="gmail-m_-8662785027418076932gmail-kwd">as</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">double</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">precision</span><span class="gmail-m_-8662785027418076932gmail-pun">)),</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-lit">4326</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">from</span><span class="gmail-m_-8662785027418076932gmail-pln"> all_trips_non_zero </span><span class="gmail-m_-8662785027418076932gmail-kwd">where</span><span class="gmail-m_-8662785027418076932gmail-pln"> destination </span><span class="gmail-m_-8662785027418076932gmail-pun">=</span><span class="gmail-m_-8662785027418076932gmail-pln"> dest LIMIT </span><span class="gmail-m_-8662785027418076932gmail-lit">1</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">ASC</span><span class="gmail-m_-8662785027418076932gmail-pln">
LIMIT </span><span class="gmail-m_-8662785027418076932gmail-lit">1</span><span class="gmail-m_-8662785027418076932gmail-pun">),</span><span class="gmail-m_-8662785027418076932gmail-pln"> directed </span><span class="gmail-m_-8662785027418076932gmail-pun">:=</span><span class="gmail-m_-8662785027418076932gmail-pln"> false
</span><span class="gmail-m_-8662785027418076932gmail-pun">)</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">as</span><span class="gmail-m_-8662785027418076932gmail-pln"> pt
</span><span class="gmail-m_-8662785027418076932gmail-kwd">JOIN</span><span class="gmail-m_-8662785027418076932gmail-pln"> </span><span class="gmail-m_-8662785027418076932gmail-kwd">public</span><span class="gmail-m_-8662785027418076932gmail-pun">.</span><span class="gmail-m_-8662785027418076932gmail-str">"WA_roads"</span><span class="gmail-m_-8662785027418076932gmail-pln"> rd </span><span class="gmail-m_-8662785027418076932gmail-kwd">ON</span><span class="gmail-m_-8662785027418076932gmail-pln"> pt</span><span class="gmail-m_-8662785027418076932gmail-pun">.</span><span class="gmail-m_-8662785027418076932gmail-pln">edge </span><span class="gmail-m_-8662785027418076932gmail-pun">=</span><span class="gmail-m_-8662785027418076932gmail-pln"> rd</span><span class="gmail-m_-8662785027418076932gmail-pun">.</span><span class="gmail-m_-8662785027418076932gmail-pln">id</span><span class="gmail-m_-8662785027418076932gmail-pun">;</span><span class="gmail-m_-8662785027418076932gmail-pln">

</span><span class="gmail-m_-8662785027418076932gmail-pun">$</span><span class="gmail-m_-8662785027418076932gmail-pln">BODY</span><span class="gmail-m_-8662785027418076932gmail-pun">$;<br><br></span></code></pre><pre class="gmail-m_-8662785027418076932gmail-lang-sql gmail-m_-8662785027418076932gmail-prettyprint gmail-m_-8662785027418076932gmail-prettyprinted"><code><span class="gmail-m_-8662785027418076932gmail-pun"><font face="arial,sans-serif">However, the returned result is wacky, as in the geometries are not stitched in order. I tried using ST_Union instead of ST_Makeline to make the line, which results in the correct geometry. However, the returned geometry is a multilinestring which is not amenable to the rest of my analysis. <br></font></span></code></pre><pre class="gmail-m_-8662785027418076932gmail-lang-sql gmail-m_-8662785027418076932gmail-prettyprint gmail-m_-8662785027418076932gmail-prettyprinted"><code><span class="gmail-m_-8662785027418076932gmail-pun"><font face="arial,sans-serif">The question and my attempts are detailed here: <a href="https://gis.stackexchange.com/questions/334302/pgr-dijkstra-gives-wacky-routes-sometimes-with-undirected-graph" target="_blank">https://gis.stackexchange.com/questions/334302/pgr-dijkstra-gives-wacky-routes-sometimes-with-undirected-graph</a>?<br></font></span></code></pre><pre class="gmail-m_-8662785027418076932gmail-lang-sql gmail-m_-8662785027418076932gmail-prettyprint gmail-m_-8662785027418076932gmail-prettyprinted"><code><span class="gmail-m_-8662785027418076932gmail-pun"><font face="arial,sans-serif">and <a href="https://gis.stackexchange.com/questions/335892/combining-the-output-of-st-collect-st-union-to-create-linestring-from-multilines" target="_blank">https://gis.stackexchange.com/questions/335892/combining-the-output-of-st-collect-st-union-to-create-linestring-from-multilines</a>? <br></font></span></code></pre><pre class="gmail-m_-8662785027418076932gmail-lang-sql gmail-m_-8662785027418076932gmail-prettyprint gmail-m_-8662785027418076932gmail-prettyprinted"><code><span class="gmail-m_-8662785027418076932gmail-pun"><font face="arial,sans-serif">The only "solution" to the problem seems to be reversing the edges that are not in the correct direction, as described here: <a href="https://lists.osgeo.org/pipermail/pgrouting-users/2010-April/000288.html">https://lists.osgeo.org/pipermail/pgrouting-users/2010-April/000288.html</a>  but this doesnt seem trivial. <br> <br>Any help in this regards is appreciated. <br><br></font></span></code></pre><pre class="gmail-m_-8662785027418076932gmail-lang-sql gmail-m_-8662785027418076932gmail-prettyprint gmail-m_-8662785027418076932gmail-prettyprinted"><code><span class="gmail-m_-8662785027418076932gmail-pun"><font face="arial,sans-serif">Thanks </font></span></code><br clear="all"></pre></div>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><table style="margin:0px;padding:0px;border:0px;outline:0px;font-size:16px;font-family:proxima-nova-1,proxima-nova-2,Tahoma,Helvetica,Verdana,sans-serif;vertical-align:baseline;border-spacing:0px;color:rgb(51,51,51);line-height:22.4px;text-align:center" cellspacing="0" cellpadding="0" border="0"><tbody style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><tr style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><td style="padding:0px;border:0px;outline:0px;font-style:inherit;font-size:0px;font-family:inherit;vertical-align:baseline;text-align:left;width:auto;height:20px"> </td></tr><tr style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><td style="padding:0px;border:0px;outline:0px;font-style:inherit;font-family:inherit;vertical-align:baseline;text-align:left;width:auto"><div style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;line-height:0"><a href="https://about.me/chintanpathak?promo=email_sig&utm_source=email_sig&utm_medium=email_sig&utm_campaign=external_links" style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;color:rgb(43,130,173);text-decoration:none;display:inline-block" target="_blank"><table style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;border-spacing:0px" cellspacing="0" cellpadding="0" border="0"><tbody style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><tr style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><td style="padding:0px;border:0px;outline:0px;font-style:inherit;font-family:inherit;vertical-align:top;width:auto;line-height:1" valign="top" align="left"><div style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:bold;font-style:inherit;font-size:18px;font-family:proxima-nova-1,Proxima-Nova,Helvetica,Arial,sans-serif;vertical-align:baseline;line-height:1;color:rgb(51,51,51)">Chintan Pathak</div></td></tr><tr style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><td style="padding:0px;border:0px;outline:0px;font-style:inherit;font-family:inherit;vertical-align:top;width:auto;line-height:1" valign="top" align="left"><div style="margin:3px 0px 0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-size:12px;font-family:proxima-nova-1,Proxima-Nova,Helvetica,Arial,sans-serif;vertical-align:baseline"><img alt="https://" style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;display:block;width:0px;height:0px;overflow:hidden" width="0" height="0">about.me/chintanpathak</div></td></tr></tbody></table></a></div></td></tr><tr style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline"><td style="padding:0px;border:0px;outline:0px;font-style:inherit;font-size:0px;font-family:inherit;vertical-align:baseline;text-align:left;width:auto;height:20px"><img src="https://about.me/t/sig?u=chintanpathak" style="margin:0px;padding:0px;border:0px;outline:0px;font-weight:inherit;font-style:inherit;font-family:inherit;vertical-align:baseline;overflow:hidden" width="1" height="1"></td></tr></tbody></table></div></div></div></div>