CODEWITHSUNDEEP
phpmysqlgeolocation
max_
max_

Reputation: 24481

Work out the nearest users MySQL

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?

Upvotes: 5

Views: 170

Answers (2)

zeros-and-ones
zeros-and-ones

Reputation: 4438

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";

Upvotes: 1

ToBe
ToBe

Reputation: 2681

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

Upvotes: 2

Related Questions