OpenStreetMap Proximity search using mySQL

Question

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

Answer 1

It may be faster to add extra columns to your table for the latitude and longitude expressed as the double data type (so the trigonometric functions stand a chance) - you may want to go further and precalculate the xaxis, yaxis and zaxis as columns (again, stored as double)

So, your new columns are loosely (you may need to add data type conversions as required):

XAxis   = cos(radians(Latitude / 10000000)) * cos(radians(Longitude / 10000000))
YAxis   = cos(radians(Latitude / 10000000)) * sin(radians(Longitude / 10000000))
ZAxis   = sin(radians(Latitude / 10000000))

Then, your proximity search becomes:

set @nodeid = 36187002;
set @dist = 10;
SELECT XAxis, YAxis, ZAxis
INTO @CntXAxis, @CntYAxis, @CntZAxis
FROM nodes
WHERE id=@nodeid limit 1;

SELECT id, ( 6371 * acos(
             CASE
                WHEN nodes.XAxis * @CntXAxis
              + nodes.YAxis * @CntYAxis
              + nodes.ZAxis * @CntZAxis > 1.0 THEN 1.0
              ELSE  nodes.XAxis * @CntXAxis
              + nodes.YAxis * @CntYAxis
              + nodes.ZAxis * @CntZAxis 
             END
           ) AS Distance
FROM nodes 
HAVING Distance < @dist 
ORDER BY distance LIMIT 0 , 100;