[pgrouting-users] PGRouting question

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


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


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(




                               ".($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


          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


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


This email has been checked for viruses by Avast antivirus software.