Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point.

To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes.

Currently, the query runs over roughly 5000 records in about 5 minutes. This is acceptable, but I would like to make it as fast as possible.

The main query I am using:

select loc.*, base.key
from locations loc left outer join baseline base
on base.key in
(select key
from baseline
where [dbo].CIRCLEDISTANCE(loc.LAT, base.LATITUDE, loc.LONG, base.LONGITUDE) <= 1)

The Circle Distance scalar value function:

ALTER FUNCTION CIRCLEDISTANCE 
(
    -- Add the parameters for the function here
    @LAT1 varchar(250), 
    @LAT2 varchar(250),
    @LNG1 varchar(250), 
    @LNG2 varchar(250)
)
RETURNS float
AS
BEGIN

    DECLARE @Distance float
    DECLARE @LAT1_FLOAT float
    DECLARE @LNG1_FLOAT float
    DECLARE @LAT2_FLOAT float
    DECLARE @LNG2_FLOAT float

    select @LAT1_FLOAT = cast(@LAT1 as float)
    select @LNG1_FLOAT = cast(@LNG1 as float)
    select @LAT2_FLOAT = cast(@LAT2  as float)
    select @LNG2_FLOAT = cast(@LNG2 as float)

    SELECT @Distance = acos(sin(radians(@LAT1_FLOAT))*
            sin(radians(@LAT2_FLOAT))+
            cos(radians(@LAT1_FLOAT))*
            cos(radians(@LAT2_FLOAT))*
            cos(radians(@LNG2_FLOAT)-radians(@LNG1_FLOAT ))
            )*6371;

    RETURN @Distance

END
GO

The execution plan (with some anonymizing)

I have indexed all columns named in both query and function.

Some caveats:

• I am converting to float in the function because the data I am getting arrives as a string and I am unable to change it prior to this point

• The left outer join is required because I need to know locations that do not have a known point

• I am using the Law of Cosines rather than Haversine because of it's simplicity and I don't require a large amount of accuracy, since I am only interested in things within 1 km.!

I am not a DBA myself, me

Create a computed column which converts your lat and long columns into a geography type using the Point constructor. Then put a spatial index on this computed column.

Then your query can create a geography point from your circle centre, and compare distances. Should be very quick.

StackExchange Database Administrators Q#69081, answer score: 6