SELECT question

Question

Let's say I have a table containing a list of cities:

city | latitude | longitude
---------------------------
XX   | 34.800   | 48.550

Assuming I have an approximate location (latitude/longitude) of a user, how do I find the city that is nearest? That is, how do I find the city whose latitude and longitude is closest to the user's lat/long?

Answer 1

You could use the geospatial extensions for MySQL, or

The formula below will find the distance in nautical miles between two points.

3600 * acos(sin(latitude2_rads) * sin(latitude1_rads) +cos(latitude2_rads) * cos(latitude1_rads) * cos(longitude1_rads - longitude2_rads))

So you can connect this into a select as follows (in the following -7 is the required lat and -14 is the required lon)

Assuming that the lat/lon fields are in degrees:

select * FROM NDB as c1
order by  acos(sin(radians(-7))
          * sin(radians(latitude)) + cos(radians(-7))
          * cos(radians(latitude))
          * cos(radians(longitude) - radians(-14)))
limit 0,1

This may not be very efficient with large datasets, I just ran it against a table with 22,706 records and it took 0.163seconds.

If performance is an issue then it may be better to pre-compute the distance of all points from a fixed datum and then use that instead of computing it in the SQL.

Answer 2

Check out

Creating a Store Locator with PHP, MySQL & Google Maps

the calculation method presented there is independent from Google Maps, you should be able to get the complete algorithm from there.

One just needs to be wary of different mapping methods and the resulting different coordinates. Depending on what mapping your coordinates use, you may have to tweak the algorithm's parameters.

Answer 3

Google maps api has a new feature that does reverse geo caching

Have fun :)

More info on their wiki