Haversine Formula / Magic Square / Circle

This function would do this (providing you have Member extended with PostCode)

function FilterMemberByPostCodeDistance($params, $query){
	$query->where('Member.PostCode IS NOT NULL')
		->innerJoin('PostCodeToLocation',"SUBSTRING_INDEX(SUBSTRING_INDEX(Member.PostCode,' ', 1),' ',-1) = PostCodeToLocation.OutCode");

	$latitude = (float)$postCodeToLocation->Latitude;
	$longitude = (float)$postCodeToLocation->Longitude;

	$fTemp = floatval($params['Distance']) / 111.045;
	$fMagicSquareMinLatitude = $latitude - $fTemp;
	$fMagicSquareMaxLatitude = $latitude + $fTemp;

	$fTemp = 50.0 / (111.045 * cos(deg2rad($latitude)));
	$fMagicSquareMinLongitude = $longitude - $fTemp;
	$fMagicSquareMaxLongitude = $longitude + $fTemp;

	$query->where(
		//Magic Square - this is a simple square to filter out most out of distance values before the magic circle
		//this is done because the circle calculation is much more expensive that the square

		'PostCodeToLocation.Latitude  BETWEEN '.$fMagicSquareMinLatitude.' AND '.$fMagicSquareMaxLatitude.'
			AND PostCodeToLocation.Longitude BETWEEN '.$fMagicSquareMinLongitude.' AND '.$fMagicSquareMaxLongitude

		//Magic Circle (https://en.wikipedia.org/wiki/Haversine_formula)
		//This is what does the complicated maths to determine if the postcode is in the the cirectle or not
		//not as we are using out codes only, this is a "good estimate" but not 100% accurate

		//.' AND acos(sin(RADIANS('.$latitude.'))
		//	* sin(RADIANS(PostCodeToLocation.Latitude))
		//	+ cos(RADIANS('.$latitude.'))
		//	* cos(RADIANS(PostCodeToLocation.Latitude))
		//	* cos(RADIANS(PostCodeToLocation.Longitude)
		//	- (RADIANS('.$longitude.'))))
		//	* 6371 <= '.($params['Distance'] * 1.60934) //Kilometers

		//REFACTOR of above to process more upfront within PHP
		.' AND acos(sin('.deg2rad($latitude).')
		   * sin(RADIANS(PostCodeToLocation.Latitude)) + '.cos(deg2rad($latitude))
		.' * cos(RADIANS(PostCodeToLocation.Latitude))
		   * cos(RADIANS(PostCodeToLocation.Longitude) - '.deg2rad($longitude).'))
		   * 6371 <= '.($params['Distance'] * 1.60934) //Kilometers
	);
	return $query;
}


This is the PostCodeToLocation


class PostCodeToLocation extends DataObject{

	static $db = array(
		'OutCode'		=> 'Varchar(5)',
		'Latitude'		=> 'Float',
		'Longitude'		=> 'Float'
	);

	public static $indexes = array(
		'OutCode'			=> true
	);

	public function PopulatePostCodeToLocationTable() {

		DB::query('TRUNCATE TABLE PostCodeToLocation');

		$arrPostCodetoLocations = file(BASE_PATH .'/mysite/.../postcode_outcode_to_latlong.csv');
		if(!empty($arrPostCodetoLocations))
			foreach ($arrPostCodetoLocations as $strPostCodetoLocation) {
				list ($strOutCode,$strLatitude,$strLongitude) = explode(',',$strPostCodetoLocation);
				DB::query("INSERT INTO PostCodeToLocation (OutCode, Latitude, Longitude )
					VALUES ('".$strOutCode."','".$strLatitude."','".$strLongitude."')"
				);
			}
	}
}

 

 I've uploaded the file here.

But this is just from a free source online

Rate this post

Post your comment

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments