[RESOLVED] Zip code radius, etc.
Results 1 to 6 of 6

Thread: [RESOLVED] Zip code radius, etc.

  1. #1
    Member
    Join Date
    Feb 2006
    Posts
    77

    resolved [RESOLVED] Zip code radius, etc.

    Yeah, I know this stuff is all over the webz. But, I have yet to find a good way to use PHP/MySQL to pull a list of other zipcodes from a radius number.

    My example is this. My table contains the zip code along with the center latitude and longitude, as well as other data. I want a user to be able to choose mileage distances (such as 10,20,50, etc) and have the system return what zip codes are within X miles of the chosen zip code into an array for use in another script. There are over 80,000 records in the database.

    I want a fast, lean method for doing this and I know it requires more math than I know how to handle right now.

    Any ideas? I've tried to reverse engineer the point-to-point calculators out there, with no results.

    My basic HTML:
    HTML Code:
    <select name="distance">
    <option value="10">10 miles</option>
    <option value="20">20 miles</option>
    <option value="50">50 miles</option>
    </select>  <!-- User chooses a radius -->
    
    <input type="text" name="zipcode" maxlength="5"> <!-- User enters Zip -->

  2. #2
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,715
    http://www.movable-type.co.uk/scripts/latlong.html

    I've got access to a PHP function for this, but it's not mine, and it's proprietary.

    What have you tried?
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  3. #3
    Member
    Join Date
    Feb 2006
    Posts
    77

    Resolved!

    I found some snippets here and there and here's how I made out. It worked great. I'll just stick the results into an array and use that in further searches. Of course, I'll refine it some as I work out the final product.

    HTML Code:
            <form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="zipform">
                <label>Enter your ZIP Code: <input type="text" name="zipcode" size="6" maxlength="5" value="<?php echo $_POST['zipcode']; ?>" /></label>
                <br />
                <label>Select a distance in miles from this point:</label>
                <select name="distance">
                    <option<?php if($_POST['distance'] == "5") { echo " selected=\"selected\""; } ?>>5</option>
                    <option<?php if($_POST['distance'] == "10") { echo " selected=\"selected\""; } ?>>10</option>
                    <option<?php if($_POST['distance'] == "25") { echo " selected=\"selected\""; } ?>>25</option>
                    <option<?php if($_POST['distance'] == "50") { echo " selected=\"selected\""; } ?>>50</option>
                    <option<?php if($_POST['distance'] == "100") { echo " selected=\"selected\""; } ?>>100</option>
                </select>
                <br />
                <input type="submit" name="submit" value="Submit" />
            </form>
    PHP Code:
            <?php 
                
    if(isset($_POST['submit'])) {
                    if(!
    preg_match('/^[0-9]{5}$/'$_POST['zipcode'])) {
                        echo 
    "<p><strong>You did not enter a properly formatted ZIP Code.</strong> Please try again.</p>\n";
                    }
                    elseif(!
    preg_match('/^[0-9]{1,3}$/'$_POST['distance'])) {
                        echo 
    "<p><strong>You did not enter a properly formatted distance.</strong> Please try again.</p>\n";
                    }
                    else {
                        
    //connect to db server; select database
                        
    $link mysql_connect('HOST''USER''PASS') or die('Cannot connect to database server');
                        
    mysql_select_db('DATABASE') or die('Cannot select database');
                        
                        
    //query for coordinates of provided ZIP Code
                        
    if(!$rs mysql_query("SELECT * FROM table WHERE Zipcode LIKE '%$_POST[zipcode]%'")) {
                            echo 
    "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.</p>\n";
                        }
                        else {
                            if(
    mysql_num_rows($rs) == 0) {
                                echo 
    "<p><strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.</p>\n";    
                            }
                            else {
                                
    //if found, set variables
                                
    $row mysql_fetch_array($rs);
                                
    $lat1 $row['Lat'];
                                
    $lon1 $row['llong'];
                                
    $d $_POST['distance'];
                                
    $r 3959;
                                
                                
    //compute max and min latitudes / longitudes for search square
                                
    $latN rad2deg(asin(sin(deg2rad($lat1)) * cos($d $r) + cos(deg2rad($lat1)) * sin($d $r) * cos(deg2rad(0))));
                                
    $latS rad2deg(asin(sin(deg2rad($lat1)) * cos($d $r) + cos(deg2rad($lat1)) * sin($d $r) * cos(deg2rad(180))));
                                
    $lonE rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d $r) * cos(deg2rad($lat1)), cos($d $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));
                                
    $lonW rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d $r) * cos(deg2rad($lat1)), cos($d $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));
                                
                                
    //display information about starting point
                                //provide max and min latitudes / longitudes
                                
    echo "<table class=\"bordered\" cellspacing=\"0\">\n";
                                echo 
    "<tr><th>City</th><th>State</th><th>Lat</th><th>Lon</th><th>Max Lat (N)</th><th>Min Lat (S)</th><th>Max Lon (E)</th><th>Min Lon (W)</th></tr>\n";
                                echo 
    "<tr><td>".str_replace('"','',$row[City])."</td><td>".str_replace('"','',$row[State])."</td><td>$row[Lat]</td><td>$row[llong]</td><td>$latN</td><td>$latS</td><td>$lonE</td><td>$lonW</td></tr>\n";
                                echo 
    "</table>\n<br />\n";
                                
                                
    //find all coordinates within the search square's area
                                //exclude the starting point and any empty city values
                                
    $query "SELECT * FROM table WHERE (Lat <= $latN AND Lat >= $latS AND llong <= $lonE AND llong >= $lonW) AND (Zipcode != $_POST[zipcode]) ORDER BY State, City, Lat, llong";
                                if(!
    $rs mysql_query($query)) {
                                    echo 
    "<p><strong>There was an error selecting nearby ZIP Codes from the database.</strong></p>\n";
                                }
                                elseif(
    mysql_num_rows($rs) == 0) {
                                    echo 
    "<p><strong>No nearby ZIP Codes located within the distance specified.</strong> Please try a different distance.</p>\n";                                
                                }
                                else {
                                    
    //output all matches to screen
                                    
    echo "Zip codes within $_POST[distance] miles of the center of $_POST[zipcode]:<BR><BR>";
                                while(
    $row mysql_fetch_array($rs)) {
                                    echo 
    str_replace('"','',$row[Zipcode])." "str_replace('"','',$row[City]) ." ("str_replace('"','',$row[ZipCodeType]) .")<BR>";
                                        
    // echo acos(sin(deg2rad($lat1)) * sin(deg2rad($row['Lat'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['Lat'])) * cos(deg2rad($row['llong']) - deg2rad($lon1))) * $r;
                                            
                                    
    }
                                    
                                }
                            }
                        }
                    }
                } 
            
    ?>

  4. #4
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,889
    You'd think there'd be some extension to MySQL to provide for such tasks as these, seeing as how common they are. Other DBMSs do.

    Still,
    PHP Code:
    cos(deg2rad(0)) 
    Because doing nothing has to look really complicated.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  5. #5
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,715
    Quote Originally Posted by Weedpacket View Post
    Because doing nothing has to look really complicated.
    Bwahaha!

    Why Weed's so much smarter than the rest of us ... he actually reads it ALL


    You'd think there'd be some extension to MySQL to provide for such tasks as these, seeing as how common they are. Other DBMSs do.
    You mean a Distance() function?

    I wonder, their reasons ... technical? Philosophical?
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  6. #6
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,889
    Quote Originally Posted by dalecosp
    I wonder, their reasons ... technical? Philosophical?
    Now you mention it ... does MySQL have an extensible interface? You can certainly implement custom storage engines...

    ...ah: it does indeed. More to the point, there is a spatial extension.

    Looking at that, though, I don't see any spherical geometry (unlike, say, PostgreSQL's earthdistance module).

    Then again, on Earth at a scale of a few dozen miles, flat geometry is probably close enough. One hundred kilometres as the crow flies is 99.999 kilometres as the neutrino travels; for the error to be as large as one kilometre the two endpoints (assuming they're known exactly) would have to be something like a thousand kilometres apart.
    Last edited by Weedpacket; 05-11-2012 at 01:57 AM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •