>>100 cities become 100 * 100 / 2 records - 100 = 5,000 records - 100 = 4,900 records
>>
>>ie
>>The distance from New York to Dallas is the same as the distance from Dallas to New York
>
>Correct, thanks for the clarification.
>
>I still do not understand one part at the http://msdn.microsoft.com/en-us/library/ms980211.aspx page. They have a section in Formatting the Data about how they calculate the XAxis, YAxis and ZAxis. Then, a few lines below, the come up with a different formula. I am not sure which one I should take. Even by having tried them both, I end up with two different results in my query, and both results are not good.


The formulae are the same
eg

XAxis = cos(radians(Latitude)) * cos(radians(Longitude))

Radians—degrees * (PI/180)
PI—4*((4*atn(1/5))-(atn(1/239)))

&& let's consider
cos(radians(Latitude))

&& convert to radians
cos( (Latitude  * (PI/180) ))

&& subsitute PI
cos( (Latitude  * (4*((4*atn(1/5))-(atn(1/239)))/180) ))

I have the distance between New York and Brussels

(1) 5880.640510371473 km from here http://www.chemical-ecology.net/java/lat-long.htm

(2) The link of John : 5884.32 km

(3) using the link you gave: 5891 km

(2) and (3) are in fact the same formula

In the code below
Distance = (2)
Distance2 = (3)


so basically, each location can store its x, y and z

&& 6378.137 is the radius of the earth in km

*___________________________________________________________________________
function do_it()

	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, 'New York', dtor(40.0 + 45/60 + 6/3600), -dtor(73.0 + 59/60 + 39/3600) )
	
	insert into Coordinates ;
		( co_pk, co_city, co_latitude, co_longitude) ;
		values ;
		(2, 'Brussels', dtor(50.00 + 50/60 + 0/3600), dtor(4.0 + 20/60 + 0/3600) )
		
	
	update Coordinates ;
		set	co_x	= cos(co_latitude) * cos(co_longitude), ;
			co_y	= cos(co_latitude) * sin(co_longitude), ;
			co_z	= sin(co_latitude)
	
	
	select	T1.co_pk, ;
			T1.co_city, ;
			T2.co_pk, ;
			T2.co_city, ;
			Distance(T1.co_latitude, T1.co_longitude, T2.co_latitude, T2.co_longitude) as Distance, ;
			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
	

	&& =brow()
	
	
endfunc
*___________________________________________________________________________
function Distance(lat1, lon1, lat2, lon2)

	local theta, dist
	
	theta = m.lon1 - m.lon2
	
	dist = sin(m.lat1) * sin(m.lat2) + cos(m.lat1) * cos(m.lat2) * cos(m.theta)
	
	dist = acos(m.dist)
	dist = rtod(m.dist)
	
	dist = m.dist * 60 * 1.1515 * 1.609344
	
	return m.dist

endfunc
*___________________________________________________________________________