I am tired of seeking a solution. :/ Help plz.

public | planet_osm_line
public | planet_osm_nodes
public | planet_osm_point
public | planet_osm_polygon
public | planet_osm_rels
public | planet_osm_roads
public | planet_osm_ways

I've seen a lot of docs but yet I could't do it.

asked 11 Apr '16, 16:35

SpbSprut's gravatar image

SpbSprut
21113
accept rate: 0%

edited 11 Apr '16, 16:40


There is not necessarily a direct link between houses and streets in OSM. The following situations are frequent:

  • house is mapped without a street address (addr:street tag) or relation (type=associated_street)
  • house has a street address tag
  • house is part of an associated_street relation

Unfortunately addr:street tags are not imported by default when you run osm2pgsql; you need to add that column to the config file or import with --hstore. Then you can do something like

SELECT way 
FROM planet_osm_polygon 
WHERE tags->'addr:street'='My Street'

You'll probably also want to check for tags->'addr:city' or for being contained in a certain administrative polygon. Accessing relations is possible when you have used --slim but difficult; it requires looking up the way ID of the house in question in the planet_osm_rels table. A simple alternative is loading all houses in a certain, small distance from a street like

SELECT h.way
FROM planet_osm_polygon h, planet_osm_line s
WHERE s.highway IS NOT NULL and s.name='My Street'
AND ST_DWITHIN(s.way, h.way, 50)

This will of course also find houses belonging to other streets within the specified distance.

permanent link

answered 11 Apr '16, 17:02

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
76.4k886861181
accept rate: 23%

Thank you so much!

SELECT way FROM planet_osm_polygon WHERE tags->'addr:street'='My Street'

it works for me

(12 Apr '16, 10:08) SpbSprut
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×129
×124
×38

question asked: 11 Apr '16, 16:35

question was seen: 3,298 times

last updated: 12 Apr '16, 10:18

powered by OSQA