PHP MySql and geolocation

Question

I am writing a site that basically looks for places within a 25 mile radius of a lat and long using php and mysql.

I am wondering how something like this would work?

I would pass a lat and long to the scrip and have it pull out only locations that are within 25 miles of the lat and long from my Database of locations.

What is the best way to do this?

EDIT: I found this code for calculating the distance between 2 points.

    function distance($lat1, $lon1, $lat2, $lon2, $unit) { 

  $theta = $lon1 - $lon2; 
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
  $dist = acos($dist); 
  $dist = rad2deg($dist); 
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344); 
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}

Is ther a way to do this calc in the MYSQL look up so I can only return if miles =< 25?

Answer 1

Calculating the distance using that function there is pretty computationally expensive, because it involves a whole bunch of transcendental functions. This is going to be problematic when you have a large number of rows to filter on.

Here's an alternative, an approximation that's way less computationally expensive:

Approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1) 
and y = 53.0 * (lon2 - lon1) 

You can improve the accuracy of this approximate distance calculation by adding the cosine math function:

Improved approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1) 
and y = 69.1 * (lon2 - lon1) * cos(lat1/57.3) 

Source: http://www.meridianworlddata.com/Distance-Calculation.asp

---

I ran a bunch of tests with randomly generated datasets.

• The difference in accuracy for the 3 algorithms is minimal, especially at short distances
• The slowest algorithm is, of course, the one with the trig functions (the one on your question). It is 4x slower than the other two.

Definitely not worth it. Just go with an approximation.
Code is here: http://pastebin.org/424186

---

To use this on MySQL, create a stored procedure that takes coordinate arguments and returns the distance, then you can do something like:

SELECT columns 
  FROM table 
 WHERE DISTANCE(col_x, col_y, target_x, target_y) < 25

Answer 2

You may want to take a look at this solution - a somewhat brilliat workaround.