* Steve Bennett <stevag...@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. _______________________________________________ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk