Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
443 views
in Technique[技术] by (71.8m points)

php - Sorting MySQL query by Latitude/Longitude

Every user in my database has their latitude and longitude stored in two fields (lat, lon)

The format of each field is:

lon | -1.403976 
lat | 53.428691

If a user searches for other users within, say 100 miles, I perform the following in order to calculate the appropriate lat/lon range ($lat and $lon are the 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 for degrees longitude getting smaller with 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 then perform a query such as:

$query = "SELECT * FROM table WHERE lon BETWEEN '$minLon' AND '$maxLon' AND lat BETWEEN '$minLat' AND '$maxLat'";

This works fine, and I use a function to calulate and display the actual distance between users at output stage, but I'd like to be able to sort the results by decreasing or increasing distance at the query stage.

Is there any way of doing this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Remember Pythagoras?

$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY (POW((lon-$lon),2) + POW((lat-$lat),2))";

Technically that's the square of the distance instead of the actual distance, but since you're just using it for sorting that doesn't matter.

This uses the planar distance formula, which should be good over small distances.

HOWEVER:

If you want to be more precise or use longer distances, use this formula for great circle distances in radians:

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

(To get the distance in real units instead of radians, multiply it by the radius of the Earth. That's not necessary for ordering purposes, though.)

Latitude and longitude is assumed by the MySQL computation engine to be in radians, so if it's stored in degrees (and it probably is), you'll have to multiply each value by pi/180, approximately 0.01745:

$sf = 3.14159 / 180; // scaling factor
$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY 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 * FROM table 
    WHERE $mr >= $er * ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...