Caching Google Maps geocoding lookups

Google Maps geocoding — the ability to send an AJAX request with a street address and get back a specific latitude and longitude (and lots of other data) in response — is a very useful tool for any number of mapping applications. Best of all, these applications can be written in pure JavaScript — but at a price. Google has strict usage limits for its free geocoding service, which can only be increased for a fee.

However, if your application tends to hit the same addresses over and over again — say, to plot the location of restaurants near your business address — it’s possible to cache these lookups in a database. You’ll still be using Google to look up new addresses, but any user hitting the same address twice in the same day will use your database instead of Google’s service.

A second problem is that Google only allows you to send a certain number of requests per second. If you try fetching more than about ten addresses at a time, the eleventh is returned with an OVER_QUERY_LIMIT status and no data. Catching this in JavaScript is possible, but with AJAX, it can be a pain.

This solution resolves both issues. It doesn’t use pure JavaScript — although it could, for individual users — but PHP and an SQL database aren’t hard to come by.

First, the database (I’m using MySQL):

CREATE TABLE `address_geocoding_json` (
 `addressID` int(11) NOT NULL auto_increment,
 `address` varchar(250) default NULL,
 `json` blob,
 `date_added` date default NULL,
 PRIMARY KEY (`addressID`)
) ENGINE=MyISAM AUTO_INCREMENT=1177 DEFAULT CHARSET=latin1;

Nothing much there except the basics: a unique ID for each row, the street address that’s sent to Google, the JSON Google returns, and the date it was added. Since Google’s most important limits on free geocoding are per-day, and since businesses could theoretically change their location at any time, a daily update seems prudent.

Now the PHP:

include_once "$myroot/db.php"; 
// creates a new PDO database handle called $dbh

$addr = trim($_REQUEST{'address'});

// clean old entries out of the database
$sql0 = "DELETE FROM address_geocoding_json WHERE date_added != CURDATE()";
$dbh->query($sql0);

// check the database to see if the json has been cached today
$sql1 = "SELECT json FROM address_geocoding_json WHERE address=?";
$ret = $dbh->prepare($sql1);
if ($ret===false) die(print_r($dbh->errorInfo(), true));
$sts = $ret->execute(array($addr));
if ($sts===false) die(print_r($dbh->errorInfo(), true));
$r = $ret->fetch(PDO::FETCH_NUM);
$json = $r[0];

if (!$json) { // if not cached, fetch the json from Google 
	$params = "address=" . urlencode($addr);
	$url = 'http://maps.googleapis.com/maps/api/geocode/json?sensor=false&' . $params;
	$json = file_get_contents($url);
	$status = json_decode($json)->status;

	// check for over_query_limit status from Google
	$loopmax = 100; // avoid possible infinite loops
	while ($loopmax>0 && $status=="OVER_QUERY_LIMIT") {
		sleep(0.2); // seconds
		$loopmax--;
		$json = file_get_contents($url); //look it up again
		$status = json_decode($json)->status;
	}

	// cache in the database for future use
	if ($status=="OK") {
		$sql2 = "INSERT INTO address_geocoding_json (address,json,date_added) VALUES (?,?,NOW())";
		$ret = $dbh->prepare($sql2);
		if ($ret===false) die(print_r($dbh->errorInfo(), true));
		$sts = $ret->execute(array($addr,$json));
		if ($sts===false) die(print_r($dbh->errorInfo(), true));
	} // end if
} // end if

// output the json to the browser
header('application/json');
echo $json;

Now, instead of pointing an AJAX geocoding request to http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=(whatever), you can point it to the above PHP script instead. It will automatically retrieve your request from the SQL database if it exists, add it to the database if it’s not, and sleep() for a fraction of a second if Google throttles the request. Perfect for medium-frequency geocoding lookups that are, or might be, just a little bit over Google’s usage limits.

Leave a Reply

Your email address will not be published. Required fields are marked *

*