Nearest records by latitude / longitude in SQL
This is mostly a reminder for my future self, but maybe it will help someone else. Given a database table that contains columns for latitude and longitude (in decimal notation), here’s how to pull rows nearest to a point. For more complex operations it makes sense to use a database with geospatial features (perhaps PostegreSQL with PostGIS), but this is simple and will work fine with something like MySQL.
Let’s call our table “locations”, the latitude column “latitude” and the longitude column “longitude”. We’re checking for locations near the point [27.950898, -82.461517] and we want the nearest five locations. Here’s the SQL:
SELECT *,
SQRT(POW((69.1 * (locations.latitude - 27.950898)) , 2 ) +
POW((53 * (locations.longitude - -82.461517)), 2)) AS distance
FROM locations
ORDER BY distance ASC
LIMIT 5
The distance column in your results is formatted in miles and is reasonably accurate but nothing to bet your life on.
And as a bonus, you can use this function if you’re using PHP and want to convert the decimal points to degree (handy if your users want to input the point into their GPS):
/**
* Converts decimal formatted points to degree format
*
* @param float $pnt
* @return string
* @author Jon Gales
*/
function dec2deg($pnt)
{
if ($pnt > 0)
{
$deg = floor($pnt);
}
else
{
$deg = ceil($pnt);
}
$min_sec = (abs($pnt) - floor(abs($pnt))) * 60;
$sec = (abs($min_sec) - floor(abs($min_sec))) * 60;
return sprintf('%dยบ %d\' %2.3f"', $deg, round($min_sec), round($sec,3));
}
//Example usage, prints 27ยบ 57' 3.233"
echo dec2deg(27.950898);
distance is actually in degress. to get to miles you need to find the arc length for that number of degrees at the radius of the earth. so convert degrees to radians (multiply by pi/180), then multiply radians by radius to get arc length. the average radius of the earth is 3959 miles, so the factor works out to about 69.0975.
i missed the factors of 69.1 and 53 in the query the first time. i would have used 69.1 in both places; where does the 53 come from?
The numbers are an approximation, this is a much more simple formula that’s quicker to run than some more exact methods. For my needs it suited the bill perfectly (I was just looking for nearby zip codes). I’m not exactly sure what the numbers represent, but I know they work. Meridian World Data has some more info.
I don’t think you’re right, the results come out in miles. I just compared with Google Earth and it all lined up.
Thanks a lot for this. With correct keyword – i found your page #1 in google search ๐
Hi there,
thanks for this post! It was a great help for me… I was searching for hours to find an easy way ordering my db records by long and lat!!!
Thanks! ๐
Cheers
Sascha
Many thanks! A really elegant solution to a problem that it so often over-complicated!
Thanks for this post, I have been through tons of complex algorithms and queries. This is actually simple enough to understand and is the only one I have been able to make work with my app. Works well too I might add, accurate enough for what I need.
This is a life saver.
You rock.