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?
Can someone help me here?
The unit is degree, as you seem to have geometries referenced in EPSG:4326, and the edge length as cost
.
If so, two options
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 meterST_Length(geom::GEOGRAPHY)
of all edges you joined back to the pgr_Dijkstra()
result set; again, the sum is the total distance in meterNote 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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP