Sorting records by distance using PHP & MySQL

I recently got to the point in my project where I was ready to implement sorting for the search results.  I wanted to sort by the distance between two zip codes. However, I quickly realized I had a major problem on my hands. I had a (potentially) large number of search results that I needed to sort. Too many to sort using PHP; I needed a way to sort them in MySQL. But the method I’d planned to use to calculate the distance between zip codes, Micah Carrick’s distance calculation class, was implemented entirely in PHP. I needed some way for the distance to end up in the SQL query so that I could sort on it.

After searching around for a bit I came across this nice bit of code which creates a MySQL query using PHP variables to calculate the distance. By combining the two methods I was able to create a very workable solution for sorting by distance. Here’s what I did:

First, download the zipcode SQL from Micah’s page and run each SQL file. That should give you a new shiny table called zip_code filled to the brim with (what else?) zip codes.

Second, you’ll need the get_zip_point function from Micah’s code:

/**
 * This function pulls just the latitude and longitude from the
 * database for a given zip code.
 */
function get_zip_point($zip)
{
	$sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
	$r = mysql_query($sql);
	if (!$r)
	{
		$this->last_error = mysql_error();
		return FALSE;
	}
	else
	{
		$row = mysql_fetch_array($r);
		mysql_free_result($r);
		return $row;
	}
}

Third, you’ll want a function to generate the SQL you’ll use in your query:

/**
 * Returns the SQL to calculate distance based on latitude and longitude
 */
function get_distance_SQL($lat, $lon)
{
	$sql = "
	3957 * 2 *
	atan2(
		sqrt(
			pow((sin(0.0174*(lat-$lat)/2)),2) +
			cos(0.0174*$lat) * cos(0.0174*lat) *
			pow((sin(0.0174*(lon-$lon)/2)),2)
		)
		,
		sqrt(1-
			(
				pow((sin(0.0174*(lat-$lat)/2)),2) +
				cos(0.0174*$lat) * cos(0.0174*lat) *
				pow((sin(0.0174*(lon-$lon)/2)),2)
			)
		)
	)
	as distance";
 
	return $sql;
}

Fourth, you wrap it all together with a few lines of PHP. I’m using CodeIgniter to handle the SQL call, so your mileage may vary a bit on the exact implementation details.

list($lat, $lon) = $this->get_zip_point($user_zip_code);
 
$this->db->select("users.username, zip_code.city, zip_code.state_prefix," . $this->get_distance_SQL($lat, $lon), FALSE)
		->from('users')
		->join('zip_code',"users.zip_code = zip_code.zip_code")
		->order_by('distance', 'asc');
 
$search_results = $this->db->get();

And shazam, you now have a way to sort a set of SQL results by distance.