MySQL Great Circle Distance (Haversine formula)

Question

I've got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query. I'd like to make it solely MySQL. Here's my current PHP Code:

if ($distance != "Any" && $customer_zip != "") { //get the great circle distance

    //get the origin zip code info
    $zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
    $result = mysql_query($zip_sql);
    $row = mysql_fetch_array($result);
    $origin_lat = $row['lat'];
    $origin_lon = $row['lon'];

    //get the range
    $lat_range = $distance/69.172;
    $lon_range = abs($distance/(cos($details[0]) * 69.172));
    $min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
    $max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
    $min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
    $max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
    $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
    }

Does anyone know how to make this entirely MySQL? I've browsed the Internet a bit but most of the literature on it is pretty confusing.

Answer 1

From Google Code FAQ - Creating a Store Locator with PHP, MySQL & Google Maps:

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

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;

Answer 2

I have had to work this out in some detail, so I'll share my result. This uses a zip table with latitude and longitude tables. It doesn't depend on Google Maps; rather you can adapt it to any table containing lat/long.

SELECT zip, primary_city, 
       latitude, longitude, distance_in_mi
  FROM (
SELECT zip, primary_city, latitude, longitude,r,
       (3963.17 * ACOS(COS(RADIANS(latpoint)) 
                 * COS(RADIANS(latitude)) 
                 * COS(RADIANS(longpoint) - RADIANS(longitude)) 
                 + SIN(RADIANS(latpoint)) 
                 * SIN(RADIANS(latitude)))) AS distance_in_mi
 FROM zip
 JOIN (
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r
   ) AS p 
 WHERE latitude  
  BETWEEN latpoint  - (r / 69) 
      AND latpoint  + (r / 69)
   AND longitude 
  BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
      AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
  ) d
 WHERE distance_in_mi <= r
 ORDER BY distance_in_mi
 LIMIT 30

Look at this line in the middle of that query:

    SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r

This searches for the 30 nearest entries in the zip table within 50.0 miles of the lat/long point 42.81/-70.81 . When you build this into an app, that's where you put your own point and search radius.

If you want to work in kilometers rather than miles, change 69 to 111.045 and change 3963.17 to 6378.10 in the query.

Here's a detailed writeup. I hope it helps somebody. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/