Reputation: 997
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?
Upvotes: 18
Views: 26135
Reputation: 3654
None of the answers above work correctly across the Greenwich meridian. The Haversine formula:
// 6371 is the Earth's radius in km
6371 * 2 * ASIN(SQRT(
POWER(SIN((lat - abs(:latitude)) * pi()/180 / 2), 2)
+ COS(lat * pi()/180 ) * COS(abs(:latitude) * pi()/180)
* POWER(SIN((lon - :longitude) * pi()/180 / 2), 2)
)) as distance
which I took from here and which is referenced in this answer on a similar question, does work.
Upvotes: 1
Reputation: 5276
This is the formula that gave me the correct results (as opposed to the solutions above). Confirmed by using the Google Maps "Measure distance" feature (direct distance, not the transportation distance).
SELECT
*,
( 3959 * acos( cos( radians(:latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( latitude ) ) ) ) AS `distance`
FROM `locations`
ORDER BY `distance` ASC
:latitude
and :longitude
are the placeholders for the PDO functions. You can replace them with the actual values if you'd like. latitude
and longitude
are the column names.
3959
is the Earth radius in miles; the distance
output will be in miles as well. To change it to kilometers, replace 3959
with 6371
.
Upvotes: 6
Reputation: 1242
Using just SELECT * FROM Table WHERE lat between $minlat and $maxlat
will not be accurate enough.
The correct way to query distance is using the coordinates in radians.
<?php
$sql = "SELECT * FROM Table WHERE acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) * 6371 <= 1000";
Here is a handy reference - http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates
For example:
<?php
$distance = 100;
$current_lat = 1.3963;
$current_lon = -0.6981;
$earths_radius = 6371;
$sql = "SELECT * FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance";
And if you want to do the order by and show the distance:
<?php
$distance = 100;
$current_lat = 1.3963;
$current_lon = -0.6981;
$earths_radius = 6371;
$sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";
Edited for @Blazemonger and the avoidance of doubt :) If you want to work in degrees instead of radians:
<?php
$current_lat_deg = 80.00209691585;
$current_lon_deg = -39.99818366895;
$radians_to_degs = 57.2957795;
$distance = 100;
$current_lat = $current_lat_deg / $radians_to_degs;
$current_lon = $current_lon_deg / $radians_to_degs;
$earths_radius = 6371;
$sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";
You could easily wrap this up into a class that accepted Radians or Degrees from the information provided above.
Upvotes: 8
Reputation: 93003
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))";
Upvotes: 49
Reputation: 24665
wont give you results ordered by planar distance (not accounting for curvature of the earth) but for small radius' should work out.
SELECT * from table where lon between '$minLon' and '$maxLon' and lat between '$minLat' and '$maxLat' order by (abs(lon-$lon)/2) + (abs(lat-$lat)/2);
Upvotes: 0