- Dec
- 17
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.
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
* 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.
* 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/
Hi,
Great tutorial and script. Its very handy!
I have one problem, the latitude and longitude values don't seem to update the sql table when I run the script. They still stay at 0,0 so all the search results are the same distance based on the value 0,0 and the postcode entered. Its probably something with my mysql settings, but any ideas on how to fix it would be good?
Thanks
Hi - this is the part of the script that updates the database:
//Update store with its lat lng
$this->db->quick_update("store",
array('lat','lng'),
array($latlng['lat'],$latlng['lng']),
array('id'=>$store['id'])
);
It uses the function quick_update from the db class. This is a custom class that wraps the mysql functions in php. Make sure you download the full source to have access to this function: http://aciddrop.com/aciddrop/easy-store-finder-tutorial.zip
Really useful stuff, thanks.
I got the lat/lng table update working once I worked out that the postcodes needed the space in the correct position.
Only problem I have is that Google appears to return accuracy=5 co-ordinates based on the sector level postcode, which is OK in general use or in dense urban areas, but is pretty inaccurate in rural areas. Not sure if there's anything I can do about this.
Thanks again
Very useful script, I couldn't find anything else that comes close, and the price is right too! I used it in one of my sites. It also helped me learn a bit about PHP and SQL.
Thank you.
Hi,
we currently have a website that uses a postode search to locate the store. This is then filtered down by the user choosing the range name, therefore giving the closest store to the user that stocks the particular range they are looking for. What i am looking to do is to add a distance in the results to show how far away the store is from the original postcode. Also for some of our customers who live in Southern Ireland they dont have a postcode and so will have to filter by town. Any help gratefully received!
Thanks
Hi Chris,
What I would suggest is the following:
1. Just have one search box where the user can enter their postcode OR town.
2. They click search, and the results page lists the stores that are nearest to them, with the distance from their postcode and the ranges that are available at each store.
This would make it easier for the user as:
1. They only have one box to fill in
2. They may just want the store, and as of yet have no idea what range they like. (That's why they want to go to the store - to chose the range!)
My system would work perfectly for this. Have a look at the demo:
http://aciddrop.com/aciddrop/easy-store-finder-tutorial.php
It works if you enter a postcode, but likewise if you just enter a town. Eg:
http://aciddrop.com/aciddrop/easy-store-finder-tutorial.php?postcode=cork
If you need something like this implemented I'm available for hire for web work, feel free to get in touch; leon at aciddrop.com
Smashing! Looks like exactly what I need. Thanks for the good work.
Hi guys no this is goin to sonds stupid but how to you get the full thing to link togather the sql database and php files etc can someone get me a step by step guide from naming files and where to paste data into..
need help big time would be much appriecated!!!!
Hey does anyone have the source code to this as im really struggling and also could do with some help?
cheers
Craig - you can download the source code. See in "The Demo" section above.
How would i get the google maps to display like in the examples?
Also can someone help me ...... i have setup the page now and entered one address but im not sure if its working correctly????
www.tktest.co.uk/keystore/easy-store-finder-tutorial.php
please can someone check this for me?
Also if i was to have a form that the user can add their store what fields do i enter?? such as name, address, postcode, lat, lng, domain?
Craig - if you mean the Google maps on pitch-invasion, I'm afraid that's not so easy to explain. It would be the subject of a whole new tutorial.
It looks like you've set up everything OK judging by the demo page. Just enter a postcode, and if the distance seems correct you're good.
The fields would be the same as those in the store database - `name` , `address` , `postcode`. You would then update the lat/lng via the update_lat_lngs function.