Caching data from Google's Geocoding service

UPDATE: Please note that the code in this post no longer works due to changes in the Google maps API.  Please see my latest post about this.

So you have looked at the free Find Nearby app and decided it was cool but didn't fit your needs.   Maybe you even looked at someone's google maps mashup with salesforce and decided you want to develop your own integration with google maps.   This is the road I recently went down.

To show items on google maps, you need the Longitude and Latitude coordinates.  You can get these for a given address by using the google geocoding service.  The thing is you are only allowed a total of 2500 requests per day (per IP address) and you must space them out.  I haven't found any documentation on the limit per minute but it appears you need to space requests out by at least 200ms each.   This can make for a really slow painting map if you have alot of points.

Google recommends that you cache out your geocoding results so that you can quickly render maps on the fly.

This post is going to look at how you can do this (for billing addresses on accounts) in apex on salesforce.com.

First you will need to make the following fields on the account object:

These fields will hold the results of the geocode request.

Please note that the text fields are longer than needed given the current resolution of google's geocoding service.   This was done to leave room for greater detail in the future but you can limit them if you need to save space.  Google's current length is 18 characters all inclusive.

These fields (again on the account object) will be used to insure previous attempts don't get repeated until after the initial process (i.e. untouched entries will always get priority).

 The formula field will need to be set to the following formula:
If(LastModifiedDate > GeocodeLastUpdated__c, "True", "False")

Next, you will need to setup a remote site because the salesforce documentation indicates that "Before you can access external servers from an endpoint or redirect endpoint using Apex or any other feature, you must add the remote site to a list of authorized remote sites in the Salesforce user interface. To do this, log in to Salesforce and select Your Name | Setup | Security Controls | Remote Site Settings."

Here is how those settings should look:


And now you are ready to create this apex class:


public with sharing class GoogleGeoCodeUpdater { // used to build address to geocode (from database fields in SFDC) public string Address {get;set;} public string City {get;set;} public string State {get;set;} public string Zip {get;set;} public static List <Account> AccountsToGeoCode = new List <Account>(); // When you specify future, the method executes when Salesforce has available resources. @future(callout=true) public static void ProcessUpdates(){ if (PopulateAccountList() == true ) { for(integer i=0; i<AccountsToGeoCode.size(); i++) { GeocodeAccountAddress(AccountsToGeoCode[i]); } // loop thru accounts list // Update all changed records in one DML statement update AccountsToGeoCode; } // check for PopulateAccountList = true } // ProcessUpdates private static string BuildAddressString(Account AccountToBuildAddress) { String Address = ''; // urlEncode = obtain the bytes for unsafe characters if (AccountToBuildAddress != null) { if(AccountToBuildAddress.BillingStreet != null) { Address = EncodingUtil.urlEncode(AccountToBuildAddress.BillingStreet,'UTF-8')+ ',+'; } // Street != null if(AccountToBuildAddress.BillingCity != null && AccountToBuildAddress.BillingState != null) { Address = Address + EncodingUtil.urlEncode(AccountToBuildAddress.BillingCity,'UTF-8')+ ',+' + EncodingUtil.urlEncode(AccountToBuildAddress.BillingState,'UTF-8'); } // City != null && State != null if(AccountToBuildAddress.BillingPostalCode != null) { Address = Address + ',+' + EncodingUtil.urlEncode(AccountToBuildAddress.BillingPostalCode,'UTF-8'); } // zip ! = null } // AccountToBuildAddress != null Return Address; } // BuildAddressString private static void GeocodeAccountAddress(Account AccountToGeoCode) { String GeoCodeRequestURL = ''; GeoCodePoint CurrentGeoCodeResult = new GeoCodePoint(); HttpRequest GeoCodeRequest = new HttpRequest(); Http GeoCodeHttp = new Http(); GeoCodeRequest.setMethod('GET'); GeoCodeRequestURL = 'http://maps.google.com/maps/geo?q='+ BuildAddressString(AccountToGeoCode) + '&output=json&sensor=false'; GeoCodeRequest.setEndpoint(GeoCodeRequestURL); // no HTTP call outs from test methods ... if (test.IsRunningTest() == false) { HttpResponse GeoCodeResponse = GeoCodeHttp.send(GeoCodeRequest); CurrentGeoCodeResult = ParseGeoCodeJSONReponse(GeoCodeResponse.getBody()); } else { String GeoCodeResponse = '{"Point": { "coordinates": [ -86.8142376, 33.5195411, 0 ] } } '; CurrentGeoCodeResult = ParseGeoCodeJSONReponse(GeoCodeResponse); } // Istest == false if (CurrentGeoCodeResult != null) { if (CurrentGeoCodeResult.Latitude != '' && CurrentGeoCodeResult.Longitude != '') { AccountToGeoCode.BillingLat__c = CurrentGeoCodeResult.Latitude; AccountToGeoCode.BillingLong__c = CurrentGeoCodeResult.Longitude; AccountToGeoCode.GeocodeLastUpdated__c = System.Now(); } // check for null coords } else { system.debug('CurrentGeoCodeResult was null! Skipping this address due to bad JSON parse or response!'); } // update the date even if it failed to prevent reupdating and looping over bad records during inital caching // and to give new records priority over reruns AccountToGeoCode.GeocodeLastUpdated__c = System.Now(); } // GeocodeAddress private static GeoCodePoint ParseGeoCodeJSONReponse(String ResponseBody) { GeoCodePoint ReturnGeoCodeResult = null; JSONParser parser = JSON.createParser(ResponseBody); while (parser.nextToken() != null) { if ((parser.getCurrentToken() == JSONToken.FIELD_NAME) && (parser.getText() == 'coordinates')) { ReturnGeoCodeResult = new GeoCodePoint(); parser.nextToken(); parser.nextToken(); ReturnGeoCodeResult.Longitude = parser.getText(); system.debug('Longitude: ' + ReturnGeoCodeResult.Longitude); parser.nextToken(); ReturnGeoCodeResult.Latitude = parser.getText(); system.debug('Latitude: ' + ReturnGeoCodeResult.Latitude); return ReturnGeoCodeResult; } // if point } // while (parser.nextToken() != null) return ReturnGeoCodeResult; } // GeoCodeResult private static boolean PopulateAccountList() { boolean success = false; // first we look for accounts that haven't been geocoded // 2500 is the max daily limit we can process on the free google service // but 10 is the max number of call outs per execute on SFDC as of 3/22/12 // Notice how entries without city state combinations or postal codes are filtered up front for faster processing AccountsToGeoCode = [ SELECT Id, BillingState, BillingCity, BillingStreet, BillingPostalCode, BillingLat__c, BillingLong__c, LastModifiedDate, GeocodeLastUpdated__c, GeocodeNeedsUpdated__c FROM Account WHERE GeocodeLastUpdated__c = null AND ( (BillingCity != null AND BillingState != null) OR (BillingPostalCode!=null) ) LIMIT 10 ]; if (AccountsToGeoCode.size()==0) { // Next look for geocodes that are out of date AccountsToGeoCode = [ SELECT Id, BillingState, BillingCity, BillingStreet, BillingPostalCode, BillingLat__c, BillingLong__c, LastModifiedDate, GeocodeLastUpdated__c, GeocodeNeedsUpdated__c FROM Account WHERE GeocodeNeedsUpdated__c = 'True' LIMIT 10 ]; } // check for size = 0 if (AccountsToGeoCode.size()>0) { success = true; } // check for size > 0 return success; } // PopulateAccountList public class GeoCodePoint { String Longitude {get;set;} String Latitude {get;set;} } // GeoCodePoint class static testMethod void TestGoogleGeoCodeUpdater() { // create an account Account MyAccount = new Account(Name='Test GeoCode Account'); MyAccount.BillingStreet = '251 South Donahue Drive'; MyAccount.BillingCity = 'Auburn'; MyAccount.BillingState = 'AL'; insert MyAccount; GoogleGeoCodeUpdater.ProcessUpdates(); } } // end GoogleGeoCodeUpdater class Here is a wrapper class used for scheduling the geocoding. During initial caching, I recommend you schedule this 10 times per hour as you get 10 callouts per run. This would give you a maximum effective rate of 2400 per day. Once the initial caching is done however, you probably want to drop this down to once or twice an hour depending on how often new accounts get added. This way you won't be using all of your allowed @future methods and/or allowed scheduled apex jobs. global class ScheduleGoogleGeoCodeUpdater implements Schedulable { // Run the job every day at 1 am public static String CRON_EXP = '0 0 1 * * ?'; global void execute(SchedulableContext ctx) { GoogleGeoCodeUpdater.ProcessUpdates(); } // execute static testmethod void TestScheduleGoogleGeoCodeUpdater() { Test.startTest(); // Schedule the test job String jobId = System.schedule('TestScheduleGoogleGeoCodeUpdater', ScheduleGoogleGeoCodeUpdater.CRON_EXP, new ScheduleGoogleGeoCodeUpdater()); // Get the information from the CronTrigger API object CronTrigger ct = [SELECT id, CronExpression, TimesTriggered, NextFireTime FROM CronTrigger WHERE id = :jobId]; // Verify the expressions are the same System.assertEquals(ScheduleGoogleGeoCodeUpdater.CRON_EXP, ct.CronExpression); // Verify the job has not run System.assertEquals(0, ct.TimesTriggered); Test.stopTest(); } // test method } // class ScheduleGoogleGeoCodeUpdater 

Per the documentation, you can then use execute anonymous to to schedule the class to run on forever.  For example:

 ScheduleGoogleGeoCodeUpdater  g = new ScheduleGoogleGeoCodeUpdater (); String sch = '0 0 * * * ?'; system.schedule('GoogleGeoCodeUpdater at top of the hour', sch, g);