Winter ’13: Using Apex and SOQL with Geolocation

With Winter '13, you can tag, query and manipulate your data easily based on location and distance. Let's take a round trip with this beta feature coming to a sandbox near you soon.

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.

 

Published
October 9, 2012

Leave your comments...

Winter ’13: Using Apex and SOQL with Geolocation