• Dec
  • 17

Quick, easy and free nearest store postcode/ZIP finder

This tutorial will describe step-by-step how to create a store locator for your website. It works for post codes in any country, and if you're UK based that means you won't have to pay hundreds to the Post Office for the data.

If you want to just straight to the demo it's online here: Easy Store Finder Demo. To see a live example go to http://www.pitch-invasion.com and enter a UK postcode (or address) into the "Your nearest league" box in the right bar, or check out this search for a plumber in central London from simplifydiy.com

Step 1 – Preparing the data

You will need to have the data for your stores in a database. For each store, you will have to know the postcode. This should be stored in a "postcode" field. You should also create fields for "lat" and "lng". Note that the longitude is stored as "lng" as "long" is a reserved word in MySQL. Optionally, you can include a field for the domain of the address. This allows for easy lookups for multiple countries - as you can see below we have addresses from around the world.

Here is an example table, in MySQL format.

MySQL:
CREATE DATABASE `postcode_finder` ;

CREATE TABLE IF NOT EXISTS `store` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(75) NOT NULL DEFAULT '',
`address` TEXT NOT NULL,
`postcode` VARCHAR(10) NOT NULL DEFAULT '',
`lat` DOUBLE NOT NULL DEFAULT '0',
`lng` DOUBLE NOT NULL DEFAULT '0',
`domain` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM

INSERT INTO `store` (`name` , `address` , `postcode`, `domain`)
VALUES (
'Waterloo Station', 'Lambeth, London', 'SE1', 'co.uk'
), (
'Gatwick Airport ', 'South Terminal, Gatwick', 'RH6', 'co.uk'
), (
'Edinburgh Waverley Railway Station ', 'Network Rail, Room 255, North Block, Edinburgh', 'EH1 1BB', 'co.uk'
), (
'Beverly Hills', 'California', '90210', 'com'
), (
'Penn Station ', '17 W 32nd St New York', '10001', 'com'
), (
'Sagrada Familia', 'Barcelona', '08013', 'es'
), (
'FC Bayern Munchen', 'Sabener Str. 51, Munchen, Germany ', '81547', 'de'
)

Step 2 – Inserting the latitudes and longitudes

For each of the stores in your database you need to know the latitude and longitude. You can find this out from Google Maps very easily. They have a Geocoding service which will return the latitude and longitude for any address. So all we need to do is loop through our database and update each store with the lat and long.

Note that the API key below (the part after key=) is the key for aciddrop.com. You should get your own key here: http://code.google.com/apis/maps/signup.html

PHP:
/**
* Update lat lngs
*
*/

function update_lat_lngs() {

//Get the list of stores
$query = "SELECT * FROM store";
$stores = $this->db->executeQuery($query);
$stores = $stores['result'];

//Run through stores and get lat / lng
foreach($stores AS $store) {

$latlng = $this->get_lat_long($store['postcode'],$store['domain']);

//Update store with its lat lng
$this->db->quick_update("store",
array('lat','lng'),
array($latlng['lat'],$latlng['lng']),
array('id'=>$store['id'])
);

}

}

/**
* Returns a lat / long of a given postcode
*
*/

function get_lat_long($postcode,$domain=null) {

if(!$domain) {
$domain = "co.uk";
}

$url = "http://maps.google." . $domain . "/maps/geo?q=" . urlencode($postcode) . "&output=json&key=ABQIAAAAWjc0ZH2RENLxziofASg9ABQH987j_SlqISv1l93HS7ksPkvN9xRAXjKLSj-Yj2Xw7I6gP3RHQb4UQj";

$json = $this->curl->get_page(array("url"=>$url));

$store_data = json_decode(str_replace(""","\"",htmlentities($json))); //Take care of accents

$lng = $store_data->Placemark[0]->Point->coordinates[0];
$lat = $store_data->Placemark[0]->Point->coordinates[1];

//Return
if($lng && $lat) {

return array('lat'=>$lat,
'lng'=>$lng
);

} else {

return false;

}

}

The first function gets the list of stores form the database, and stores it in the array $stores. For each store postcode (and domain), we then run the function get_lat_long. This queries the Google maps geocoder, which returns a json string. This string is then decoded, and the lat and long taken from the resulting object. Finally, the stores table is updated with the lat and long for each store. Note that this uses curl and json_decode, which need to be installed on your server. There are alternatives, however.

The time that this takes will vary depending on how many stores you have. If you have hundreds the script will take a while to process. Also, the Google API restricts you to 15,000 queries a day so if you have more stores than this you may have to run it a few times on different days. Note that this will also be a problem if you have more than 15,000 lookups a day from your users.

Step 3 – Querying the stores database

Now that we have all our data set all we have to do is query our stores database with the latitude and longitude of the postcode that is our starting point. The query does the maths directly in MySQL so it's very quick – even for thousands of records. It uses the Haversine formula to take into account the curvature of the earth, so it's pretty accurate (certainly more so than other formulas which use Pythagoras).

Note that we also use a checkPostcode() function, which is UK specific. If the postcode entered is a UK postcode it will format it correctly. Thanks to John Gardner at http://www.braemoor.co.uk/software/postcodes.shtml for the code.

PHP:
/**
* Get a list of our stores, sorted by distance to this postcode
*
*/

function get_stores_list($postcode) {

//If it's a UK postcode then format correctly
$postcode = $this->checkPostcode($postcode);

$latlng = $this->get_lat_long($postcode);

if(!$latlng) { //Unrecognised postcode
return false;
}

$latitude = $latlng['lat'];
$longitude = $latlng['lng'];
//    print_r($latlng);

$query = "SELECT *,
(((acos(sin(("
.$latitude."*pi()/180)) * sin((`lat`*pi()/180))
+cos(("
.$latitude."*pi()/180)) * cos((`lat`*pi()/180))
* cos((("
.$longitude."- `lng`)*pi()/180))))*180/pi())*60*1.1515)
as distance
FROM `store`
ORDER BY distance ASC
"
;
$stores = $this->db->executeQuery($query);
$stores = $stores['result'];

return $stores;

}

This will return a list of all the stores, sorted by distance. You can now display as you see fit. If you wish to limit the amount of stores returned, this can be done directly in the query.

So there you have it – a completely free store finder that will work for any international postcode.

The Demo

No tutorial is complete without a demo. You can see it online here: Easy Store Finder Demo. The source code for the demo is available here: Easy Store Finder Demo Source

References

Many thanks to the following sites for useful information in putting this tutorial together:
http://ben.milleare.com/2006/09/03/calculating-distance-with-latitude-and-longitude/
http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html
http://www.petefreitag.com/item/622.cfm
http://www.douglaskarr.com/2007/09/15/calculate-distance/
http://www.pjenkins.co.uk/blog/index.php/2007/04/04/uk_post_code_distance_calculation/

Posted by admin in mysql, php, tutorials
17 comments
 
  • Dec
  • 12

Back from holiday

Thanks to everyone who has waited so patiently for me to return from holiday. According to my site, I've been away since August 2006:

That's not strictly true - I've just been too busy to update the site. But now I'm back.

My first task will be to take some classics from the archive and post them up in their original dates. They should be coming in regularly below...

Posted by admin in aciddrop.com
add a comment