[OSM-talk] Getting roles of relation's members in PostGIS using osm2pgsql

[Lennard]

Ciprian Talaba wrote:
> Thank you about this info. I will probably start with the last approach 
> (using planet_osm_rels and planet_osm_line tables) because this will 
> keep my code separate from osm2pgsql. I will try to publish my tries 
> because I believe others will be interested in something like this.

Some code snippets that may be useful in your endeavours:

Unnest an array. Later postgresql has this builtin, I think:

CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;

Unnesting a relation from planet_osm_rels gives you a list of all 
members, with the first character telling you what type of object it is, 
with the rest being the osm id.


Unnest only way members of a relation in the planet_osm_rels table:

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%';$$;

select unnest_rel_members_ways(members) as members from planet_osm_rels 
where id=<your relation id>;

This gives you a list of only way ids, 1 per row, with the first 'w' 
character stripped. You still need some way to join the member role in 
the results. I didn't get that far when I wrote the unnest function, as 
I didn't need the role at the time.

-- 
Lennard