[pgrouting-users] PGRouting question

[Stephen Woodbridge]

Hi Pavel,

First, you should user the pgRouting-Users list for this type of 
question so everyone can learn from the answer and if I'm away there are 
lots of people that can answer if I can't.

Pgrouting-users mailing list
Pgrouting-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users << sign up here

I would recommend that you look at the workshop examples as there is one 
the directly answers your question.

http://workshop.pgrouting.org/2.1.0-dev/en/chapters/introduction.html

and specifically Exercise 13, but I think you would learn a lot by going 
through all the exercises as it is an excellent tutorial.

-Steve

On 6/8/2017 5:19 AM, Pavel Varnavsky wrote:
> Hello Stephen!
> 
> I have question about PGRouting, hope you can help me.
> 
> Initial data:
> 
> I’ve got ESRI shapefile with roads (it was made by other man), which I 
> put into PostGIS + PGRouting (2.4) DB.
> 
> Then I create topology on this table, create indexes – all work fine.
> 
> After it I create PHP script to get shortest path with Dijkstra algorithm:
> 
> ==============================================
> 
> // Find the nearest edge
> 
>     $startEdge = findNearestEdge($startPoint);
> 
>     $endEdge   = findNearestEdge($endPoint);
> 
>     // FUNCTION findNearestEdge
> 
>     function findNearestEdge($lonlat) {
> 
>           // Connect to database
> 
>           $con = pg_connect("dbname=".PG_DB." host=".PG_HOST." 
> user=".PG_USER." password=".PG_PASSWORD);
> 
>           $sql = "SELECT gid, source, target, the_geom,
> 
>                            ST_Distance(the_geom, ST_GeometryFromText(
> 
>                         'POINT(".$lonlat[0]." ".$lonlat[1].")', 4326)) 
> AS dist
> 
>                   FROM ".TABLE."
> 
>                   WHERE the_geom && ST_Setsrid(
> 
>                         'BOX3D(".($lonlat[0]-0.1)."
> 
>                                ".($lonlat[1]-0.1).",
> 
>                                ".($lonlat[0]+0.1)."
> 
>                                ".($lonlat[1]+0.1).")'::box3d, 4326)
> 
>                   ORDER BY dist LIMIT 1";
> 
>           $query = pg_query($con,$sql);
> 
>           $edge['gid']      = pg_fetch_result($query, 0, 0);
> 
>           $edge['source']   = pg_fetch_result($query, 0, 1);
> 
>           $edge['target']   = pg_fetch_result($query, 0, 2);
> 
>           $edge['the_geom'] = pg_fetch_result($query, 0, 3);
> 
>           // Close database connection
> 
>           pg_close($con);
> 
>           return $edge;
> 
>     }
> 
>                  $sql = "SELECT route.id2, ST_AsGeoJSON(ways.the_geom) 
> AS geojson, ST_length(ways.the_geom) AS length,ways.gid
> 
>                 FROM pgr_dijkstra('SELECT gid AS id, source::integer, 
> target::integer, length::double precision AS cost FROM ways', 
> ".$startEdge['source'].", ".$endEdge['target'].", false, false ) AS 
> route LEFT JOIN ways ON route.id2 = ways.gid;";
> 
>     // Connect to database
> 
>     $dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." 
> user=".PG_USER." password=".PG_PASSWORD);
> 
>     // Perform database query
> 
>     $query = pg_query($dbcon,$sql);
> 
> // Return route as GeoJSON
> 
>      $geojson = array(
> 
>         'type'      => 'FeatureCollection',
> 
>         'features'  => array()
> 
>      );
> 
>      // Add edges to GeoJSON array
> 
>      while($edge=pg_fetch_assoc($query)) {
> 
>         $feature = array(
> 
>            'type' => 'Feature',
> 
>            'geometry' => json_decode($edge['geojson'], true),
> 
>            'crs' => array(
> 
>               'type' => 'EPSG',
> 
>               'properties' => array('code' => '4326')
> 
>           ),
> 
>            'properties' => array(
> 
>               'id' => $edge['gid'],
> 
>               'length' => $edge['length']
> 
>            )
> 
>         );
> 
>         // Add feature array to feature collection array
> 
>         array_push($geojson['features'], $feature);
> 
>      }
> 
>      // Close database connection
> 
>      pg_close($dbcon);
> 
>      // Return routing result
> 
>      //header('Content-type: application/json',true);
> 
>      echo json_encode($geojson);
> 
> ==============================================
> 
> It also works fine, but the data inside result sometimes is not correct 
> (as I think).
> 
> When I get the result I want to merge all segments into one big path. If 
> result is as in example 0 – it’s perfect case. But more often directions 
> of the segments are unpredictable. So it’s driving me crazy to 
> manipulate them and merging.
> 
> Below are couple of examples (in all examples on the left is start point 
> and in the right is end point):
> 
> 0. Perfect path, where each segment starts where previous ends:
> 
> |----------------------------------------->|------------------------->|---------------------------->|--------------->|
> 
> 1. First segment in right direction, others in random.
> 
> |----------------------------------------->|<-------------------------|<----------------------------|--------------->|
> 
> 2. First segment in wrong direction, others in random.
> 
> |<-----------------------------------------|<-------------------------|<----------------------------|--------------->|
> 
> So I would like that all segment will be in right directions: from start 
> to end – as in example 0.
> 
> How can I achieve it?
> 
> Do I need additional manipulation with DB? Do I need to change SQL 
> queries? Do maybe I need just to change merging algorithm?
> 
> Thanks in advance!
> 
> Best regards,
> 
> Pavel.
> 


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus