Syntax Highlighter JS

Saturday, March 31, 2012

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);

19 comments:

  1. Hi,

    Nice Coding for GeoLocation Updater. Is it possible to show the accounts in the map which are nearBy City or State or ZipCode in visual force page. Can you post that code also.

    Thanks,
    Basha

    ReplyDelete
  2. Baji,

    Thanks for the kind words. I am happy that you find the example useful.

    I am working on a follow-up post to cover some of your concerns.

    Look for it on the main page sometime in the next week.

    Thanks,

    Tony

    ReplyDelete
  3. Baji,

    I have posted a follow-up and it can be found here.

    Thanks,

    Tony

    ReplyDelete
  4. Hi,

    I am unable to view your code .can you please publish it again..

    Thanks

    ReplyDelete
  5. Anonymous, java script is required to view the code on the page as I am using a syntax highlighter. If you have java script enabled, would you mind telling me what browser you are running? That way I can look into and possibly fix the issue long term.

    The code has also been posted to get hub and can be found here

    ReplyDelete
  6. Hi,

    Thanks for your work, this is working perfectly.

    A question remains, the limit of 2500 requests per day is per IP address, but what is the ip google api sees ?

    is it the salesforce server ip ? if so if we are many to do this on the same server it will be problematic...

    ReplyDelete
  7. Anonymous,

    Since this runs as a scheduled class on Salesforce servers, Google should see the public IP address of the server that the class runs on. I have seen some requests in my systems fail seemingly randomly but I suspect it was because the running server hit the daily limit. When this happens, the system admin may get an apex exception email but it doesn't seem to happen often and isn't a big deal as the next run usually completes just fine.

    Please note that this is different than using java-script to do geocoding on the client side (which has its own limits).

    I am not sure how the HTTP call-outs are handled on Salesforce but if they are handled in the same fashion as emails, then the IP will be from a pool of addresses and as such is likely to vary across different calls. In the case of email servers, I think they use a pool of 52 addresses.

    Given that this is a shared platform, I don't think it is wise to assume that you should or that everyone could go over the 2500 limit by use of the IP pooling. Further, since you can't know that the IP address will be different it is probably best to stay at the 2400 requests per day as given in my example.

    Thanks

    ReplyDelete
  8. Thanks for your detailed answer, there is also another limit to consider (this is from Salesforce documentation) :

    Salesforce also imposes a limit on the number of future method invocations: 200 method calls per full Salesforce user license or Force.com App Subscription user license, per 24 hours. This is an organization-wide limit. Chatter Only, Chatter customer users, Customer Portal User, and partner portal User licenses aren’t included in this limit calculation. For example, suppose your organization has three full Salesforce licenses, two Force.com App Subscription licenses, and 100 Customer Portal User licenses. Your entire organization is limited to only 1,000 method calls every 24 hours, calculated as 200 x (3+2), not 200 x (3+2+100).

    ReplyDelete
  9. I am trying to build this APEX class listed above but when testing the execution I receive a error "line 9, column 34: Only top-level class variables can be declared static" any reason why this is happening? what does this mean?

    ReplyDelete
  10. Danielle,

    If you are building the "GoogleGeoCodeUpdater" class, then you need to make sure that you are doing so in its own file. You can't make this an inner class as the use of static variables isn't allowed in inner classes on the salesforce platform. See this link for more details as to why.

    You should end up with 2 .cls files, one for the updater and another for the schedule class.

    I just did a copy and paste of the "GoogleGeoCodeUpdater" class above into a development org on API version 26 to confirm that it does save without error in the eclipse editor.

    Thanks

    ReplyDelete
  11. I have saved both files as directed above and uploaded them into the "Static Resources" section on Salesforce. Am I missing a step I am not seeing anything populate in the Geolocation field.

    ReplyDelete
  12. Danielle,

    These files are apex classes and shouldn't be loaded as Static Resources.

    To create these classes in the web interface, login to your salesforce sand box (please note you can't directly save these in a production environment). Then once logged into Salesforce, Click Your Name | Setup | Develop | Apex Classes and click New. Then copy the first class and save it using the name indicated. Then repeat for the other class.

    This should result in one class named GoogleGeoCodeUpdater and another named ScheduleGoogleGeoCodeUpdater.

    Then select Your Name | Setup | Security Controls | Remote Site Settings and add http://maps.google.com as an authorized remote site.

    Once you have saved these files and added the trusted site, you will need to schedule the classes to run using anonymous apex. You can access anonymous apex in the developer console.

    Once the scheduled class is triggered (at the top of the hour in the example given), it will start geocoding your accounts.

    Once you are happy with the test in the sandbox, you can use your deploy tool to send these classes to production. Once the classes are deployed in production, you will need to add the remote site to the production instance and schedule the geocoder to for production.

    Please let me know if you need more help getting this going.

    Thanks

    ReplyDelete
  13. I have entered in the class in the sandbox, in the developer console. I am getting a error message stating

    "No such column "GeocodeLastUpdated_c" on entity "Account". If you are attempting to use a custom field name. Please reference your WSDL of the describe call for the appropriate names."

    ReplyDelete
  14. Danielle,

    This post was originally meant for developers. To assist administrators in getting this going, I have created a managed package that will automate the install. Please see my post about this managed package and let me know if it works for you.

    Thanks

    ReplyDelete
  15. Hi
    I have a requirement to show all location of records in googelmap to display in visualforce page.Already am done, shown one account record in googlemap using visualforce.
    How to do that, Help in this regard,
    Udaya

    ReplyDelete
  16. im new in salesforce,i save this code in apex class and then what to do

    ReplyDelete
  17. Udaya,

    If you browse the rest of this site you might find an example long the lines you are looking for.

    Thanks,

    ReplyDelete
  18. John,

    This post was orignally meant for developers. To help automate the process for users / administrators, I have created a managed package that is easy to install.

    Please delete your apex class and install the managed package instead as it will create the fields, add the classes, and schedule them for you.

    Thanks

    ReplyDelete