<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 list,<br><br>looks like it's fixed. i don't get many results, but there's no more crashes.<br><br>after running assign_vertex_id the source/target tabel was rebuilt correctly.<br><br>only problem was that i could not find the function.<br>turned out to be in routing_topology.sql.<br><br>thanks,<br><br>EJ<br><br><br><br><br><br><div><hr id="stopSpelling">From: thnxfernuttin@hotmail.com<br>To: pgrouting-users@lists.osgeo.org<br>Subject: RE: [pgrouting-users] server closed the connection unexpectedly<br>Date: Mon, 11 Jul 2011 07:14:35 +0000<br><br>
<meta http-equiv="Content-Type" content="text/html; charset=unicode">
<meta name="Generator" content="Microsoft SafeHTML">
<style>
.ExternalClass .ecxhmmessage P
{padding:0px;}
.ExternalClass body.ecxhmmessage
{font-size:10pt;font-family:Tahoma;}
</style>
<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><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>> Date: Sun, 10 Jul 2011 12:52:43 -0400<br>> From: woodbri@swoodbridge.com<br>> To: pgrouting-users@lists.osgeo.org<br>> Subject: Re: [pgrouting-users] server closed the connection unexpectedly<br>> <br>> Hi EJ,<br>> <br>> A few questions:<br>> <br>> what version of postgresql, postgis, pgrouting are you running?<br>> <br>> what do thess querys report?<br>> <br>> select count(*) from ma_routing;<br>> <br>> select count(*) from ma_routing<br>> where the_geom && st_expand(makeline(<br>> (select the_geom from vertices_tmp where id=64629654),<br>> (select the_geom from vertices_tmp where id=64630762)), 0.1);<br>> <br>> <br>> this is building a graph of the whole database instead of a smaller bbox <br>> of data about your source and target nodes. Does this crash?<br>> <br>> SELECT * FROM shortest_path('SELECT id, source::integer, <br>> target::integer, cost::double precision FROM ma_routing where the_geom <br>> && st_expand(makeline((select the_geom from vertices_tmp where <br>> id=64629654),(select the_geom from vertices_tmp where id=64630762)), <br>> 0.1)', 64629654, 64630762, false, false);<br>> <br>> -Steve<br>> <br>> On 7/10/2011 12:15 PM, E. . wrote:<br>> > Hi all,<br>> ><br>> > i really need your help.<br>> ><br>> > While running a shortest_path query i had the following error message:<br>> ><br>> > SELECT * FROM shortest_path('SELECT id, source::integer,<br>> > target::integer, cost::double precision FROM ma_routing', 64629654,<br>> > 64630762, false,<br>> > false)<br>> ><br>> > " server closed the connection unexpectedly".<br>> ><br>> > I do not have negative cost or reverse_cost values. In another thread a<br>> > long time ago it was advised to rebuild the "source" and "target" tables<br>> > using "assign_vertex_id".<br>> ><br>> > The posting is at:<br>> > http://postgis.refractions.net/pipermail/postgis-users/2008-June/020137.html<br>> ><br>> > That function i cannot find.<br>> ><br>> > For the following samples goes:<br>> > Projection _should_ be in 4326.<br>> ><br>> ><br>> > Here's a sample of the data in the routing table:<br>> ><br>> > select * from ma_routing limit 3;<br>> ><br>> > source | target | cost | reverse_cost | sens | x1 | y1 | x2 | y2 | rule<br>> > | to_cost | length | ogc_fid | id | length_shortest |<br>> > length_reverse_shortest<br>> > ----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+----+-----------------+-------------------------<br>> > 64629390 | 64629391 | 2.98541825999812 | 2.98541825999812 | double sens<br>> > | -73.167897 | 42.705496 | -73.167836 | 42.705791 | | | 33.1713139999791<br>> > | 582 | 1 | |<br>> > 64629654 | 64630762 | 15.4108990193286 | 15.4108990193286 | double sens<br>> > | -73.115287 | 42.646935 | -73.113203 | 42.647023 | | | 171.232211325874<br>> > | 718 | 2 | |<br>> > 64629691 | 64629692 | 1.03336877316695 | 1.03336877316695 | double sens<br>> > | -73.047407 | 42.648043 | -73.047296 | 42.648106 | | | 11.4818752574105<br>> > | 738 | 3 | |<br>> > (3 rows)<br>> ><br>> > OK, let's take line 2: source=64629654 , target=64630762.<br>> > Let's do a lookup where the source is the target of line 2, to go from<br>> > source to target:<br>> ><br>> > select * from ma_routing where source=64630762;<br>> ><br>> > source | target | cost | reverse_cost | sens | x1 | y1 | x2 | y2 | rule<br>> > | to_cost | length | ogc_fid | id | length_shortest |<br>> > length_reverse_shortest<br>> > ----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+-------+-----------------+-------------------------<br>> > 64630762 | 64629653 | 10.5842324315852 | 10.5842324315852 | double sens<br>> > | -73.115501 | 42.647023 | -73.115287 | 42.648065 | | | 117.602582573169<br>> > | 1502 | 5692 | |<br>> > 64630762 | 64630760 | 10.324871753826 | 10.324871753826 | double sens |<br>> > -73.115287 | 42.646049 | -73.114822 | 42.647023 | | | 114.720797264734 |<br>> > 7776 | 11879 | |<br>> > (2 rows)<br>> ><br>> ><br>> > Maybe i am overlooking something.<br>> > The geometry column is in another table.<br>> ><br>> > Roads table is called "027_nosr_r".<br>> ><br>> > Sorry if this is going to be confusing but i want to get the same rows<br>> > from the "roads" table (which is the origin) as from the routing table.<br>> ><br>> ><br>> > Here's a sample of the roads table:<br>> ><br>> > select * from "027_nosr_r" limit 3;<br>> ><br>> > ogc_fid | wkb_geometry | fnode_ | tnode_ | lpoly_ | rpoly_ | length |<br>> > reg25_ | rd_1 | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |<br>> > rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 | rd_17 | rd_18 |<br>> > rd_19 | rd_20 | rd_21 | rd_22 | rd_23 | rd_24 | rd_25 | rd_26 | rd_27 |<br>> > rd_28 | textsearchable_index_col<br>> > ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>> > 582 |<br>> > 0102000020E6100000030000002D060FD3BE4A52C0BABA63B14D5A4540B0FECF61BE4A52C0ADA415DF505A4540DAC534D3BD4A52C0D367075C575A4540<br>> > | 64629390 | 64629391 | 206361314 | 206361314 | 0.000000 | 3915987 |<br>> > 3915987 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | 1 | | | | | | | | 0 | 0 | 0 | 0<br>> > | 0 | 0 | fc#4 | 25003 | 0 | 2500346225 | | |<br>> > 718 |<br>> > 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540<br>> > | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 |<br>> > 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 |<br>> > 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220<br>> > | 01220 | 'st':2 'brown':1<br>> > 738 |<br>> > 0102000020E6100000020000007FA65EB7084352C06CB3B112F3524540D34ECDE5064352C0B22B2D23F5524540<br>> > | 64629691 | 64629692 | 206354450 | 206354451 | 0.000000 | 3916317 |<br>> > 3916317 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | | | | | | | | 0 | 0 | 0 | 0 |<br>> > 0 | 0 | fc#4 | 25003 | 0 | 2500360225 | | |<br>> > (3 rows)<br>> ><br>> ><br>> ><br>> > Let's take 64629654 as fnode_, which is the "source column" of the road,<br>> > like in line 2 of the first sql, source=64629654 , target=64630762.<br>> ><br>> > select * from "027_nosr_r" where fnode_=64629654 ;<br>> ><br>> ><br>> > ogc_fid | wkb_geometry | fnode_ | tnode_ | lpoly_ | rpoly_ | length |<br>> > reg25_ | rd_1 | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |<br>> > rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 | rd_17 | rd_18 |<br>> > rd_19 | rd_20 | rd_21 | rd_22 | rd_23 | rd_24 | rd_25 | rd_26 | rd_27 |<br>> > rd_28 | textsearchable_index_col<br>> > ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>> > 718 |<br>> > 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540<br>> > | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 |<br>> > 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 |<br>> > 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220<br>> > | 01220 | 'st':2 'brown':1<br>> > (1 row)<br>> ><br>> ><br>> > OK, let's consider line 2 of the routing table: source=64629654 ,<br>> > target=64630762. Same second sample above, only this time from the<br>> > "roads" table, not the routing table.<br>> > Let's do a lookup where the source is the target of line 2, to go from<br>> > source to target:<br>> ><br>> > tnode_="target":<br>> ><br>> > select * from "027_nosr_r" where fnode_=64629654 limit 3;<br>> ><br>> ><br>> > ogc_fid | wkb_geometry | fnode_ | tnode_ | lpoly_ | rpoly_ | length |<br>> > reg25_ | rd_1 | rd_2 | rd_3 | rd_4 | rd_5 | rd_6 | rd_7 | rd_8 | rd_9 |<br>> > rd_10 | rd_11 | rd_12 | rd_13 | rd_14 | rd_15 | rd_16 | rd_17 | rd_18 |<br>> > rd_19 | rd_20 | rd_21 | rd_22 | rd_23 | rd_24 | rd_25 | rd_26 | rd_27 |<br>> > rd_28 | textsearchable_index_col<br>> > ---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>> > 718 |<br>> > 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540<br>> > | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 |<br>> > 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 |<br>> > 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220<br>> > | 01220 | 'st':2 'brown':1<br>> > (1 row)<br>> ><br>> ><br>> ><br>> > The thing is, this setup works in another DB (with data from a another<br>> > country).<br>> ><br>> > Any help would be greatly appreciated, i've been looking for days.<br>> ><br>> > If needed i will attach some sql to create the routing table and sample<br>> > data. This would be an attachment of course, should that be posted at<br>> > pastebin.com?<br>> ><br>> > Sorry if this post is way off, i'm not sure about pasting and formatting.<br>> ><br>> > Thanks for your time,<br>> ><br>> ><br>> ><br>> > EJ<br>> ><br>> ><br>> ><br>> ><br>> > _______________________________________________<br>> > Pgrouting-users mailing list<br>> > Pgrouting-users@lists.osgeo.org<br>> > http://lists.osgeo.org/mailman/listinfo/pgrouting-users<br>> <br>> _______________________________________________<br>> Pgrouting-users mailing list<br>> Pgrouting-users@lists.osgeo.org<br>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users<br></div> </div>
<br>_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users</div> </div></body>
</html>