Finding good examples for Calculating Distance in Miles from Latitude and Longitude Using Mysql and PHP wasn’t as easy as I thought it would be. I thought I would compile the working quires and the functions that I wrote/found.

I found a very good paper on the topic at Scribd. I used one the the examples from this paper.

 Here are a couple of PHP functions that I thought would be helpful.  Just a heads up, you will need and XML parser for the first function listed here(I have posted  the one I used for this function below).

getGeoCords

/**
 * getGeoCords()
 * uses geonames.org to get lat and long of postal codes
 * @param string $zip
 * @param string $country | use 2 letter , US
 * @author Jesse Baird <jebaird@gmail.com> 
 * @return array('lat','long')
 */
function getGeoCords($zip='',$country=''){
	
	if($zip!=''&&$country!=''){
		$url = 'http://ws.geonames.org/findNearbyPostalCodes?postalcode='.$zip.'&country='.$country.'&maxRows=1&style=short';
	$myFile = new XMLParser($url);
	//print_r($myFile);
	$xmlRoot = $myFile->data[0];    // GEO Element
	
	if (sizeof($xmlRoot) > 0 ) {
			$geoLocation = $xmlRoot['child'];
			if (sizeof($geoLocation) > 0) {
				$geoLocationProperties = $geoLocation[0]['child'];
			$long = $geoLocationProperties[4]['content'];
				$lat = $geoLocationProperties[3]['content'];
			return array('lat'=>$lat,'long'=>$long);
			}
			
	}else
		return false;
	}else
	return false;
	
}

useage

$cords=getGeoCords($zip,'US');

XMLParser Class

xml_url = $xml_url;
      // print($xml_url."
");
       $this->xml = xml_parser_create();
       xml_set_object($this->xml, $this);
       xml_set_element_handler($this->xml, 'startHandler', 'endHandler');
       xml_set_character_data_handler($this->xml, 'dataHandler');
       $this->parse($xml_url);
   }

   function parse($xml_url) {
   	//$bad_chr = array("x00" => "chr(0)", "x01" => "chr(1)", "x02" => "chr(2)", "x03" => "chr(3)", "x04" => "chr(4)", "x05" => "chr(5)", "x06" => "chr(6)", "x07" => "chr(7)", "x08" => "chr(8)", "x09" => "chr(9)", "x0a" => "chr(10)", "x0b" => "chr(11)", "x0c" => "chr(12)", "x0d" => "chr(13)", "x0e" => "chr(14)", "x0f" => "chr(15)", "x10" => "chr(16)", "x11" => "chr(17)", "x12" => "chr(18)", "x13" => "chr(19)", "x14" => "chr(20)", "x15" => "chr(21)", "x16" => "chr(22)", "x17" => "chr(23)", "x18" => "chr(24)", "x19" => "chr(25)", "x1a" => "chr(26)", "x1b" => "chr(27)", "x1c" => "chr(28)", "x1d" => "chr(29)", "x1e" => "chr(30)", "x1f" => "chr(31)");
   	
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $xml_url);
        curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
        //$store = curl_exec ($ch);
        $data = curl_exec ($ch);
      // print_r(sizeof($data)."
");
        curl_close ($ch);

      $parse = xml_parse($this->xml, $data, sizeof($data));
//$parse = xml_parse($xml_parser, strtr($data, $bad_chr),sizeof($data));
       if (!$parse) {
     	 
           die(sprintf("XML error: %s at line %d",
               xml_error_string(xml_get_error_code($this->xml)),
                   xml_get_current_line_number($this->xml)));
                   xml_parser_free($this->xml
                 );
       }
       return true;
   }

   function startHandler($parser, $name, $attributes) {
       $data['name'] = $name;
       if ($attributes) { $data['attributes'] = $attributes; }
       $this->data[] = $data;
   }

   function dataHandler($parser, $data) {
       if ($data = trim($data)) {
           $index = count($this->data) - 1;
           // begin multi-line bug fix (use the .= operator)
           $this->data[$index]['content'] .= $data;
           // end multi-line bug fix
       }
   }

   function endHandler($parser, $name) {
       if (count($this->data) > 1) {
           $data = array_pop($this->data);
           $index = count($this->data) - 1;
           $this->data[$index]['child'][] = $data;
       }
   }
}
?>

getDistanceSQL

I wrote the php for this function to make life easy on me.  It is very flexible and allows you to use it on any table.

/**
 * getDistanceSQL()
 *
 * @param mixed $lat
 * @param mixed $long
 * @param string $sql_lat_col | place_lat -- alows you to use it on any table
 * @param string $sql_long_col place_long
 * @return
 */
function getDistanceSQL($lat,$long,$sql_lat_col="",$sql_long_col=""){
	/*
	SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((41.648207 - abs(tournament_lat)) * pi()/180 / 2), 2) + COS(41.648207 * pi()/180 ) * COS(abs(tournament_lat) * pi()/180) * POWER(SIN((-91.541579-tournament_long) * pi()/180 / 2), 2) )) as distance From tournament
	*/
	return ', 3956 * 2 * ASIN(SQRT(
POWER(SIN(('.$lat.' - abs('.$sql_lat_col.')) * pi()/180 / 2), 2) + COS('.$lat.' * pi()/180 ) * COS(abs('.$sql_lat_col.') * pi()/180) * POWER(SIN(('.$long.'-'.$sql_long_col.') * pi()/180 / 2), 2) )) as distance';
	
}

useage

$sql="SELECT *";
//check to see if user is using this option
if($cords['lat']!='')
$sql.=getDistanceSQL($cords['lat'],$cords['long'],'tournament_lat','tournament_long');
$sql.=" FROM tournament ".$whereClause;
//having clause
if(is_numeric($r['miles'])){
	if($r['miles']>199)

//using the HAVING because WHERE you will end up with an error
$sql.='HAVING distance < '.$r['miles'];
else
$sql.='HAVING distance > '.$r['miles'];

}
$sql.= " ORDER BY tournament_date ASC";
//you end up with something like this
SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((42.524071 - abs(tournament_lat)) * pi()/180 / 2), 2) +
COS(42.524071 * pi()/180 ) * COS(abs(tournament_lat) * pi()/180) *
POWER(SIN((-92.449725-tournament_long) * pi()/180 / 2), 2) )) as
distance FROM tournament WHERE tournament_date between '1199167200' AND
'1228024800' AND 1 HAVING distance > 25 ORDER BY tournament_date ASC