Has anyone found a way to use the DISTANCE function in a SELECT phrase. I've seen how to use it in the WHERE and ORDER BY clauses, but what I'm trying to do is something like...

SELECT id, name, location, DISTANCE(location, GEOLOCATION(lat, lon), 'mi') 

Why? I want to show the distance to users in the UI, rather than just sorting by distance.




Unfortunately, according to the documentation, you can't do that:

Location-based queries are supported in SOQL in Apex and in the SOAP and REST APIs with the following limitations:

DISTANCE and GEOLOCATION are supported in WHERE and ORDER BY clauses in SOQL, but not in GROUP BY or SELECT.

Referenced from here

You can then go through the list and calculate distance using the Haversine formula, as shown in this answer by Daniel Ballinger.

If you don't mind a few queries, you could perform several queries to get "Bands" of distance away - 1mile, 5 miles, 10 miles etc by adding

DISTANCE () < 2 

And then

DISTANCE () < 6 AND DISTANCE () > 1 ///etc
| improve this answer | |
  • Thank you! The limitation from SELECT is exactly what I'm trying to work around. I really like your suggestion of the Haversine formula and that would probably be good enough despite being "as the crow flies". Then again, I wouldn't be surprised if the DISTANCE function is taking a crow flies approach anyway. – sberley May 18 '15 at 1:29
  • Yes, it probably is using a Haversine type approach, although I'm definitely guessing at this point :) – Caspar Harmer May 18 '15 at 1:50
  • lame... it even allows you to put it in the select clause without throwing error. Hopefully they will add support for this sometime in the future... – NSjonas Oct 6 '15 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.