Exercise 3: Using EXCEL to process navigation data

Exercise 4: Using EXCEL to process navigation data

We will do the following in this exercise:

1. Aquire navigation data from another computer system using FTP or NETSCAPE . The data reside in an anonymous Ftp account, ie accessible to the public.

The address with the nav data is:

ftp.u.washington.edu/pub/user-supported/blewis

2. Compute the distance between each navigation fix and then compute the total distance travelled.

3. Plot the track chart.

Mathematical considerations.

The equation for the distance between 2 points on a spherical earth can be derived as follows.

Let R be the radius of the sphere, A1 be the latitude of point 1 and B1 be the longitude of point 1.

Let R be the radius of the sphere, A2 be the latitude of point 2 and B2 be the longitude of point 2

Then the vector describing point 1 will be V1 = (R, A1, B1)

and the vector describing point 2 will be V2 = (R, A2, B2)

The distance along the surface of the sphere between points 1 and 2 will then be R*C where C is the angle between 1 and 2 and cos(C) is given by the dot product between V1/R and V2/R.

To be numerically useful we need to transform the latitude and longitude into an orthogonal coordinate system x,y,z. In this system

x1=R*cos(A1)*cos(B1)

y1=R*cos(A1)*sin(B1)

z1=R*sin(A1)

and

x2=R*cos(A2)*cos(B2)

y2=R*cos(A2)*sin(B2)

z2=R*sin(A2)

Now V1 = (x1,y1,z1)

and V2 = (x2,y2,z2)

and V1/R dot V2/R =(x1*x2+y1*y2+z1*z2)/R**2=cos(C).

Expanding and simplifying we get.

Cos(C)= (cos(A1)*cos(B1)*cos(A2)*cos(B2)+cos(A1)*sin(B1)*cos(A2)*sin(B2)+sin(A1)*sin(A2))

= M (say)

Then C = acos(M)

and the distance we want is R*C

---------------------------------------------------------------------------------------------------

Practical considerations:

1. We will need to have the lat and longs expressed in fractions and not in deg and minutes.

2. We need to make sure we are dealing with the correct units (ie radians or degrees, km or miles)

3. Most of the calculations can be done with the Visual Basic component of EXCEL.

In Visual Basic we can use functions or subroutines (called subs). Functions are used to do calculations and return a result. Subs are used to manipulate EXCEL objects, such as cells, formats.

For more info on Visual Basic go to the item called visual basic on the Table of Contents for this course.

5. In EXCEL 97 Visual Basic is invoked by using the Tools --> Macro----> Visual Basic

--------------------------------------------------------------------------------------------------------------

A function to do the navigation calculation is as follows:

Function deltnm(lat1, long1, lat2, long2)

' This function calculates the distance between two points on the surface of the earth.

‘ Comments begin with apostrophe

' convert degrees to radians

50 the = 0.01745329252 * lat1

ale = 0.01745329252 * long1

ths = 0.01745329252 * lat2

als = 0.01745329252 * long2

c = Sin(the)

ak = -Cos(the)

d = Sin(ale)

e = -Cos(ale)

a = ak * e

b = -ak * d

g = -c * e

h = c * d

cp = Sin(ths)

akp = -Cos(ths)

dp = Sin(als)

ep = -Cos(als)

ap = akp * ep

bp = -akp * dp

gp = -cp * ep

hp = cp * dp

c1 = a * ap + b * bp + c * cp

29 delt = acos(c1)

‘ convert to km

deltkm = 6371 * delt

‘ convert to nautical miles

deltnm = deltkm * 3281 / 6076

Exit Function

End Function

Function asin(x)

asin = Atn(x / Sqr(-x * x + 1))

End Function

Function acos(x)

acos = Atn(-x / Sqr(-x * x + 1)) + 1.5708

End Function

Function atan2(x, y)

atan2 = Atn(x / y)

End Function

----------------------------------------------------------------------------------

The procedure immediately above is a function. It returns a value but does not change the spreadsheet environment. A procedure called a Sub does not return a value but can change the environment.

An example of a Sub is given below. This Sub, called "changesign" is designed to change the sign of a cell if the adjacent left cell is negative.

-----------------------------------------------------------------------------------------------------

Sub changesign()

' Declare variables

Dim RowNum As Integer, colnum As Integer, currcell As Range, nextcol As Integer, temp As Single

RowNum = ActiveCell.Row ' intialize row number

colnum = ActiveCell.Column ' intitialize col number

nextcol = colnum + 1

Set currcell = ActiveSheet.Cells(RowNum, colnum) ' go to first cell

Set nextcell = ActiveSheet.Cells(RowNum, nextcol) ' get adjacent cell

Do While currcell.Value <> "" ' do while cell not empty

If IsNumeric(currcell.Value) Then ' is cell a number

temp = nextcell.Value

If currcell.Value < 0 Then

nextcell.Value = -1 * temp

End If

RowNum = RowNum + 1

Set currcell = ActiveSheet.Cells(RowNum, colnum)

Set nextcell = ActiveSheet.Cells(RowNum, nextcol) ' get adjacent cell

Loop

End Sub

--------------------------------------------------------------------------------------------------------

With this you should have enough info to do the exercise.