>SET XAxis = cos([TABLENAME].Latitude) * cos([TABLENAME].Longitude) > > >likewise >SET YAxis = cos([TABLENAME].Latitude) * sin([TABLENAME].Longitude) >SET ZAxis = sin([TABLENAME].Latitude) > >Thanks
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))Then, applying the related code would give the image attached:
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 ASCThe code works, but the distance calculated is enormous. There is only about 12 to 16 km between those two coordinates.