[pgrouting-users] sort result

pgRouting pgrouting-users
1

Hi,

I’m playing with Pgrouting using my own routing network (for bicycle) to
navigate from sub-line A to sub-line B:

SELECT rt.gid, AsText(rt.the_geom) AS wkt,
length(rt.the_geom) AS length, routen_sub.id
FROM routen_sub,
(SELECT gid, the_geom
FROM dijkstra_sp_directed(
'routen_sub',
5,
22,
true, true)
) as rt
WHERE routen_sub.gid=rt.gid;

First Question:
This works fine but the result geometry of my direction is unsorted. Is
there a possibility to sort the result directly via SQL?

Second Question:
Is it possible to set a "via" point directly in the SQL? With
dijkstra_sp_directed is it only possible to set a start and a endpoint.

Thanks
Sven

2

Hi

On Tue, 20 Mar 2012 13:50:46 +0100
"Sven Schroeter" <schroeter@netgis.de> wrote:

Second Question:
Is it possible to set a "via" point directly in the SQL? With
dijkstra_sp_directed is it only possible to set a start and a
endpoint.

I guess with "via" you mean something like a "waypoint"?

"Normally" that would be two queries
start -> via + via -> end
Then combine the result lists to get the complete path

Regards

max

3

Hi Sven,

This is an example query that solves the order problem :
SELECT rp.the_geom, rp.id_tron::integer, ST_AsGeoJSON(rp.the_geom) AS geojson, length(rp.the_geom) AS length, type_tron::integer, nom
FROM (SELECT edge_id, ROW_NUMBER() OVER (PARTITION BY 1) AS order_tron
FROM turn_restrict_shortest_path(
'SELECT id_tron::integer AS id, source::integer, target::integer,
cost::double precision, rev_cost::double precision AS reverse_cost
FROM reseau_global', 15326, 8756, true, true,
'SELECT to_cost::double precision, target_id::integer, via_path::text FROM reseau_global_rest')
) AS sp
JOIN (SELECT * FROM reseau_global) AS rp ON sp.edge_id = rp.id_tron ORDER BY order_tron;

This query is for trsp. That is why two queries are sent to the shortest_path function.
However, the "ROW_NUMBER() OVER (PARTITION BY 1) AS order_tron" instruction, combined with the ORDER BY order_tron shows you how to get an ordered result. This is necessary because there is a join in the external query.

Hope this help,

--
Christophe DAMOUR (SIGéal)
"Chez Charles"
16480 Saint Laurent des Combes
Téléphone/Télécopie : 05 45 98 36 06
Courriel : sigeal@sigeal.fr
Site : www.sigeal.fr

Le 20/03/2012 13:50, Sven Schroeter a écrit :

Hi,

I’m playing with Pgrouting using my own routing network (for bicycle) to
navigate from sub-line A to sub-line B:

SELECT rt.gid, AsText(rt.the_geom) AS wkt,
                        length(rt.the_geom) AS length, routen_sub.id
                     FROM routen_sub,
                         (SELECT gid, the_geom
                             FROM dijkstra_sp_directed(
                                 'routen_sub',
                                 5,
                                 22,
                                 true, true)
                          ) as rt
                     WHERE routen_sub.gid=rt.gid;

First Question:
This works fine but the result geometry of my direction is unsorted. Is
there a possibility to sort the result directly via SQL?

Second Question:
Is it possible to set a "via" point directly in the SQL? With
dijkstra_sp_directed is it only possible to set a start and a endpoint.

Thanks
Sven

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

4

On 3/20/2012 8:50 AM, Sven Schroeter wrote:

Hi,

I’m playing with Pgrouting using my own routing network (for bicycle) to
navigate from sub-line A to sub-line B:

SELECT rt.gid, AsText(rt.the_geom) AS wkt,
                        length(rt.the_geom) AS length, routen_sub.id
                     FROM routen_sub,
                         (SELECT gid, the_geom
                             FROM dijkstra_sp_directed(
                                 'routen_sub',
                                 5,
                                 22,
                                 true, true)
                          ) as rt
                     WHERE routen_sub.gid=rt.gid;

First Question:
This works fine but the result geometry of my direction is unsorted. Is
there a possibility to sort the result directly via SQL?

The results should be sorted, but the geometry may be reversed with resprct to the direction of travel and the geometry order in the database. I would write a simple stored procedure to reverse the segments as needed.

- check if the first edge needs to be reversed, ie if the source is in the 2nd edge (source or target).
- for the rest of the edges if last.target != this.source then reverse

Second Question:
Is it possible to set a "via" point directly in the SQL? With
dijkstra_sp_directed is it only possible to set a start and a endpoint.

If you are asking if you can specify the a start or end somewhere on an edge rather than a vertex, then consider using TRSP (it is in a branch in git and you will need to build it yourself).

If you are asking if you can create a route from A to B to C to ..., then you need to compute each leg as a separate query.

-Steve

Thanks
Sven

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users