[OSM-talk] Osm2pgsql/TileMill - how to detect whether way is in a route?

[Phil! Gold]

* Steve Bennett <stevagewp at gmail.com> [2013-03-19 10:52 +1100]:
> One way to fudge this is to render the way, then render the route
> relation over the top, obscuring it. But that doesn't work if you want
> to use line smoothing (eg, line-smooth:0.6). The route relation will
> form a smooth bezier curve the intersection of two ways, but the way
> rendering will form a sharp point.
> 
> So I'm just wondering if there is a way around this - some setting in
> osm2pgsql, some SQL magic I could play with.

I do something very similar with my highway shield rendering, which is
driven by route relations.  I go through the planet_osm_rels table that's
created by osm2pgsql's slim mode, which is considered cheating in some
circles.

My rendering has code at https://launchpad.net/osm-shields , but the meat
of what you're interested in is the following two functions:

    -- osm2pgsql stores tags in PostgreSQL arrays, where even-numbered array
    -- members are tags and odd-numbered members are the tags' values.  This
    -- is a convenience function to go through such an array and retrieve the
    -- value for a given tag.
    CREATE OR REPLACE FUNCTION osm_get_tag_value(tags TEXT[], tag TEXT)
    RETURNS TEXT
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    LANGUAGE plpgsql
    AS $$
    DECLARE
      tag_index INTEGER;
    BEGIN
      SELECT i INTO tag_index
        FROM (SELECT generate_subscripts(tags, 1) AS i) AS indices
        WHERE i % 2 = 1 AND tags[i] = tag;
      RETURN tags[tag_index + 1];
    END;
    $$;
    
    CREATE OR REPLACE FUNCTION shields_get_route_memberships(planet_osm_rels.id%TYPE)
    RETURNS fullref[]
    STABLE
    RETURNS NULL ON NULL INPUT
    COST 5000
    LANGUAGE SQL
    AS $$
      SELECT shields_replace_groups(array_agg(ROW(shields_sanitize_text(network), shields_sanitize_text(ref))::fullref))
        FROM (SELECT osm_get_tag_value(tags, 'route') route,
                     UPPER(osm_get_tag_value(tags, 'network')) network,
                     UPPER(COALESCE(NULLIF(osm_get_tag_value(tags, 'ref'), ''), osm_get_tag_value(tags, 'name'))) AS ref
                FROM planet_osm_rels
                WHERE parts && ARRAY[$1]
                  AND parts[way_off:array_upper(parts, 1)] && ARRAY[$1]) AS ref_inner
        WHERE route = 'road' AND network IS NOT NULL AND ref IS NOT NULL;
    $$;

There's a lot of shield-rendering-specific code in there.  A more succinct
query might be the following (where $1 is the OSM ID of the way for which
you want to determine route membership):

    SELECT *
      FROM (SELECT osm_get_tag_value(tags, 'route') route,
                   osm_get_tag_value(tags, 'network') network,
                   osm_get_tag_value(tags, 'ref') ref,
                   osm_get_tag_value(tags, 'name') name
              FROM planet_osm_rels
              WHERE parts && ARRAY[$1]
                AND parts[way_off:array_upper(parts, 1)] && ARRAY[$1]) AS ref_inner
      WHERE route IS NOT NULL;

Obviously, you can add your own parameters in the statement.