More

How to aggregate pgr_dijkstra results by attribute?


I working with a table with a lot of points per road/street. I'm using pgRouting'spgr_dijkstramethod 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);

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;

No, there is no built-in function in pgRouting. But you can use PostgreSQL and PostGIS aggregate functions, such assumfor yourcostcolumn and ST_Collect or ST_Union for geometries.


Watch the video: Multimengen (October 2021).