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 ."
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);
Hi,
ReplyDeleteNice 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
Baji,
ReplyDeleteThanks 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
Baji,
ReplyDeleteI have posted a follow-up and it can be found here.
Thanks,
Tony
Good Work
ReplyDeleteHi,
ReplyDeleteI am unable to view your code .can you please publish it again..
Thanks
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.
ReplyDeleteThe code has also been posted to get hub and can be found here
Hi,
ReplyDeleteThanks 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...
Anonymous,
ReplyDeleteSince 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
Thanks for your detailed answer, there is also another limit to consider (this is from Salesforce documentation) :
ReplyDeleteSalesforce 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).
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?
ReplyDeleteDanielle,
ReplyDeleteIf 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
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.
ReplyDeleteDanielle,
ReplyDeleteThese 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
I have entered in the class in the sandbox, in the developer console. I am getting a error message stating
ReplyDelete"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."
Danielle,
ReplyDeleteThis 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
Hi
ReplyDeleteI 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
im new in salesforce,i save this code in apex class and then what to do
ReplyDeleteUdaya,
ReplyDeleteIf you browse the rest of this site you might find an example long the lines you are looking for.
Thanks,
John,
ReplyDeleteThis 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