sql - php/mysql zip code proximity search

Question

I'm just looking for suggestions on the best way to do this...

I need to create a search function that searches for "users" within a 50 mile radius of a zip code. I have a zip code table that contains all the U.S. zip codes with their latitude/longitude but I'm just trying to figure out the best way to structure and query my data...

Should I add latitude/longitude columns to the users table and query it for all the users within the radius of the given zip code? Or should I query the zip codes table for all the zip codes that fall within the radius then query the users table for all the users with the results(zip codes)? Or... ??? I am open to any suggestions at this point!

Thanks!

Answer 1

Here is the best way I have found. Of course it will require that you have all of your zipcodes lat/lon encoded in the database.

// get all the zipcodes within the specified radius - default 20
function zipcodeRadius($lat, $lon, $radius)
{
    $radius = $radius ? $radius : 20;
    $sql = 'SELECT distinct(ZipCode) FROM zipcode  WHERE (3958*3.1415926*sqrt((Latitude-'.$lat.')*(Latitude-'.$lat.') + cos(Latitude/57.29578)*cos('.$lat.'/57.29578)*(Longitude-'.$lon.')*(Longitude-'.$lon.'))/180) <= '.$radius.';';
    $result = $this->db->query($sql);
    // get each result
    $zipcodeList = array();
    while($row = $this->db->fetch_array($result))
    {
        array_push($zipcodeList, $row['ZipCode']);
    }
    return $zipcodeList;
}

You should be able to just drop in this function. Pass it the $lat and $lon of the zipcode you want the radius for, include the optional radius, and get a list of zipcodes back.

You could very easily modify this to get all users where zipcode IN (radius_sql) and get your list users back.

Happy Coding!