>declare @CenterLat float=47.7795 >declare @CenterLon float=-65.7191 >declare @SearchDistance float=250 > >select * from test > where (latitude>=@CenterLat - @SearchDistance/111.0 and latitude<=@CenterLat + @SearchDistance/111.0) and > (longitude>=@CenterLon - @SearchDistance/111.0 and longitude<=@CenterLon + @SearchDistance/111.0) > and dbo.CalculateDistance(@CenterLon, @CenterLat, Longitude, Latitude) <=@SearchDistance >>
>ALTER FUNCTION [dbo].[CalculateDistance] > (@Longitude1 DECIMAL(8,5), > @Latitude1 DECIMAL(8,5), > @Longitude2 DECIMAL(8,5), > @Latitude2 DECIMAL(8,5)) >RETURNS FLOAT >AS >BEGIN >DECLARE @Temp FLOAT > >SET @Temp = SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) + > COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 - > @Longitude1/57.2957795130823) > >IF @Temp > 1 > SET @Temp = 1 >ELSE IF @Temp < -1 > SET @Temp = -1 > >RETURN (3958.75586574 * ACOS(@Temp) ) >>