Re: improving speed of mysql distance filter

[Joost]

I've got the same problem. I've got a table holding >500.000 lat, lng values and want to find the ones closest as fast as possible.

Aubrey: I already did what you suggested.

I use the following SQL to find distances (in km):
"3963.191 * ACOS( (SIN(PI()*41.883190/180)*SIN(PI()*MapLat/180)) + (COS(PI()*41.883190/180)*cos(PI()*MapLat/180)*COS(PI()*MapLong/180-PI()*-87.627940/180)))

(input = decimal degrees.. radians in the database would speed up the whole thing a lot probably?!?!??)

To calculate boundaries (for the WHERE clause) I use the following calculation (in Ruby):

delta = distance.to_f
dlat = delta / (Math::PI * EARTH_RADIUS/180)
lat_radius = Math.cos(@latRadians) * EARTH_RADIUS # The radius of the earth at given latitude.
dlng = delta / (Math::PI * lat_radius/180)

sw = (@lat-dlat, @lng-dlng) # SouthWest corner
ne = (@lat+dlat, @lng+dlng) # NorthEast corner

Please let me know if you can use this?! I also would like to know if there is a FAST(er) way in MySQL to select/order rows according to their distance to a latitude, longitude coordinate.