Re: improving speed of mysql distance filter

Posted by:

**Joost**
Date: June 28, 2006 01:40AM

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.

Subject

Views

Written By

Posted

9518

May 19, 2006 05:27PM

3408

June 17, 2006 02:52PM

3373

June 17, 2006 03:12PM

**Re: improving speed of mysql distance filter**

4845

June 28, 2006 01:40AM

5423

October 05, 2006 09:56AM

3175

June 28, 2006 11:27AM

3678

June 29, 2006 07:54PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.