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

    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;

							}

						}
					}
				}
			}
		} 
	?>

      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.

        Weedpacket;11003794 wrote:

        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?

          dalecosp wrote:

          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.

            Write a Reply...