[RESOLVED] Zip code radius, etc.

[mrhinman]

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:

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

[dalecosp]

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?

[mrhinman]

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.

        <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 
            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;
                                        
                                }
                                
                            }
                        }
                    }
                }
            } 
        ?>

[Weedpacket]

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,

* cos(deg2rad(0)) 

Because doing nothing has to look really complicated.

[dalecosp]

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?

[Weedpacket]

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.