>>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 >>
eg >UPDATE test SET XAxis = (cos(((PI())/180)*Latitude)*cos(((PI())/180)*Longitude)) >UPDATE test SET XAxis = (cos(Latitude*PI()/180)*cos(Longitude*PI()/180))I don't know how sql server handles a combination of float and int but maybe use 180.0 instead of 180
create cursor Coordinates ; ( co_pk I, ; co_city c(40), ; co_latitude b(16), ; co_longitude b(16), ; co_x b(16), ; co_y b(16), ; co_z b(16) ; ) insert into Coordinates ; ( co_pk, co_city, co_latitude, co_longitude) ; values ; (1, 'Here', 47.7795, -65.7191 ) insert into Coordinates ; ( co_pk, co_city, co_latitude, co_longitude) ; values ; (2, 'There', 47.5993, -65.6506 ) update Coordinates ; set co_x = cos(co_latitude*PI()/180) * cos(co_longitude*PI()/180), ; co_y = cos(co_latitude*PI()/180) * sin(co_longitude*PI()/180), ; co_z = sin(co_latitude*PI()/180) select T1.co_pk, ; T1.co_city, ; T2.co_pk, ; T2.co_city, ; 6378.137 * acos( T1.co_x * T2.co_x + T1.co_y * T2.co_y + T1.co_z * T2.co_z) as Distance2 ; from Coordinates T1 ; join Coordinates T2 on (T1.co_pk < T2.co_pk) ; into cursor tmpCoordinates table + query result attached