[pgrouting-users] server closed the connection unexpectedly

[E11]

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

[Stephen_Woodbridge1]

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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

[E11]

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@swoodbridge.com
To: pgrouting-users@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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

---

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

[dkastl]

On Mon, Jul 11, 2011 at 4:14 PM, E. . <thnxfernuttin@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@swoodbridge.com
To: pgrouting-users@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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

---

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

---

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

[E11]

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@hotmail.com
To: pgrouting-users@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@swoodbridge.com
To: pgrouting-users@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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

---

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________ Pgrouting-users mailing list Pgrouting-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/pgrouting-users

[E11]

hi Daniel,

it was not working on both servers.

looks like it’s ok now.

thanks!

EJ

---

From: daniel@georepublic.de
Date: Mon, 11 Jul 2011 17:01:24 +0900
Subject: Re: [pgrouting-users] server closed the connection unexpectedly
To: pgrouting-users@lists.osgeo.org

On Mon, Jul 11, 2011 at 4:14 PM, E. . <thnxfernuttin@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@swoodbridge.com
To: pgrouting-users@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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

---

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

---

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

_______________________________________________ Pgrouting-users mailing list Pgrouting-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/pgrouting-users