Wherefore art thou?

Trying to deal with data in a location friendly manner is not a new problem.  Years ago, when Apex was but a new language and lacked some higher mathematical functions like sine and cosine I was on a project to provide data sets based on an account record’s address.  Trying to determine location without things like sine and cosine is difficult enough even for someone with an advanced knowledge of trigonometry.  So that was three things I didn’t have.  Our solution was to tag data based on a database which could translate ZIP codes into lat and long and then do queries based on squares.  Because when you don’t have trig on your side, squares are much easier to deal with.

It was clumsy and hacky.  It was also kind of awesome – and as a side note was the project that got me up on stage for the first time in front of developers.  Now with Winter ’13, though, you can get even more awesome without the hackery.

Adding Geolocation to your Objects

The first thing you’ll need to do as a new custom field to your object.  Go to the object definition in Setup, and click New Custom Field.  In Winter ’13, you’ll see a new field type – Geolocation.

 

 

Currently you are not able to add Geolocation fields via Schema Builder.  One of the choices you’ll need to make is whether you will want the field to be defined in degree or decimal notation.  Personally, I find decimal more common when using most geolocation services and is overall easier to store.  Apex will also refer to the value with decimal notation regardless, so this decision is mostly about how it will be represented externally and you’ll need to determine the number of decimal places to extend your field with regardless.  More decimal places mean greater accuracy, with six decimal places equalling to about 1 meter of accuracy:

Having extended the definition of Contact to know latitude and longitude, let us update a contact record quickly so that we can start using SOQL and Apex.  For this, I just grabbed this Geolocation Chrome Extension which makes it easy to look up lat and long based on an existing address, popped over to some sample records and added the information.  Don’t worry though, we’ll get to how to programmatically update the data as well as adding it via Bulk API.

Searching for Data based on Location

Now the fun stuff.  Having geocoded some contacts based on the Sears (err, uh … Willis) Tower here in Chicago as well as the Salesforce HQ in downtown San Francisco, we can hop into the also-new-to-Winter13 query editor within Developer Console and do the following search:

SELECT Name FROM Contact WHERE DISTANCE(Location__c, GEOLOCATION(37.7945391,-122.3947166), 'mi') < 1

That SOQL statement will do the distance calculation for us and return our one contact within a mile tagged for those coordinates (One Market Street in San Francisco):

If I flip that lesser than over to a greater than, we get everyone outside of a mile from Salesforce HQ, namely:

Accessing Geolocation via Apex and Visualforce

Easy to add the field, easy to query for data based on a location.  How about if you wanted to update those fields programmatically?  Or if you have a database of existing geolocation data based on ZIP code?  Or I dunno – you wanted to enter a new Contact and have the location automatically be entered based on your browser’s location?  Most HTML5 friendly browsers support geolocation via JavaScript so it would just be a matter of extending some basic Visualforce with that particular bit of functionality.  The trick here is knowing the syntax for the latitude and longitude fields, which goes:

Custom_Field__Latitude__s

and

Custom_Field__Longitude__s

Note the multiple uses of double underlines after the custom field name.  So in our case, Contact.Location__c_Latitude__s would refer specifically to the Latitude portion of our Location field.  So knowing that, we can create a Visualforce form powered by some HTML5 trickery to add latitude and longitude based on the current user’s actual location:

<apex:page StandardController="Contact" showHeader="true" sidebar="false">

<script>
var pos = {};
function success(position) {
  pos = position.coords;
  console.log(pos);
}

function error(msg) {
 console.log(msg);
}

if (navigator.geolocation) {
  navigator.geolocation.getCurrentPosition(success, error);
} else {
  error('not supported');
}

function setPos() {
	var inputs = document.getElementsByTagName('input');
	for(var x = 0; x < inputs.length; x++) {
		if(inputs[x].id.indexOf('contactlat') >= 0) { inputs[x].value = pos.latitude; }
		if(inputs[x].id.indexOf('contactlong') >= 0) { inputs[x].value = pos.longitude; }
	}
}

</script>

<apex:form >

<apex:pageBlock>
	<apex:pageBlockButtons>
                <apex:commandButton action="{!quicksave}" value="Save"/>
				<button onClick="setPos(); return false;" id="setPosBtn">Set Position</button>
</apex:pageBlockButtons>
<apex:pageBlockSection title="My Contact" columns="2">
	<apex:inputField value="{!Contact.FirstName}" />
	<apex:inputField value="{!Contact.LastName}" />
	<apex:inputField value="{!Contact.Location__Latitude__s}" id="contactlat" />
	<apex:inputField value="{!Contact.Location__Longitude__s}" id="contactlong" />
</apex:pageBlockSection>
</apex:pageBlock>
</apex:form>

</apex:page>

Here’s a screenshot of the page in action:

If you want to perform dynamic geolocation calls in Apex, you currently a little limited in that the DISTANCE filter does not allow for bound data (although I’m told this is high on the list for post-beta plans).  So, in order to perform dynamic searches you would need to utilize a generated string passed into Database.query().  As an example, here’s a RemoteAction method to find anyone within 10 miles of a specific location:

@RemoteAction
	public static List<Contact> getNearbyContacts(Decimal clat, Decimal clong) {
		String q = 'SELECT Name FROM Contact WHERE DISTANCE(Location__c, GEOLOCATION('+String.valueOf(clat)+','+String.valueOf(clong)+'), "mi") < 10';
		List<Contact> contacts = Database.query(q);
		return contacts;
	}

 

Accessing Geolocation Via API

Using the same syntax, we can offer up API access to the Location fields as well.  So if I sent this POST data via REST to create a new record:

{ "LastName" : "Geo", "FirstName" : "Neo", "Location__Latitude__s" : 37.794539, "Location__Longitude__s" : -122.394717 }

The record would be created with the Location fields filled out.  Or if I wanted to search, I could send this over GET:

/services/data/v26.0/query?q=SELECT+Name+FROM+Contact+WHERE+DISTANCE(Location__c,+GEOLOCATION(37.7945391,-122.3947166),+'mi')+<+1

A geocoded world of possibilities

Geolocation is one of those technologies which has become slowly more and more pervasive.  It’s in our browsers, our tablets, our smartphones – and now in our datasets and query language for the Salesforce Platform.  Even in beta form, Geolocation in Winter ’13 provides some pretty amazing capabilities to both existing and new applications.

What will you build with it?  Let us know in the comment section below or ping me on twitter @joshbirk. I also have the code samples above available as a gist.

 

tagged , , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • http://twitter.com/corycowgill Cory Cowgill

    Great article! Bounding Algorithm was clunky, but only way to solve the problem until now! I built a Geolocation App last year on Force.com to query transit data based on users Geolocation and the source is on Github here: https://github.com/corycowgill/sfbusradar/tree/master/src. It has a sample of how to invoke GeoLocation Encoding Services to GeoCode your data via the Google GeoCode service for people who would like to know the guts of Geocoding their data.

    • Kal

      Is Custom_Field__c__Latitude__s available in a SOQL selection statement?
      I would like to return the Longitude and Latitude from the Geolocation
      field as part of a resultset, not calculate distance (in this
      instance).

      • Josh Birk

        Yeah, it is available as part of the SOQL select in the same format you’d use on VF or via the API.

        • Kal

          Josh,

          Thank you for the prompt response. In my testing I have not been able to query the geolocation field directly using SOQL. After creating and populating a custom geolocation fields called Location on the Account record, the simple query ‘SELECT Id, Name, Location__c__Latitude__s FROM Account’ fails as I get the ‘No such column exists on the account’ error message. I also tried querying Location__c directly without success. I see use for it in the examples you provided determining accounts within x miles of a specified location, but I would also like to use it to plot account locations on a google map.

          Kal

          • http://twitter.com/joshbirk Josh Birk

            The syntax is slightly different: Location__Latitude__s

          • Kal

            Perfect. Works great. Thank you very much. I will post the update to the article.

  • http://www.facebook.com/rgao2009 Raymond Gao

    Nice write-up Josh, APEX getting more powerful everyday. Any plan to support Zip code to GEO coord look up & reverse?

    • Josh Birk

      Not AFAIK Ray. It’s relatively straightforward to call out to Google’s reverse lookup in Apex, but some licensing restrictions may apply.

      • Chris Kellie

        Where can I find examples call-outs to Google for a reverse lookup in Apex?

  • http://twitter.com/abhinavguptas Abhinav Gupta

    Thanks for this detailed write up Josh, you covered good use cases. Hands getting itchy now to try something out on the same :)

  • Kal

    Thanks to Josh Birk I was able to understand the proper SOQL syntax. The actual syntax for a SOQL query is Custom_Field__Latitude__s, instead of the Custom_Field__c__Latitude__s as provided in the article above.

    This is certainly confusing as the Accounts field page displays the appended __c as you would expect for a custom field.

  • david

    Thanks for the article, but *please* place the corrected field names into the text to reduce confusion.

  • http://www.facebook.com/bpirih Benjamin Pirih

    How would one sort by distance and limit?? i.e. I want to select the 10 nearest records to my current location.

    • http://www.facebook.com/bpirih Benjamin Pirih

      Anyone have ideas?? Seems like you should be able to do this??

      • Anonymous

        HI Benjamin, have you tried using Order By with the same DISTANCE function call. e.g. in Josh’s @RemoteAction example above replacing the dynamic SOQL with something like this:

        SELECT Name FROM Contact WHERE DISTANCE(Location__c, GEOLOCATION(‘+String.valueOf(clat)+’,'+String.valueOf(clong)+’), “mi”) < 10 ' +
        ORDER BY DISTANCE(Location__c, GEOLOCATION('+String.valueOf(clat)+','+String.valueOf(clong)+'), "mi")

        • http://www.facebook.com/bpirih Benjamin Pirih

          Thanks Bacchus.. I wasn’t able to originally save this code in the IDE but it does appear to work now..

          SearchObject( decimal latitude, decimal longitude, decimal radius)
          {
          string qfields = ‘Id, Name, Location__Longitude__s, Location__Latitude__s’;
          string qFrom = ‘ FROM MyObject__c’;

          string qWhere = ‘ WHERE DISTANCE(Location__c, GEOLOCATION(‘ + String.valueOf(latitude) + ‘,’ + String.valueOf(longitude) + ‘),’ + ”mi’ + ”) < ' + string.valueOf(radius);

          string qOrderBy = ' ORDER BY DISTANCE(Location__c, GEOLOCATION(' + String.valueOf(latitude) + ',' + String.valueOf(longitude) + '),' + ''mi' + '') ASC LIMIT 20';

          string query = 'Select ' + qfields + qFrom + qWhere + qOrderBy;

  • http://www.facebook.com/pandeyharshit Harshit Pandey

    What if I do not have custom object ‘Location__c’ rather I have fields on account something like Account_lat__s, Account__long__s and I to find nearby account in 30 miles radius using distance method

    Something in SOQL String q = ‘SELECT Name FROM Account WHERE DISTANCE(Location__c, GEOLOCATION(‘+String.valueOf(clat)+’,'+String.valueOf(clong)+’), “mi”) Do I have to always create a custom object Location__c ? How would be my query be looking like ?

  • Rajendra Rathore

    I have need to Requirement to calculate distance between two Geo Location field in a formula field.

    . Please can you help me to how to find formula for that requirement.
    Thanks

  • Seth Carstens

    How soon until we can build this directly into the APEX instead of having to build these string queries?