[SalesForce] Is it possible to use the DISTANCE SOQL function without a GEOLOCATION field, or some other approach

I want to make a SOQL call to "near me" filter the ACCOUNT records server-side. (Too many to bring down and filter on the client). It would seem that using DISTANCE function in the call would suit, however presently my ACCOUNT object has no GEOLOCATION field, but it does have Latitude__c and Longitude__c fields. Do I absolutely have to create a GEOLOCATION field on the Account object to use the DISTANCE functionality. Should I do this (then deprecate Lat and Long__c) or should I create custom Apex to do the near me calculation server side, or is there some other easier approach that I have not yet thought of?

Best Answer

It is better strategy to use geolocation field and use distance functionality rather than going for server side painful calculations. Also, it is tricky to do such calculation and is error prone. The hardest part is you have to deprecate the fields that you are having now and run a one time batch job to populate the new field. So, the idea is,

Take a backup of the data
Deprecate the fields
Create geolocation field
Use it in your query or in apex
load the data back in the geolocation fields

Finally, use anything that platform provides is always scalable.

Related Solutions

[SalesForce] What formulae does the Geolocation Distance use

The documentation states:

Distance is calculated as a straight line—as the crow flies—regardless of geography and topography between the two points.

In reality Salesforce uses the haversine formula to calculate the great-circle distance between two points.

For any two points on a sphere, the haversine of the central angle between them is given by $\operatorname{haversine}\left(\frac{d}{r}\right) = \operatorname{haversin}(\phi_2 - \phi_1) + \cos(\phi_1) \cos(\phi_2)\operatorname{haversin}(\lambda_2-\lambda_1)$

where

haversin is the haversine function:
$\operatorname{haversin}(\theta)=\sin^2\left(\frac{\theta}{2}\right)=\frac{1-\cos(\theta)}{2}$

d is the distance between the two points (along a great circle of the sphere; see spherical distance),

r is the radius of the sphere,

$\phi1, \phi2$ : latitude of point 1 and latitude of point 2

$\lambda_1, \lambda_2$ : longitude of point 1 and longitude of point 2

On the left side of the equals sign d/r is the central angle, assuming angles are measured in radians (note that φ and λ can be converted from degrees to radians by multiplying by π/180 as usual).

Solve for d by applying the inverse haversine (if available) or by using the arcsine (inverse sine) function:

$d = r \operatorname{haversin}^{-1}(h) = 2 r \arcsin\left(\sqrt{h}\right)$

where h is haversin(d/r), or more explicitly:

$d = 2 r \arcsin\left(\sqrt{\operatorname{haversin}(\phi_2 - \phi_1) + \cos(\phi_1) \cos(\phi_2)\operatorname{haversin}(\lambda_2-\lambda_1)}\right)$ $= 2 r \arcsin\left(\sqrt{\sin^2\left(\frac{\phi_2 - \phi_1}{2}\right) + \cos(\phi_1) \cos(\phi_2)\sin^2\left(\frac{\lambda_2 - \lambda_1}{2}\right)}\right)$

You can confirm this by creating a custom object with two geolocation fields and a formula field which uses the DISTANCE formula to calculate the distance between the two locations, and then comparing the results to this haversine calculator.

Here are some examples:

Wellington (-41.31666667, 174.81666667) to Salamanca (40.96666667, -5.5)

Salesforce: 19,968.02154120 km
Haversine Calculator: 19970 km (to 4 SF)

London (51.507222, -0.1275) to Tokyo (35.689506, 139.6917)

Salesforce: 9,558.73252169 km
Haversine Calculator: 9559 km (to 4 SF)

Moscow (55.75, 37.616667) to Nice (43.7034, 7.2663)

Salesforce: 2,529.41039443 km
Haversine Calculator: 2529 km (to 4 SF)

Madrid (40.383333, -3.716667) to Sydney (-33.859972, 151.211111)

Salesforce: 17,686.13743193 km
Haversine Calculator: 17690 km (to 4 SF)

Pensacola (30.433333, -87.2) to Asmara (15.333333, 38.933333)

Salesforce: 12,329.08258719 km
Haversine Calculator: 12330 km (to 4 SF)

A couple of related posts have since appeared in the Salesforce documentation.

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: an irregular one. Errors from this assumption can be up to 0.55% crossing the equator, but are usually below 0.3%, depending on latitude and direction of travel.

The DISTANCE function is 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.

Blog Post: Geolocation Field Types in Salesforce1 Platform

The distance calculation is based on the haversine formula, which is used in mapping and navigation applications and is illustrated below.

enter image description here

[SalesForce] can geolocation fields be used to calculate and display distance

You can use the DISTANCE and GEOLOCATION formula functions to create a custom field that will calculate the distance in miles to a fixed location. Then you can use this field in your Visualforce pages as required.

DISTANCE(acme_distribution__c, GEOLOCATION(37.775,-122.418), "mi")

Failing that, you can do the haversine formula calculation yourself in Apex:

public Decimal calculateHaversineDistance(Decimal lat1, Decimal lon1, Decimal lat2, Decimal lon2){
    // Earth's radius varies from 6356.752 km at the poles to 6378.137 km at the equator
    Double radius = 6371.00;
    Double dLat = toRadians(lat2-lat1);
    Double dLon = toRadians(lon2-lon1);
    Double a = Math.sin(dLat/2) * Math.sin(dLat/2) +
        Math.cos(toRadians(lat1)) * Math.cos(toRadians(lat2)) *
        Math.sin(dLon/2) * Math.sin(dLon/2);
    Double c = 2 * Math.asin(Math.sqrt(a));

    double kmToMiles = 0.621371;
    return radius * c * kmToMiles;
}

private Double toRadians(Decimal degree){
    return degree * 3.1415926 / 180;
}

Note that this includes the conversion from kilometres to miles in case you are in one of the three countries in the world that don't use the metric system :)

You may want to work to, say, 4 significant figures, as there are a number of simplifications in this approach.

Best Answer

Related Solutions

[SalesForce] What formulae does the Geolocation Distance use

[SalesForce] can geolocation fields be used to calculate and display distance

Related Topic