>>SET XAxis = cos([TABLENAME].Latitude) * cos([TABLENAME].Longitude) >> >> >>likewise >>SET YAxis = cos([TABLENAME].Latitude) * sin([TABLENAME].Longitude) >>SET ZAxis = sin([TABLENAME].Latitude) >> >>>
>UPDATE test SET XAxis = (cos(((PI())/180)*Latitude)*cos(((PI())/180)*Longitude)) >UPDATE test SET XAxis = (cos(((PI())/180)*Latitude)*sin(((PI())/180)*Longitude)) >UPDATE test SET ZAxis = (sin(((PI())/180)*Latitude)) >>
>declare @CenterLat float=47.7795 >declare @CenterLon float=-65.7191 >declare @EarthRadius float=6371 >declare @SearchDistance float=10000 > >declare @CntXAxis float >declare @CntYAxis float >declare @CntZAxis float > >set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon)) >set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon)) >set @CntZAxis = sin(radians(@CenterLat)) > >select *, ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + ZAxis*@CntZAxis) >from test >where @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + ZAxis*@CntZAxis) <= @SearchDistance >order by ProxDistance ASC >>
DECLARE @g1 geography, @g2 geography; SET @g1 = geography::STGeomFromText('POINT(-65.7142 47.7661)', 4326); SET @g2 = geography::STGeomFromText('POINT(-65.7305 47.7975)', 4326); select @g1.STDistance(@g2);I get result as 3698.76 meters which looked right to me.