# [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

15 messages
Open this post in threaded view
|

## [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 Hi, I have a simple network like the one on the left side of the image below (https://i.imgur.com/4mHWfCz.png). The line directions of the left side are those I used to draw the lines. Choosing a starting point (in the image the red one), is there in pgrouting a way to assign/calculate the "right" direction of the network (the one I have on the right)?Imagine that this is an hidraulic network in a flat world and that I open a water faucet in the red point. I want to "correct" the directions highlighted in green on the right, and have the water direction. Thank you -- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 Hi,Maybe you want to see if all vertices are reachable from the starting (red) point.What I would do is a pgr_dijkstra(one to many) and verify which vertices are not reachable and manually change the direction of the ones that are not.VickyOn Wed, May 9, 2018 at 1:51 AM, andy wrote: Hi, I have a simple network like the one on the left side of the image below (https://i.imgur.com/4mHWfCz.png). The line directions of the left side are those I used to draw the lines. Choosing a starting point (in the image the red one), is there in pgrouting a way to assign/calculate the "right" direction of the network (the one I have on the right)?Imagine that this is an hidraulic network in a flat world and that I open a water faucet in the red point. I want to "correct" the directions highlighted in green on the right, and have the water direction. Thank you -- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users-- ```Georepublic UG (haftungsbeschränkt) Salzmannstraße 44, 81739 München, Germany Vicky Vergara Operations Research eMail: vicky@georepublic.de Web: https://georepublic.info Tel: +49 (089) 4161 7698-1 Fax: +49 (089) 4161 7698-9 Commercial register: Amtsgericht München, HRB 181428 CEO: Daniel Kastl ``` _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 Hi VickyOn Tue, 15 May 2018 at 12:58, Vicky Vergara <[hidden email]> wrote:Maybe you want to see if all vertices are reachable from the starting (red) point.What I would do is a pgr_dijkstra(one to many) and verify which vertices are not reachable and manually change the direction of the ones that are not.my real network has hundreds of nodes and it's impossible to manually change the direction, there could be  too many.Thank you-- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users immagine.png (29K) Download Attachment
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 So, one thing is the direction of the geometry which is a matter of postGIS, another is graph logicfor the edge circled green if the geometry is "backwards" that is one thing, you fix with postGIS,I dont know your data, but supposeid = 1, source = A, target = B, cost > 0, reverse_cost = -1 and that vertex A is not reachablethen "manually" would be:UPDATE TABLE foo set source = target, target = source WHERE source IS IN ()On Wed, May 16, 2018 at 5:01 PM, andy wrote:Hi VickyOn Tue, 15 May 2018 at 12:58, Vicky Vergara <[hidden email]> wrote:Maybe you want to see if all vertices are reachable from the starting (red) point.What I would do is a pgr_dijkstra(one to many) and verify which vertices are not reachable and manually change the direction of the ones that are not.my real network has hundreds of nodes and it's impossible to manually change the direction, there could be  too many.Thank you-- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users-- ```Georepublic UG (haftungsbeschränkt) Salzmannstraße 44, 81739 München, Germany Vicky Vergara Operations Research eMail: vicky@georepublic.de Web: https://georepublic.info Tel: +49 (089) 4161 7698-1 Fax: +49 (089) 4161 7698-9 Commercial register: Amtsgericht München, HRB 181428 CEO: Daniel Kastl ``` _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 On Thu, 17 May 2018 at 12:07, Vicky Vergara <[hidden email]> wrote:So, one thing is the direction of the geometry which is a matter of postGIS, another is graph logicfor the edge circled green if the geometry is "backwards" that is one thing, you fix with postGIS,I dont know your data, but supposeid = 1, source = A, target = B, cost > 0, reverse_cost = -1 and that vertex A is not reachablethen "manually" would be:UPDATE TABLE foo set source = target, target = source WHERE source IS IN ()Great, I will try!! -- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 In reply to this post by Vicky Vergara-2 Hi Vicky,these are my first steps in pgrouting, and I’m sorry for some stupid question. On Thu, 17 May 2018 at 12:07, Vicky Vergara vicky@... wrote: I dont know your data, but supposeid = 1, source = A, target = B, cost > 0, reverse_cost = -1 and that vertex A is not reachablethen "manually" would be:UPDATE TABLE foo set source = target, target = source WHERE source IS IN () I have created my network with `pgr_createTopology`. Now for one to many I must apply something like ``````SELECT * FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM edge_table', 2, ARRAY[3,5], FALSE ); `````` I do not have a `cost` and `reverse_cost` columns in my `edge_table` and than I have SQL error.Do I must create them? Thank you ​-- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

 Hello Andy,I did have my share of first steps with pgRouting some years ago,I suggest that you follow the workshop,https://workshop.pgrouting.org/2.4.11/en/index.htmland/or use the sample data of the documentation and do the examples on the documentation (there is a "tutorial" in the documentation, maybe out of date, but gives an idea)https://docs.pgrouting.org/2.6/en/sampledata.html(I did both things when I was in my baby steps on pgRouting)The straight answer to your question is yes, you need a cost (& reverse_cost is optional)On Thu, May 17, 2018 at 7:25 AM, andy wrote:Hi Vicky,these are my first steps in pgrouting, and I’m sorry for some stupid question. On Thu, 17 May 2018 at 12:07, Vicky Vergara vicky@... wrote: I dont know your data, but supposeid = 1, source = A, target = B, cost > 0, reverse_cost = -1 and that vertex A is not reachablethen "manually" would be:UPDATE TABLE foo set source = target, target = source WHERE source IS IN () I have created my network with `pgr_createTopology`. Now for one to many I must apply something like ``````SELECT * FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM edge_table', 2, ARRAY[3,5], FALSE ); `````` I do not have a `cost` and `reverse_cost` columns in my `edge_table` and than I have SQL error.Do I must create them? Thank you ​-- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users-- ```Georepublic UG (haftungsbeschränkt) Salzmannstraße 44, 81739 München, Germany Vicky Vergara Operations Research eMail: vicky@georepublic.de Web: https://georepublic.info Tel: +49 (089) 4161 7698-1 Fax: +49 (089) 4161 7698-9 Commercial register: Amtsgericht München, HRB 181428 CEO: Daniel Kastl ``` _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

Hi Vicky,

On Thu, 17 May 2018 at 20:22, Vicky Vergara vicky@... wrote:

I suggest that you follow the workshop,
and/or use the sample data of the documentation and do the examples on the documentation (there is a "tutorial" in the documentation, maybe out of date, but gives an idea)

Thank you.

The straight answer to your question is yes, you need a cost (& reverse_cost is optional)

Now I have a route with a cost:

seq path_seq end_vid node edge cost agg_cost
1 1 2 1 1 1875.887941 0.0
2 2 2 2 -1 0.0 1875.887941
3 1 3 1 2 1551.56248159 0.0
4 2 3 3 -1 0.0 1551.56248159
5 1 4 1 3 1506.74402482 0.0
6 2 4 4 -1 0.0 1506.74402482
7 1 5 1 3 1506.74402482 0.0
8 2 5 4 4 1208.08883443 1506.74402482
9 3 5 5 -1 0.0 2714.83285924
10 1 6 1 1 1875.887941 0.0
11 2 6 2 5 1447.12212188 1875.887941
12 3 6 6 -1 0.0 3323.01006288

The command I have used:

``````SELECT * FROM pgr_dijkstra('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6])
``````

The starting point is `1`, but I’m able to read in the data that `3` is not reachable from `1`.

What’s wrong in my reasoning ?

Thank you

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

I must correct myself: I’m NOT able to read in the data that 3 is not reachable from 1.

Thank you

Il gio 17 mag 2018, 23:57 andy <[hidden email]> ha scritto:

Hi Vicky,

On Thu, 17 May 2018 at 20:22, Vicky Vergara vicky@... wrote:

I suggest that you follow the workshop,
and/or use the sample data of the documentation and do the examples on the documentation (there is a "tutorial" in the documentation, maybe out of date, but gives an idea)

Thank you.

The straight answer to your question is yes, you need a cost (& reverse_cost is optional)

Now I have a route with a cost:

seq path_seq end_vid node edge cost agg_cost
1 1 2 1 1 1875.887941 0.0
2 2 2 2 -1 0.0 1875.887941
3 1 3 1 2 1551.56248159 0.0
4 2 3 3 -1 0.0 1551.56248159
5 1 4 1 3 1506.74402482 0.0
6 2 4 4 -1 0.0 1506.74402482
7 1 5 1 3 1506.74402482 0.0
8 2 5 4 4 1208.08883443 1506.74402482
9 3 5 5 -1 0.0 2714.83285924
10 1 6 1 1 1875.887941 0.0
11 2 6 2 5 1447.12212188 1875.887941
12 3 6 6 -1 0.0 3323.01006288

The command I have used:

``````SELECT * FROM pgr_dijkstra('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6])
``````

The starting point is `1`, but I’m able to read in the data that `3` is not reachable from `1`.

What’s wrong in my reasoning ?

Thank you

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino
--

______
Scusami per la brevità, ti sto scrivendo dal cellulare.

website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E EPSG:4326

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

H
​i​

On Fri, May 18, 2018 at 12:47 AM, andy wrote:
I must correct myself: I’m NOT able to read in the data that 3 is not reachable from 1.

Thank you

Il gio 17 mag 2018, 23:57 andy <[hidden email]> ha scritto:

Hi Vicky,

On Thu, 17 May 2018 at 20:22, Vicky Vergara vicky@... wrote:

I suggest that you follow the workshop,
and/or use the sample data of the documentation and do the examples on the documentation (there is a "tutorial" in the documentation, maybe out of date, but gives an idea)

Thank you.

The straight answer to your question is yes, you need a cost (& reverse_cost is optional)

Now I have a route with a cost:

seq path_seq end_vid node edge cost agg_cost
1 1 2 1 1 1875.887941 0.0
2 2 2 2 -1 0.0 1875.887941
3 1 3 1 2 1551.56248159 0.0
4 2 3 3 -1 0.0 1551.56248159
5 1 4 1 3 1506.74402482 0.0
6 2 4 4 -1 0.0 1506.74402482
7 1 5 1 3 1506.74402482 0.0
8 2 5 4 4 1208.08883443 1506.74402482
9 3 5 5 -1 0.0 2714.83285924
10 1 6 1 1 1875.887941 0.0
11 2 6 2 5 1447.12212188 1875.887941
12 3 6 6 -1 0.0 3323.01006288

​I can see in your results that vertex 3 is reachable from 1

The command I have used:

``````SELECT * FROM pgr_dijkstra('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6])
``````

The starting point is `1`, but I’m able to read in the data that `3` is not reachable from `1`.

​I don't see your data, can you put the contents of the data​

​The only similar thing as "data" I have is your interpretation of a table contents​.

This is the data:
``````SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee;``````
and maybe this are not the correct interpretation of that data

What’s wrong in my reasoning ?

​Maybe what is wrong is how you are interpreting the graph data​

Thank you

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino
--

______
Scusami per la brevità, ti sto scrivendo dal cellulare.

website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E EPSG:4326

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

--
```Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@georepublic.de
Web: https://georepublic.info

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl

```

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

Hi Vicky,

On Sat, 19 May 2018 at 19:18, Vicky Vergara vicky@... wrote:

I don't see your data, can you put the contents of the data​

​The only similar thing as "data" I have is your interpretation of a table contents​.

This is the data:
``````SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee;``````

Here the output (I have added the geom)

id source target cost geom
1 1 2 1875.887941 SRID=32632;LINESTRING(526471.864222973 5083313.32108108,527988.654493243 5082209.55351351)
2 3 1 1551.56248159 SRID=32632;LINESTRING(525738.392871622 5081946.07351351,526471.864222973 5083313.32108108)
3 1 4 1506.74402482 SRID=32632;LINESTRING(526471.864222973 5083313.32108108,527803.506385135 5084018.30810811)
4 4 5 1208.08883443 SRID=32632;LINESTRING(527803.506385135 5084018.30810811,528985.605844594 5084267.54594595)
5 2 6 1447.12212188 SRID=32632;LINESTRING(527988.654493243 5082209.55351351,527020.187466217 5081134.27027027)

And this is an image generated from this data (my starting point is 1).

Thank you very much

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

So I see your data now,
as a table on a mail, and as first glance I "see" that something is wrong,
But I need to reproduce your problem, so I need the data in such a way that I can copy paste.

So imagine I have a problem and I need you help me finding what is my problem:

I have this data:
```CREATE TABLE edge_table (
id BIGSERIAL,
dir character varying,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
capacity BIGINT,
reverse_capacity BIGINT,
category_id INTEGER,
reverse_category_id INTEGER,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
the_geom geometry
);```
```INSERT INTO edge_table (
category_id, reverse_category_id,
cost, reverse_cost,
capacity, reverse_capacity,
x1, y1,
x2, y2) VALUES
(3, 1,    1,  1,  80, 130,   2,   0,    2, 1),
(3, 2,   -1,  1,  -1, 100,   2,   1,    3, 1),
(2, 1,   -1,  1,  -1, 130,   3,   1,    4, 1),
(2, 4,    1,  1, 100,  50,   2,   1,    2, 2),
(1, 4,    1, -1, 130,  -1,   3,   1,    3, 2),
(4, 2,    1,  1,  50, 100,   0,   2,    1, 2),
(4, 1,    1,  1,  50, 130,   1,   2,    2, 2),
(2, 1,    1,  1, 100, 130,   2,   2,    3, 2),
(1, 3,    1,  1, 130,  80,   3,   2,    4, 2),
(1, 4,    1,  1, 130,  50,   2,   2,    2, 3),
(1, 2,    1, -1, 130,  -1,   3,   2,    3, 3),
(2, 3,    1, -1, 100,  -1,   2,   3,    3, 3),
(2, 4,    1, -1, 100,  -1,   3,   3,    4, 3),
(3, 1,    1,  1,  80, 130,   2,   3,    2, 4),
(3, 4,    1,  1,  80,  50,   4,   2,    4, 3),
(3, 3,    1,  1,  80,  80,   4,   1,    4, 2),
(1, 2,    1,  1, 130, 100,   0.5, 3.5,  1.999999999999,3.5),
(4, 1,    1,  1,  50, 130,   3.5, 2.3,  3.5,4);

UPDATE edge_table SET the_geom = st_makeline(st_point(x1,y1),st_point(x2,y2)),
dir = CASE WHEN (cost>0 AND reverse_cost>0) THEN 'B'   -- both ways
WHEN (cost>0 AND reverse_cost<0) THEN 'FT'  -- direction of the LINESSTRING
WHEN (cost<0 AND reverse_cost>0) THEN 'TF'  -- reverse direction of the LINESTRING
ELSE '' END;                                -- unknown```
```SELECT pgr_createTopology('edge_table',0.001);
```
With that data I am getting:

SELECT * FROM pgr_dijkstra('select id, source, target, cost, reverse_cost FROM edge_table', 1, 15);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
(0 rows)

But this image clearly shows there is a path from 1 to 15, what is the problem?

and send me data that I can copy/paste into a database so that I can reproduce your problem.

Regards
Vicky

On Sun, May 20, 2018 at 4:43 AM, andy wrote:

Hi Vicky,

On Sat, 19 May 2018 at 19:18, Vicky Vergara vicky@... wrote:

I don't see your data, can you put the contents of the data​

​The only similar thing as "data" I have is your interpretation of a table contents​.

This is the data:
``````SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee;``````

Here the output (I have added the geom)

id source target cost geom
1 1 2 1875.887941 SRID=32632;LINESTRING(526471.864222973 5083313.32108108,527988.654493243 5082209.55351351)
2 3 1 1551.56248159 SRID=32632;LINESTRING(525738.392871622 5081946.07351351,526471.864222973 5083313.32108108)
3 1 4 1506.74402482 SRID=32632;LINESTRING(526471.864222973 5083313.32108108,527803.506385135 5084018.30810811)
4 4 5 1208.08883443 SRID=32632;LINESTRING(527803.506385135 5084018.30810811,528985.605844594 5084267.54594595)
5 2 6 1447.12212188 SRID=32632;LINESTRING(527988.654493243 5082209.55351351,527020.187466217 5081134.27027027)

And this is an image generated from this data (my starting point is 1).

Thank you very much

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

--
```Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@georepublic.de
Web: https://georepublic.info

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl

```

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users
Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

Hi Vicky,
you are right. Below my steps.

In you example you create a table with the geometric and cost infos for every edge.
Instead I import a shapefile that have inside the right directions of the edges (but not the cost), and I want to use these directions to calculate if it’s possible to go in example from point A to point N.
Than I think that before to use pgr_createTopology and pgr_dijkstra, I must calculate the cost outside pg_routing. Am I right?

These are my steps:

• I import a shapefile (I’m attaching it) with `ogr2ogr -overwrite -lco LAUNDER=No -f PostgreSQL PG:"dbname=mydb host=localhost port=5432 user=myuser password=mypassword" "linee.shp" -nln "linee"`;
• then I run
``````ALTER TABLE linee ADD source INT4;
ALTER TABLE linee ADD target INT4;
select pgr_createTopology('linee', 0.000001,the_geom:='wkb_geometry',id:='ogc_fid');
``````
• and finally
``````SELECT * FROM pgr_dijkstra('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6])
``````

It gives me in output the table below:

seq path_seq end_vid node edge cost agg_cost
1 1 2 1 1 1875.887941 0.0
2 2 2 2 -1 0.0 1875.887941
3 1 4 1 3 1506.74402482 0.0
4 2 4 4 -1 0.0 1506.74402482
5 1 5 1 3 1506.74402482 0.0
6 2 5 4 4 1208.08883443 1506.74402482
7 3 5 5 -1 0.0 2714.83285924
8 1 6 1 1 1875.887941 0.0
9 2 6 2 5 1447.12212188 1875.887941
10 3 6 6 -1 0.0 3323.01006288

I need way to have that it’s impossible to go from `1` to `3`, because the edge `2` has direction that blocks it.

Thank you very much, especially for your patience

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

Open this post in threaded view
|

## Re: [pgrouting-users] Build the "right" direction in a network, from a fixed starting point

​SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
test1tmp-#                            as cost FROM linee;
id | source | target |       cost
----+--------+--------+------------------
1 |      1 |      2 | 1875.88794100276
2 |      3 |      1 | 1551.56248158866
3 |      1 |      4 | 1506.74402481746
4 |      4 |      5 | 1208.08883442544
5 |      2 |      6 | 1447.12212188142
(5 rows)

test1tmp=#SELECT * FROM pgr_dijkstraCost('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6]);
start_vid | end_vid |     agg_cost
-----------+---------+------------------
1 |       2 | 1875.88794100276
1 |       4 | 1506.74402481746
1 |       5 |  2714.8328592429
1 |       6 | 3323.01006288418
(4 rows)

From the results, vertex 3 is never reached (end_vid does not have a 3)

WITH unreach_vertex AS
(SELECT id FROM linee_vertices_pgr LEFT JOIN pgr_dijkstraCost('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6])  ON (end_vid = id) WHERE id !=1 AND end_vid IS NULL)
UPDATE  linee set source = target, target = source WHERE source  IN (SELECT id FROM unreach_vertex);
UPDATE 1
test1tmp=# SELECT * FROM pgr_dijkstraCost('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6]);
start_vid | end_vid |     agg_cost
-----------+---------+------------------
1 |       2 | 1875.88794100276
1 |       3 | 1551.56248158866
1 |       4 | 1506.74402481746
1 |       5 |  2714.8328592429
1 |       6 | 3323.01006288418
(5 rows)

you still won me an explanation of :

>  Please, try to find out what I am doing wrong. (your answer will help see your current level of understanding of pgRouting)

​VIcky​

On Mon, May 21, 2018 at 8:34 AM, andy wrote:

Hi Vicky,
you are right. Below my steps.

In you example you create a table with the geometric and cost infos for every edge.
Instead I import a shapefile that have inside the right directions of the edges (but not the cost), and I want to use these directions to calculate if it’s possible to go in example from point A to point N.
Than I think that before to use pgr_createTopology and pgr_dijkstra, I must calculate the cost outside pg_routing. Am I right?

These are my steps:

• I import a shapefile (I’m attaching it) with `ogr2ogr -overwrite -lco LAUNDER=No -f PostgreSQL PG:"dbname=mydb host=localhost port=5432 user=myuser password=mypassword" "linee.shp" -nln "linee"`;
• then I run
``````ALTER TABLE linee ADD source INT4;
ALTER TABLE linee ADD target INT4;
select pgr_createTopology('linee', 0.000001,the_geom:='wkb_geometry',id:='ogc_fid');
``````
• and finally
``````SELECT * FROM pgr_dijkstra('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)
as "cost" FROM linee',1,ARRAY[2,3,4,5,6])
``````

It gives me in output the table below:

seq path_seq end_vid node edge cost agg_cost
1 1 2 1 1 1875.887941 0.0
2 2 2 2 -1 0.0 1875.887941
3 1 4 1 3 1506.74402482 0.0
4 2 4 4 -1 0.0 1506.74402482
5 1 5 1 3 1506.74402482 0.0
6 2 5 4 4 1208.08883443 1506.74402482
7 3 5 5 -1 0.0 2714.83285924
8 1 6 1 1 1875.887941 0.0
9 2 6 2 5 1447.12212188 1875.887941
10 3 6 6 -1 0.0 3323.01006288

I need way to have that it’s impossible to go from `1` to `3`, because the edge `2` has direction that blocks it.

Thank you very much, especially for your patience

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"

Italo Calvino

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

--
```Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky@georepublic.de
Web: https://georepublic.info

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl

```

_______________________________________________
Pgrouting-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/pgrouting-users
 Hi Vicky,as usual you are fantastic. Thank you On Tue, 22 May 2018 at 02:36, Vicky Vergara vicky@... wrote: ​SELECT ogc_fid as id, source, target, st_length(wkb_geometry) test1tmp-#                            as cost FROM linee; id | source | target |       cost       ----+--------+--------+------------------  1 |      1 |      2 | 1875.88794100276  2 |      3 |      1 | 1551.56248158866  3 |      1 |      4 | 1506.74402481746  4 |      4 |      5 | 1208.08883442544  5 |      2 |      6 | 1447.12212188142(5 rows)test1tmp=#SELECT * FROM pgr_dijkstraCost('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)                             as "cost" FROM linee',1,ARRAY[2,3,4,5,6]); start_vid | end_vid |     agg_cost     -----------+---------+------------------         1 |       2 | 1875.88794100276         1 |       4 | 1506.74402481746         1 |       5 |  2714.8328592429         1 |       6 | 3323.01006288418(4 rows)From the results, vertex 3 is never reached (end_vid does not have a 3)WITH unreach_vertex AS (SELECT id FROM linee_vertices_pgr LEFT JOIN pgr_dijkstraCost('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)                            as "cost" FROM linee',1,ARRAY[2,3,4,5,6])  ON (end_vid = id) WHERE id !=1 AND end_vid IS NULL)UPDATE  linee set source = target, target = source WHERE source  IN (SELECT id FROM unreach_vertex);UPDATE 1test1tmp=# SELECT * FROM pgr_dijkstraCost('SELECT ogc_fid as id, source, target, st_length(wkb_geometry)                            as "cost" FROM linee',1,ARRAY[2,3,4,5,6]); start_vid | end_vid |     agg_cost     -----------+---------+------------------         1 |       2 | 1875.88794100276         1 |       3 | 1551.56248158866         1 |       4 | 1506.74402481746         1 |       5 |  2714.8328592429         1 |       6 | 3323.01006288418(5 rows) This is a presente for me and I see some light :) But I was very interested to this your I dont know your data, but suppose id = 1, source = A, target = B, cost > 0, reverse_cost = -1 and that vertex A is not reachable then “manually” would be: UPDATE TABLE foo set source = target, target = source WHERE source IS IN () I understood that it was possible to have a negative cost, for the edge `2` of my example and starting from my data (my shapefile). A sort of direct way to have this negative cost.Than to have that 3 was reacheable, but with negative cost. I’ve probably misunderstood it and it’s not like that you still won me an explanation of :>  Please, try to find out what I am doing wrong. (your answer will help see your current level of understanding of pgRouting) I will do it, but probably I’m a little dumb. Thank you again ​-- ___________________Andrea Borruso website: https://medium.com/tantotanto38° 7' 48" N, 13° 21' 9" E, EPSG:4326___________________ "cercare e saper riconoscere chi e cosa,  in mezzo all’inferno, non è inferno,  e farlo durare, e dargli spazio" Italo Calvino _______________________________________________ Pgrouting-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/pgrouting-users immagine.png (22K) Download Attachment