<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style>
</head>
<body class='hmmessage'><div dir='ltr'>
hi Daniel,<br><br>it was not working on both servers.<br><br>looks like it's ok now.<br><br>thanks!<br><br><br>EJ<br><br><div><hr id="stopSpelling">From: daniel@georepublic.de<br>Date: Mon, 11 Jul 2011 17:01:24 +0900<br>Subject: Re: [pgrouting-users] server closed the connection unexpectedly<br>To: pgrouting-users@lists.osgeo.org<br><br><br><br><div class="ecxgmail_quote">On Mon, Jul 11, 2011 at 4:14 PM, E. . <span dir="ltr">&lt;<a href="mailto:thnxfernuttin@hotmail.com">thnxfernuttin@hotmail.com</a>&gt;</span> wrote:<br><blockquote class="ecxgmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">





<div><div dir="ltr">
hello Steve,<br><br>thanks for your swift reply!<br><br>i am running the following versions:<br>postgres 8.3.3<br>postgis 1.3.3<br>pgrouting 1.03<br><br>on another server we have<br>postgres 8.4<br>postgis 1.5.1<br>pgrouting 1.03<br>

</div></div></blockquote><div><br></div><div>Hi, which is the server that works and which one that doesn't?</div><div>Have you copied data from on to the other?</div><div><br></div><div>vertices_tmp table is not needed, but you need a valid network. It's strange that you can't find assign_vertex_id function. Maybe you made a database dump and copied it to the other server?</div>

<div><br></div><div>Daniel</div><div><br></div><div><br></div><div>&nbsp;</div><blockquote class="ecxgmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div><div dir="ltr"><br>but this does not help.<br>

<br>we do not use OSM data, and we have no tmp_vertices table.<br><br><br><br><div>&gt; Date: Sun, 10 Jul 2011 12:52:43 -0400<br>&gt; From: <a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a><br>

&gt; To: <a href="mailto:pgrouting-users@lists.osgeo.org">pgrouting-users@lists.osgeo.org</a><br>&gt; Subject: Re: [pgrouting-users] server closed the connection unexpectedly<div><div></div><div class="h5">

<br>&gt; <br>&gt; Hi EJ,<br>&gt; <br>&gt; A few questions:<br>&gt; <br>&gt; what version of postgresql, postgis, pgrouting are you running?<br>&gt; <br>&gt; what do thess querys report?<br>&gt; <br>&gt; select count(*) from ma_routing;<br>

&gt; <br>&gt; select count(*) from ma_routing<br>&gt;   where the_geom &amp;&amp; st_expand(makeline(<br>&gt;      (select the_geom from vertices_tmp where id=64629654),<br>&gt;      (select the_geom from vertices_tmp where id=64630762)), 0.1);<br>

&gt; <br>&gt; <br>&gt; this is building a graph of the whole database instead of a smaller bbox <br>&gt; of data about your source and target nodes. Does this crash?<br>&gt; <br>&gt; SELECT * FROM shortest_path('SELECT id, source::integer, <br>

&gt; target::integer, cost::double precision FROM ma_routing where the_geom <br>&gt; &amp;&amp; st_expand(makeline((select the_geom from vertices_tmp where <br>&gt; id=64629654),(select the_geom from vertices_tmp where id=64630762)), <br>

&gt; 0.1)', 64629654, 64630762, false, false);<br>&gt; <br>&gt; -Steve<br>&gt; <br>&gt; On 7/10/2011 12:15 PM, E. . wrote:<br>&gt; &gt; Hi all,<br>&gt; &gt;<br>&gt; &gt; i really need your help.<br>&gt; &gt;<br>&gt; &gt; While running a shortest_path query i had the following error message:<br>

&gt; &gt;<br>&gt; &gt; SELECT * FROM shortest_path('SELECT id, source::integer,<br>&gt; &gt; target::integer, cost::double precision FROM ma_routing', 64629654,<br>&gt; &gt; 64630762, false,<br>&gt; &gt; false)<br>

&gt; &gt;<br>&gt; &gt; " server closed the connection unexpectedly".<br>&gt; &gt;<br>&gt; &gt; I do not have negative cost or reverse_cost values. In another thread a<br>&gt; &gt; long time ago it was advised to rebuild the "source" and "target" tables<br>

&gt; &gt; using "assign_vertex_id".<br>&gt; &gt;<br>&gt; &gt; The posting is at:<br>&gt; &gt; <a href="http://postgis.refractions.net/pipermail/postgis-users/2008-June/020137.html" target="_blank">http://postgis.refractions.net/pipermail/postgis-users/2008-June/020137.html</a><br>

&gt; &gt;<br>&gt; &gt; That function i cannot find.<br>&gt; &gt;<br>&gt; &gt; For the following samples goes:<br>&gt; &gt; Projection _should_ be in 4326.<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; Here's a sample of the data in the routing table:<br>

&gt; &gt;<br>&gt; &gt; select * from ma_routing limit 3;<br>&gt; &gt;<br>&gt; &gt; source | target | cost | reverse_cost | sens | x1 | y1 | x2 | y2 | rule<br>&gt; &gt; | to_cost | length | ogc_fid | id | length_shortest |<br>

&gt; &gt; length_reverse_shortest<br>&gt; &gt; ----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+----+-----------------+-------------------------<br>

&gt; &gt; 64629390 | 64629391 | 2.98541825999812 | 2.98541825999812 | double sens<br>&gt; &gt; | -73.167897 | 42.705496 | -73.167836 | 42.705791 | | | 33.1713139999791<br>&gt; &gt; | 582 | 1 | |<br>&gt; &gt; 64629654 | 64630762 | 15.4108990193286 | 15.4108990193286 | double sens<br>

&gt; &gt; | -73.115287 | 42.646935 | -73.113203 | 42.647023 | | | 171.232211325874<br>&gt; &gt; | 718 | 2 | |<br>&gt; &gt; 64629691 | 64629692 | 1.03336877316695 | 1.03336877316695 | double sens<br>&gt; &gt; | -73.047407 | 42.648043 | -73.047296 | 42.648106 | | | 11.4818752574105<br>

&gt; &gt; | 738 | 3 | |<br>&gt; &gt; (3 rows)<br>&gt; &gt;<br>&gt; &gt; OK, let's take line 2: source=64629654 , target=64630762.<br>&gt; &gt; Let's do a lookup where the source is the target of line 2, to go from<br>

&gt; &gt; source to target:<br>&gt; &gt;<br>&gt; &gt; select * from ma_routing where source=64630762;<br>&gt; &gt;<br>&gt; &gt; source | target | cost | reverse_cost | sens | x1 | y1 | x2 | y2 | rule<br>&gt; &gt; | to_cost | length | ogc_fid | id | length_shortest |<br>

&gt; &gt; length_reverse_shortest<br>&gt; &gt; ----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+-------+-----------------+-------------------------<br>

&gt; &gt; 64630762 | 64629653 | 10.5842324315852 | 10.5842324315852 | double sens<br>&gt; &gt; | -73.115501 | 42.647023 | -73.115287 | 42.648065 | | | 117.602582573169<br>&gt; &gt; | 1502 | 5692 | |<br>&gt; &gt; 64630762 | 64630760 | 10.324871753826 | 10.324871753826 | double sens |<br>

&gt; &gt; -73.115287 | 42.646049 | -73.114822 | 42.647023 | | | 114.720797264734 |<br>&gt; &gt; 7776 | 11879 | |<br>&gt; &gt; (2 rows)<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; Maybe i am overlooking something.<br>&gt; &gt; The geometry column is in another table.<br>

&gt; &gt;<br>&gt; &gt; Roads table is called "027_nosr_r".<br>&gt; &gt;<br>&gt; &gt; Sorry if this is going to be confusing but i want to get the same rows<br>&gt; &gt; from the "roads" table (which is the origin) as from the routing table.<br>

&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; Here's a sample of the roads table:<br>&gt; &gt;<br>&gt; &gt; select * from "027_nosr_r" limit 3;<br>&gt; &gt;<br>&gt; &gt; ogc_fid | wkb_geometry | fnode_ | tnode_ | lpoly_ | rpoly_ | length |<br>

&gt; &gt; reg25_ | rd_1 | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |<br>&gt; &gt; rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 | rd_17 | rd_18 |<br>&gt; &gt; rd_19 | rd_20 | rd_21 | rd_22 | rd_23 | rd_24 | rd_25 | rd_26 | rd_27 |<br>

&gt; &gt; rd_28 | textsearchable_index_col<br>&gt; &gt; ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>

&gt; &gt; 582 |<br>&gt; &gt; 0102000020E6100000030000002D060FD3BE4A52C0BABA63B14D5A4540B0FECF61BE4A52C0ADA415DF505A4540DAC534D3BD4A52C0D367075C575A4540<br>&gt; &gt; | 64629390 | 64629391 | 206361314 | 206361314 | 0.000000 | 3915987 |<br>

&gt; &gt; 3915987 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | 1 | | | | | | | | 0 | 0 | 0 | 0<br>&gt; &gt; | 0 | 0 | fc#4 | 25003 | 0 | 2500346225 | | |<br>&gt; &gt; 718 |<br>&gt; &gt; 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540<br>

&gt; &gt; | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 |<br>&gt; &gt; 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 |<br>&gt; &gt; 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220<br>

&gt; &gt; | 01220 | 'st':2 'brown':1<br>&gt; &gt; 738 |<br>&gt; &gt; 0102000020E6100000020000007FA65EB7084352C06CB3B112F3524540D34ECDE5064352C0B22B2D23F5524540<br>&gt; &gt; | 64629691 | 64629692 | 206354450 | 206354451 | 0.000000 | 3916317 |<br>

&gt; &gt; 3916317 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | | | | | | | | 0 | 0 | 0 | 0 |<br>&gt; &gt; 0 | 0 | fc#4 | 25003 | 0 | 2500360225 | | |<br>&gt; &gt; (3 rows)<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; Let's take 64629654 as fnode_, which is the "source column" of the road,<br>

&gt; &gt; like in line 2 of the first sql, source=64629654 , target=64630762.<br>&gt; &gt;<br>&gt; &gt; select * from "027_nosr_r" where fnode_=64629654 ;<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; ogc_fid | wkb_geometry | fnode_ | tnode_ | lpoly_ | rpoly_ | length |<br>

&gt; &gt; reg25_ | rd_1 | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |<br>&gt; &gt; rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 | rd_17 | rd_18 |<br>&gt; &gt; rd_19 | rd_20 | rd_21 | rd_22 | rd_23 | rd_24 | rd_25 | rd_26 | rd_27 |<br>

&gt; &gt; rd_28 | textsearchable_index_col<br>&gt; &gt; ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>

&gt; &gt; 718 |<br>&gt; &gt; 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540<br>&gt; &gt; | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 |<br>

&gt; &gt; 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 |<br>&gt; &gt; 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220<br>&gt; &gt; | 01220 | 'st':2 'brown':1<br>

&gt; &gt; (1 row)<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; OK, let's consider line 2 of the routing table: source=64629654 ,<br>&gt; &gt; target=64630762. Same second sample above, only this time from the<br>&gt; &gt; "roads" table, not the routing table.<br>

&gt; &gt; Let's do a lookup where the source is the target of line 2, to go from<br>&gt; &gt; source to target:<br>&gt; &gt;<br>&gt; &gt; tnode_="target":<br>&gt; &gt;<br>&gt; &gt; select * from "027_nosr_r" where fnode_=64629654 limit 3;<br>

&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; ogc_fid | wkb_geometry | fnode_ | tnode_ | lpoly_ | rpoly_ | length |<br>&gt; &gt; reg25_ | rd_1 | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |<br>&gt; &gt; rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 | rd_17 | rd_18 |<br>

&gt; &gt; rd_19 | rd_20 | rd_21 | rd_22 | rd_23 | rd_24 | rd_25 | rd_26 | rd_27 |<br>&gt; &gt; rd_28 | textsearchable_index_col<br>&gt; &gt; ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>

&gt; &gt; 718 |<br>&gt; &gt; 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540<br>&gt; &gt; | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 |<br>

&gt; &gt; 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 |<br>&gt; &gt; 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220<br>&gt; &gt; | 01220 | 'st':2 'brown':1<br>

&gt; &gt; (1 row)<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; The thing is, this setup works in another DB (with data from a another<br>&gt; &gt; country).<br>&gt; &gt;<br>&gt; &gt; Any help would be greatly appreciated, i've been looking for days.<br>

&gt; &gt;<br>&gt; &gt; If needed i will attach some sql to create the routing table and sample<br>&gt; &gt; data. This would be an attachment of course, should that be posted at<br>&gt; &gt; <a href="http://pastebin.com" target="_blank">pastebin.com</a>?<br>

&gt; &gt;<br>&gt; &gt; Sorry if this post is way off, i'm not sure about pasting and formatting.<br>&gt; &gt;<br>&gt; &gt; Thanks for your time,<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; EJ<br>&gt; &gt;<br>
&gt; &gt;<br>
&gt; &gt;<br>&gt; &gt;<br>&gt; &gt; _______________________________________________<br>&gt; &gt; Pgrouting-users mailing list<br>&gt; &gt; <a href="mailto:Pgrouting-users@lists.osgeo.org">Pgrouting-users@lists.osgeo.org</a><br>

&gt; &gt; <a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>&gt; <br>&gt; _______________________________________________<br>
&gt; Pgrouting-users mailing list<br>
&gt; <a href="mailto:Pgrouting-users@lists.osgeo.org">Pgrouting-users@lists.osgeo.org</a><br>&gt; <a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>

</div></div></div>                                               </div></div>
<br>_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org">Pgrouting-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br><span style="font-family: arial,sans-serif; font-size: 13px; border-collapse: collapse;">Georepublic UG &amp; Georepublic Japan<br>eMail:&nbsp;<a href="mailto:daniel.kastl@georepublic.de" style="color: rgb(66, 99, 171);">daniel.kastl@georepublic.de</a><br>

Web:&nbsp;<a href="http://georepublic.de/" style="color: rgb(66, 99, 171);" target="_blank">http://georepublic.de</a></span><br>
<br>_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users</div>                                               </div></body>
</html>