5

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?

Francisco
  • 10,005
  • 5
  • 34
  • 42
max_
  • 23,337
  • 38
  • 120
  • 209

2 Answers2

2

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

ToBe
  • 2,619
  • 1
  • 16
  • 28
1

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";
zeros-and-ones
  • 3,925
  • 5
  • 32
  • 54