zip-code-range.php

<?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;

}