Selecting data from database based on numbers

Question

Hey, guys,

I'm trying to figure out how to select data from a MySQL table based of closeness to a number. Here's what I mean.

I'm writing an application that stores the coordinates of places (longitude and latitude) what I'd like to be able to do is select data from the database based on the location of where the user is. So, say, for example, I've got three locations in the database: [(-70.425, 45.836), (-74.234, 41.639), (-75.747, 41.836)], and the user's location is (-74.345, 41.625). I'd like to be able to select the entries so that they spread out according to distance from the user, getting the three entries in this order: [(-74.234, 41.639), (-75.747, 41.836), (-70.425, 45.836)].

Is this even possible in MySQL, or am I going to have to select a few entries from the database and do the calculation in my programming language?

Answer 1

Take a look at this article

http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/

To quote

"So you have a whole table full of members or places with latitude and longitude’s associated with them. Just replace the $lat and $lon with the center point you want to find distances from. You can also change the distance<=10 to a number you want to search from. This will limit your results to all results that are under 10 miles from the starting point

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) ASdistanceFROMmembersHAVINGdistance<=’10′ ORDER BYdistanceASC

"

Note $lon and $lat would be your php fvariables. lat and lon (sans $) are the column names in this example i.e.

Table would be members with columns lat and lon