Aiming to retrieve source and destination from Android, use pgrouting dijkstra to find shortest path and display it back on Android

Question

I have created an Android application that will accept source and destination from the user. It then calls a php page which takes in the lattitude and longitude of the destination and the source. I am using postgis in the backend, and have created the topology required for pgrouting. Following is my php code:

First I find the all the source integers thats near to my current location using the following function:-

    function         getSourceId($currentPositionLongitude,$currentPositionLattitude,$approximation,$dbConnectionString){
    // the query will return the source id of the user.
   $db_connection = pg_connect($dbConnectionString);
 $queryForSource = "select source  from mumbai_roads
where ST_Dwithin(geom,ST_GeomFromText('POINT( $currentPositionLongitude $currentPositionLattitude)'),$approximation)";
      $source=array();
        $myresult = pg_query($db_connection,$queryForSource);
        while($myrow = pg_fetch_assoc($myresult)) { 
             $resultSource = $myrow['source'];

      array_push($source, $resultSource);

        }
    //print_r($source);
    return $source;
}

Now i find all the target integers that are near my destination location:-

    function getTarget($destinationLongitude,$destinationLattitude,$approximation,$dbConnectionString){
   $db_connection = pg_connect($dbConnectionString);
  $queryForDestination = "select target  from mumbai_roads
where ST_Dwithin(geom,ST_GeomFromText('POINT( $destinationLongitude $destinationLattitude)'),$approximation)";
  $target = array();
    $myresult = pg_query($db_connection,$queryForDestination);
    while($myrow = pg_fetch_assoc($myresult)) { 
       $resultTarget = $myrow['target'];

      array_push($target, $resultTarget);
    }
    //print_r($target);
    return $target;
}

Finally as i have both the source and target integers, I try to get the shortest path for all the sources and the targets:-

if(!(empty($source) || empty($target))){
      /*looping through all the source and the targets */
      foreach ($source as $sourceId) {
        foreach ($target as $targetId) {
          $json = getShortestPath($sourceId,$targetId,$dbConnectionString);
          if($json!=""){
            $result=$result.$json;
              echo "$result";
          }
        }

      }
    }

function getShortestPath($source,$target,$dbConnectionString){
   $db_connection = pg_connect($dbConnectionString);
  $queryForShortestDistance = "select seq, id1 AS node, id2 AS edge, di.cost AS cost1, ST_AsGeoJSON(geom) AS geom
  FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM mumbai_roads',
    $source, $target, false, false
  ) as di
  JOIN mumbai_roads pt
  ON di.id2 = pt.id";
  $geom="";
     $myresult = pg_query($db_connection,$queryForShortestDistance);
    while($myrow = pg_fetch_assoc($myresult)) { 
       $geom = $myrow['geom'];

    }
    return $geom;
}

I am getting the path as a multiline which i am mapping on android, but using the above code, i am not getting the whole path, but just a small fraction of the whole path. Please help!

Answer 1

A pgRouting shortest path query returns a set of records, in other words a list road segments.

It might look like this after you join to also return the geometry attribute:

 seq | node | edge |        cost         |  the_geom
-----+------+------+---------------------+---------------
   0 |   30 |   53 |  0.0591267653820616 |     ...
   1 |   44 |   52 |  0.0665408320949312 |     ...
   2 |   14 |   15 |  0.0809556879332114 |     ...
   ...
   6 |   10 | 6869 |  0.0164274192597773 |     ...
   7 |   59 |   72 |  0.0109385169537801 |     ...
   8 |   60 |   -1 |                   0 |     ...
(9 rows)

If you want to have a single record instead you can aggregate your result for cost and the_geom:

SELECT sum(cost) AS cost, ST_Collect(the_geom) AS geom 
    FROM pgr_dijkstra( ...

More Information about ST_Collect and possible issues here: http://postgis.net/docs/ST_Collect.html