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
via
> 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',
nn(ST_StartPoint(destination),0.0001,2,100,'usmr_route','source','geom_way')::int4,
nn(ST_EndPoint(destination),0.0001,2,100,'usmr_route','target','geom_way')::int4,
true,true) r join usmr_route o on r.edge_id=o.id) where
unitnumber='(something)'