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

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

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

aborruso
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

immagine.png


--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

Vicky Vergara-2
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.
Vicky

On Wed, May 9, 2018 at 1:51 AM, andy <[hidden email]> 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

immagine.png


--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

aborruso
Hi Vicky

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

immagine.png (29K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Vicky Vergara-2
So, one thing is the direction of the geometry which is a matter of postGIS, another is graph logic
for the edge circled green if the geometry is "backwards" that is one thing, you fix with postGIS,
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 (<set of unreachable vertices>)


On Wed, May 16, 2018 at 5:01 PM, andy <[hidden email]> wrote:
Hi Vicky

On 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/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
Reply | Threaded
Open this post in threaded view
|

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

aborruso


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 logic
for the edge circled green if the geometry is "backwards" that is one thing, you fix with postGIS,
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 (<set of unreachable vertices>)


Great, I will try!!

--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

aborruso
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 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 (<set of unreachable vertices>)

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/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
Reply | Threaded
Open this post in threaded view
|

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

Vicky Vergara-2
Hello Andy,
I did have my share of first steps with pgRouting some years ago,
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)
(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 <[hidden email]> 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 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 (<set of unreachable vertices>)

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/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
Reply | Threaded
Open this post in threaded view
|

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

aborruso

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

immagine.png

--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

aborruso
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

immagine.png

--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

Vicky Vergara-2

H
​i​

On Fri, May 18, 2018 at 12:47 AM, andy <[hidden email]> 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

immagine.png

--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

aborruso

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

immagine.png


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

image.png (39K) Download Attachment
immagine.png (24K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Vicky Vergara-2
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?


Please, try to find out what I am doing wrong. (your answer will help see your current level of understanding of pgRouting)
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 <[hidden email]> 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).

immagine.png


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
Reply | Threaded
Open this post in threaded view
|

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

aborruso

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

immagine.png

--
___________________

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

linee.zip (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Vicky Vergara-2


​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 <[hidden email]> 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

immagine.png

--
___________________

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
Reply | Threaded
Open this post in threaded view
|

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

aborruso

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


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

immagine.png (22K) Download Attachment