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
I have indexed all columns named in both query and function.
- 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.
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 <columns> from locations loc left outer join baseline base on base.key in (select key from baseline where base.location_geo.STDistance(loc.location_geo) <= 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:
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