<p>Goed bezig!</p>
<p>Mij lijkt het een balletje int wijzigen in long in de pgrouting broncode...</p>
<div class="gmail_quote">Op 14 nov. 2011 10:12 schreef "E. ." <<a href="mailto:thnxfernuttin@hotmail.com">thnxfernuttin@hotmail.com</a>> het volgende:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div dir="ltr">
Hi Steve,<br><br>thanks for your swift reply!<br><br>well, rebuilding the table helps in that the source and target values are not too high anymore then.<br><br>i have a bunch of info here for you, i'll split it:<br>
<br>the query is:<br><br>SELECT * FROM shortest_path('SELECT id, source::integer,<br>target::integer, cost::double precision FROM ma_routing', 64629654, 64630762, false,<br>false)<br><br>sample data to get crashing:<br>
<br> source | target | cost | reverse_cost | sens | x1 | y1 | x2 | y2 | rule | to_cost | length | ogc_fid | id | length_shortest | length_reverse_shortes
t<br>----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+----+-----------------+-------------------------<br>
64629390 | 64629391 | 2.98541825999812 | 2.98541825999812 | double sens | -73.167897 | 42.705496 | -73.167836 | 42.705791 | | | 33.1713139999791 | 582 | 1 | | <br>
64629654 | 64630762 | 15.4108990193286 | 15.4108990193286 | double sens | -73.115287 | 42.646935 | -73.113203 | 42.647023 | | | 171.232211325874 | &nb
sp; 718 | 2 | | <br> 64629691 | 64629692 | 1.03336877316695 | 1.03336877316695 | double sens | -73.047407 | 42.648043 | -73.047296 | 42.648106 | | | 11.4818752574105 | 738 | 3 | | <br>
(3 rows)<br><br><br>if you change source and target in any existing table and make it at least as high as the values here, crashing is guaranteed!<br><br><br><br>ok, here comes the whole thing, this was my original pos
t with a lot of bla bla:<br><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, 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 long time ago it was advised to rebuild the "source" and "target" tables using "assign_vertex_id".<br>
<br>The posting is at:<br><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><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 |&nbs
p; sens | x1 | y1 | x2 | y2 | rule | to_cost | length | ogc_fid | id | length_shortest | length_reverse_shortest<br>----------+----------+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+----+-----------------+-------------------------<br>
64629390 | 64629391 | 2.98541825999812 | 2.98541825999812 | double sens | -73.167897 | 42.705496 | -73.167836 | 42.705791 | | | 33.1713139999791 | 582 | 1 | | &
nbsp; <br> 64629654 | 64630762 | 15.4108990193286 | 15.4108990193286 | double sens | -73.115287 | 42.646935 | -73.113203 | 42.647023 | | | 171.232211325874 | 718 | 2 | | <br>
64629691 | 64629692 | 1.03336877316695 | 1.03336877316695 | double sens | -73.047407 | 42.648043 | -73.047296 | 42.648106 | | | 11.4818752574105 | 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 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 | to_cost | length | ogc_fid | id | length_shortest | length_reverse_shortest<br>
----------+-------
---+------------------+------------------+-------------+------------+-----------+------------+-----------+------+---------+------------------+---------+-------+-----------------+-------------------------<br> 64630762 | 64629653 | 10.5842324315852 | 10.5842324315852 | double sens | -73.115501 | 42.647023 | -73.115287 | 42.648065 | | | 117.602582573169 | 1502 | 5692 | | <br>
64630762 | 64630760 | 10.324871753826 | 10.324871753826 | double sens | -73.115287 | 42.646049 | -73.114822 | 42.647023 | | | 114.720797264734 | 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 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 | &nb
sp; 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<br>---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+------------+-------+-------+--------------------------<br>
582 | 0102000020E6100000030000002D060
FD3BE4A52C0BABA63B14D5A4540B0FECF61BE4A52C0ADA415DF505A4540DAC534D3BD4A52C0D367075C575A4540 | 64629390 | 64629391 | 206361314 | 206361314 | 0.000000 | 3915987 | 3915987 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | 1 | | | | | | | &nbsp
; | 0 | 0 | 0 | 0 | 0 | 0 | fc#4 | 25003 | 0 | 2500346225 | | |<br> 718 | 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540 | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 | 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St &nbsp
; | | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220 | 01220 | 'st':2 'brown':1<br>
738 | 0102000020E6100000020000007FA65EB7084352C06CB3B112F3524540D34ECDE5064352C0B22B2D23F5524540 &nb
sp; | 64629691 | 64629692 | 206354450 | 206354451 | 0.000000 | 3916317 | 3916317 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | | | | | | &
nbsp; | | 0 | 0 | 0 | 0 | 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, 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 | &
nbsp; 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<br>
---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+---------------
--------------------------+-------+-------+------------+-------+-------+--------------------------<br> 718 | 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540 | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 | 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | &nbs
p; 0 | 0 | 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220 | 01220 | 'st':2 'brown':1<br>(1 row)<br><br><br>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.<br>
Let's do a lookup where the source is the target of line 2, to go from source to target:<br><br>tnode_="target":<br><br>select * from "027_nosr_r" where fnode_=64629654 limit 3;<br><br><br> ogc_fid | &nbs
p; 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 &nb
sp; | 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<br>
---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+-----------+-----------+----------+---------+---------+------+------+------+------+------+------+------+------+------------------------------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-----------------------------------------+-------+-------+--------
----+-------+-------+--------------------------<br> 718 | 0102000020E610000004000000C8B3CBB73E4752C0A2D11DC4CE5245408124ECDB494752C0DDCF29C8CF52454032225168594752C00072C284D1524540DAA9B9DC604752C0416150A6D1524540 | 64629654 | 64630762 | 206354958 | 206354960 | 0.000000 | 3916275 | 3916275 | 0 | 0 | 0 | 6 | 100 | 6 | 0 | | Brown St | | | | | | | 0 | 0 | &nbsp
; 0 | 0 | 0 | 0 | fc#4@hn#RE12-98#LO13-99 | 25003 | 0 | 2500300555 | 01220 | 01220 | 'st':2 'brown':1<br>(1 row)<br><br><br><br><br>thanks,<br><br>EJ<br><br><br><br><div>
> Date: Sun, 13 Nov 2011 11:11:29 -0500<br>> From: <a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a><br>> To: <a href="mailto:pgrouting-users@lists.osgeo.org" target="_blank">pgrouting-users@lists.osgeo.org</a><br>
> Subject: Re: [pgrouting-users] bug in pgRouting: server closed the connection unexpectedly<br>> <br>> On 11/13/2011 3:36 AM, E. . wrote:<br>> > hi all,<br>> ><br>> > i finally figured out what the problem is.<br>
> ><br>> > high values of "source" and "target" fields crash pgRouting.<br>> ><br>> > with high i mean this:<br>> ><br>> > source | target |<br>> > ----------+---------<br>
> > 64629390 | 64629391 |
<br>> > 64629654 | 64630762 |<br>> ><br>> > must be a register overflow.<br>> ><br>> > that's prpbably why the solution was to rebuild the tables, if the<br>> > source and targets fields will be renumbered.<br>
> <br>> Hi EJ,<br>> <br>> Thank you for the report. Which method were you using? Dijkstra, astar, <br>> shooting star? Can you give use the query the was causing the fault? It <br>> might also be helpful if you could provide the results of:<br>
> <br>> select min(source), min(target), max(source), max(target), count(*)<br>> from <your table>;<br>> <br>> Does renumbering the table solve your problem?<br>> What was the error/crash message you were getting?<br>
> <br>> In my mind, it is a bug to crash the server or the connection, we should <br>> understand and fail gracefully if we have to but not crash.<br>> <br>> Thank you for help,<br>> <br>> -Steve<br>
> ____________
___________________________________<br>> Pgrouting-users mailing list<br>> <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">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>
</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>