NL OV kaart

Preparing the database

Stored Procedure

CREATE OR REPLACE FUNCTION unnest_rel_members_ways(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT substring($1[i] from E'w(\\d+)') FROM
generate_series(array_lower($1,1),array_upper($1,1)) i WHERE $1[i] LIKE 'w%';$$;

Array Aggregate

CREATE AGGREGATE array_accum (anyelement)
( 
  sfunc = array_append, 
  stype = anyarray, 
  initcond = '{}'
);

The awesome query

SELECT ways, array_accum(ref)
FROM 
(
  SELECT DISTINCT unnest_rel_members_ways(members) AS ways, ref
  FROM planet_osm_rels AS rels,
  (
    SELECT -osm_id AS osm_id, ref
    FROM planet_osm_line 
    WHERE 
      (
        (
          route='bus' OR
          route='subway' OR
          route='tram'
        )
      AND osm_id < 0)
    GROUP BY osm_id, ref
  ) AS routes 
  WHERE rels.id=routes.osm_id
) AS unique_routes
GROUP BY ways;