Script (php) to store coordinates of zip codes or postal codes in mysql database with Google geocode and calculate the distance between 2 locations.

Posted Sat, 01/31/2009 - 23:49 by ma

The following script was designed to get distance between 2 zip codes or postal code (tested with Canada and USA). It is possible to do one by one with google API but I prefer to have the coordinates stored into my own database for speed and reliability. Once you have those coordinates, a simple function can calculate the distance between 2 location using zip/postal code.

You can only make 15000 zip code/postal code requests per day to Google or they will block your IP. The exact code below will make 100000 requests (00000 to 99999) so you will have to gradually change the following variable : 

$a_z1 = "0"; //day 1
$a_z1 = "1"; //day 2
$a_z1 = "2"; //day 3
etc...

<?php
/*
WRITEN BY MARC-ANDRE CARON WWW.BLOGAMA.ORG
 
getCoord function inspired from
http://www.eyesis.ca/projects/calcdistance.html
 
MYSQL TABLE CREATION : 
 
CREATE TABLE IF NOT EXISTS `location` (
  `id` int(11) NOT NULL auto_increment,
  `country_code` text NOT NULL,
  `country` text NOT NULL,
  `state` text NOT NULL,
  `zipcode` varchar(20) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `city` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
*/
 
/////CONFIGURATION PART/////
define('DBSERVER', 'localhost');
define('DBUSER', '');
define('DBUSERPASSWORD', '');
define('DB', '');
define('DBTABLE', 'location');
define('KEY', 'YOURKEY');
define('COUNTRYCODE', 'US');
/////END CONFIGURATION/////
 
 
$con = mysql_connect(DBSERVER, DBUSER, DBUSERPASSWORD) or die(mysql_error());
 
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db(DB, $con);
 
 
 
 
function getCoord($postal)
{
    $d = file_get_contents('http://maps.google.com/maps/geo?q=' . $postal . '&output=xml&key=' . KEY);
    if (!$d)
        return false; // Failed to open connection
 
    $d = utf8_encode($d);
    $enc = mb_detect_encoding($d);
    $d = mb_convert_encoding($d, 'UTF-8', $enc);
 
    $coord = new SimpleXMLElement($d);
 
    if ((string) $coord->Response->Status->code != '200')
        return false; // Invalid status code
 
	//see if multiple results
	$nb_results = count($coord->Response->Placemark);
	$CA_results = 0;
 
	for ($i=0;$i<$nb_results;$i++){
 
		//get the accuracy. Result of 5 is a postal code
		foreach($coord->Response->Placemark[$i]->AddressDetails->attributes() as $a => $b) {
			$accuracy = $b;
		}
 
		if (($coord->Response->Placemark[$i]->AddressDetails->Country->CountryNameCode == COUNTRYCODE) && ($accuracy == 5)){
			$CA_results++;
			$ONLY_results = $i;
		}
	}
 
	//multiple results
	if ($CA_results > 1){
	return false;
	}
 
	//no results
	if ($CA_results < 1){
	return false;
	}
 
	//Get the latitude and longitude
    list($lng, $lat) = explode(',', (string) $coord->Response->Placemark[$ONLY_results]->Point->coordinates);
 
	//Get the latitude and longitude
    list($lng, $lat) = explode(',', (string) $coord->Response->Placemark[$ONLY_results]->Point->coordinates);
 
	//Get the city
	$city = utf8_decode($coord->Response->Placemark[$ONLY_results]->AddressDetails->Country->AdministrativeArea->Locality->LocalityName);
 
	//Get the province/state
	$state = utf8_decode($coord->Response->Placemark[$ONLY_results]->AddressDetails->Country->AdministrativeArea->AdministrativeAreaName);
 
	//Get the country
	$country = utf8_decode($coord->Response->Placemark[$ONLY_results]->AddressDetails->Country->CountryName);
 
	//Get the country code
	$country_code = utf8_decode($coord->Response->Placemark[$ONLY_results]->AddressDetails->Country->CountryNameCode);
 
	//Return the data as an array
    return array('Lat' => (float) $lat, 'Lng' => (float) $lng, 'City' => $city, 'State' => $state, 'Country' => $country, 'Country_code' => $country_code);
}
 
////////////////////////////////////////////////////////////////////////////////////////////
///// MODIFY THIS PART FOR THE COUNTRY YOU WANT THE RESULTS. EXAMPLE FOR USA
////////////////////////////////////////////////////////////////////////////////////////////
$a_z1 = "0123456789";
$a_z2 = "0123456789";
$a_z3 = "0123456789";
$a_z4 = "0123456789";
$a_z5 = "0123456789";
 
for ($i=0; $i<strlen($a_z1); $i++){
 
	for ($j=0; $j<strlen($a_z2); $j++){
 
		for ($k=0; $k<strlen($a_z3); $k++){
 
			for ($l=0; $l<strlen($a_z4); $l++){
 
				for ($m=0; $m<strlen($a_z5); $m++){
 
					$code = $a_z1[$i] . $a_z2[$j] . $a_z3[$k] . $a_z4[$l] . $a_z5[$m];
					$coord = getCoord($code);
					if ($coord){
						$sql = "INSERT INTO " . DBTABLE . " (country_code, country, state, zipcode, latitude, longitude, city) 
						VALUES ('" . mysql_real_escape_string($coord['Country_code']) . "','"  . mysql_real_escape_string($coord['Country']) . "','" . mysql_real_escape_string($coord['State']) . "','" . mysql_real_escape_string($code) . "','" . mysql_real_escape_string($coord['Lat']) . "','" . mysql_real_escape_string($coord['Lng']) . "','" . mysql_real_escape_string($coord['City']) . "')";
 
						if (!mysql_query($sql,$con))
						  {
						  die('Error: ' . mysql_error());
						  }
						echo $code . " record added to database\n";
					}else{
						echo $code . " not added\n";
					}
 
				}
 
			}
 
		}
 
	}
 
}
////////////////////////////////////////////////////////////////////////////////////////////
///// END PART TO BE MODIFIED
////////////////////////////////////////////////////////////////////////////////////////////
mysql_close($con)
?>

Below is the part to be modified for Canada. There is over 7 millions possibility so the script will get only the first 3 caracters which is enough to calculate the distance.

DONT FORGET TO

define('COUNTRYCODE', 'CA');

////////////////////////////////////////////////////////////////////////////////////////////
///// MODIFY THIS PART FOR THE COUNTRY YOU WANT THE RESULTS. EXAMPLE FOR CANADA
////////////////////////////////////////////////////////////////////////////////////////////
//1st letter possibilities. D, F, I, O, Q, U, W, Z impossible for 1st letter. Size of array 18
$a_z1 = "abceghjklmnprstvxy";
//1st number possibilities.
$a_z2 = "1234567890";
//2nd letter possibilities. D, F, I, O, Q, U, impossible for 2nd letter. Size of array 20
$a_z3 = "abceghjklmnprstvwxyz";
 
//total possible results 18x10x20 = 3600 districts
 
for ($i=0; $i<strlen($a_z1); $i++){
 
	for ($j=0; $j<strlen($a_z2); $j++){
 
		for ($k=0; $k<strlen($a_z3); $k++){
 
			$code = $a_z1[$i] . $a_z2[$j] . $a_z3[$k];
			$coord = getCoord($code);
			if ($coord){
				$sql = "INSERT INTO " . DBTABLE . " (country_code, country, state, zipcode, latitude, longitude, city) 
				VALUES ('" . mysql_real_escape_string($coord['Country_code']) . "','"  . mysql_real_escape_string($coord['Country']) . "','" . mysql_real_escape_string($coord['State']) . "','" . mysql_real_escape_string($code) . "','" . mysql_real_escape_string($coord['Lat']) . "','" . mysql_real_escape_string($coord['Lng']) . "','" . mysql_real_escape_string($coord['City']) . "')";
					if (!mysql_query($sql,$con))
				  {
				  die('Error: ' . mysql_error());
				  }
				echo $code . " record added to database\n";
			}else{
				echo $code . " not added\n";
			}
 
		}
 
	}
 
}
////////////////////////////////////////////////////////////////////////////////////////////
///// END PART TO BE MODIFIED
////////////////////////////////////////////////////////////////////////////////////////////

Once you have coordinates, you can calculate the distance between 2 points with this function (from http://www.eyesis.ca/projects/calcdistance.html) : 

function calcDistance($postal1, $postal2) 
{ 
    $dst1 = getCoord($postal1); 
    $dst2 = getCoord($postal2); 
 
    if (!$dst1 or !$dst2) 
        return false; // Invalid postal codes 
 
    $kms = rad2deg(acos(sin(deg2rad($dst1['Lat'])) * sin(deg2rad($dst2['Lat'])) +   
        cos(deg2rad($dst1['Lat'])) * cos(deg2rad($dst2['Lat'])) *  
        cos(deg2rad($dst1['Lng'] - $dst2['Lng'])))) * 60 * 1.1515 * 1.609344;  
 
    return $kms; 
} 
 
echo calcDistance('r3g3j6', 'r0c3e0') . ' kms'; // prints 37.... kms