[pgrouting-users] server closed the connection unexpectedly

Sun Jul 10 12:15:41 EDT 2011

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110710/aa94b664/attachment-0001.html