/**
* Calculate Distance using SQL
*
* Calculates the distance, in miles, to a specified location using MySQL
* math functions within the query.
*
* @access private
* @param string
* @return float
*/
private function calcDistanceSql($location)
{
$sql = 'SELECT 3956 * 2 * ATAN2(SQRT(POW(SIN((RADIANS(t2.lat) - ' . 'RADIANS(t1.lat)) / 2), 2) + COS(RADIANS(t1.lat)) * ' . 'COS(RADIANS(t2.lat)) * POW(SIN((RADIANS(t2.lon) - ' . 'RADIANS(t1.lon)) / 2), 2)), ' . 'SQRT(1 - POW(SIN((RADIANS(t2.lat) - RADIANS(t1.lat)) / 2), 2) + ' . 'COS(RADIANS(t1.lat)) * COS(RADIANS(t2.lat)) * ' . 'POW(SIN((RADIANS(t2.lon) - RADIANS(t1.lon)) / 2), 2))) ' . 'AS "miles" ' . "FROM {$this->mysql_table} t1 INNER JOIN {$this->mysql_table} t2 ";
switch ($this->location_type) {
case ZipCode::LOCATION_ZIP:
// note: zip code is sanitized in the constructor
$sql .= "WHERE t1.zip_code = '{$this->zip_code}' ";
break;
case ZipCode::LOCATION_CITY_STATE:
$city = @mysql_real_escape_string($this->city);
$state = @mysql_real_escape_string($this->state_prefix);
$sql .= "WHERE (t1.city = '{$city}' AND t1.state_prefix = '{$state}') AND t2.zip_code = '{$zip_to}'";
break;
default:
throw new Exception('Invalid location type for ' . __CLASS__);
}
switch (ZipCode::locationType($location)) {
case ZipCode::LOCATION_ZIP:
$zip_to = $this->sanitizeZip($location);
$sql .= "AND t2.zip_code = '{$zip_to}'";
break;
case ZipCode::LOCATION_CITY_STATE:
$a = $this->parseCityState($location);
$city = @mysql_real_escape_string($a[0]);
$state = @mysql_real_escape_string($a[1]);
$sql .= "AND (t2.city = '{$city}' AND t2.state_prefix = '{$state}')";
break;
}
$r = @mysql_query($sql);
if (!$r) {
throw new Exception(mysql_error());
}
if (mysql_num_rows($r) == 0) {
throw new Exception("Record does not exist calculating distance between {$zip_from} and {$zip_to}");
}
$miles = mysql_result($r, 0);
mysql_free_result($r);
return $miles;
}
