Work out the nearest users MySQL

Question

I have a database named users with the columns latitude and longitude, and I want to work out the nearest users (users in the users table) which are within a 50km radius to a certain latitude and longitude provided by $_POST.

Can you tell me how I could do that? Is there a certain mathematical function that needs to be used, or a function in MySQL like latitude_compare() or something?

possible duplicate of [Proximity Search](http://stackoverflow.com/questions/260335/proximity-search) — Paul Sasik, May 27 '12 at 16:24

ToBe · Accepted Answer · 2013-04-17T15:00:47.240

You can do something like this:

SELECT *,

(ACOS((SIN(RADIANS(ref_latitude))*SIN(RADIANS(latitude))) + 
(COS(RADIANS(ref_latitude))*COS(RADIANS( latitude ))*COS(RADIANS( longitude)
-RADIANS(ref_longitude)))) * 6371) AS distance

FROM table
ORDER BY distance ASC

Point A: ref_latitude ref_longitude

Point B: latitude longitude

My Lat/Lon Values are in degrees, like this one:

Dresden: 13.721067614881400 / 51.060033646337900

score 1 · Answer 2 · answered May 15 '13 at 07:09

Here is a code I have been using, this is in miles. It selects all lat and lng within given radius.

$q = "SELECT * ( 3959 * acos( cos( radians($current_lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($current_lng) ) + sin( radians($current_lat) ) * sin( radians( lat )))) AS distance FROM users HAVING distance < $radius";

Work out the nearest users MySQL

2 Answers2