How to render overlapping bus trips as one route from OSM?

Question

I would like to create bus map for my city from OSM data.

I imported data into Postgresql with osm2psql and I can render routes with Mapnik. But the problem is that same route ref is shown multiple times and very close together.

I think this is because each bus route is made from multiple trips. For example bus line 4 has 11 different stop combinations but most of them go through same stops only in different order.

My question is how to avoid repeating name of the same route multiple times by any means necessary. (preprocessing XML, different SQL, carto css attributes) I found a post aboutOverlapping lines but this are lines from different routes and a different problem.

I fixed this a little by select distinct(way) but it isn't great.

I found out about ST_DIFFERENCE Postgis function but I don't know how to construct SQL that would create difference of all different trips on same route joined them and represents them as one.

I read similar question and added text-clip:true and text-avoid-edges:false to #Routes, buffer-size:768 to Map and changed MetaTile to 8 with no visible differences.

I render map with Tilemill with this cartocss:

#Routes {
  line-width:1.5;
  line-cap: round;
  ::labels {
    text-name: "[ref]";
    text-face-name: "Trebuchet MS Regular";
    text-placement:line;
  }
}

I select bus routes from DB like that:

(select distinct on (way)   way, ref from planet_osm_line WHERE "route" = 'bus'  order by way, ST_LENGTH(way) DESC) as buses

Answer 1

Instead of select distinct (which would not work, because all routes differ slightly, even if for a direction), use select ST_LineMerge(ST_Union(way)) as way, ref from ... where ... group by ref.