php - Sorting MySQL query by Latitude/Longitude -

every user in database has latitude , longitude stored in 2 fields (lat, lon)

the format of each field is:

lon | -1.403976  lat | 53.428691 

if user searches other users within, 100 miles, perform following in order calculate appropriate lat/lon range ($lat , $lon current users values)

$r = 3960;  // earth's mean radius $rad = '100'; // first-cut bounding box (in degrees) $maxlat = $lat + rad2deg($rad/$r); $minlat = $lat - rad2deg($rad/$r); // compensate degrees longitude getting smaller increasing latitude $maxlon = $lon + rad2deg($rad/$r/cos(deg2rad($lat))); $minlon = $lon - rad2deg($rad/$r/cos(deg2rad($lat)));  $maxlat=number_format((float)$maxlat, 6, '.', ''); $minlat=number_format((float)$minlat, 6, '.', ''); $maxlon=number_format((float)$maxlon, 6, '.', ''); $minlon=number_format((float)$minlon, 6, '.', ''); 

i can perform query such as:

$query = "select * table lon between '$minlon' , '$maxlon' , lat between '$minlat' , '$maxlat'"; 

this works fine, , use function calulate , display actual distance between users @ output stage, i'd able sort results decreasing or increasing distance @ query stage.

is there way of doing this?

remember pythagoras?

$sql = "select * table      lon between '$minlon' , '$maxlon'        , lat between '$minlat' , '$maxlat'     order (pow((lon-$lon),2) + pow((lat-$lat),2))"; 

technically that's square of distance instead of actual distance, since you're using sorting doesn't matter.

this uses planar distance formula, should on small distances.

however:

if want more precise or use longer distances, use this formula great circle distances in radians:

dist = acos[ sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lng1-lng2) ] 

(to distance in real units instead of radians, multiply radius of earth. that's not necessary ordering purposes, though.)

latitude , longitude assumed mysql computation engine in radians, if it's stored in degrees (and is), you'll have multiply each value pi/180, approximately 0.01745:

$sf = 3.14159 / 180; // scaling factor $sql = "select * table      lon between '$minlon' , '$maxlon'        , lat between '$minlat' , '$maxlat'     order acos(sin(lat*$sf)*sin($lat*$sf) + cos(lat*$sf)*cos($lat*$sf)*cos((lon-$lon)*$sf))"; 

or even:

$sf = 3.14159 / 180; // scaling factor $er = 6350; // earth radius in miles, approximate $mr = 100; // max radius $sql = "select * table      $mr >= $er * acos(sin(lat*$sf)*sin($lat*$sf) + cos(lat*$sf)*cos($lat*$sf)*cos((lon-$lon)*$sf))     order acos(sin(lat*$sf)*sin($lat*$sf) + cos(lat*$sf)*cos($lat*$sf)*cos((lon-$lon)*$sf))";