in All Posts, APIs/Mashups, Web

Caching Google Maps Geocoder Results

After my post Geocoding UK Postcodes with Google Maps API I’ve had a few people contact me about caching geocoding results back to a server, for subsequent pages.

It’s a good question – Google’s geocoder permits you to make 50,000 queries a day, which sounds like a lot. However, that is only 35 a minute, which if you sustain for more than a few minutes, kicks in the limit (apparently…). So you might be interested in caching your results.

If you aren’t fussed about UK geocoding, you can access the regular Google geocoder using HTTP, as documented in the Google Maps Documentation.

However, if you want to temporarily cache results collected by the client (as per my UK geocoding example linked to above), you need to send them back over an AJAX connection, and that is what this article discusses. Although aimed at using the UK geocoding technique, a lot of this tutorial will be applicable to results via the normal Google Javascript geocoder.

So what this short tutorial is going to do is show you how once a postcode has been translated into longitude and latitude, the result can be sent back to your server to be temporarily stored in a database. Then we are going to look at how we can query our own database before we query Google’s, for each result.

Note: I’m told it used to be in Google’s Terms that you couldn’t store geocoding results, but it doesn’t appear to be that way now. However, we are only going to store them temporarily, so we don’t have to hit Google’s server repeatedly for the same query.

If you are eager just to see how this all will work, you can go straight to the demo page.

So, lets get going…

Getting started

To begin with, we need to create our Google map page, and get our UK geocoder working. If you’ve not done so yet, take a look at the UK Postcoder Geocoder tutorial and get that setup.

We are going to make only a couple of changes to the Javascript files, but firstly we need some PHP, and a MySQL database to store the results in. At this point, if you are confident with your server side scripting, go ahead and use ASP and MSSQL or anything else that takes your fancy; just remember to asjust the calls we’ll add to the Javascript accordingly.

Make the database

We need to make a database in order to store our cached results. We will have 1 table, which will have 5 fields: id, postcode, latitude, longitude and data_added. id is our primary key, you can ignore it. The other fields should be fairly self evident.

You can use this SQL to create your table once you’ve setup a database:


CREATE TABLE `postcodes` (
  `id` int(11) NOT NULL auto_increment,
  `postcode` text NOT NULL,
  `latitude` float NOT NULL default '0',
  `longitude` float NOT NULL default '0',
  `date_added` datetime default NULL,
  PRIMARY KEY  (`id`)
)

We need the date_added field simply as we are only storing the results temporarily, in our case for 24 hours. After a result is 24 hours old, we ignore it; I’ll leave it as an exercise to the reader to write code to clean up their old results.

Write the PHP to store entries

Now we need to store results in our shiny new table; so we are going write cache.php which writes a retrieved result to the database. It is pretty short and sweet, here is the code:

<?php

  $db = @ mysql_connect('localhost', 'DBUSER', 'DBPASSWORD');
  
  if (!$db) {
    die("Sorry, database error!");
  }
  
  mysql_select_db('DBNAME',$db);
  
  $sql = "INSERT into `postcodes` (  `postcode` ,  `latitude` ,  `longitude` ,  `date_added` ) VALUES (UPPER('" . mysql_real_escape_string($_GET['postcode']) . "'),  '" . mysql_real_escape_string($_GET['latitude']) . "',  '" . mysql_real_escape_string($_GET['longitude']) . "', NOW( ))";
  
  if ($result = mysql_query($sql))
  {
    echo "Success";
  }else{
    echo "Problem";
  }
  
?>

It just connects to the database, and sends one SQL query. Note we use UPPER() to ensure all postcodes are written to the database in upper case, we don’t want to be storing cached copies for both upper and lower case.

Don’t forget to change DBUSER, DBPASSWORD and DBNAME to the correct values; your database user, password and your database name.

If you have problems, you can access this file directly, and it should say “Problem” if it has a problem!

Write the PHP to read

Ok, we now have the PHP code to write results to the database, we just need to be able to retrieve them. For this we need geocode.php, another small PHP script.

Lets dive straight in with the code:

<?php
  header("Content-Type: text/xml");
?>
<locations>
<?php

  $db = @ mysql_connect('localhost', 'DBUSER', 'DBPASSWORD');

  if (!$db) {
    die("Sorry, database error!");
  }

  mysql_select_db('DBNAME',$db);

  $sql = "SELECT * FROM  `postcodes` WHERE `postcode` = UPPER('" . $_GET['postcode'] . "') AND `date_added` >  DATE_SUB(NOW(), INTERVAL 1 DAY)";

  if ($result = mysql_query($sql))
  {
    if (mysql_num_rows($result) > 0)
    {
      $location = mysql_fetch_array($result);
      echo "\t<location postcode='" . $location['postcode'] . "' latitude='" . $location['latitude'] . "' longitude='" . $location['longitude'] . "' date_added='" . $location['date_added'] ."' />\n";
    }
  }

?>
</locations>

The <locations> tags are because we are outputting XML. Eeek! XML! Don’t be scared, it is very simple, the actualy output we send to the browser looks something like this:

<locations>
  <location postcode='W1B 2EL' latitude='51.5144' longitude='-0.141959' date_added='2007-03-21 19:04:25' />
</locations>

We then do a simple SQL query to grab matching results that were added in the last day, and output the first one (there should only be one) into our little XML document. That wasn’t so bad, right?

Adjusting the Javascript

Ok, we have PHP that reads and writes to a database with our results; which just means we need to adjust our Javascript. Currently our code queries Google and then uses the result, all we want to do is add a couple of steps to this process.

We want to query our own database first, and if we find a result, then great, we use that. If we don’t find a result, then we want to query Google, as before; we’ll also want to write the result from Google back to our cache, for use with future queries.

To achieve all of this, we are going to add 2 new functions, and we are going to make a couple of modifications to one of our current functions.

First we will add createRequest() which is a common function for AJAX applications. It just creates and AJAX request object, depending on your browser. This tutorial isn’t designed to be an AJAX tutorial, so if you need help understanding this function, you should find plenty of helpful tutorials on the web.

Here it is:

function createRequest() {
  
  // create an Ajax Request
  
  var ajaxRequest;
  
  try
  {
    ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
  }    
    catch (e1)
    {
      try
      {
        ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
      }
        catch (e2)
        {
          ajaxRequest = new XMLHttpRequest();
        }
    }
  
  return ajaxRequest;
}

The object this creates will simple enable us to send requests back to the web server, via Javascript, and receive the reply; perfect for querying our cache.

We already have a function usePointFromPostcode() that queries Google, we just need a middleman to try our cache first. This challenge is answered by the mighty and brave usePointFromPostcodeViaCache() function:

function usePointFromPostcodeViaCache(postcode, callbackFunction) {
  
  map.clearOverlays();
  var ajax_connection = createRequest();
  
  ajax_connection.open('get', "geocode.php?postcode=" + postcode);
  
  // setup the function to deal with the reply
  ajax_connection.onreadystatechange = function(){
    
    if (ajax_connection.readyState == 4) {
      var xmlDoc = ajax_connection.responseXML;
      var markers = xmlDoc.documentElement.getElementsByTagName("location");
      
      if (markers.length > 0)
      {
        var resultLat = markers[0].getAttribute('latitude');
        var resultLng = markers[0].getAttribute('longitude');
        var resultDate = markers[0].getAttribute('date_added');
        var point = new GLatLng(resultLat,resultLng);
        document.getElementById("result").innerHTML = "Result for " + postcode + " came from cache. It was last cached on " + resultDate;
        callbackFunction(point);
      }else{
        usePointFromPostcode(postcode, callbackFunction);
      }
    }
  }  
  
  ajax_connection.send(null);
}

We clear any current markers, just for the sake of this tutorial, so you can query the same postcode over and over.

Then we setup our AJAX request, and write our callback function (for AJAX newbies, this is just the function that is called when a result is received from the webserver). This function parses the XML, and checks to see if we have any results by checking markers.length.

If we have no results, we call usePointFromPostcode(), which queries Google, as before. If we do get a result, then we handle it exactly as we do in usePointFromPostcode() when we get a result from Google; we make a GLatLng object and pass it to our designated callback function.

Lastly, having set this up, we actually send the request. Phew! Almost there.

Finally, we need to add a couple of lines to usePointFromPostcode() to write retrieved results to our cache. Between the lines var point = new GLatLng(resultLat,resultLng); and callbackFunction(point); add this code:

document.getElementById("result").innerHTML = "Result for " + postcode + " came from Google.";
  var ajax_connection = createRequest();
  ajax_connection.open('get', "cache.php?postcode=" + postcode + "&latitude=" + resultLat + "&longitude=" + resultLng);
  ajax_connection.send(null);

This sends an AJAX request, just to send the result to the cache. We need no callback function for this AJAX request, as we don’t need to examine the response.

You’ll notice in this function and the previous one we’ve added a line writing a comment to a ‘result’ element on our page. This is just so you can see in your working example, or on the demo page where the result is coming from. So we need to add this element to the index.php file, which is 2 minutes work. Last hurdle I promise, lets go…

Update index.php

Wherever you like add to your HTML file this code:

<p id="result"></p>

The very last thing to do is, in your HTML file, change all instances of usePointFromPostcode() to usePointFromPostcodeViaCache() or we will continue to query Google without checking our cache first.

You can check this all works on the demo page. If you can’t think of a Postcode, try ‘SP4 7DE’ to see Stonehenge!

Final words

This tutorial has kinda blitzed through the AJAX stuff, I know; but I wanted to focus on the task at hand. If you go and read a couple of AJAX tutorials, you shouldn’t find what is being done here too complicated. Having said that, post any questions in the comments, and I’ll do my best to answer.

Also, to keep it simple I’ve cut some corners here. Some examples are:

1) This code caches results across different visitors. If you have visitors who maintain long sessions and redo the same queries, you’d probably also want to cache them in a Javascript array so no repeat remote queries were necessary.

2) There is no thought to security given here; it is possible for someone to fill your cache up with junk (though they’d need to do it every 24 hours).

3) This is aimed particularly towards UK results. If you aren’t bothered about UK results, you could query your database, as we are doing, and if the result is missing, then your server could use the HTTP geocoder to get the result directly from Google to pass back to you. However, this technique will work for you too (you just need to modify it a bit).

However, hopefully it will be enough to get the people who’ve asked to get going with their projects.

Have fun with it, and please do post comments if you have questions (or just to say hi!:).

Write a Comment

Comment

39 Comments

  1. Tom

    Looking forward to some free time over the weekend to try this out (although i may try to get some time before then -assuming the wife doesnt throw a wobbly! 😉 )

    You are true gent – thank you for sharing your briliance. (looking forward to the book! “Tom’s 101 tricks and tips on Geo mapping with Google – UK edition”)

    Dave

  2. Hi Dave,

    Thanks for the kinds words! Tell your wife it’s important!!

    I think i’ve got a long way to go before a book; but I’ll keep working on it!

    Let me know how you get on. 🙂

  3. I should also mention, the other area for expansion with this tutorial is passing multiple postcodes to your server in a single query (both reading and writing). Which could be of significant help if you have lots and lots of markers.

  4. Tom – think i must be doing something wrong here – as I am getting nothing with this code.
    Im not sure that its even getting to go through the gmap.js code.. not sure how to check this – i have added some echo statements at the start of the cache.php and geocode.php files – but nothing happens – thus I am not sure i am even getting this far.

    save clogging up the comments (and probably cos its a dum mistake’) you can email me any comments…

  5. hi Tom

    FWIW Not quite the same thing, but I thought it might be relevant to this article – if only just to show how easy it is to extend your script…

    The ‘d’ link above points to such an adaptation that facilitates MANUAL CACHING of results from batch queries for use in either JavaScript or PHP.

    The output can be pasted back into the query page, and saved. This way when new postcodes are added, the same batch query can be run more quickly and without overworking the google server.

    While a relative newbie at this Google Maps thing, it seems to me inevitable that geocodes get stored with practically ANY mashup…?

    d

  6. Any one got this to work yet?

    Still having the same problems as beofre – if i modify the index.html to call “usePointFromPostcode” then everything works – except everything is called from Google.

    if i use the ViaCache version – nothing.

    I am also failing to get the display pointer to work as well.

    Any hints greatfully recived

    email me at spamdelux at g mail ~ you know what to do.

    Dave

  7. I need to know how can i Caching Google Maps. and how to save the cache permentally that when another time we request the google map they will get maps from cache.

    Please help me or tell me is this possible or not.

  8. Saurav – The technique used here could be easily adjusted to keep the cached copies forever, but I wouldn’t advise it.

    Peter – Great, I’d love to hear how you ge on. 🙂

    d – Thanks for the info.

    Dave – So sorry for the slow slow reply, I’ve been away and then had my parents in town! Did you get this working? It sounds like a problem with the database.

  9. Hi loved the mapping! Has something changed recently with goggle? I can’t seem to get the cache demo to work.
    The code in theory does exactly what I want.
    The other demos work fine just the cache – is it the old postcode copyright thing again?

  10. Hi Mark,

    Sorry – my fault. I moved servers and forgot to setup the database for this post again. You should find the demo is fully functional again. Thanks for pointing it out. 🙂

  11. Hi,

    I’m trying to achieve this cache result, except I’m doing it in a different concept. You see, when a property (building) gets processed into our database, I’m trying to convert the given Post Code (UK) into latitude and longitude values, but I want it doing automatically and silently (in the process script). Then the lng & lat values are inserted into the database and stored.

    When the property is viewed, there is an option to “locate on map”, this will center the Google Map to the lng & lat values read from the database.

    The bit that I’m stuck with is processing the Post Code (from the URL) into co-ords. I am using PHP & mySQL, what JavaScript would I need to implement?

    I would appreciate any samples which I can use!

    Thanks

  12. This would seem to be well on the way to being exacly what I need, but I just can’t get my head around what I need to change.

    What I’m after is basically a PHP-based geocoder for UK postcodes. I need to be able to pass a postcode to a PHP function and have it return an array containing the latitide and longitude.

    From what I can see, this looks like it should be able to do it without too much modification, but I’ve never touched AJAX before and I dont’ know where to start.

    Any advice would be much appreciated.

  13. Hi Mat,

    The problem you are going to have is until a postcode is cached, it must be collected using Javascript, which is client-side and so must occur in the users browser. You won’t be able to eliminate the Javascript element.

    Depending on what you are trying to achieve this may or may not be a problem. Do you *need* to do it purely with PHP?

  14. Hi I have gone around the houses so many times and ended up back on your great site with a little more knowledge each time and now I believe I finally think I know what I need but my brain is hurting trying to strip it out.

    I basically need only the javascript code to return the lat and long for the UK postcode to 2 hidden variables upon a click.

    I think I can manage the rest from there, if it is possible for you to strip the code back to what I need it would be soooo much appreciated.

    Many thanks.

  15. Sergey – Provided the archive contains a readme that points over to this tutorial, then sure – that is fine. 🙂

    Darren – Well, that is certainly possible. However, I’m afraid I don’t have the time to reorganise the code for you at the moment. Though, the following tutorial to this one on Caching may help you, if you are looking to send your results to the server for further processing. Otherwise, you could eitehr write to hidden fields, or to text fields of a form which you could just hide with CSS. Let me know how you get on. 🙂

  16. Hello,

    I’m a novice but found your tutorial very accessible. I have come upon a stumbling block, and was hoping you might be able to help.

    I am trying to load a bunch (~50) of dynamically generated markers when the map opens and use the cache when appropriate or look them up as needed. I can’t seem to get it to load more than 5 or so before it stops loading the markers and caching the results. I have been trying to load them in the following manner

    for (var i = 0; i ‘, placeMarkerAtPoint);
    }

    I have also tried using a window.setTimeout with a variety of times, but no luck. Any help would be greatly appreciated.
    Thanks.
    -M

  17. I don’t know what my problem pasting code is, it’s a for loop that proceeds through an array of addresses.
    Thanks and sorry for the redundant messages.

  18. hi

    neat application just what i am looking for, i need to use it for ireland not the uk
    in ireland there are no postcodes, so i would need to have address look up.
    how do i adapt your app do this?

    i would also like for the user to click on the map, a marker added and for their long & lat values to be stored in mysql database. then retrived to display on map. i have a js script that adds a marker from user click but not sure how to get the values and store in db using php.

    any help greatly appreciated
    darren

  19. Hi Tom,

    great tutorial thank you very much, i have adapted it slightly to run the cached script using a database list of results which runs through an XML file. However the non cached results do not carry across the postcode data individually. For some reason it overwrites all the previous information. i’m sure it is because the postcode variable is outside the inner function but i cannot seem to get round it ?is there anything i can do to prevent the innerHTML being re-written – i use += to just add the information into the p tag – the function USES the postcode data correctly and returns the correct lat long into the database, but it stores the same postcode for all records.

    please can you help ?

  20. i.e
    Supplied :
    EX12 3TD
    EX12 5TY
    EX12 5AD
    EX12 6TR

    Outputted:
    EX12 3TD – with correct lat & long for EX12 3TD
    EX12 3TD – with correct lat & long for EX12 5TY
    EX12 3TD – with correct lat & long for EX12 5AD
    EX12 3TD – with correct lat & long for EX12 6TR

    n.b these postcodes are made up for the example
    cheers
    ally

  21. Sh*t Hot
    Really love this – Had to mess about a lot though due to my lack of knowledge and experience but now fully working. Could someone please explain why its not a good idea to cache results for ever as it seems stupid to me to request for the post code again when you know its not ever going to change ( is it ?? ).
    Could anyone also advise if it would be possible at all to store the distance from a fixed point for all the postal codes obtained so that the result could be used in other applications/calculations. I see a few of these around but the results they produce do not seem to be extractable/storable in txt format. If no-one can say exactly how to do this, just a definate can/can’t be done would be great.
    Thank you Mr Anthony for your valued work here.

  22. Sorry!
    Additional to above – Please, Please not as the crow flies, I never tried that. I always find it better to drive from A to B.
    Thanks again.

  23. i want to use this code for placing markers on the map where people live.
    I want to have a form and then have the client enter postcode and address fields then it will place a marker on the map where they live.
    So all markers are there when you browse.
    How can i do this thanks.

  24. Hi Tom i’ve managed to build my own ASP/Access caching system. But I would like to hardcode UK postcode in script, but i don’t know how ??? I would like that my location is immidiatly displayed and centered on the map without pasting uk post code in input file field.

    Tnx

  25. Hi Tom

    I have just started looking at the Google maps software, when I stumbled on your page. Both the code and the explaination were top quality and were very useful.

    Keep up the good work.

  26. Hi Tom
    a while ago you said
    “I should also mention, the other area for expansion with this tutorial is passing multiple postcodes to your server in a single query (both reading and writing). Which could be of significant help if you have lots and lots of markers.”

    How can we do this? I am trying to plot multiple markers on a map via twitter posts using the ajax lookup, it works fine for geocoder.getLatLng but using your example only the results of the first request are posted.

    in the above example only marker B is at the posted address the other is posted at another location.

    Any help is much appreciated – my head hurts

  27. How do we get a HTTP request in ASP from google API in order to retrieve the longitude and latitude of any location and then use those variables to post forward to another API application that requires the long/lat to return its results in simple ASP script.

    In our website the user would search for plumbers in luton and post the request through to the results.asp page, which would then need to dig out the long/lat of luton from google API and post those through to a directory engine returning an xml feed of plumber in and around luton based on radial search of luton

  28. Hey Tom,

    Great tutorial thanks for all the info. I have followed it and everything seems to work except the lat and lng are coming back as 0.

    just saves into the database as 0.000000000 and if you click show lat n lng it just shows 0.

    Do you have an idea what this could be?

    Thanks

    Glynn