TransWikia.com

Getting meters from pgr_dijkstra

Geographic Information Systems Asked on September 3, 2021

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 "rn";
   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?

enter image description here

enter image description here

enter image description here

Can someone help me here?

One Answer

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:

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:

Correct answer by geozelot on September 3, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP