* Steve Bennett [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