[pgrouting-users] server closed the connection unexpectedly

Mon Jul 11 04:01:24 EDT 2011

On Mon, Jul 11, 2011 at 4:14 PM, E. . <thnxfernuttin at hotmail.com> wrote:

>  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
>

Hi, which is the server that works and which one that doesn't?
Have you copied data from on to the other?

vertices_tmp table is not needed, but you need a valid network. It's strange
that you can't find assign_vertex_id function. Maybe you made a database
dump and copied it to the other server?

Daniel

>
> 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
>
>

-- 
Georepublic UG & Georepublic Japan
eMail: daniel.kastl at georepublic.de
Web: http://georepublic.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20110711/6fcd01b9/attachment-0001.html