[pgrouting-users] Pgrouting-users Digest, Vol 54, Issue 23

On Tue, Mar 26, 2013 at 2:00 PM, <pgrouting-users-request@lists.osgeo.org>wrote:

> Hello. I'm using pgRouting for traffic congestion analysis. After
> gathering enough data I'd like to display estimated traffic congestion
> Google Maps API using pgRouting edges geometries as reference. How can I
> aggregate pgRouting edges to segments representing larger road sections,
> eg. between two major intersections?

You could use ST_Union to aggregate geometries, but you need to be able to
define where a major intersection is.
For example you could loop through your result and when there is a change
of a road name, you would start a new aggregation.

I found that st_collect worked best:

update units set route=(SELECT st_setsrid(ST_LineMerge(
st_collect(ST_AsText(geom_way))),4326) FROM shortest_path_astar
('SELECT id, source::int4, target::int4, reverse_cost, cost,x1, y1,x2, y2
FROM usmr_route order by id',


true,true) r join usmr_route o on r.edge_id=o.id) where