5

Options

$lat = '25.7742658';
$lng = '-80.1936589';
$miles = 30;

Query

SELECT *, 
   ( 3959 * acos( cos( radians($lat) ) 
   * cos( radians( lat ) ) 
   * cos( radians( lng ) - radians($lng) ) 
   + sin( radians($lat) ) 
   * sin( radians( lat ) ) ) ) AS distance 
FROM locations 
HAVING distance < $miles 
ORDER BY distance 
LIMIT 0, 20

I have a database table with 4 columns:

  • unique id
  • city name
  • latitude (lat)
  • longitude (lng)

I'm using the query on top to return locations that are within a specified number of miles from the specified coordinates. It seems to work but I'm not sure how accurate it is. I'm curios to know whether the query is good or if you have a better solution.

CyberJunkie
  • 21,596
  • 59
  • 148
  • 215

1 Answers1

3

that looks like the correct great circle distance query.

what are you concerned with wrt accuracy?

Randy
  • 16,480
  • 1
  • 37
  • 55
  • Thanks for confirming! I'm just concerned that there's a better solution. I read a little about MYSQL spatial extensions and I'm not sure whether I should implement that instead. – CyberJunkie Jul 04 '11 at 18:28
  • if this is what you are doing, then the extension is overkill. you should be good to go :) – Randy Jul 04 '11 at 18:41
  • 1
    The formula you are using is spherical law of cosines. Check it out from http://en.wikipedia.org/wiki/Spherical_law_of_cosines. Another formula for great circle distance is Haversine formula. Check it out from http://en.wikipedia.org/wiki/Haversine_formula. Both formulas assume a spherical earth. Law of cosines is faster to compute. You are on the right track. – O.C. Jul 08 '11 at 06:46
  • else you should consider is that you want to handle distances to distances few miles the formula for spheres is good, for longer distances, you would be a more complicated formula, but the distance to use this right. – phipex Mar 10 '16 at 14:14