Interactive Spreadsheets for Celestial Navigation

Lines of position fix

If, for instance, both the sun and the moon are simultaneously visible in the sky it is possible to obtain your position by finding the intersection of the two lines of position (LOP) obtained from each sight. Typically there are two distinct possible positions and it should be very easy to decide which one is the correct one.

The measured sextant altitude is corrected for index error, refraction,
parallax, and semidiameter which results in the observed altitude
(Ho). The Universal Time (UT) of the sight is used to determine
the Geographical Position (GP) with the help of an almanac spreadsheet. The
declination and Greenwich Hour Angle (GHA) of the GP plus the Ho for
the two sights are entered in row 3 of the spreadsheet. The solutions
are displayed in rows 8 and 10.

The following image shows the spreadsheet ** lops.xls**.

**Summary for spreadsheet lops.xls:**

The problem preset in this spreadsheet is treated in The Celestial Navigation Mystery Solved by David Owen Bell on p. 79.

The spreadsheet *two_body_fix.xls* has the same interface as *lops.xls *and solves the same problem using a different method. Whereas *lops.xls* employs the techniques of spatial geometry (Van Allen paper), *two_body_fix.xls *applies the equations of spherical trigonometry (John Karl, Celestial Navigation in the GPS Age, pp. 78-79). The latter reference also discusses the applicability of this approach to finding the latitude without a meridian sight or knowledge of UT (the double-altitude method).

**2. Lines of position (many-body) fix**

Spreadsheet *many_body_fix.xls* implements a procedure for computing a location from multiple lines of position obtained on a moving vessel. This navigation problem typically arises from a round of observations acquired during twilight. Enter the desired UT of the fix in cell A2, vessel speed in knots in cell B2, and course (track) in cell C2. The initial best guess for the position goes into cells E2 and F2. The observational and GP data start in line 9 and are entered into columns A (name), B (UT of observation), C (observed altitude Ho), E (GHA), and G (declination). The computed coordinates are displayed in row 6. The spreadsheet performs a block of four iterations of this procedure. On output, the value *d* in cell D4 should be less than 20 nautical miles; if that is not the case, copy cells A6 and D6 into cells E2 and F2 and repeat this procedure until convergence is reached.

The following image shows the spreadsheet *many_body_fix.xls*.

**Input cells:** A2, B2, C3, E2, F2, from row 9 down columns A, B, C, E, G

**Output cells:** D4, B6, C6, E6, F6

The method and the problem preset in this spreadsheet were taken from pp. 282-283 of the Nautical Almanac, 2010 Commercial Edition.

DOWNLOAD TAR ARCHIVE FILE (13 MB)

DOWNLOAD ZIP ARCHIVE FILE (7 MB)