Please visit my new campsite listing site ukcampingmap.co.uk


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.

No related posts.

Tags: , , , , ,

48 Responses to “Geocoding in the UK”

  1. Rabbul says:

    Hello
    I have tried whatever you have explained but couldnt find any result. Would you please help me?

  2. daves6599 says:

    Any chance you could post a zip file with all the code in as I just cant get this to work.

    TIA, Dave

      • daves6599 says:

        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

        • wheresrhys says:

          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.

          • daves6599 says:

            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

          • wheresrhys says:

            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.

          • daves6599 says:

            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?

          • wheresrhys says:

            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

          • daves6599 says:

            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

          • wheresrhys says:

            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.

          • wheresrhys says:

            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.

          • daves6599 says:

            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

          • wheresrhys says:

            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.

          • ksc1919 says:

            What should the column heading be? I have id, postcode, lat, lng

          • wheresrhys says:

            They can be anything you like as long as the column names in your actual database match the column names in the SQL queries

          • Fred says:

            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

          • Fred says:

            Problem solved … would only show first row … until I allowed ALL cookies in the browser!

          • wheresrhys says:

            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

  3. ksc1919 says:

    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

  4. Mat says:

    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

  5. Mat says:

    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

    • wheresrhys says:

      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.

  6. Mani says:

    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

  7. Simon says:

    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.

    • wheresrhys says:

      Two things might be happening here:

      1. Off the top of my head, to find an ungeocoded row the sql query looks for entries where lat = ”, so null would probably break this. The datatype of this column has to be a string and MySQL by default leaves the default entry of a string as ”
      2. Less likely is that Google’s geocoder can’t handle strangely formatted postcodes, in which case you’d have to write a script to go through your postcodes putting in a space before the last 3 characters (which I think will correctly format most if not all british postcodes)
  8. Nik says:

    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?

  9. I trust that if we can build out the page using ASP, this function can be applied to that tech, rather than PHP?

  10. Hi

    I have set up an Access Database and logged it via a DSN as ‘geocode’. It has the correct fields etc.

    My ‘mysqlConnect.php’ file consists of:

    I have not altered your files in any way. other than to substitute
    ‘jquery-1.4.2.min.js’ for ‘jquery-1.3.2.js’ in the html.

    However, when I doubleclick on the html file, it displays nothing, but gives an ‘error on page’ message. if I try loading ‘http://localhost/E|/VB Projects/ChurchRecords/GeoCode/geocode.html’ – which is the windows address of the file, it says it cannot find the page.

    Ant thorts?

    Paul

    • wheresrhys says:

      Not too sure what’s happening here. My guess is that it’s something to do with the server or character encoding if the geocode.html file can’t be found… in which case I’m of no help as I know next to nothing about servers and character encoding.

  11. Sorry about the double entry. Some security code thingy. My post seems to have appeared half way down the blog comments, for some reason.

    Paul

  12. lee says:

    I still can not get this to work< i have another method which uses the normal google geocode but like the accuracy of localsearch.

    When I call the script http://yoshinasbo.webhop.net/geocode/geocode.html

    it simply shows the first postcode in the DB
    if i call geocode.php passing in the parameters id-1&lat=1&lng=2 it updates the db entry with id 1

    How do I actually get it to sequence through the DB?
    Thanks for any help

  13. Doyley says:

    I changed a little bit of the sql to match my database and it works prefectly.

    Is there a way to run this as a cron job or something similar? Our database is added to frequently and it would be good to get it to update with the geographic data automatically.

    Thanks

  14. free link directory, link directory, web directory, free web directory…

    [...]wheresrhys » Blog Archive » Geocoding in the UK[...]…

  15. pixnet.net says:

    Thanks for ones marvelous posting! I certainly enjoyed reading it, you might be
    a great author.I will be sure to bookmark your blog and will eventually come back very soon.
    I want to encourage one to continue your great posts, have a nice
    evening!

  16. Face the family “competitive”, “and female Cheng – Feng” high expectations – Drum dryer,Artificial sand maker and
    other machines are the sand making equipment,, facing the school
    high test scores pressure of study, the network has become middle school students from burden and pressure “bomb shelter,” one
    of the academic and psychological pressure release burden the spirit of paradise.
    Brain size has been shown to decrease 20-30% if a child is
    not touched, played with or talked to. Having electrical
    components also makes electric airsoft rifles more susceptible to the elements, and moisture has a much greater effect on electric guns than on spring-operated guns.

  17. It is approved for hypotrichosis of eyelashes, meaning hair loss.

    In some cases, therapy Latisse up to four months of results.

    To learn more about these products follow this link ‘.

  18. Pretty portion of content. I simply stumbled upon your site and in accession capital to assert that I acquire
    in fact loved account your weblog posts. Anyway I will
    be subscribing in your augment or even I achievement you access constantly fast.

  19. Hello! Do you know if they make any plugins to help with Search Engine Optimization?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing
    very good success. If you know of any please share. Thanks!

  20. Brandy says:

    It’s really a cool and useful piece of information.
    I am satisfied that you just shared this helpful
    info with us. Please keep us informed like this. Thanks for sharing.

    Here is my blog :: naperville dental (Brandy)

  21. Sophia says:

    Advertising exposure is obviously one from the oldest business tactics around the world, as old as business itself.
    While an excellent list builder and autoresponder will amount to some money
    upfront, it’s well worth it as your subscribers, readers and purchases climb higher.
    Article marketing is amongst the effective tools that can send traffic to your website or affiliate links free of charge.

    my weblog marketing consultant salary (Sophia)

  22. Arlie says:

    It’s the best time to make some plans for the future and it’s time to be
    happy. I’ve read this post and if I could I desire to suggest you few interesting things or tips.
    Perhaps you could write next articles referring to this article.
    I desire to read more things about it!

    my blog – juicing for colds (Arlie)

  23. Hello, i think that i saw you visited my site thus
    i came to “return the favor”.I’m attempting to find things to enhance my website!I suppose its ok to use some of your ideas!!

  24. Bart says:

    Getting a site name that is certainly memorable and unique can be a way through which you can leave a mark within your customers’ minds.
    While a fantastic list builder and autoresponder will
    cost you some money upfront, it is well worth becoming your subscribers, readers and purchases climb higher.
    E-commerce requires little or no face-to-face
    client contact, but dress the part, arrive to be effective on time
    and leave with the end of the day which has a sense
    of accomplishment.

    my blog marketing consultants nj – Bart -

  25. Tisha says:

    Howdy! I simply would like to offer you a big thumbs up for the great
    info you have got here on this post. I am returning to your site for more soon.

    Here is my website – mottoes to live by (Tisha)

Leave a Reply

Security Code: