mysql - Finding Cities within 'X' Kilometers (or Miles)

Question

This may or may not be clear, leave me a comment if I am off base, or you need more information. Perhaps there is a solution out there already for what I want in PHP.

I am looking for a function that will add or subtract a distance from a longitude OR latitude value.

Reason: I have a database with all Latitudes and Longitudes in it and want to form a query to extract all cities within X kilometers (or miles). My query would look something like this...

Select * From Cities Where (Longitude > X1 and Longitude < X2) And (Latitude > Y1 and Latitude < Y2)

 Where X1 = Longitude - (distance)
 Where X2 = Longitude + (distance)

 Where Y1 = Latitude - (distance)
 Where Y2 = Latitude + (distance)

I am working in PHP, with a MySql Database.

Open to any suggestions also! :)

Answer 1

This is a MySQL query that will do exactly what you want. Keep in mind things like this are approximations generally, as the earth is not perfectly spherical nor does this take into account mountains, hills, valleys, etc.. We use this code on AcademicHomes.com with PHP and MySQL, it returns records within $radius miles of $latitude, $longitude.

$res = mysql_query("SELECT
    * 
FROM
    your_table
WHERE
    (
        (69.1 * (latitude - " . $latitude . ")) * 
        (69.1 * (latitude - " . $latitude . "))
    ) + ( 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3)) * 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3))
    ) < " . pow($radius, 2) . " 
ORDER BY 
    (
        (69.1 * (latitude - " . $latitude . ")) * 
        (69.1 * (latitude - " . $latitude . "))
    ) + ( 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3)) * 
        (69.1 * (longitude - " . $longitude . ") * COS(" . $latitude . " / 57.3))
    ) ASC");