[OSM-talk] collection/street relation: which one to use?

[Jukka Rahkonen]

Anthony <osm <at> inbox.org> writes:


> > So I prefer to add the street name to the street (as name) and addr:street
> > to the building/shop etc.
> 
> I think for now that's probably the best solution.  And just hope
> there aren't too many instances of "Main Street" on the addr vs. "Main
> St" on the way.  And do some sort of "nearby" search.
> 
> Unfortunately, that means I can't do a simple SQL query to find out
> "what street has the most addresses on it in the OSM database".  OTOH,
> I could probably come up with a fairly simple SQL query to answer "How
> many addresses are there on Broad Street in Philadelphia", especially
> if I'm willing to approximate Philadelphia as a rectangle.

Something like this might work with data imported with osm2pgsql

select count(*) from osm_polygon opoly, osm_line oline
where oline.highway is not null
and opoly.building is not null
and oline.name=opoly.addr:street
and [distance from street to buildings is less than x meters];

However, the distance query is not very simple for the whole 
world data, see 
http://postgis.refractions.net/
pipermail/postgis-users/2009-February/022648.html

There is no need to approximate Philadelphia as a rectangle, you can 
use the whole geometry in spatial "intersects" filter