Tom Anthony

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!:).