ZIP Radius Search sortation issue
January 24, 2010 - 10:07pm
You can possibly store the
You can possibly store the results and sort them via PHP array, but I did a tutorial on this a while back that uses the following sql query to calculate and whatnot.
SELECT *, (ROUND( SQRT( POW(69.1 * (? - latitude), 2) + POW(53 * (? - longitude), 2)), 1)) AS distance FROM atvi_tour_schedules HAVING distance < $distance ORDER BY distance ASC
http://blog.jylin.com/2009/10/...
It actually works really well and has been used on some high-traffic sites.
Lemme know your thoughts.
Hey everyone,
So I've worked on getting a ZIP Radius Search working for one of my sites.
BTW, the solution I've found came from (Thanks Doug Vanderweide)(BTW if you use this, please get Doug something on his Amazon Wish List.):
http://www.dougv.com/blog/2009...
Anyway, I've also downloaded the SQL from another site (actually he just notes it in this article):
http://www.goondocks.com/blog/...
So here is my code:
<?php ?> <html> <head> </head> <body style="font-family: arial; color: black; font-size: 10px;"> <form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="zipform"> <fieldset> <legend>ZIP Code Radius</legend> <table style="font-family: arial; color: black; font-size: 10px;"> <tr> <td>Enter your ZIP Code:</td><td><input type="text" name="zipcode" size="5" maxlength="5" /></td> </tr> <tr> <td>Select a distance in miles from this point:</td><td><input type="text" name="distance" size="4" maxlength="4" /></td> </tr> <tr> <td>Sort Results by:</td><td><select name="sortby"> <option value="default">default</option> <option value="city">City</option> <option value="state">State</option> <option value="zip">ZIP</option> </select></td> </tr> </table> <input type="submit" name="submit" value="Submit" /> </fieldset> </form> </body> </html> <?php if(isset($_POST['submit'])) { $zipcode = ($_POST['zipcode']); $distance = ($_POST['distance']); $sortby = ($_POST['sortby']); if(!preg_match('/^[0-9]{5}$/', $zipcode)) { echo "<p><strong>Please enter a 5 digit ZIP Code.</strong> Please try again.</p>"; }elseif(!preg_match('/^[0-9]{1,4}$/', $distance)){ echo "<p><strong>Please enter a distance less than 9999 miles.</strong> Please try again.</p>"; }else{ //connect to db server; select database $link = mysql_connect('localhost', 'zipcodedb', 'zipcodedb') or die('Cannot connect to database server'); mysql_select_db('zip_codedb') or die('Cannot select database'); //query for coordinates of provided ZIP Code if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zipcode = '$zipcode'")) { echo "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.</p>"; }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>"; }else{ //if found, set variables $row = mysql_fetch_array($rs); $lat1 = $row['latitude']; $lon1 = $row['longitude']; $d = "$distance"; //earth's radius in miles $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 "Your originating ZIP code:<table><tr><td valign='top'> <table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'> <tr><th>ZIP</th><td>$row[zipcode]</td></tr> <tr><th>City</th><td>$row[city]</td></tr> <tr><th>State</th><td>$row[state]</td></tr> <tr><th>Lat</th><td>$lat1</td></tr> <tr><th>Lon</th><td>$lon1</td></tr> </table> </td> <td valign='top'> <table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'> <tr><th>Max Lat (N)</th><td>$latN</td></tr> <tr><th>Min Lat (S)</th><td>$latS</td></tr> <tr><th>Max Lon (E)</th><td>$lonE</td></tr> <tr><th>Min Lon (W)</th><td>$lonW</td></tr>"; echo "</table> </td></tr></table><br />"; //find all coordinates within the search square's area //exclude the starting point and any empty city values if ($sortby == "default"){ $query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state, city, latitude, longitude, zipcode"; } if ($sortby == "state"){ $query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state"; } if ($sortby == "city"){ $query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY city"; } if ($sortby == "zip"){ $query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY zipcode"; } if(!$rs = mysql_query($query)) { echo "<p><strong>There was an error selecting nearby ZIP Codes from the database.</strong></p>"; }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>"; }else{ //output all matches to screen echo "Click on the ZIP Code to view the available auctions:<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'> <tr><th>ZIP</th> <th>City</th> <th>State</th> <!-- <th>Latitude</th> <th>Longitude</th> --> <th>Distance</th></tr>"; while($row = mysql_fetch_array($rs)) { $truedistance = acos(sin(deg2rad($lat1)) * sin(deg2rad($row['latitude'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['latitude'])) * cos(deg2rad($row['longitude']) - deg2rad($lon1))) * $r; if($truedistance < $d) { echo "<tr><td>$row[zipcode]</td> <td>$row[city]</td> <td>$row[state]</td> <!-- <td>$row[latitude]</td> <td>$row[longitude]</td> --> <td>$truedistance</td></tr>"; } } echo "</table><br />"; } } } } } ?>OK so my current issue is that I was to SORT by the $truedistance variable... obviously you can see that the other sort methods being is used are in the sql query...
Some help would be greatly appreciated. This is my last bit that I need to finish a project I've been working on for around two months.
Thank you for your help and God bless,
Johnathan