find the nearest location in ms-sql

Question

I send these paramaters my script : Latitude : 41.0186 Longitude : 28.964701 (it is sample). i want to find nearest location's name. how to do this? (query's where code's must be changed)

Sql Query :

   SELECT  Name FROM Location 
   WHERE Latitude = 41.0186 AND longitude= 28.964701

Location table likes this: (in real, this is huge table)

Latitude         longitude          Name
41.0200500000   40.5234490000        a
41.0185714000   37.0975924000        b
41.0184913000   34.0373739000        c
41.0166667000   39.5833333000        d
41.0166667000   28.9333333000        e

Answer 1

Use this function

CREATE FUNCTION dbo.DictanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
RETURNS FLOAT 
AS
BEGIN

    RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

You may order by this function, BUT on large datasets it will be very slow, so try to prefilter the recordset

UPD:

Using @chopikadze's test data:

declare @lat float, @lng float
select @lat = 41.0186, @lng = 28.964701

declare @Location table(Latitude float, Longtitude float, Name nvarchar(50))
insert into @Location(Latitude, Longtitude, Name) values (41.0200500000, 40.5234490000, 'a')
insert into @Location(Latitude, Longtitude, Name) values (41.0185714000, 37.0975924000, 'b')
insert into @Location(Latitude, Longtitude, Name) values (41.0184913000, 34.0373739000, 'c')
insert into @Location(Latitude, Longtitude, Name) values (41.0166667000, 39.5833333000, 'd')
insert into @Location(Latitude, Longtitude, Name) values (41.0166667000, 28.9333333000, 'e')

SELECT ABS(dbo.DictanceKM(@lat, Latitude, @lng, Longtitude)) DistanceKm, * FROM @Location
ORDER BY ABS(dbo.DictanceKM(@lat, Latitude, @lng, Longtitude))

Assuming that the Earth is NOT a geoid, but the round ball, if you need under 1m exact formula - I can find it, don't have it with me

Answer 2

declare @latitude float, @longitude float
select @latitude = 41.0186, @longitude = 28.964701

 SELECT [Name]   --, other columns
      ,Distance
      from
      (
      select 
      [Name]       --, other columns 
       ,( 3959 * acos( cos( radians(@latitude) ) * cos( radians( [Lattitude] ) ) * cos( radians( [Longitude] )
       - radians(@longitude) ) + sin( radians(@latitude) ) * sin( radians( [Lattitude] ) ) ) ) 
       AS Distance  FROM [dbo].[Location]
   ) as x   
    where Distance < 5   
  order by distance