Sailing to the British Virgin Islands in the Caribbean 1500

How To Make A Track Map For Google Earth

Click the image to launch Google Earth and display a track map of our voyage. If you don't have Google Earth on your computer, there's an internet link at the bottom of this page to download a copy.		Click the image to launch Google Maps in your browser window and display the track map. This option is available for people who don't have Google Earth installed, however Google Maps is much slower than Google Earth when displaying lengthy tracks.

By the end of our voyage on Night Heron, we had accumulated several pages of log sheets containing our hourly position fixes. These fixes were obtained by reading latitude/longitude from a GPS, then writing the numbers by hand on a log sheet, along with the date and time. We used several GPS units, including the main Furuno Navnet chartplotter and a couple of handheld GPS units. We used the handheld units while the main chartplotter was turned off to conserve electricity.

Any of the GPS units (including the chartplotter) could have been configured to save an internal track log, which could then be downloaded to a PC for later display. There were a few problems with this approach. First of all, we didn't keep any of the GPS units powered-up continuously during the trip, so there would have been gaps in any track log. The main chartplotter was turned off for long periods of time to conserve electricity, and the handheld GPS units were only battery powered, and thus couldn't be powered-up continuously. Also, on a voyage of unknown duration, it might be tricky configuring a GPS track log to avoid overfilling the track log memory and thus losing data. That said, any of these problems could have been solved easily enough if we had wanted to keep a continuous GPS track log.

After we arrived at Tortola, I photographed the log sheets to get a copy of all the position information. Back at my computer in Maryland, I wanted to create a track map for my web site to show Night Heron's route. This required some fiddling and futzing, but I finally figured out how to make a pretty nice track map.

The basic process of making a track map has several steps:

1. Capture all the raw position information in a text file.
2. Import the text file into a spreadsheet, then create the desired display format for the position information. Save this information in a new file, in a format known as "comma-separated values" (abbreviated CSV).
3. Edit the CSV file in a word processor to strip off unneeded information.
4. Read the CSV file into another spreadsheet and add annotations required by GPS Visualizer (more on this later). Save the annotated spreadsheet in a new CSV file.
5. Go online and process the annotated CSV file using GPS Visualizer. This is a free online application that converts a CSV file into a file that can be read by Google Earth to display a track map. GPS Visualizer creates the new file on its server, which you then download to your computer as a KMZ file (Google Earth's native file type).
6. Open the KMZ file in Google Earth then debug and clean up your track map.

As usual, the devil is in the details, so I've included detailed instructions below:

1. Capture all the raw position information in a text file.
• Each position fix consists of a date, time, latitude, and longitude. Using Windows "Notepad" (or similar program), create a text file with one fix per line and a comma between each field of the fix. Use a consistent format for date (mm/dd/yy) and time (hh:mm using 24-hour format). The latitude/longitude requires a little gimmick. On the log sheet, these figures were written as "degrees plus minutes.decimal minutes" (for example, 36 29.9, meaning 36 degrees and 29.9 minutes). Ultimately, you want the values to be "degrees.decimal degrees" (for example, 36.49833 degrees). For now, enter each figure as two numbers separated by a comma (for example, 36,29.9). You'll see why in a moment.
• It pays to proofread this file carefully, even though it's very tedious. You want to make sure that your original data file is error-free, since the old computer homily applies: "Garbage in = garbage out".

Here's how the information started out. On the left is a portion of a log sheet showing handwritten position fixes, on the right is a portion of the text file that I first created.



2. Import the text file into a spreadsheet, then create the desired display format for the position information.
• The text file you just created is in a format known as "comma-separated values" (abbreviated CSV), which spreadsheets can import without difficulty. I used the excellent free spreadsheet program from OpenOffice.org called "Calc", and my instructions are for that program.
• When you open the text file in Calc, you first need to specify that the file type is "Text CSV", otherwise the program will treat it as pure text and open it in the word processor. Later, in the "Text Import" dialog box, select "Comma" as the separator, then click "OK" and the text file information is displayed in the spreadsheet.
• Insert two new columns for the final latitude and longitude values, placing them after the date and time fields. Set the cell format for both columns to be "Number". Then enter the formulas for the final values, for example, latitude is "=E1+(F1/60)" and longitude is "=-(G1+(H1/60))". Note the minus sign at the beginning of the longitude formula, since ultimately you want longitude to be a negative number (at least for this part of the world). Finally, replicate each formula throughout the rest of its column to populate all the cells with values.
• Insert a new column after the date, time, and latitude columns, and put a comma in each cell of these new columns. The commas are needed later when outputting the spreadsheet in fixed-width format.
• Modify the cell format as follows: For the date column, format cells as "Date" and "YYYY-MM-DD"; for the time column, format cells as "Time" and "HH:MM:SS"; for the latitude and longitude columns, format cells as "Number" with five decimal places; for all three comma columns, format cells as "Text".
• Modify all the cell widths so there is no extra space in any of the columns. This can be done using the "Optimum Column Width" command, adding 0.0" of extra space except for longitude which needs 0.1" of extra space.
• Save the spreadsheet in two different output formats. First, save it as an "OpenDocument Spreadsheet", which saves all the formulas and formatting information. Then, save it as a "Text CSV" file, but check the box to "Edit Filter Settings" then check both boxes "Save cell content as shown" and "Fixed column width". After this CSV output file is generated, open the file using Windows "Notepad", and if any fields display ###, add 0.1" of extra space to the column width of those columns, then resave the spreadsheet as both file types again.

The left-hand picture shows the result of importing the original text file into the spreadsheet. The right-hand picture shows what the spreadsheet should look like when it is ready to be saved in both output formats (ODS and CSV).



3. Edit the CSV file in a word processor to strip off unneeded information.
• You'll notice that the CSV file also contains the original raw latitude/longitude values that we no longer need. Unfortunately, I wasn't able to find a way to prevent those columns from being saved by the spreadsheet program, so now you use a word processor program to strip off this information. To make things easier, you should use a program that supports "regular expression find/replace". I used the excellent free word processor program from OpenOffice.org called "Writer", and my instructions are for that program.
• Before opening the CSV file, change the file name to a new name, and end the name with ".txt". You want a new name so you don't accidentally overwrite your original text file, and you must change it to ".txt" otherwise the file will be opened in spreadsheet mode. Open the file in Writer and count how many characters you want to strip off the end of each line to eliminate the original lat/long information, in my case 14 characters. Now use the "Find/Replace" command, but be sure to click on "More Options" and check the box for "Regular expressions". In my case, I entered ".{14}$" in the "Search for" box and nothing in the "Replace with" box. Make sure the cursor is at the start of the file, then click "Replace All" and the file is modified, easy as pie.
• Save the file, exit Writer, then change the file extension from ".txt" back to ".csv".

This is what the CSV file looks like. At left is the output from the spreadsheet (note the unneeded junk at the end of each line), at right is the result after trimming the junk off in a word processor.



4. Read the CSV file into another spreadsheet and add annotations required by GPS Visualizer.
• Make sure you close the previous spreadsheet, then open the new CSV file in the spreadsheet program. You have to go through the same process of setting the cell format for the desired display format, but don't worry about the column widths; also, don't create any columns with commas.
• Insert a column in front of the date field and set the cell format to "Text". For now, fill this column with the letter "T" to indicate "trackpoint".
• Insert a row at the top of the spreadsheet, and enter the following words (one per column): type, date, time, lat, long, name, descr. These words are later used by GPS Visualizer to learn what type of information is in each column.
• The basic track map will only contain a continuous series of similar-looking trackpoints. I wanted to add some different-looking information once per day, to show how far we progressed each day. To do this, I added one waypoint per day, starting with the first day and every 24 hours after that, plus I added a waypoint at the very end. I based the waypoint information on information from an existing trackpoint. I didn't always have a trackpoint at the same time every day, so I chose a trackpoint that was closest to 24 hours later.
• To make waypoints as I've described, decide where you want them in the sequence of trackpoints. Let's say you want a waypoint after the 15th trackpoint. Select the 15th row in the spreadsheet, press "Ctrl+C" to copy to the clipboard, then insert a blank row before the 15th row (the blank row becomes the new row 15). Select blank row 15, press "Ctrl+V" to paste from the clipboard, now change the first cell of row 15 from "T" to "W" to indicate "waypoint". For now, leave the rest of the row unchanged. Go through the rest of the spreadsheet and insert additional waypoints the same way.
• Annotate each waypoint you just created by filling in the "name" and "descr" (description) fields. In my case, I entered the day number plus the trackpoint time in the "name" field, for example, "Day 3, 08:00:00" (don't include the quotes). The reason I added the time is that I will later tell GPS Visualizer to display the time for each trackpoint, but the time doesn't show up for the waypoints, which are right on top of existing trackpoints and obscure the time from the trackpoints. Therefore, I manually added the time to the "name" field for each waypoint. In the "descr" field, I added descriptive information to each waypoint but left this field blank for all the trackpoints. The information in the "descr" field can contain basic HTML such as <br> to create a line break, but you shouldn't include any double quote characters (use a single quote instead).
• Once you have added all your annotations, save the spreadsheet in two output formats, ODS and CSV, but this time don't check the "Fixed column width" box when saving the CSV file.

This is what the CSV file looks like. This is a different part of the file than I usually show, in order to show the column headings required by GPS Visualizer. The "descr" field of the first waypoint extends off the right side of the image. The trailing commas on the trackpoint lines indicate blank fields for "name" and "descr".




5. Go online and process the annotated CSV file using GPS Visualizer.
• Open a browser window and go to the GPS Visualizer Google Earth page. Click on the "+" to expand the form for "Track options". Make the following changes to the default forms:
• Google Earth doc name - Make this a useful name, for example "Night Heron Track Map"
• Connect segments (in "Track options") - "yes"
• Draw as waypoints (in "Track options") - "Yes, named with timestamps"
• Default icon color (in "Waypoint options") - "red"
• Default icon (in "Waypoint options") - "small circle"
• File #1 (in "Upload your GPS data files here:") - click "Browse" and select the CSV file you just created
(remember, this is the second CSV file you created, not the first).
• Open in new window (under the "Create KML file" button) - check this box so the window with the filled-in forms is preserved
• Click "Create KML file" and wait for a new browser window to open. Right-click on the long file name, select "Save Target As...", enter a file name (keep the extension as .kmz), and download the KMZ file to your hard disk. FYI, a KMZ file contains the same information as a KML file, except the KMZ file is greatly compressed so it will download much faster. An uncompressed KML file is in plain text and can be edited with Windows "Notepad".
• Close the new window but keep the main GPS Visualizer window open until your track map is debugged and cleaned up. This way, you won't have to keep filling in the form.
6. Open the KMZ file in Google Earth then debug and clean up your track map.
• Run Google Earth on your computer, then open the KMZ file you just downloaded. Inspect the track map carefully, looking for typos or other problems. I found several problems with my initial track map:
• Position errors - Sometimes a trackpoint would be displayed way out of position, which was obviously an error. These errors can crop up in a few ways. Maybe you have a typo in the original text file that captured the position fixes from the log sheets. A number might be wrong, or a decimal point or comma might be misplaced. It's possible that the original position fix itself is wrong—maybe someone wrote down the wrong numbers. On Night Heron, the Furuno chartplotter normally positioned the cursor at the boat's current location, and you wrote down the cursor lat/long for the fix. But the chartplotter also allowed you to move the cursor all over the chart, so perhaps the cursor wasn't at the boat's location when somebody wrote down the lat/long. This happened for several of our position fixes, and I had to delete those fixes from the spreadsheet and regenerate the track map.
• Missing fix - We wrote down fixes every hour, more or less, but sometimes we forgot, so there could be missing data. This is not so important out on the ocean, but when the track map shows you navigating close to land, the missing data could cause the track map to cross over land. This looks silly and is obviously not how you sailed. In our case, this happened at the start of the trip in Chesapeake Bay and at the end of the trip in the British Virgin Islands. Fixes weren't logged often enough to accurately show how we navigated out of the bay or around the islands. The way to fix this is to create more trackpoints at the appropriate positions and insert these "synthetic fixes" in the spreadsheet at the proper place, then regenerate the track map. To figure out the lat/long of these new trackpoints, display the track map on Google Earth, then click the "Pushpin" icon to create a placemark on the map. Drag the placemark to the desired location of a new trackpoint, then cut and paste the lat/long from the placemark's "properties" dialog box to a new row that you insert in the spreadsheet. Use the date/time information from the visible trackpoints to figure out where to insert the new trackpoint in the spreadsheet. Using this technique, I was able to make very reasonable approximations of our tracks through the bay and around the islands. I set the time of these synthetic trackpoints at 00:00:00 so they would stand out and could be explained by a comment.

Links To Pages On My Web Site

• My Final Spreadsheet File - download this file to see my final spreadsheet (requires "Calc" program from OpenOffice.org). If the download process wants to change the OpenOffice file extension to .zip, change it back to .odt.
• My Final CSV File - download this text file to see the input to GPS Visualizer

Internet Links For This Section

• GPS Visualizer - main page of the web site that makes maps from GPS data
• GPS Visualizer - the page that makes maps for Google Earth
• KML Documentation - explains what a KML (or KMZ) file is, from Google
• Google Earth - all about Google Earth, plus a link to download the application
• OpenOffice.org - producers of a free open-source suite of applications, highly recommended