>>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