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

Answer 2

EDIT: If you have, somewhere, a list of all of the cities in the world along with their lat. and long. values, you can do a lookup. In this case, see my first link below for the formula to calculate the width of one longitudinal degree at latitude :

Honestly, the complications behind this problem are such that you'd be far better off using a service such as Google Maps to get your data. Specifically, the Earth is not a perfect sphere, and the distance between two degrees varies as you are closer to / further from the equator.

See http://en.wikipedia.org/wiki/Geographic_coordinate_system for examples of what I mean, and check out the Google Maps API.

Answer 3

Depending on how many cities you are including, you can precompute the list. We do this here for an internal application where an inaccuracy of +100m is too much for our setup. It works by having a two key table of location1, location2, distance. We can then pull back locations x distance from location1 very quickly.

Also since the calcs can be done offline, it doesn't impact the running of the system. Users also get faster results.