[pgrouting-users] sort result

["Christophe Damour (SIGéal)"]

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 at 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users