2

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)
1

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

  • Ah right, Yes thats it. (select member_id from relation_members where relation_id = 57582 and member_type = 'W') Is there any osm database documentation that would answer my question? – Ryan Jun 27 '15 at 23:01
1

Did you try to check if this id (57582) is present in your table 'relations' ?

I'm not really used to the pgnapshot schema but i guess it should be referenced itself as a relationship in your database ?

Hope you will find (or already have) a solution to your answer!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.