1

I working with a table with a lot of points per road/street. I'm using pgRouting's pgr_dijkstra method to give me a path, but it returns too many points per road/street, I'd like to know if there is a built-in method in pgRouting to aggregate the results by column, in this case the same street name IF it exists. I want to transform the results table:

seq | cost |   street   |     st_asgeojson     |
----+------+------------+----------------------|
 1  |  2.5 |  Street 1  | {coordinates: {...}  |
 2  |  4.5 |  Street 1  | {coordinates: {...}  |
 3  |  2.1 |  Street 2  | {coordinates: {...}  |
 4  |  1.9 |  Street 2  | {coordinates: {...}  |
 5  |  2.9 |  Street 1  | {coordinates: {...}  |
 6  |  3.0 |            | {coordinates: {...}  |
 7  |  4.5 |            | {coordinates: {...}  |

into this:

seq | cost |   street   |     st_asgeojson     |
----+------+------------+----------------------|
 1  |  7.0 |  Street 1  | {coordinates: {...}  |
 2  |  4.0 |  Street 2  | {coordinates: {...}  |
 3  |  2.9 |  Street 1  | {coordinates: {...}  | 
 4  |  3.0 |            | {coordinates: {...}  |
 5  |  4.5 |            | {coordinates: {...}  |

the current query is something like this:

SELECT seq, cost::double precision, street::character varying(192), 
    ST_AsGeoJSON(geom) 
    FROM pgr_dijkstra('SELECT gid AS id,    
                              source::integer,
                              target::integer,
                              cost::double precision AS cost 
                              FROM my_table,                                                                                      
                                  get_nearest_vertex_to_lon_lat(lat_st, long_st),       
                                  get_nearest_vertex_to_lon_lat(-9.1511504, 38.7487786), 
                                        false, 
                                        false) 
    a LEFT JOIN my_table b ON (a.id2 = b.gid);
2

The solution is not pretty, but I think this should work for you:

WITH p AS (SELECT seq, cost::double precision, 
     street::character varying(192), geom,
   -- if a street name is not same as previous street name,
   -- its the begining of a new group
     COALESCE((lag(COALESCE(b.street,'')) OVER(ORDER BY r.seq) ) 
        != COALESCE(b.street,''),true)  As is_grouping  
FROM pgr_dijkstra('SELECT gid AS id,    
                          source::integer,
                          target::integer,
                          cost::double precision AS cost 
                          FROM my_table',                                                                                      
                              get_nearest_vertex_to_lon_lat(lat_st, long_st),       
                              get_nearest_vertex_to_lon_lat(-9.1511504, 38.7487786), 
                                    false, 
                                    false) 
a LEFT JOIN my_table b ON (a.id2 = b.gid) )
-- break distinct groups into ranges
 ps AS (SELECT p.seq AS start_seq, 
       COALESCE((lead(p.seq) OVER (ORDER BY p.seq) - 1),p.seq) As end_seq
     FROM p  
   WHERE is_grouping
 )
-- aggregate by grouping sets
SELECT ps.start_seq, ps.end_seq, p.street, SUM(p.cost) As cost,   
   ST_AsGeoJSON(ST_Union(p.geom)) As gjson
  FROM p INNER JOIN ps ON (p.seq BETWEEN ps.start_seq AND ps.end_seq)
  GROUP BY ps.start_seq, ps.end_seq, p.street
  ORDER BY ps.start_seq; 
1

No, there is no built-in function in pgRouting. But you can use PostgreSQL and PostGIS aggregate functions, such as sum for your cost column and ST_Collect or ST_Union for geometries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.