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);