OpenStreetMap Proximity search using mySQL
- by Chris
Hi,
I'm just playing around with a dataset of my region generated by JOSM. I moved it into a mySQL DB with the 0.6 API scheme using Osmosis and now I'm desperately trying the following:
I want to get all streets of a city.
AFAIK there is no tag/relation in the OSM data to determine this so I tried it using a proximity search to get all nodes in a radius around a node representing the city center.
Most of the time I looked at the approaches here: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula
What I got is the following SQL code that should get me the closest 100 nodes around the node with id 36187002 and within a radius of 10km.
set @nodeid = 36187002;
set @dist = 10;
select longitude, latitude into @mylon, @mylat from nodes where id=@nodeid limit 1;
SELECT id, ( 6371 * acos( cos( radians(@mylon) ) * cos( radians( latitude ) ) *
cos( radians( longitude ) - radians(@mylat) ) + sin( radians(@mylon) ) * sin( radians( latitude ) ) ) )
AS distance
FROM nodes HAVING distance < @dist ORDER BY distance LIMIT 0 , 100;
Well.. it doesn't work. :( I guess the main problem is that OSM lats/lons are multiplied by 10.000.000 and I don't know how I can correct this function to make it work.
Any ideas about this? All solutions/alternatives are very welcome!
Have a nice weekend!
Chris
