0

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?

enter image description here

enter image description here

enter image description here

Can someone help me here?

2

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:

| improve this answer | |
  • How can I found out in which measure my costs are? Where exactly should I do the ST_Length(geom::GEOGRAPHY) and geom should be my geography value? I tried this in pgr_dijkstra() with the cost value and it says that it can not convert a double precision to geography. Can you maybe give an exact example? I am a bit confused with this stuff, because it is all kind of new for me. – Lars Jul 16 at 10:21
  • I edited the tag 'osm2po' since it wasn't really related to the question, but it may be after all. It seems that, if you want to know anything about that tool, you will have to contact the author. Nevertheless, it seems also that it creates a km column in the table, which you could use instead of the cost column. See updated answer. – geozelot Jul 16 at 10:37
  • I edited my post and add an overview of the database. Yesterday I tried it already with the km column instead of the cost column, but it did not work out --> gave me not the right answer. – Lars Jul 16 at 10:43
  • Seeing your added image: thegeom_way column is defined as GEOMETRY(LINESTRING, 4326), so the cost units are likely a degree value, since EPSG:4326 is a geographic CRS. If you wanted to use the type conversion, you will have to replace both cost and reverse_cost in the edge_sql with ST_Length(geom_way::GEOGRAPHY) AS cost/reverse_cost. – geozelot Jul 16 at 10:46
  • SELECT id, source, target, ST_Length(geom_way::GEOGRAPHY) AS cost, ST_LENGTH(geom_way::GEOGRAPHY) AS reverse_cost I changed this one in pgr_dijkstra now and got this agg_cost = 17644.794232367145 which stands for 17,6km. How can Google Maps give me 24,3km on the same route? Do you think the values of the OSM data are wrong or the import with Osm2po went wrong or you have some other idea? – Lars Jul 16 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.