Fastest way to calculate distances between two coordinates?

Question

We currently use the Geography type to calculate distance between a current location and the coordinates in our tsql table. Our code is based on this sqlauthority.com example.

Is there a faster way to retrieve the distance between two points? These calls will be done by a mobile phone app, so they should ideally be very fast.

Have you thought about calculating the distance on the client instead of having the database calculate it? That way the server only has to pass the coordinates. Here is a link that explains how to write a client side function that will do it. Just tweak it for the language you're coding the app in. https://stackoverflow.com/questions/365826/calculate-distance-between-2-gps-coordinates — BillRuhl, Mar 14 '19 at 23:00
Are you falling prey to a premature optimization problem? That is, is the current solution unacceptable in terms of performance? Alternatively, is there any way to predict ahead of time when the user is going to need that data and get the distance ahead of when they actually make the interaction? — Ben Thul, Mar 14 '19 at 23:48

score 0 · Answer 1 · edited Oct 07 '21 at 13:28

After testing it with a distance I know, looping 100 times per batch and running the batch 15 times to make sure the 10 runs the client statistics stores in SSMS are cycled past initial query plan generation so it doesn't skew the results. Here are the averages of the remaining. The calculation method seems to be twice as fast as the geography option.

With a difference in distance returned of 0.0000000020044.

Calculation script used (returned miles: 41.9013152732833)

set nocount on;
declare 
     @lat1 float = 45.489614
    ,@lon1 float = -122.650021
    ,@lat2 float = 44.94404
    ,@lon2 float = -123.025739
select 3959.1825574 * acos(sin(@lat1/57.295779513082323) * sin(@lat2/57.295779513082323) + cos(@lat1/57.295779513082323) * cos(@lat2/57.295779513082323) * cos((@lon2-@lon1)/57.295779513082323)) distance_in_miles
GO 100

Geography script used (returned miles: 41.9013152752877)

set nocount on;
declare
     @g geography = geography::Point(45.489614, -122.650021, 4326)
    ,@h geography = geography::Point(44.94404, -123.025739, 4326)
select @h.STDistance(@g) / 1609.344 distance_in_miles -- 1609.344 is meters in mile. STDistance = meters.
GO 100

Fair warning, doing it in a non-system function will still have unpredictable performance. I would recommend doing it inline for calculation.

Here's a raw calculation example.

Working example of inline syntax for miles. It is the easiest, most accurate and shortest syntax I could find.

adjusted for accuracy

if object_id('tempdb..#LatLongInfo','U') is not null
    begin
        drop table #LatLongInfo;
    end;
create table #LatLongInfo (
    lat1 float,
    lon1 float,
    lat2 float,
    lon2 float
);
insert into #LatLongInfo
values (21, -76, 23, -72);

select 
    3959.1825574 * acos(sin(lat1/57.295779513082323) * sin(lat2/57.295779513082323) + cos(lat1/57.295779513082323) * cos(lat2/57.295779513082323) * cos((lon2-lon1)/57.295779513082323)) distance_in_miles
from #LatLongInfo;

Hope this helps. I used something like this to find the doctors within a given range for patients back when sql2000 was released, it's been a while. Google was a newborn, no maps, nothing but a search box and one button. You have me all nostalgic now...I remember reading this when I coded that the first time.

Fastest way to calculate distances between two coordinates?

1 Answers1