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>