Hi Pavel,
First, you should user the pgRouting-Users list for this type of question so everyone can learn from the answer and if I'm away there are lots of people that can answer if I can't.
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users << sign up here
I would recommend that you look at the workshop examples as there is one the directly answers your question.
http://workshop.pgrouting.org/2.1.0-dev/en/chapters/introduction.html
and specifically Exercise 13, but I think you would learn a lot by going through all the exercises as it is an excellent tutorial.
-Steve
On 6/8/2017 5:19 AM, Pavel Varnavsky wrote:
Hello Stephen!
I have question about PGRouting, hope you can help me.
Initial data:
I’ve got ESRI shapefile with roads (it was made by other man), which I put into PostGIS + PGRouting (2.4) DB.
Then I create topology on this table, create indexes – all work fine.
After it I create PHP script to get shortest path with Dijkstra algorithm:
==============================================
// Find the nearest edge
$startEdge = findNearestEdge($startPoint);
$endEdge = findNearestEdge($endPoint);
// FUNCTION findNearestEdge
function findNearestEdge($lonlat) {
// Connect to database
$con = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWORD);
$sql = "SELECT gid, source, target, the_geom,
ST_Distance(the_geom, ST_GeometryFromText(
'POINT(".$lonlat[0]." ".$lonlat[1].")', 4326)) AS dist
FROM ".TABLE."
WHERE the_geom && 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['gid'] = pg_fetch_result($query, 0, 0);
$edge['source'] = pg_fetch_result($query, 0, 1);
$edge['target'] = pg_fetch_result($query, 0, 2);
$edge['the_geom'] = pg_fetch_result($query, 0, 3);
// Close database connection
pg_close($con);
return $edge;
}
$sql = "SELECT route.id2, ST_AsGeoJSON(ways.the_geom) AS geojson, ST_length(ways.the_geom) AS length,ways.gid
FROM pgr_dijkstra('SELECT gid AS id, source::integer, target::integer, length::double precision AS cost FROM ways', ".$startEdge['source'].", ".$endEdge['target'].", false, false ) AS route LEFT JOIN ways ON route.id2 = ways.gid;";
// Connect to database
$dbcon = pg_connect("dbname=".PG_DB." host=".PG_HOST." user=".PG_USER." password=".PG_PASSWORD);
// Perform database query
$query = pg_query($dbcon,$sql);
// Return route as GeoJSON
$geojson = array(
'type' => 'FeatureCollection',
'features' => array()
);
// Add edges to GeoJSON array
while($edge=pg_fetch_assoc($query)) {
$feature = array(
'type' => 'Feature',
'geometry' => json_decode($edge['geojson'], true),
'crs' => array(
'type' => 'EPSG',
'properties' => array('code' => '4326')
),
'properties' => array(
'id' => $edge['gid'],
'length' => $edge['length']
)
);
// Add feature array to feature collection array
array_push($geojson['features'], $feature);
}
// Close database connection
pg_close($dbcon);
// Return routing result
//header('Content-type: application/json',true);
echo json_encode($geojson);
==============================================
It also works fine, but the data inside result sometimes is not correct (as I think).
When I get the result I want to merge all segments into one big path. If result is as in example 0 – it’s perfect case. But more often directions of the segments are unpredictable. So it’s driving me crazy to manipulate them and merging.
Below are couple of examples (in all examples on the left is start point and in the right is end point):
0. Perfect path, where each segment starts where previous ends:
|----------------------------------------->|------------------------->|---------------------------->|--------------->|
1. First segment in right direction, others in random.
|----------------------------------------->|<-------------------------|<----------------------------|--------------->|
2. First segment in wrong direction, others in random.
|<-----------------------------------------|<-------------------------|<----------------------------|--------------->|
So I would like that all segment will be in right directions: from start to end – as in example 0.
How can I achieve it?
Do I need additional manipulation with DB? Do I need to change SQL queries? Do maybe I need just to change merging algorithm?
Thanks in advance!
Best regards,
Pavel.
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus