[GIS] How are housenumbers and streets linked in a OSM based PostGIS DB

openstreetmappostgis

I'm trying to understand how OSM is linking housenumbers and streets with each other. I downloaded an osm file for germany and imported it into a PostGIS DB.
To understand how the linking is working I just took a street with a housenumber and exported it as xml from osm.org.
I opened the xml file and saw the entry for the housenumber and the street and it looked like this:

<way id="141827749" user="MichaH" uid="8464" visible="true" version="1" changeset="10149457" timestamp="2011-12-18T18:41:33Z">
 <nd ref="1005720791"/>
 <nd ref="1552517039"/>
 <nd ref="1552517025"/>
 <nd ref="1005720152"/>
 <nd ref="1005720791"/>
 <tag k="addr:city" v="Frankfurt"/>
 <tag k="addr:housenumber" v="16"/>
 <tag k="addr:postcode" v="60326"/>
 <tag k="addr:street" v="Wörsdorfer Straße"/>
 <tag k="building" v="yes"/>
</way>

Obviously I took the id=141827749 and found one entry in planet_osm_ways.
Result:

id        | nodes                                                    | tags
141827749 | {1005720791,1552517039,1552517025,1005720152,1005720791} | {building,yes,addr:housenumber,16}

So I got the housenumber. But where is the street name? When I search the tags for the street name i get 3 entries pointing to other nodes. The correct entry for it is as I know (because its a residential area):

id      | nodes                                                                                                                  | tags
6235469 | {52082395,1552517028,1552516972,1552516886,1552516856,1552516836,1552516769,1552516705,1552516652,1552516571,52082405} | {oneway,yes,name,"Wörsdorfer Straße",highway,residential}

But where is the link between all this? Does anybody know? I didnt find any info on the OSM wiki pages.

Thanks in advance.

Best Answer

It depends on how the data has been imported. Looking at http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema it looks like you should be using planet_osm_line or planet_osm_roads since planet_osm_ways is temporary. However, if the names were not specified to be included as a tag on the import (unlikely) it could be that they are not being imported into the database. You'll have to specify how the xml was imported into the database, because there are a large number of different ways of doing it.

The nature of OSM means that there is no de-facto standard for translating the xml file into a database schema, although there are several common ways of doing it, facilitated by software.

Related Question