Geocoding in the UK
The art of geocoding addresses in the UK, as I previously explained, is a soul-destroying process, frought with inaccuracy, bugs and convoluted workarounds. And for all that work you end up with a set of points of which a great deal are probably somewhat inaccurate and at least some of which are completely wrong. UK addresses (and probably those elsewhere in the world) are complicated creatures, which Google’s geocoding engine often interprets wrongly.
Postcodes, on the other hand, are rather easier; there is a well-defined relationship between a UK postcode and its corresponding (usually pretty small) piece of the British countryside. But google’s geocoding api will only return a geocode for the postcode sector (ie will give a geocode for LL12 5 when you searched for LL12 5TH). However, someone did figure out a way of using Google’s local search API combined with google maps to geocode UK postcodes. Since he blogged about it the API has changed, so below is an outline of how to geocode a batch of postcodes in the UK using just some simple php, the current google ajax search API and a little javascript (jQuery isn’t essential, but cuts down on coding a bit). The javascript is the crucial step.
Assuming you have a database full of postcodes and id numbers, and 2 empty columns to store latitude and longitude values, this is how it’s done. (Download source geocode.zip).
1. Create a html page geocode.html with the following content:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" > <head> <title></title> <meta name="description" content="" /> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> <link rel="stylesheet" href="" type="text/css" media="screen" /> <script type="text/javascript" src="jquery-1.3.2.js"></script> <script src="http://www.google.com/jsapi" type="text/javascript"></script> <script type="text/javascript" src="geocode.js"></script> </head> <body> <div id="counter"></div> </body> </html>
(Make sure you specify the correct location for your local javascript files)
2. Create a php file (in the same directory), geocode.php, with the following rough structure (it will only be accessed via ajax, so is very stripped down):
<?php
require_once ('mysqlConnect.php'); //or other database connection details
if($_GET)
{
//var_dump($_GET);
update_record();
send_new_data();
}
//gets the next record without a geocode and sends the id and postcode to the browser
function send_new_data() {
$query = @mysql_query("SELECT id, postcode FROM geocode_table WHERE lat = '' AND postcode != '' ORDER BY id LIMIT 1");
if(($query) &&mysql_num_rows($query)) {
$row = mysql_fetch_array($query, MYSQL_ASSOC);
echo $row['id'].','.$row['postcode'];
} else {
echo 'stop';
}
}
//updates the last record with data sent from browser
function update_record() {
$id = $_GET['id'];
$lat = $_GET['lat'];
$lng = $_GET['lng'];
if($id > 0)
{
$update = "UPDATE geocode_table SET lat = '".$lat."', lng = '".$lng."' WHERE id = ".$id;
$result = @mysql_query($update);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
}
}
?>
3. Create a javascript file geocode.js, saved in the same directory again (I would paste it here but it keeps breaking wordpress)
4. Running the code
Once you’ve altered the database connection details, and SQL query to suit your setup, simply open geocode.html in your browser. A counter will tell you which record you’re on. To stop the code simply close your browser/browser tab.
How it all works
In a nutshell (ignoring the special case of starting off the loop) the code repeatedly performs the following process:
….in geocode.php, send_new_data() finds a record which has no latitude value and sends it’s id number and postcode as an ajax response to set_and_get_next(). This keeps track of the id in a global variable and sends the postcode to getPointFromPostcode(), which uses google’s local search to get a geocode. Once it’s found a geocode it passes it to set_and_get_next(), which sends it to geocode.php in an ajax request. There update_record()… well… updates the record, and send_new_data() finds a record which has no la….
Compared to my previous approach iterating a script over large sets of data, using ajax is very sleek. Similarly to a pure php script I can load from a browser, though with much of the resource intensive scripting taking place on my or google’s server. But with ajax there’s no problem with the browser timing out from time to time, or baulking at the number of times a page is requested. It’s a little harder to code, and probably less efficient… but I like it. And I’ll definitely be using my shiny new geocoded postcode data.
Related posts:
- Where you headin’, luv?In I’ve covered the inherent difficulty of geocoding addresses and...
- Anarchy in the UKThis damn economic crisis/swine flu outbreak isn’t quite that bad...
- In defence of jQuery browser detectionI read somewhere the other week that jQuery is deprecating...
- What’s up, firebug?In the space of one day (in real-time, not blog-post-spacing...
- My jQuery plugin writing tipsI’ve now written , and have a pretty good idea...
Tags: accuracy, batch, geocoding, google maps api, postcodes, uk
Hello
I have tried whatever you have explained but couldnt find any result. Would you please help me?
I’m afraid that any requests for help should come with some detail of what the problem is.
Any chance you could post a zip file with all the code in as I just cant get this to work.
TIA, Dave
Consider it done
OK, thanks, got it all set up and nothin happens :(
In geocode.js there is a reference to a postcode.php file. Do I need this file? How does the loop get started?
TIA, Dave
Hopefully the mistake you’ve found is the only mistake. I originally called the files postcode.php, postcode.html etc…, but must have missed one when renaming the files ready to publish. So every reference to a php file should be geocode.php.
The loop starts with
$(function() {
counter = $(‘#counter’);
send_and_get_next(0, 0, 0)
});
which sends no data (0, 0, 0) to the php page, and the php page recognises the lack of data, updates no records, but fetches the next record for the js to process. $(function()… is an alias for $(document).ready(function()… I should really rewrite the whole thing without using jQuery in order to make it more self-contained, but too lazy at the moment.
Let me know if it still doesn’t work.
Hi,
Thanks for the help – getting a bit further now. It connects to database and prints the 1st int_id (1) along with its relevant postcode. Then thats it. I have 3 postcodes in my table so would expect to see 3 values. Also, there is no values stored back in the database. Any more help would be much appreciated.
Cheers, Dave
Rhys and Dave, I seem to have hit the very same problem too. Firstly, where do I get a copy of 1_jquery-1.3.2.js from? Also can you confirm what the table name should be?
thanks and regards
This looks good, but sadly I can’t get it to work either!
No clue on the AJAX, but a bit of simple PHP debugging showed that for some reason it objected to:
echo $row['id'].’,’.$row['postcode'];
(if I put a subsequent, “echo ‘Hello’” after that, it didn’t reach it), but it has no problem with just echo $row['postcode'];
Have you had any progress with this? It would be great to get it working. The database columns etc. all match (I can run the individual queries in MySQL),
Cheers,
Mat
Hi,
This looks really good, but I’m having the same problem. It gets to the first row, but then doesn’t do anything. Have you had any progress? My column names and whatnot all work – I can run the queries directly in MySQL – but it only appears to go through the PHP once.
Any progress?
Cheers,
Mat
Here’s one idea, scraping the barrel. The data types of your lat and lng columns should be varchar (length of about 12) or a similar text field. Not a number field basically, as this might interfere with the WHERE lat = ” condition in the SQL.
Just found downloadable UK postcode list alongwith latitude & longitude positions. Website has also good online tools (eventhough they are not helpful in coding)
http://www.freemaptools.com/download-uk-postcode-lat-lng.htm
I have a table with the postcodes. When i run the code, it says that it has finished coding, yet it hasnt actually done anything. The postcodes in the table do not have any spaces (i.e. AB123CD as apposed to AB12 3CD) and the lat & lng columns are ‘null’. Does this make any difference?
This is a really useful piece of code, and I would really like to get it to work for me.
Two things might be happening here:
Hi I have a database which contains all the details for england, will this populate the geocodes for each of the cities/towns villages, neighborhoods, etc without having the postcode for them? essentially can it be manipulated to use the town/city names and the other required fields to automatically generate the geocodes for each of the locations accurately?
Unfortunately not, and this is more a problem with google’s geocoding of UK addresses than with the method itself: http://wheresrhys.co.uk/2009/07/anarchy-in-the-uk/
I would guess it’s some problem with the php page. As geocode.php uses GET to receive data you can load it directly in your browser to see if there’s an error eg. …/geocode.php?id=1&lat=12&lng=14 should display a page with “2, APOST CODE” at the top of the page, but my guess is it’ll show an error message.
Hi,
I tried geocode.php?id=1&lat=12&lng=14 and initially this didnt work so I added an int_ to the id (geocode.php?int_id=1&lat=12&lng=14) and this successfully wrote the numbers to the database and the id updated. What I did notice in geocode.js is there are references to a int_id and an id. Is this a typo?
Yes youre probably right. I clearly need to proof read things more thoroughly before publishing.
Fingers crossed thats the problem solved for you. Thanks for all the feedback. Ill update the article and files shortly
Hi,
Well not entirely because if I run …/geocode.html it still only displays the first id and postcode, writes nothing to the database then stops. I’ll check back regularly if you’re gonna update the files or can you suggest what the problem is? Thanks for helping
Dave
Sometime in the next few days I’ll set up a subdomain where I can test all this and when it works perfectly I’ll upload all the files exactly as they are. I’ll send you an email to let you know when it’s done.
OK – now it’s totally fixed. Exactly these files work OK on my machine, so fingers crossed the batch uk postcode geocoder is now bug free.
Hi, Me again
I must be doing something wrong cos I still cant get it to work. It displays the first ID with the postcode and thats it, same as before – stores nothing in the database. I tried the previous link /geocode.php?id=1&lat=12&lng=14 and that works ok. I was wondering if it has anything to do with the php version or browser Im using. Even tried on a localhost and that did the same. I cant understand what the difference betweens setups could be. I’m lost!!! You can see what it does here http://www.scible.co.uk/geo/geo6/geocode.html
Thanks, Dave
Well, that’s me more or less out of ideas. The only thing I can think of now is to check your database column names – it didn’t work for me for ages, very similar behaviour to what you’re getting, because I had changed a column name.
Dave, you appear to have got this working. Can you please send me the contents of your geo directory i.e. the varios html, php, script files.
Thanks
Fred
What should the column heading be? I have id, postcode, lat, lng
They can be anything you like as long as the column names in your actual database match the column names in the SQL queries
Problem solved … would only show first row … until I allowed ALL cookies in the browser!
Really odd that as the code doesn’t use cookies. Maybe if you’re loading the html page as C:/…/geocode.html instead of http://localhost/…./geocode.html then the ajax would be cross-domain, so allowing cross-domain cookies would allow cross-domain ajax too?
Anyway, glad it worked