+ Start a Discussion
Rex Rodriguez 4Rex Rodriguez 4 

One-time batch geocode of all Salesforce records

I am looking for a service that will do a one-time geocoding of all records of a particular Salesforce object, saving these gecoordinates into a cutom field on the record. Preferably, I would like to do this without having to export the Salesforce information, for example into a csv file. 

Does anyone know of a good app or service to do this? I have done some research myself but would like to look into as many options as possible. 
Best Answer chosen by Rex Rodriguez 4
SKolakanSKolakan
@Rex
See if this helps! This works for updating Account object's lat and long. Modify accordingly. Make sure that you account for Apex callout limitations. You can use any geo code service that you have subscribed to. I am using google's api which is free with some limitations..
global class batchUpdateAccountGeoLocation implements Database.Batchable<sObject>,Database.AllowsCallouts{
    
  //Start 
   global Database.QueryLocator start(Database.BatchableContext BC){
       string query = 'SELECT Id, Name, BillingStreet, BillingCity, BillingState,Location__Latitude__s,Location__Longitude__s ' +
                   'FROM Account ' + 
                   'WHERE BillingStreet!=null and  Location__Latitude__s = null ';
      return Database.getQueryLocator(query);
   }

    //Execute
   global void execute(Database.BatchableContext BC,List<Account> scope){
      
      for(Account a : scope){          
          GeoCodeResponse ogc = GeoCodeAddress.getGeoCodeAddress(a.BillingStreet.replace(' ','+') + ',' + a.BillingCity + ',' + a.BillingState);
                     a.Location__Latitude__s = ogc.results[0].geometry.location.lat;
                     a.Location__Longitude__s = ogc.results[0].geometry.location.lng;                  
      }     
       
       try{
           update scope;
       }catch(exception e){
           system.debug('batchUpdateAccountGeoLocation exception:' + e.getMessage());
       }       
   }

    //Finish
   global void finish(Database.BatchableContext BC){

   }
}




public class GeoCodeAddress {    
        
    public static GeoCodeResponse getGeoCodeAddress(String Address) {
        string url ='https://maps.googleapis.com/maps/api/geocode/json?key=<Your Key Here>&address=' + EncodingUtil.urlEncode(Address, 'UTF-8');
        
        // Instantiate a new http object
        Http h = new Http();
        
        // Instantiate a new HTTP request, specify the method (GET) as well as the endpoint
        HttpRequest req = new HttpRequest();
        req.setEndpoint(url);
        req.setMethod('GET');
        
        // Send the request, and return a response
        HttpResponse res = h.send(req);        
        
        return GeoCodeResponse.parse(res.getBody());         
    }
    
}


public class GeoCodeResponse {

  public class Address_components {
    public String long_name;
    public String short_name;
    public List<String> types;
  }

  public List<Results> results;
  public String status;

  public class Location {
    public Double lat;
    public Double lng;
  }

  public class Geometry {
    public Location location;
    public String location_type;
    public Viewport viewport;
  }

  public class Results {
    public List<Address_components> address_components;
    public String formatted_address;
    public Geometry geometry;
    public List<String> types;
  }

  public class Viewport {
    public Location northeast;
    public Location southwest;
  }
        
  public static GeoCodeResponse parse(String json) {
    return (GeoCodeResponse) System.JSON.deserialize(json, GeoCodeResponse.class);
  }
 }

 

All Answers

SKolakanSKolakan
@Rex
See if this helps! This works for updating Account object's lat and long. Modify accordingly. Make sure that you account for Apex callout limitations. You can use any geo code service that you have subscribed to. I am using google's api which is free with some limitations..
global class batchUpdateAccountGeoLocation implements Database.Batchable<sObject>,Database.AllowsCallouts{
    
  //Start 
   global Database.QueryLocator start(Database.BatchableContext BC){
       string query = 'SELECT Id, Name, BillingStreet, BillingCity, BillingState,Location__Latitude__s,Location__Longitude__s ' +
                   'FROM Account ' + 
                   'WHERE BillingStreet!=null and  Location__Latitude__s = null ';
      return Database.getQueryLocator(query);
   }

    //Execute
   global void execute(Database.BatchableContext BC,List<Account> scope){
      
      for(Account a : scope){          
          GeoCodeResponse ogc = GeoCodeAddress.getGeoCodeAddress(a.BillingStreet.replace(' ','+') + ',' + a.BillingCity + ',' + a.BillingState);
                     a.Location__Latitude__s = ogc.results[0].geometry.location.lat;
                     a.Location__Longitude__s = ogc.results[0].geometry.location.lng;                  
      }     
       
       try{
           update scope;
       }catch(exception e){
           system.debug('batchUpdateAccountGeoLocation exception:' + e.getMessage());
       }       
   }

    //Finish
   global void finish(Database.BatchableContext BC){

   }
}




public class GeoCodeAddress {    
        
    public static GeoCodeResponse getGeoCodeAddress(String Address) {
        string url ='https://maps.googleapis.com/maps/api/geocode/json?key=<Your Key Here>&address=' + EncodingUtil.urlEncode(Address, 'UTF-8');
        
        // Instantiate a new http object
        Http h = new Http();
        
        // Instantiate a new HTTP request, specify the method (GET) as well as the endpoint
        HttpRequest req = new HttpRequest();
        req.setEndpoint(url);
        req.setMethod('GET');
        
        // Send the request, and return a response
        HttpResponse res = h.send(req);        
        
        return GeoCodeResponse.parse(res.getBody());         
    }
    
}


public class GeoCodeResponse {

  public class Address_components {
    public String long_name;
    public String short_name;
    public List<String> types;
  }

  public List<Results> results;
  public String status;

  public class Location {
    public Double lat;
    public Double lng;
  }

  public class Geometry {
    public Location location;
    public String location_type;
    public Viewport viewport;
  }

  public class Results {
    public List<Address_components> address_components;
    public String formatted_address;
    public Geometry geometry;
    public List<String> types;
  }

  public class Viewport {
    public Location northeast;
    public Location southwest;
  }
        
  public static GeoCodeResponse parse(String json) {
    return (GeoCodeResponse) System.JSON.deserialize(json, GeoCodeResponse.class);
  }
 }

 
This was selected as the best answer
Rex Rodriguez 4Rex Rodriguez 4
Thank you for this response.I can certainly try it, but I am worried that I have too many records for this method. I have over 218,000 records that need to be updated. According to google, the limit is 2500 records, however I am considering them as we may have to pay for this service in any case.
Rex Rodriguez 4Rex Rodriguez 4

Additionally, in trying to use your code, I received the following error:

Error: Compile Error: unexpected token: public at line 36 column 0
SKolakanSKolakan
@Rex
You need to create 3 classes from above code.

As far as api limitation is concerned, google api costs $0.5 for 1000 calls. Which is awesome for your needs.
Rex Rodriguez 4Rex Rodriguez 4
Oh yes my mistake. Thank you, I'll continue experimenting with this and hopefully it will work for me.
Rex Rodriguez 4Rex Rodriguez 4

I needed to make one edit to your code to handle a null scenario.

Changing line 16 from:

a.Location__Latitude__s = ogc.results[0].geometry.location.lat;
a.Location__Longitude__s = ogc.results[0].geometry.location.lng;
 

to

if(ogc.status!='ZERO_RESULTS')
{
a.Location__Latitude__s = ogc.results[0].geometry.location.lat; 
a.Location__Longitude__s = ogc.results[0].geometry.location.lng;
}