CODEWITHSUNDEEP
phpsqlmysql
mike
mike

Reputation: 8141

php/mysql zip code proximity search

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!

Upvotes: 22

Views: 34127

Answers (7)

Pekka
Pekka

Reputation: 449385

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.

Upvotes: 2

Introgy
Introgy

Reputation: 541

Start here but note that the solution isn't very fast:

PHP Zip Code Range and Distance Calculation

Now to make it fast - we are going to replace the lookup to use a spatial index :)

  1. Use MySQL

  2. Add a column to the database called location and make it type POINT

  3. Make sure it accepts nulls right now

  4. Run the following SQL Query

    UPDATE zip_code SET location = PointFromText(CONCAT('POINT(',lon,' ',lat,')'));

  5. Now, make the column not accept nulls

  6. Add a spatial index to the location column

  7. In the code from the above project replace the function 'get_zips_in_range' with the following:

    function get_zips_in_range($zip, $range, $sort=1, $include_base) 
     {


    // returns an array of the zip codes within $range of $zip. Returns
    // an array with keys as zip codes and values as the distance from
    // the zipcode defined in $zip.

    $this->chronometer();                     // start the clock

    $details = $this->get_zip_point($zip);  // base zip details
    if ($details == false) return false;

    // This portion of the routine  calculates the minimum and maximum lat and
    // long within a given range.  This portion of the code was written
    // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
    // the time it takes to execute a query.  My demo took 3.2 seconds in
    // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!

    // Find Max - Min Lat / Long for Radius and zero point and query
    // only zips in that range.
    $lat = $details[0];
    $lon = $details[1];

    $return = array();    // declared here for scope

    $first = true;
    $radius = $range/69.172;
    $boundary = "POLYGON((";
    for($i=0; $i <= 360; $i += 360/24)
    {
        if($first)
        {
            $first = false;
        }
        else
        {
            $boundary .= ', ';
        }

        $clon = $radius*cos(deg2rad($i)) + $lon;
        $clat = $radius*sin(deg2rad($i)) + $lat;
        $boundary .= "$clon $clat" ;
    }

    $boundary  .= '))';

    $sql = "SELECT zip_code, city, county, state_name, state_prefix, area_code, time_zone, lat, lon FROM zip_code WHERE MBRContains(GeomFromText('$boundary'), location);";

    //echo $sql;
    $r = mysql_query($sql);

    if (!$r) {    // sql error

        $this->last_error = mysql_error();
        return false;

    } else {

        while ($row = mysql_fetch_row($r)) {

            // loop through the results to get the milage from src
            $dist = $this->calculate_mileage($details[0],$row[7],$details[1],$row[8]);
            if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
            $return[str_pad($row[0].', '.$row[1], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);

        }
        mysql_free_result($r);
    }

    // sort array
    switch($sort)
    {
        case _ZIPS_SORT_BY_DISTANCE_ASC:
            asort($return);
            break;

        case _ZIPS_SORT_BY_DISTANCE_DESC:
            arsort($return);
            break;

        case _ZIPS_SORT_BY_ZIP_ASC:
            ksort($return);
            break;

        case _ZIPS_SORT_BY_ZIP_DESC:
            krsort($return);
            break;
    }

    $this->last_time = $this->chronometer();

    if (empty($return)) return false;
    return $return;
   }

Upvotes: 2

headShrinker
headShrinker

Reputation: 33

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.

Upvotes: 2

Steve Prior
Steve Prior

Reputation: 322

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

Upvotes: 1

Chuck Burgess
Chuck Burgess

Reputation: 11574

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!

Upvotes: 25

mmundiff
mmundiff

Reputation: 3951

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.

Upvotes: 4

Derek Kurth
Derek Kurth

Reputation: 1898

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.

Upvotes: 0

Related Questions