Last active
October 11, 2022 13:41
-
-
Save jesgs/4566290 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* Retrieve range of zip codes within given radius as | |
* determined by $rad | |
* Taken from http://www.movable-type.co.uk/scripts/latlong-db.html | |
* | |
* @global wpdb $wpdb | |
* | |
* @param integer $zip Zip code to query for | |
* @param integer $radius Radius in miles | |
* @return array|false Array containing results (zip and distance) | |
*/ | |
function get_zip_code_range($zip, $radius) | |
{ | |
global $wpdb; | |
// need coordinates first for our referenced zip code | |
$coords = $wpdb->get_row( | |
$wpdb->prepare( | |
"SELECT latitude AS lat, longitude AS lon " | |
. "FROM {$wpdb->prefix}zipcodes WHERE zip = %s", | |
$zip | |
) | |
); | |
if (!$coords) | |
return false; | |
$R = 6371; // earth's radius, km | |
$k2m = 0.621371; // kilometers to miles | |
$rad = floatval($radius * 1.60934); // original radius is in mi, need to convert to km | |
// | |
// first-cut bounding box (in degrees) | |
$maxLat = floatval($coords->lat + rad2deg($rad/$R)); | |
$minLat = floatval($coords->lat - rad2deg($rad/$R)); | |
// compensate for degrees longitude getting smaller with increasing latitude | |
$maxLon = floatval($coords->lon + rad2deg($rad/$R/cos(deg2rad($coords->lat)))); | |
$minLon = floatval($coords->lon - rad2deg($rad/$R/cos(deg2rad($coords->lat)))); | |
// convert origin of filter circle to radians | |
$lat = deg2rad($coords->lat); | |
$lon = deg2rad($coords->lon); | |
$sql = <<<SQL | |
SELECT zip, | |
ROUND(( | |
ACOS(SIN($lat) * SIN(radians(latitude)) | |
+ COS($lat) * COS(RADIANS(latitude)) | |
* COS(RADIANS(longitude) - $lon) | |
) * $R) * $k2m) AS distance | |
FROM ( | |
SELECT zip, latitude, longitude | |
FROM {$wpdb->prefix}zipcodes | |
WHERE latitude > $minLat AND latitude < $maxLat | |
AND longitude > $minLon AND longitude < $maxLon | |
) AS fc | |
WHERE ACOS(SIN($lat) * SIN(RADIANS(latitude)) | |
+ COS($lat) * COS(RADIANS(latitude)) | |
* COS(RADIANS(longitude) - $lon)) * $R < $rad | |
ORDER BY distance; | |
SQL; | |
/* | |
* note: not using $wpdb->prepare here is deliberate. The prepare method | |
* seems to mess with the values so I don't get anything useful, and | |
* I'm using numbers and hard-coded values which are being processed as | |
* numbers (integers and floats) before being added to the sql string | |
*/ | |
$results = $wpdb->get_results($sql); | |
return $results; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment