SQL Server uses the type with the higher precedence
http://msdn.microsoft.com/en-us/library/ms190309.aspx. In case of integer and decimal it will be decimal. In case of 2 integers you may get interesting results (so called "integer math problem").
>>>I think you can replace 4*((4*atn(1/5))-(atn(1/239))) by PI()
>>>
>>>And if you store the Latitude and Longitude in radians, then it becomes
>>>
>>>SET XAxis = cos([TABLENAME].Latitude) * cos([TABLENAME].Longitude)
>>>
>>>
>>>likewise
>>>SET YAxis = cos([TABLENAME].Latitude) * sin([TABLENAME].Longitude)
>>>SET ZAxis = sin([TABLENAME].Latitude)
>>>
>>>
>>
>>Thanks
>>
>>I just want to verify that part. My code to replace those values are as follow:
>>
>>
>>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 ASC
>>
>>
>>The code works, but the distance calculated is enormous. There is only about 12 to 16 km between those two coordinates.
>
>
>It is always best to multiply first and to divide last
>
>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
>
>I can only give some foxpro code
>
> 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 tmp
>
>
>Coordinates table + query result attached
If it's not broken, fix it until it is.
My Blog