• 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/


Related posts

Posted by admin in mysql, php, tutorials
trackback
 

  • Dec
  • 26
Daniel
Daniel

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

  • Dec
  • 27
admin
Leon

Hi - this is the part of the script that updates the database:

PHP:
$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'])
);

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

  • Dec
  • 29
Phil
Phil

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

  • Jan
  • 22
Tom Roberts
Tom Roberts

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.

  • Jan
  • 30
Chris
Chris

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

  • Jan
  • 30
admin
Leon

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

  • Feb
  • 6
Tamlyn

Smashing! Looks like exactly what I need. Thanks for the good work.

  • Mar
  • 11
Jason
Jason

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!!!!

  • Apr
  • 8
Craig
Craig

Hey does anyone have the source code to this as im really struggling and also could do with some help?

cheers

  • Apr
  • 8
admin
Leon

Craig - you can download the source code. See in "The Demo" section above.

  • Apr
  • 11
craig
craig

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????

http://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?

  • Apr
  • 11
admin
Leon

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.

  • May
  • 5
joshua
joshua

I keep getting a

Fatal error: Call to undefined function: json_decode() in D:\Internet\vhosts\accomfort.com\httpdocs\test2.php on line 355

message. I guessing curl and json_decode aren't installed on the server. I don't think I have permission to do that. Is that the problem?

  • May
  • 11
hc fargot
hc fargot

first, let me say thanks for making this resource available.

i'm running into some difficulty getting this to work properly on a 1&1 shared hosting site, and i was wondering if you might have any insight.

the site is using php 5.2.5. i've gotten a unique key from google and substituted in the appropriate mysql db info. other than that, i have not altered any of easy-store-finder-tutorial.php.

much as with daniel's inquiry back in december, i have the same issue with longitude/latitude values not getting updated; hence all store locations are the same distance from the zip code i specify.

no complaints from google, mysql or php about the code. any thoughts you might have are most appreciated.

thanks in advance.

  • May
  • 14
leslie aubrey
leslie aubrey

Hi there,

Absolutly love this script, I was wondering if it is possible to return a url so that you could use it as part of a website to get to the relevant page?

for example if I have 3 stores, a customer types in the postcode and they are given a list of stores that are nearest to them with a url to each.

or is it possible to automatically redirect to the website that corresponds to the nearest location??

  • May
  • 18
hc fargot
hc fargot

i don't see my comment anymore. it was waiting for approval. thanks.

  • May
  • 20
admin
Leon

joshua - yes, that's the reason. You don't need curl, you can probably use a native PHP function like file_get_contents. And you can use a PHP4 version of json_decode - you should be able to Google for it easily enough.

hc fargot - did you try the solution I gave to Daniel? Does print_r($latlng) return the correct values?

leslie - of course that's possible. If you'd like some custom PHP work done get in touch, I'm available for hire (leon@aciddrop.com)

Comments are closed