Nearest records by latitude / longitude in SQL

Filed under: PHP | 8 Comments

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

Read the latest posts

8 Responses to “Nearest records by latitude / longitude in SQL”

  1. misterhaan says:

    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.

  2. Jonez says:

    Thanks a lot for this. With correct keyword – i found your page #1 in google search ๐Ÿ™‚

  3. Sascha says:

    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

  4. David says:

    Many thanks! A really elegant solution to a problem that it so often over-complicated!

  5. Isaac Price says:

    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.

Leave a Reply