RE: [pgrouting-users] bug in pgRouting: server closed the connection unexpectedly
E. .
thnxfernuttin at hotmail.com
Mon Nov 14 04:11:14 EST 2011
Hi Steve,
thanks for your swift reply!
well, rebuilding the table helps in that the source and target values are not too high anymore then.
i have a bunch of info here for you, i'll split it:
the query is:
SELECT * FROM shortest_path('SELECT id, source::integer,
target::integer, cost::double precision FROM ma_routing', 64629654, 64630762, false,
false)
sample data to get crashing:
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)
if you change source and target in any existing table and make it at least as high as the values here, crashing is guaranteed!
ok, here comes the whole thing, this was my original post with a lot of bla bla:
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)
thanks,
EJ
> Date: Sun, 13 Nov 2011 11:11:29 -0500
> From: woodbri at swoodbridge.com
> To: pgrouting-users at lists.osgeo.org
> Subject: Re: [pgrouting-users] bug in pgRouting: server closed the connection unexpectedly
>
> On 11/13/2011 3:36 AM, E. . wrote:
> > hi all,
> >
> > i finally figured out what the problem is.
> >
> > high values of "source" and "target" fields crash pgRouting.
> >
> > with high i mean this:
> >
> > source | target |
> > ----------+---------
> > 64629390 | 64629391 |
> > 64629654 | 64630762 |
> >
> > must be a register overflow.
> >
> > that's prpbably why the solution was to rebuild the tables, if the
> > source and targets fields will be renumbered.
>
> Hi EJ,
>
> Thank you for the report. Which method were you using? Dijkstra, astar,
> shooting star? Can you give use the query the was causing the fault? It
> might also be helpful if you could provide the results of:
>
> select min(source), min(target), max(source), max(target), count(*)
> from <your table>;
>
> Does renumbering the table solve your problem?
> What was the error/crash message you were getting?
>
> In my mind, it is a bug to crash the server or the connection, we should
> understand and fail gracefully if we have to but not crash.
>
> Thank you for help,
>
> -Steve
> _______________________________________________
> 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/20111114/ae7ef7bc/attachment-0001.html
More information about the Pgrouting-users
mailing list