[pgrouting-users] PGRouting question

Stephen Woodbridge woodbri at swoodbridge.com
Thu Jun 8 19:18:59 PDT 2017

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 at lists.osgeo.org

I would recommend that you look at the workshop examples as there is one

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."
>
>           \$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."
>
>     // 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
>
>
>      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?
>
>
> Best regards,
>
> Pavel.
>

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus