Version: 1.0
Type: Sample Code (HOWTO)
Category: Databases
License: GNU General Public License
Description: This code provides the mysql formula to locate and/or compute the distance between two zip codes given that you have a database of zipcodes and latitude/longitude.
I couldn’t find a good version of this anywhere else and getting the () right in mysql was a bit of a bear. Hopefully someone else will have a shorter project starting with this.
<?php
/* this program finds zip codes by distance from a particular zip code using
the Haversine Formula - considered to be the most accurate computation, if you can deal with the earth as a sphere (don't laugh it actually is an ellipsoid) */
/* the zip code database is in the form of zipcode, latitude, longitude */
/* you can get a parseable copy of this data from the us census bureau www.census.gov */
/* http://mathforum.org/library/drmath/view/51879.html for the math part */
/* replace 3957 with the radius of the earth in kilometers for metric */
/* as is, does miles */
$ddb="yourdatabasename" ;
$ddbh=mysql_connect("yourdatabasehost","yourdatabaseuser","yourdatabasepassword") ;
?>
<html>
<body>
<form action=findzips.php method=post>
<input type=text name=dealerzip value="<?php echo $dealerzip; ?>">
<input type=submit name=submit value="get zips">
</form>
<?php
$thisdealerquery = "select * from ziplatlong where zipcode='$dealerzip'" ;
$thisdealerresult = mysql_db_query($ddb,$thisdealerquery,$ddbh) ;
if ($thisdealerresult && mysql_numrows($thisdealerresult)) {
$dealerlat = mysql_result($thisdealerresult,0,latitude);
$dealerlong = mysql_result($thisdealerresult,0,longitude);
$findzips = "SELECT * ,
3957 * 2 *
atan2(
sqrt(
pow(
(sin(0.0174*(latitude-$dealerlat)/2)),2
)
+
cos(0.0174*$dealerlat) * cos(0.0174*latitude)
*
pow(
(sin(0.0174*(longitude-$dealerlong)/2)),2
)
)
,
sqrt(1-
(
pow(
(sin(0.0174*(latitude-$dealerlat)/2)),2
)
+
cos(0.0174*$dealerlat) * cos(0.0174*latitude)
*
pow(
(sin(0.0174*(longitude-$dealerlong)/2)),2
)
)
)
)
as distance
FROM ziplatlong ORDER BY distance LIMIT 100 " ;
$dealersresult = mysql_db_query($ddb,$findzips,$ddbh) ;
while ( $drow=mysql_fetch_array($dealersresult) ) {
echo "$drow[zipcode] - $drow[distance]<br>n" ;
}
}
?>
</body>
</html>
