php/mysql zip code proximity search

Question

Hi,

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

Check out the proximity search featured here:

Using PHP/MySQL with Google Maps

If your data is in the same notation / projection / format (whatever that's called), it may work for you.

Answer 2

The lat/long you have for each zip code is a geographic center for that zip, right? So if you first find zip codes with geographic centers within 50 miles, then users in those zip codes, you could easily be returning users more than 50 miles away. So you'd sacrifice some accuracy doing it that way.

But if you have a lot of users (more than the number of zip codes), this would be faster, since you'd query against the smaller zip codes table first. And you could index zip codes in the users table, so finding users with a particular zip code would be fast.

Just some thoughts! So, if you are expecting a lot of users and the radius of 50 miles doesn't need to be exact, I would find zip codes within 50 miles, then users within those zip codes.

Answer 3

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 4

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 5

I'd consider reducing the number of candidates with a bounding square first, then worrying about the radius as a second step. You start off with the coordinates of the zipcode, then calculate the long/lat of 50 miles in all 4 directions, then select only candidates within that box using simple greater/less than criteria. If your user base is well spread out this reduces your candidate set considerably, then you only have to do vector distance math to eliminate the "corners".