Getting meters from pgr_dijkstra

Question

I imported http://download.geofabrik.de/europe/germany/baden-wuerttemberg.html into my PostgreSQL database which supports PostGIS. Now I wrote a PHP script which gets two points (lon, lat, which are currently hardcoded) and should calculate the distance between them with for example the Dijkstra-algorithm.

<?PHP
// Database connection settings
   define("PG_DB"  , "routing");
   define("PG_HOST", "localhost"); 
   define("PG_USER", "postgres");
   define("PG_PORT", "5432"); 
   define("PG_PASSWORD", "****");
   define("TABLE",   "bw_2po_4pgr");
   
   
   $lonlat = array(9.1829321, 48.7758459);
   $startEdge = findNearestEdge($lonlat);
   
   $lonlat = array(9.138027, 48.6408189);
   $endEdge = findNearestEdge($lonlat);
   
  
   function findNearestEdge($lonlat) {
   $con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWORD);
   
   $sql = "SELECT id, source, target, geom_way, ST_distance(geom_way, ST_GeometryFromText(
   'POINT(".$lonlat[0]." ".$lonlat[1].")', 4326)) AS dist 
   FROM ".TABLE."
   WHERE geom_way && 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['id'] = pg_fetch_result($query, 0, 0);
   $edge['source'] = pg_fetch_result($query, 0, 1);
   $edge['target'] = pg_fetch_result($query, 0, 2);
   $edge['geom_way'] = pg_fetch_result($query, 0, 3);
   
   pg_close($con);
  
   
   print_r($edge);
   return $edge;
   
   }
    
   $con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWORD);
   
   $sql = "SELECT * FROM pgr_dijkstra(
            'SELECT id, source, target, cost, reverse_cost
            FROM ".TABLE."',
            ".$startEdge['source'].",
            ".$endEdge['target'].",
            directed := true);";
                    
    $query = pg_query($con, $sql);
    
   while($res = pg_fetch_assoc($query)) {
       $sum = $res['agg_cost'];
   }
   
   echo "\r\n";
   echo $sum;
    
    
   pg_close($con);

This is my result when I call the PHP script from the browser.

Array ( [id] => 295007 [source] => 123354 [target] => 33187 [geom_way] => 0102000020E6100000040000000DE36E10AD5D22400512B98553634840ADDA3521AD5D22406127ABD84E634840EE395563AE5D22402D2059654B6348403E48AA4AB65D2240825E6EE646634840 ) 
Array ( [id] => 164375 [source] => 162839 [target] => 162822 [geom_way] => 0102000020E6100000030000000E791563AA46224086274FB4065248406F905A722647224054049376FE514840E129E44A3D472240FBECDBA4FD514840 ) 
0.3859884999999998

The last value of my result should be the agg_cost. But here I do not know what unit measurement is this and how I can get meter/kilometer out of that?

I controlled that my route is correct with QGIS and Google Maps. Both Routes look quite similar here, so somehow I should get the right distance between my two points?

Can someone help me here?

Answer 1

The unit is degree, as you seem to have geometries referenced in EPSG:4326, and the edge length as cost.

If so, two options

• if your cost attribute is the plain length of each edge (shortest path), use a cast to GEOGRAPHY instead, either in a pre-processing step to update the cost and reverse_cost columns, or directly in the edge_query passed to pgr_Dijkstra(), i.e. ST_Length(geom::GEOGRAPHY); the agg_cost will then be a measure in meter
• if your cost is a different measure, aggregate the ST_Length(geom::GEOGRAPHY) of all edges you joined back to the pgr_Dijkstra() result set; again, the sum is the total distance in meter

Note that you will have to pass in the same value for reverse_cost!

More on the two different spatial types:

• Is the Geometry(4326) an alias for Geography datatype?

Update:

I don't know much about osm2po, as it is hard to find information about it, but it creates a km column that likely denotes the edge length.

You can simply use that as your cost columns; replace cost and reverse_cost with km in the edge_sql string, and the agg_cost will be in km.

---

Check on spatial (K)NN searches to find closest edges to given locations more efficiently; some of my own answers, covering the overall concept and some notes about precision and units:

• Efficient way to find nearest feature between huge postgres tables
• Get barrier edge id
• Find closest polygon from point and get its attributes?
• How to UPDATE with LATERAL Nearest-Neighbour query?