[GIS] geometry from relations – osm

openstreetmaposmosispostgispostgresql

Can someone show me the relevant documentation for relationships between tables?

I'm trying to get the geometry for an entry from the relations table but I'm unable to pick out the right information without this

Details:
schema: pgsnapshot (v0.6)
data-source: http://download.geofabrik.de/europe/great-britain/england/hampshire-latest.osm.pbf
db-load-command: osmosis –read-pbf ./hampshire-latest.osm.pbf –write-pgsql-dump directory=. enableBboxBuilder=yes

As far as I get:

select id, user_id, tags->'name' as name, tags->'admin_level' as level from relations limit 1;

   id   | user_id |   name   | level 
--------+---------+----------+-------
 108786 | 2089161 | Waverley | 8

select * from relation_members where member_id = 108786 limit 1;

 relation_id | member_id | member_type | member_role | sequence_id 
-------------+-----------+-------------+-------------+-------------
       57582 |    108786 | R           | subarea     |           4

select * from nodes where id = 57582;
 id | version | user_id | tstamp | changeset_id | tags | geom 
----+---------+---------+--------+--------------+------+------
(0 rows)

select * from ways where id = 57582;
 id | version | user_id | tstamp | changeset_id | tags | nodes 
----+---------+---------+--------+--------------+------+-------
(0 rows)

Best Answer

as you can see in your relation_members table, the member_role belonging to your id ( 57582 ) is 'R' <<-- Relation. So your id (108786) contains another relation.

Check: select * from relation_members where id = 57582; to see if there is another relation nested within this one.

Your can select the id from the nodes or way table if the corresponding member_role is 'N' = Node or 'W' = Way

Maybe this helps a bit more: --> http://wiki.openstreetmap.org/wiki/Relation

Related Question