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!

Answer 2

http://www.micahcarrick.com/04-19-2005/php-zip-code-range-and-distance-calculation.html

I found this very awesome.

"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)"

I found this is the best way to do it unless you need to put the users on a google map. If you're just listing the users in the mileage range it should be pretty easy to query the database (using the class) for a list of zips then select all users in those zipcodes.

Select * from Users where zip_code IN (19125,19081,19107.........);

That should do it.

Answer 3

I would first do a search for all the zipcodes in the radius of the target. Then compare all the returned zipcodes to your user table zipcodes. Pull out the matching users.

It find the zipcodes in a radius, found this MySQL call:

$query = 'SELECT zzip FROM ' . table . 
            ' WHERE (POW((69.1*(zlongitude-"' . 
            $long . '")*cos(' . $long . 
            '/57.3)),"2")+POW((69.1*(zlatitude-"' . 
            $lat . '")),"2"))<(' . $radius . 
            '*' . $radius . ')';

MySQL does all the math for you.

I found a class that uses this here: http://www.nucleusdevelopment.com/code/do/zipcode

Hope that helps.