Location-Based SOQL Queries—Beta

The geolocation custom field type allows you to create a field to store location values. Geolocation fields identify a location by its latitude and longitude. Additionally, standard addresses on Salesforce objects include a geolocation field and, once populated, can be used in similar ways (with a few restrictions). Locations of both types can be compared and queried, for example, to find the 10 closest accounts.

See “Compound Fields—Beta” in the SOAP API Developer’s Guide for additional details of using address and geolocation compound fields, including a few limitations of the beta.

Field Types that Support Location-Based SOQL Queries

SOQL supports using simple numeric values for latitude and longitude using the GEOLOCATION function. These values can come from standard numeric fields, user input, calculations, and so on. They can also come from the individual components of a geolocation field, which stores both latitude and longitude in a single logical field. And, if the geolocation field of a standard address has been populated by a geocoding service, latitude and longitude values can be used directly from an address.

SOQL queries made using the SOAP and REST APIs also support using geolocation fields, including address fields that have a geolocation component, directly in SOQL statements. This often results in simpler SOQL statements. Compound fields can only be used in SOQL queries made through the SOAP and REST APIs.

SELECT Clause

Retrieve records with locations saved in geolocation or address fields as individual latitude and longitude values by appending “__latitude__s” or “__longitude__s” to the field name, instead of the usual “__c”. For example:

1SELECT Name, Location__latitude__s, Location__longitude__s 
2FROM Warehouse__c

This query finds all of the warehouses that are stored in the custom object Warehouse. The results include each warehouse’s latitude and longitude values individually. Note the use of the separate field components for Location__c, to select the latitude and longitude components individually.

SOQL executed using the SOAP or REST APIs can SELECT the compound field, instead of the individual elements. Compound fields are returned as structured data, rather than primitive values. For example:

1SELECT Name, Location__c 
2FROM Warehouse__c

This query retrieves the same data as the previous query, but the Location__c field is a compound geolocation field, and the results combine the two primitive values. Results from a REST API request might look something like this.

1{
2  "totalSize" : 10,
3  "done" : true,
4  "records" : [ {
5    "attributes" : {
6      "type" : "Warehouse__c",
7      "url" : "/services/data/v30.0/sobjects/Warehouse__c/a06D00000017O4nIAE"
8    },
9    "Name" : "Ferry Building Depot",
10    "Location__c" : {
11      "latitude" : 37.79302,
12      "longitude" : -122.394507
13    }
14  }, {
15    "attributes" : {
16      "type" : "Warehouse__c",
17      "url" : "/services/data/v30.0/sobjects/Warehouse__c/a06D00000017O4oIAE"
18    },
19    "Name" : "Aloha Warehouse",
20    "Location__c" : {
21      "latitude" : 37.786108,
22      "longitude" : -122.430152
23    }
24  }, 
25  ...
26  ]
27}

WHERE Clause

Retrieve records with locations within or outside of a certain radius with distance conditions in the WHERE clause of the query. Use the following functions to construct an appropriate distance condition.

DISTANCE: Calculates the distance between two locations in miles or kilometers.; Usage: DISTANCE(mylocation1, mylocation2, 'unit') and replace mylocation1 and mylocation2 with two location fields, or a location field and a value returned by the GEOLOCATION function. Replace unit with mi (miles) or km (kilometers).
GEOLOCATION: Returns a geolocation based on the provided latitude and longitude. Must be used with the DISTANCE function.; Usage: GEOLOCATION(latitude, longitude) and replace latitude and longitude with the corresponding geolocation, numerical code values.

Compare two field values, or a field value with a fixed location. For example:

1SELECT Name, Location__c 
2FROM Warehouse__c 
3WHERE DISTANCE(Location__c, GEOLOCATION(37.775,-122.418), 'mi') < 20

ORDER BY Clause

Sort records by distance using a distance condition in the ORDER BY clause. For example:

1SELECT Name, StreetAddress__c 
2FROM Warehouse__c 
3WHERE DISTANCE(Location__c, GEOLOCATION(37.775,-122.418), 'mi') < 20 
4ORDER BY DISTANCE(Location__c, GEOLOCATION(37.775,-122.418), 'mi')
5LIMIT 10

This query finds up to 10 of the warehouses in the custom object Warehouse that are within 20 miles of the geolocation 37.775°, -122.418°, which is San Francisco. The results include the name and address of each warehouse, but not its geocoordinates. The nearest warehouse will be first in the list; the farthest location will be last.

How SOQL Treats Null Location Values

Geolocation fields are compound fields that combine latitude and longitude values to describe a specific point on Earth. Null values are valid only if both latitude and longitude are null.

Records that have invalid geolocation field values, that is, geolocations where either latitude or longitude is null, but not both, are treated as though both values are null when used in SOQL WHERE DISTANCE() and ORDER BY clauses. In other words, as though the field has not been set.

When a compound geolocation field is used in a SELECT clause, invalid geolocation values return null. For example:

1SELECT Name, Location__c 
2FROM Warehouse__c 
3LIMIT 5

The values returned from an API call might look something like these values.

Name	Location__c
Ferry Building Depot	null
Aloha Warehouse	(37.786108,-122.430152)
Big Tech Warehouse	null
S H Frank & Company	null
San Francisco Tech Mart	(37.77587,-122.399902)

These results include three null geolocation values. It’s not possible to tell which values are genuinely null, and which are invalid data.

When the individual field components of that same geolocation field are used in a SELECT clause, the saved values are returned as before. In other words, non-null values will be returned as that value, and null values return as null. For example:

1SELECT Name, Location__latitude__s, Location__longitude__s 
2FROM Warehouse__c 
3LIMIT 5

In this example, the results might look like these values.

Name	Location__latitude__s	Location__longitude__s
Ferry Building Depot	null	-122.394507
Aloha Warehouse	37.786108	-122.430152
Big Tech Warehouse	null	null
S H Frank & Company	37.763662	null
San Francisco Tech Mart	37.77587	-122.399902

In these results, only one geolocation field is genuinely null. The other two, with partial nulls, are invalid.

How SOQL Calculates and Compares Distances

The DISTANCE function approximates the haversine, or “great circle,” distance calculation within 0.0002%. This formula assumes that the Earth is a perfect sphere, when in fact it’s an ellipsoid, and an irregular one at that. Errors from this assumption might be up to 0.55% crossing the equator but are generally below 0.3%, depending on latitude and direction of travel.

In other words, it’s perfectly fine for calculating the 10 stores closest to a customer’s current location, but don’t fuel your plane for a flight from San Francisco to Sydney based on it.

Another implication of this approximation is that there’s no notion of “equal” for geolocations or distances. That means that you can’t check locations or distances for equality, only whether one location is farther away or closer than another location, or one distance is greater or smaller than another distance. If you need to verify that two locations are “the same,” treat their distance as a floating point number and compare the difference to a tolerance value. For example, this WHERE clause finds other records within 25 feet of testLocation.

1WHERE ( DISTANCE(Location__c, testLocation) < 0.05 )

Additionally, although the errors are small, for nearly identical distances, the errors might cause a location query to include or exclude expected locations. The use cases for DISTANCE queries include “show me the closest 10 stores” and “where are my San Francisco accounts’ contacts located?”. If your application requires extremely accurate distance calculations and comparisons, we recommend that you do your own math.

Limitations on Location-Based SOQL Queries

As a beta-release feature, location-based queries are supported in SOQL in Apex and in the SOAP and REST APIs with the following limitations:

Outside of the SOAP and REST APIs, geolocation fields are supported in the SELECT clause of SOQL queries only at the component level. In other words, you have to query the latitude or longitude; you can’t query the compound location field. Specify geolocation field components by appending “__latitude__s” or “__longitude__s” to the field name, instead of the usual “__c”.
DISTANCE and GEOLOCATION are supported in WHERE and ORDER BY clauses in SOQL, but not in GROUP BY or SELECT.
DISTANCE supports only the logical operators > and <, returning values within (<) or beyond (>) a specified radius.
When using the GEOLOCATION function in SOQL queries, the geolocation field must precede the latitude and longitude coordinates. For example, DISTANCE(warehouse_location__c, GEOLOCATION(37.775,-122.418), "km") works but DISTANCE(GEOLOCATION(37.775,-122.418), warehouse_location__c, "km") doesn’t work.

See “Compound Field Considerations and Limitations” in the SOAP API Developer’s Guide for additional details.

Force.com SOQL and SOSL Reference

No Results