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
Post your comment
Comments
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments