Five years of Navigation Spreadsheets

Navigation Spreadsheets logo
Navigation Spreadsheets

At the fifth anniversary of our website’s launch we review some of Navigation Spreadsheets functions.  All three examples are taken from the 2014 Nautical Almanac Commercial Edition.

1) Ephemeris (almanac data), Venus GP on 2014 May 5 at 13h 15m 18s (p. 256):

venus.xls

Input:
UT: 2014 May 5, 13:15:18

Output:
GHA = 58º 58.0’
Dec = S 0º 14.1’

venus2014

 

2) Sextant altitude corrections (Venus, p. 259)

alt_corr_xls
Input: Hs = 4º 32.6’
Output: Ho = 4º 17.6’

altcorr2014

 

3) The calculated altitude and azimuth (pp. 279-280)

intercept.xls
Input:
GP: GHA = 53º    Dec = S 15º
AP: Lat = N 32º    Long = W 16º
to which we add Ho = 30º 30.0’ in order to allow the calculation of the intercept and the plotting of the LOP.

intercept2014

 

Output:
The resulting LOP (intercept 38 NM away, azimuth 223) is plotted with the T-Plotter.

tplotter1

tplotter2

 

As it was also calculated by intercept.xls this LOP crosses:
the AP’s meridian at 52 NM north of the AP
the AP’s parallel at 56 NM east of the AP

tplotter3

 

(first published on February 15, 2014)

Two-body fix (Santa Barbara, 16 July 2011)

A recent trip to Santa Barbara, California, presented me with an opportunity to do some sights and calculations. In the following example I took a series of Sun sights in the morning and a single sight in the afternoon.  The four morning sights were averaged to produce a single effective data point, whose LOP was then crossed with the LOP from the afternoon sight to obtain a fix.

Observation point:
Google Earth coordinates: Santa Barbara Sailing Club beach
N    34º 24.18′    i.e.    34.403
º
W 119º 41.64′    i.e. -119.694
º

These coordinates were used as the “assumed position” (AP) in the subsequent calculations of intercepts and azimuths.

Sun semidiameter (SD) = 15.7′

Sextant: Davis Mark 15

16 July 2011 (Sun: morning):  T=25 ºC,  P=1011 mb,  Index Correction=+8.0′,  Height of eye=6 ft
UT               Hs               Ho               GHA            Declination   Intercept    Azimuth
17:42:30      55° 48.2′     56° 08.9′      84° 06.4′      N 21° 19.9′       0.4A         103.3
17:45:20      56° 23.4′     56° 44.1′      84° 48.9′      N 21° 19.8′       0.8T         103.9
17:47:50      56° 51.6′     57° 12.3′      85° 26.4′      N 21° 19.8′       1.0A         104.4
17:50:30      57° 22.4′     57° 43.1′      86° 06.4′      N 21° 19.8′       2.1A         105.0

The spreadsheet average2.xls results in a simple average of these four observed altitudes:

average2.xls:
average2sb

that is:
UT               Hs               Ho               GHA            Declination   Intercept    Azimuth
17:46:32       —               56° 57.1′      85° 06.9′      N 21° 19.8′       0.6A         104.1

The single afternoon sight was (this time the sextant’s mirrors were adjusted to eliminate index error):
16 July 2011 (Sun: afternoon):  T=26 ºC,  P=1010 mb,  Index Correction=0.0′,  Height of eye=6 ft
UT               Hs               Ho               GHA            Declination   Intercept    Azimuth
21:18:20      69° 00.6′     69° 13.6′     138° 03.7′     N 21° 18.4′       1.6T          235.8

The two LOP intersections can be computed either with spreadsheet lops.xls or two_body_fix.xls.

two_body_fix.xls:
twobodyfixsb

Solution #1 is relevant in our case:

N    34º 22.8′
W 119º 42.8′

This fix is only 1.7 nm bearing 215 from the Google Earth coordinates, as seen both from:

sailings.xls:
sailingssb

and a Google Earth measurement:
erroroffix

Overall I think I can be reasonably happy with these results and the intercepts I got. Considering the difficulties I had with the index error determination I was in fact a bit worried before I started the calculations. The error of fix and the standard deviation of intercepts are interestingly similar at about 2 nm. Using this value as the “Scatter” parameter in the weighted least-squares fitting procedure (average2.xls: fitted, not precomputed slope), all weights came out equal, so this procedure resulted in calculating the simple average of UT’s and Ho’s.

(first published on September 1, 2011)

Greenwich Hour Angle of stars

A “sight” in celestial navigation consists of measuring the body’s altitude with a sextant and marking the time of that observation using a chronometer.  The celestial “line of position” (LOP), along which the observer is located, is a circle whose radius (called “zenith distance”) is deduced from the sextant altitude measurement.  The instant of the observation, expressed in Universal Time (UT), specifies the body’s “geographical position” (GP) which is the center of this LOP.  A crossing of this LOP with at least one other such LOP results in a celestial fix on the observer’s position.

The GP is a set of two numbers: the declination (counterpart to latitude), and the Greenwich Hour Angle (GHA) which is analogous to longitude.  The declination is an angle ranging from -90º (South Pole), through 0º (Equator) to +90º (North Pole).  The GHA increases westward starting from 0º at the Prime (Greenwich) Meridian and ending at 360º upon reaching the Prime Meridian again after one full round-trip around the Earth.  The GP coordinates for the main navigation bodies are precomputed and published in almanacs for future use.

Stars differ from the bodies of the Solar System in that they have almost negligible proper motion relative to the Earth.  As a result, their declinations are nearly constant and their GHA evolution with UT is almost entirely due to Earth’s rotation alone.  In other words, the stars are practically “fixed” to their very nearly constant positions on the celestial sphere.  This allows almanac publishers to save a lot of space using the following scheme.

Instead of tabulating the GHA of each navigation star in some increments of UT (like it is done for Sun, Moon, and planets), this value is computed by adding two numbers:

GHA_Star = GHA_Aries + SHA_Star

“Aries” (i.e. the point of vernal equinox) represents the chosen “Prime Meridian” on the celestial sphere and SHA (Sidereal Hour Angle) is the star’s constant westward position relative to Aries.  Since SHA does not change with UT, almanacs can be made much more compact by tabulating each star’s SHA just once and placing all UT dependence into the single column of GHA_Aries.  This arrangement is also used in our aries_stars.xls spreadsheet, as shown in the example below (all numbers are in degrees decimal):

For UT of January 1, 2012, 12h 00m 00s, we have GHA_Aries (280.56).

To get GHA_Acamar we add its SHA (315.31) to GHA_Aries (280.56), resulting in 595.87.  From this value we subtract 360.00 to bring the GHA to its conventional range and obtain GHA_Acamar (235.87).

To get GHA_Achernar we add its SHA (335.45) to the same GHA_Aries as above (280.56), resulting in 616.01.  From this value we subtract 360.00 to bring the GHA to its conventional range and obtain GHA_Achernar (256.01).

ariesstarsxls2012

 

(first published on June 18, 2011)

Lunar occultation of Aldebaran

The Wikipedia entry for the star Aldebaran contains the following image:

http://en.wikipedia.org/wiki/File:Occultation.jpg

Based on the information on this page (e.g. image was created in July 1997) and after some trial and error with Excel (see screenshots below) I came up with the following plausible coordinates in time and space at which this image may have been created:

New Orleans area:   N 30º W 90º
UT: July 29, 1997,    10h 08m 30s

This really is only one out of many possible solutions, which I did not investigate further.  I neglected refraction which would have a small effect for such a tiny lunar distance (center-to-center topocentric LD = Moon SD = 15.5′) and the overall achievable accuracy in this exercise (no obviously visible refractional flattening of Moon’s disk).  Parallax is important (center-to-center geocentric LD = 34.4′)

Accompanying data look consistent with everything else:
The Moon age (25 days, “waning crescent”) and phase (23% or about 1/4 illuminated)
Local time (UT-6h) => around 4am, about an hour before sunrise (“predawn”)

The two bodies would have appeared due east at an altitude of roughly 34 degrees.


moon.xls:

moonocc


aries_stars.xls:

aldebaranocc


ld_prec.xls:

ldocc


intercept.xls:

interceptocc

 

(first published on May 22, 2011)

Composite sailing

If the computed great-circle route were to reach into higher than desired latitudes, it is possible to eliminate that problematic section of the voyage with a composite sailing calculation.  Such a path consists of three sections:

1) great-circle route from Departure to the chosen Limiting parallel,
2) parallel sailing along the Limiting parallel,
3) great-circle route from the Limiting parallel to Destination.

The two great circles from parts 1) and 3) are chosen in a way that places both of their vertices on the Limiting parallel.

The example below uses the same San Francisco-Yokohama trip from our earlier SAILINGS blog entry.  Here the Limiting parallel is set at N 40º, below the original great-circle vertex latitude of N 48º 03.7′.  The spreadsheet is composite.xls.

compositexls

 

(first published on February 2, 2011)

Sailings

The term “celestial navigation” (sometimes called “astronavigation”) typically evokes in our minds an image of a sextant and comes along with terms like Universal Time, line of position, nautical almanac, intercept, etc. Indeed, those are among the topics discussed in our spreadsheet project so far:

http://www.navigation-spreadsheets.com/

With these concepts, techniques, and gadgets you can establish your position from astronomical observations.  You can also keep track of your changing position during a trip, for example by bringing the method of dead reckoning into consideration.  Until now, however, an important aspect of navigation (which is relevant whether you use celestial or not) has not been addressed by our suite: trip planning, also known as sailings calculations.

We are pleased to announce that we now provide this additional capability in the latest extension to our suite.  Determining the direction (course) in which to sail (and knowing in advance the length of the journey) is an essential skill that any navigator must have.  For short trips one may directly measure the constant rhumb-line course on a Mercator chart for the path that connects the point of departure with the destination.  However, the bigger the separation between departure and destination, the more extra distance is associated with the rhumb-line path compared to the shorter great-circle path, especially in higher latitudes.  The problem is that attempting to follow the requisite great circle is very difficult since it requires a continuous adjustment of heading.

Thus, on the one hand, the great-circle route (orthodrome) is shortest but it is difficult to steer.  On the other hand, the rhumb-line route (loxodrome) can be well followed along its constant course but it is longer.  Each choice thus has a strength that is a weakness in the other one.  A solution to this dilemma is outlined, for example, in Bowditch which recommends a hybrid path combining the advantages of the two sailing possibilities.  Here the starting point in developing the sailing plan is the shorter great-circle route from departure to destination, but then along that path one identifies waypoints (separated, for example, by 5 degrees of longitude) between which the vessel is to follow easier-to-steer rhumb lines.

We illustrate such a calculation using as an example a trip from San Francisco (USA) to Yokohama (Japan) borrowed from Bowditch.  This classic publication demonstrates the idea graphically using the chart of the North Pacific Ocean shown in two different projections.

First, the great-circle path from San Francisco to Yokohama is found as the straight line connecting the two cities on the gnomonic projection chart.  We mark the waypoints as this path crosses meridians separated by 5 degrees of longitude.  Second, these waypoints are translated onto the Mercator projection chart on which they are connected by straight-line segments representing rhumb lines.  The constant course headings within each successive pair of waypoints can be directly measured on this chart.  Our new spreadsheets perform this exact same function (plus the distance calculations) with even higher accuracy, because they are not affected by the inaccuracies of physical plotting on a chart.

The problem to solve is fully specified by the coordinates of the departure and destination locations.  We have:

San Francisco:
Lat: N 37º 48.0′
Lon: W 122º 33.0′

Yokohama:
Lat: N 34º 42.0′
Lon: E 140º 06.0′

These coordinates enter the spreadsheet sailings.xls in row 2.

sailingsxls

The use of this spreadsheet is shown in this YouTube demo video.

The differences between the calculated great-circle and rhumb-line paths are substantial.  The rhumb line is longer by over 200 miles and its (constant) heading is south of west, while the initial great-circle course is north of west sailing into higher latitudes first.  Row 11 displays the coordinates of the vertex, which is the point along the great circle closest to the Pole.  The spreadsheet also shows the (relatively minor) differences arising from the use of a perfectly spherical or slightly flattened ellipsoidal model of the Earth surface in the calculation.

For our purposes the main result of this spreadsheet is the initial great-circle course displayed in the yellow cell C6.  This number, combined with the departure coordinates, completely defines the great circle.  The value is shown with three decimal digits (302.240) in order to cut down numerical round-off errors once it is copied as input into the next spreadsheet: waypoints.xls.

waypoints1

Here, the coordinates of San Francisco and this initial course from sailings.xls enter in row 2.  Then, in column A starting in row 11 we begin entering the longitudes of each waypoint.  All great circles (except those running across both Poles) intersect every meridian exactly once.  The calculated latitude of each waypoint is displayed in columns C, D, E.  The rhumb-line distance and course from the previous waypoint (using the flattened Earth model) is shown in columns F and G.

The initial course is north of west so the latitudes of the subsequent waypoints increase at first.  The courses between them, however, progressively turn away from north and at longitude W 170º (close to the vertex, row 7) it is essentially due west.  The course then heads ever more southward as the path descends back to lower latitudes toward Yokohama.  Figure 2404 in Bowditch shows the E 150º waypoint at latitude N 40º, for which waypoints.xls calculates N 39º 45.5′.  The last waypoint is the destination itself (Lon: E 140º 06.0′) with the correctly reproduced latitude (N 34º 42.0′).  This hybrid path is still longer (cell F2) than the pure great-circle route, but not by much, and it is easier to steer.

(first published on January 15, 2011)

Sight reduction of a Moon observation

The data for the following example are from John Karl’s “Celestial navigation in the GPS age” (First Edition, 2007), pp. 63-65.  With three spreadsheets we reproduce the computations for the sight reduction of the Moon observation presented in Figure 6.4 on p. 64.  Given the dead-reckoning (DR) position and the recorded time of observation (UT), the Moon sextant altitude (lower-limb) is reduced to intercept distance and azimuth needed to plot the associated line of position (LOP) according to the intercept method of Marcq St. Hilaire.

Input data:
Body
: Moon, lower limb
Date: 11 May 2005
UT: 02h 19m 14s
Hs: 25º 21.6′
Sextant index correction: -3.3′
Height of eye: 9 feet
assuming standard atmospheric conditions
DR position: S 34º 13′, E 161º 43′


moon.xls:

Input:
Date: 11 May 2005
UT: 02h 19m 14s
In cells A2-F2 entered: 2005 5 11 2 19 14

Output:
GP (row 5)
GHA: 182º 25.4′ (same as 181º 85.4′ in the book)
Dec: N 27º 42.9′ (difference of only 0.1′)

Semidiameter (SD, cell A8): 15.0′
Horizontal parallax (HP, cell C8): 55.1′

Note that there is no need for increments and corrections (v or d Corrn).

moon1


alt_corr.xls:

Input:
Height of eye (cells E1, F1): 9 ft
Using standard conditions for temperature and pressure:
Pressure (cell E2): 1010 mb
Temperature (cells E3, F3): 10 degrees Celsius
HP (in arcminutes from moon.xls cell C8): 55.1 entered in cell E6

Sextant altitude (Hs): 25º 21.6′, entered as 25 216/600 in cell B1
Index correction: -3.3′, entered as – 33/600 in cell B2
Artificial horizon was not used: entered N in cell B4
SD (in degrees from moon.xls cell A8): entered (+)15/60 (positive for lower limb observations) in cell B11

Output:
Observed altitude (Ho) in cells B12-B14: 26º 18.2′ (difference of only 0.1′)

Note the intermediate result for apparent altitude (Ha) in cells B6-B8: 25º 15.4′ (same as in the book)

altcorr1


intercept.xls

Input:
Assumed position (AP) is taken to be the dead-reckoning (DR) position:
Latitude: S 34º 13′, entered as -34 13/60 in cell A2
Longitude: E 161º 43′, entered as (+) 161 43/60 in cell B2

GP of Moon from row 5 of moon.xls:
GHA: 182 254/600 entered in cell C2
Dec: (+) 27 429/600 entered in cell D2

Ho from alt_corr.xls cells B13, B14: 26 182/600 entered in cell E2

Intermediate results:
LHA (cells F2, F3): 344º 08.4′ (same as in the book)
Computed altitude (Hc in cells A6-C6): 26º 16.4′ (difference of only 0.1′)

Main results:
Intercept (cells D6, E6): 1.8 Toward (difference of only 0.2′)
Azimuth (cell F6): 15.7 agrees with 16 from the book after rounding

intercept1


worksheet.pdf:

The data from this example are summarized in the worksheet below; input data are marked as green, and spreadsheet results (some of which are relayed as input for subsequent calculations) are blue.

worksheet1

(first published on August 17, 2010)