working_rel_ref.sql

(
  SELECT 
      w.way, string_agg(COALESCE(r.ref, w.ref),';') AS ref,
      w.highway, char_length(string_agg(COALESCE(r.ref, w.ref),';')) AS length
    FROM (
      SELECT 
          ctid, way, highway, ref
        FROM planet_osm_roads r
        WHERE r.highway in ('motorway','trunk','primary','secondary')
          AND r.way && !bbox!) AS w
      LEFT JOIN (
        SELECT 
            w.ctid AS wctid, ST_Collect(rels.way) AS geom, rels.ref as ref
          FROM (
            SELECT 
                ctid, way, highway, ref
              FROM planet_osm_roads r
              WHERE r.highway in ('motorway','trunk','primary','secondary')
                AND r.way && !bbox!) AS w
            JOIN (
              SELECT osm_id, way, ref
                FROM planet_osm_line l
                WHERE osm_id < 0
                  AND route = 'road'
                  AND ref IS NOT NULL) AS rels
              ON (ST_Intersects(w.way, rels.way)) -- Doing filtering here is cheaper than the left join
          GROUP BY ctid, rels.ref, rels.osm_id) AS r
        ON (w.ctid = r.wctid -- optimization because we did a ST_Intersects earlier
          AND ST_Within(w.way, r.geom))
    WHERE r.ref IS NOT NULL OR w.ref IS NOT NULL
    GROUP BY w.way, w.highway
    ORDER BY CASE highway WHEN 'motorway' THEN 0 WHEN 'trunk' THEN 1 WHEN 'primary' THEN 2 WHEN 'secondary' THEN 3 END
  ) AS roads_text_ref_low_zoom