Mysql within distance query

Question

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.

Randy · Accepted Answer · 2011-07-04 18:23:05Z

3

that looks like the correct great circle distance query.

what are you concerned with wrt accuracy?

answered Jul 4, 2011 at 18:23

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CyberJunkie Over a year ago

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.

Randy Over a year ago

if this is what you are doing, then the extension is overkill. you should be good to go :)

O.C. Over a year ago

The formula you are using is spherical law of cosines. Check it out from en.wikipedia.org/wiki/Spherical_law_of_cosines. Another formula for great circle distance is Haversine formula. Check it out from 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.

phipex Over a year ago

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.

Collectives™ on Stack Overflow

Mysql within distance query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related