[pgrouting-users] server closed the connection unexpectedly -FIXED

Mon Jul 11 04:02:18 EDT 2011

hi list,

looks like it's fixed. i don't get many results, but there's no more crashes.

after running assign_vertex_id the source/target tabel was rebuilt correctly.

only problem was that i could not find the function.
turned out to be in routing_topology.sql.

thanks,

EJ

From: thnxfernuttin at hotmail.com
To: pgrouting-users at lists.osgeo.org
Subject: RE: [pgrouting-users] server closed the connection unexpectedly
Date: Mon, 11 Jul 2011 07:14:35 +0000

hello Steve,

thanks for your swift reply!

i am running the following versions:
postgres 8.3.3
postgis 1.3.3
pgrouting 1.03

on another server we have
postgres 8.4
postgis 1.5.1
pgrouting 1.03

but this does not help.

we do not use OSM data, and we have no tmp_vertices table.

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

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110711/efbd80a2/attachment.html