3

Basically I send a GET request to a php file with the user lat and long values ($userLat, $userLong respectively).
In my database, I have a table 'Locations' that has the following columns:

  • Location_Name
  • Lat
  • Lng
  • Radius (in meters)

So, I wanna make a query where I compare the user's coordinates with all the Locations coordinates and see if the distance between the Location and the User is <= the radius of that location. If so, return the name of that Location.


The query would be something like this (pseudo-code):

SELECT Location_Name FROM Locations WHERE distanceBetween(($userLat, $userLong) AND (Lat, Lng)) <= Radius

Pedro Vieira
  • 3,330
  • 3
  • 41
  • 76
  • 2
    a very good description at http://de.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL – Sir Rufo Dec 19 '12 at 22:59
  • Duplicate of http://stackoverflow.com/questions/1896213/determine-if-one-coordinate-is-in-radius-of-another – Benny Hill Dec 20 '12 at 00:08
  • Here is an updated version that uses SPATIAL functions https://stackoverflow.com/questions/62739309/mysql-point-spatial-index-query-within-radius-from-center-point – ina Jul 05 '20 at 22:59

2 Answers2

2

The query you're looking for is:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

This was taken from a Google Map's article.

socca1157
  • 365
  • 3
  • 17
2

I use the same query as @socca1157 answered with but thought I could offer a tiny explanation of the constant 3959 at the start of the query.

My example also gets the lat and lng of a user's address

$address = urlencode($_GET['user-address']);

$earth_radius = 3959;
$search_radius = 100;

$ch = curl_init('https://maps.google.com/maps/api/geocode/json?address='.$address.'&sensor=false');

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$geocode = curl_exec($ch);
curl_close($ch);

$output = json_decode($geocode);

$lat = $output->results[0]->geometry->location->lat;
$lng = $output->results[0]->geometry->location->lng;


$sql = "SELECT *, ( $earth_radius * acos( cos( radians($lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM table WHERE isActive = 1 HAVING distance < $search_radius ORDER BY distance LIMIT 5";
$result = mysql_query($sql);

so, for example, if you wanted to search in km instead of miles, you'd use the radius of the earth in km, which is 6,371km.

martincarlin87
  • 10,848
  • 24
  • 98
  • 145