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

[Vicky Vergara]

​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 <aborruso at gmail.com> 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
>
> [image: 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
> Pgrouting-users at lists.osgeo.org
> 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20180521/5fef4576/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: immagine.png
Type: image/png
Size: 16712 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20180521/5fef4576/attachment-0001.png>