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) &lt;= 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, merely a .net developer, so I am not really knowledgeable about improving query performance beyond indexing, so any help is appreciated.

EDIT:

Rolling in Rob and Mark's answers allowed my to get my query execution down to 30 seconds. In case there's any further gains to be had, here is the query now:

select &lt;columns&gt;
from locations loc 
left outer join baseline base  
on base.key in
(select key
from baseline
where base.location_geo.STDistance(loc.location_geo) &lt;= 0.00015678559)


The 0.00015678559 is 1000/6378137 (Earth radius) so I don't need to do any math when the query is run.

The location_geo is a computed column using the formula:

([geography]::STGeomFromText(((('POINT('+[Longitude])+' ')+[Latitude])+')',(104001)))


104001 is the Spatial ID for a spherical Earth model, since I'm not looking for a large distance this seems to make the query quicker to return.

I also have a Spatial index on the location_geo column as suggested here: http://social.technet.microsoft.com/wiki/contents/articles/9694.tuning-spatial-point-data-queries-in-sql-server-2012.aspx
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.