Working with address data extracted from *.osm

Question

My goal is get all the addresses from a certain country, ideally - from all countries, but for now - only from one.

I downloaded an *.osm file from here http://download.geofabrik.de/ for a certain territory which contained a few countries and extracted the data from that file using Osm2pgsql to a Postgres database. There were no errors, all went well.

But now I can't get along with those tables in the db, I can't figure out how actually to query all the addresses from a country I want?

Answer 1

Probably the first thing to do is to import your data again after you have read about addresses in OSM from http://wiki.openstreetmap.org/wiki/Addresses.

Why you should do import again is that the default osm2pgsql style file does not import all the keys which will likely need, for example addr:street https://github.com/openstreetmap/osm2pgsql/blob/master/default.style

Oncy you have imported data with the tags you want to have you can find addresses from osm_points table with

select * from osm_points where addr:housenumber is not null or addr:street is not null.

You can make the query better. More important, however, is to notice that the OSM addresses are very often given for the building polygons. Compare 28 million addresses on nodes and 14 million addressed on ways and relations in http://taginfo.openstreetmap.org/keys/addr%3Ahousenumber. That means that you must run the same address queries also from the osm_polygons table. If you want to get a unified address data you can convert buildings into points with

select ST_Centroid(way) as way from osm polygons where....

Here is something to start with. Consider still if you want to invent everything yourself or if you would rather use some ready made tool like Nominatim http://wiki.openstreetmap.org/wiki/Nominatim.

• Oskar K.: Why you should do import again is that the default osm2pgsql style file does not import all the keys which will likely need, for example addr:street - yes, but there's no mention about ":street" tag there.
• user30184: If you want it add node,way addr:street text linear into styles. It is up to you to take it or leave it, this tag is not always added to addresses.
• Oskar K.: how do I re-create the db easily, just delete 4 tables (planet_osm_point, planet_osm_line, planet_osm_roads, planet_osm_polygon) and run osm2pgsql again? How about views, functions?
• user30184: Run osm2pgsql again, it should drop automatically old tables.
• user30184: I mean to select all addresses which are inside the polygon that defines the borders of UK in your case. You do not have the polygon yet but once you have you can do a query by using the PostGIS function ST_Intersects postgis.net/docs/ST_Intersects.html. Something to read about spatial relationships is for example in webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/… and in postgis.net/docs/manual-2.0/…

Answer 2

You most probably don't want to invent your own geocoder because this is a really tricky task. Addresses in OSM are rarely stored as a full, complete set for every single object. Instead, pre-calculations have to be done in order to add administrative information like cities, municipalities, countries and so on.

Instead try to use one of the already available geocoders for OSM, for example Nominatim which currently powers the search on openstreetmap.org.