[GIS] Extract addresses from osm file

addressopenstreetmappostgis

I am new in GIS, but I need extract addresses (city, street, housenumber, lat, lon) from my osm file. Can you give me advice what is the best way to do that? I found project openaddresses, but a lot of addresses of my area is missing.

Best Answer

  1. check if this addresses exists in osm project (osm.org website).
  2. If yes then keep in mind that addresses could be tagged on points and polygons (buildings) so you have to union data from 2 tables.
  3. Third is that in OSM not every address have to be complete - you could get only point with number but without city or street so you have to get rest of data from spatial relationship (nearest street or city boarder)

If you'll write which addresses you can see in osm and don't see in your database it'll be simpler to help you.

EDIT:

Assuming you're interested only in 100% sure addresses your query looks fine, but still you'll not get addresses where street and/or city is not set in tags. You can try as follows, but keep in mind that this values are not 100% sure... Also make sure you have proper indexes (including spatial) - without this query will run about 2 centuries :)

 select 
     osm_id, 
     name, 
     "addr:country" as country, 
     coalesce ("addr:city",(select name from planet_osm_polygon cit where st_intersects(adpol.way, cit.way) and admin_level = '8' limit 1)) as city, 
     coalesce ("addr:street",(select name from planet_osm_line str where name != '' and highway != '' order by st_distance(adpol.way,str.way) limit 1)) as street, 
     "addr:housenumber" as housenumber, 
     "addr:postcode" as postcode,
     st_x(st_transform(ST_Centroid(way), 4326)) as lon,
     st_y(st_transform(ST_Centroid(way), 4326)) as lat
 from planet_osm_polygon adpol
 where "addr:housenumber" <> ''
 union 
 select
     osm_id,
     name,
     "addr:country" as country, 
     coalesce ("addr:city",(select name from planet_osm_polygon cit where st_intersects(adpoi.way, cit.way) and admin_level = '8' limit 1)) as city, 
     coalesce ("addr:street",(select name from planet_osm_line str where name != '' and highway != '' order by st_distance(adpoi.way,str.way) limit 1)) as street,
     "addr:housenumber" as housenumber, 
     "addr:postcode" as postcode,
     st_x(st_transform(way, 4326)) as lon,
     st_y(st_transform(way, 4326)) as lat 
 from planet_osm_point adpoi
 where  "addr:housenumber" <> ''
Related Question