Friday, November 22, 2019

One Route to a Route Planning Spreadsheet

Ever live by a sage piece of advice, only to go back and find that such advice was never actually given? That's how I'm feeling about the topic I'm writing about today.

In my mind's eye, I can recall learning an important lesson from DIY Endurance Athlete Matt Kirk: step one in crafting a self-designed adventure is to create a spreadsheet outlining the plan. Back in 2015, I can see evidence of Matt's advice in action with his Cross Florida Run. Here is the planning spreadsheet that made such a big impact on me. I haven't looked at this spreadsheet or his trip report in years and yet I still find both move me!

But here's the thing: I can find no evidence that his Cross Florida Run or any other adventure was planned initially in spreadsheet form. Either I can't find the post where he offered up this advice, or more likely, I just assumed it. For all I know, Matt Kirk hates spreadsheets and thinks trip planning using them is a waste.

Either way, I took his never-dispensed advice on my last big run. You can find the planning spreadsheet here. Matt's planning doc covers nearly 150 miles. In comparison, mine covers a paltry 22. I created mine mainly to field test the idea for longer adventures, and yet, I found the document to be surprisingly useful. Using it I had a solid sense of both timing and caloric needs and it let me relax and enjoy the run knowing I was prepared for the ordeal.

So while Matt Kirk never said (or said it, and I've lost the source) a planning spreadsheet is key for your next Big Adventure, I'll say it is. And I'll go further, I'll give you my algorithm for creating such a document.

Step 1. Plan a general route by dropping waypoints into a Google 'My Map'.

I have mixed feelings about Google's My Map tool. On one hand, it lets you easily notate key points on a map. On the other, I find the tools generally clunky and frustrating to use. The distance tool, for example, is always tripping me up. Regardless, I know of no better way to generally outline your plan than waypoints on a My Map.

Step 2. Export your My Map as a KML file and convert the KML file to text using GPS Visualizer.

GPS Visualizer is your map-data Swiss Army Knife; learn it and love it. Google My Maps deals with KML files, my Garmin InReach likes GPX files and Google Sheets likes delimited text. GPS Visualizer will let you convert to and from these formats with ease.

Step 3. Import the CSV file into a Google Sheet.

Importing from a Google My Map insures you're not copying and pasting lat/long values which are easily mucked up.

Step 4. Use the imported waypoints to construct a detailed set of checkpoints.

In this case I'm doing an out-and-back route so the second half of the checkpoints are a mirror of the first half.

Step 5. Add terrain, distance and calculate estimated duration per checkpoint.

I've had success estimating distance using Google's My Map measuring tool as well using Google Map's built in distance measuring tool. I find the Google Maps tool to be far easier to use than the My Maps version.

After noting the distance to the checkpoint, I'll consider the terrain I'll be encountering while getting myself to that checkpoint. Each terrain is assigned a minutes per mile pace in a lookup table:

Finally, a bit of basic spreadsheet math is used to calculate the amount of time needed to make it to the checkpoint:

  =(vlookup(D2,Pacing,2,false) * C2)/1440

The vlookup() pull the minutes per mile value, and the division by 1440 (the number of minutes in a day) converts the value to a Google Sheets time value. Once the time value is calculated, you can adjust the format to 'duration' to see a properly formatted time.

Step 6. Create a route summary including a calorie needs estimate.

The route summary consists of basic spreadsheet math. The distance and estimated time sum up the relevant columns from the Checkpoint sheet. The caloric estimate is calculated by assuming I'll eat a certain number of calories every X minutes. For a long run, I plan to consume 100 calories every 30 minutes. But of course this is a spreadsheet, so those values are easily tweaked.

Step 7. Add in calorie source details.

There's nothing special about the caloric detail. It's simply a sum of all the calories I'll bring with me on the adventure. Having this in spreadsheet form is useful because I can substitute foods the day of and know that I'm still nutritionally covered.

Using the planned nutrition data I can trivially calculate how much of a buffer of food I'm planning to bring:

Step 8. Create a GPX file for import into Backcountry Navigator and the Garmin In-Reach

Back to GPS Visualizer we go! The goal will be to convert our textual data from the Checkpoints tab to a GPX file that most navigational programs and devices understand. To simplify this, you can create a formula that generates the data GPS Visualizer expects:

="C" & text(A2,"00") & ": " & B2 & "," & F2 & "," & G2

And your done! You've now got a document that serves a number of important uses. It lets you think about a large adventure in small chunks, plan a reasonable amount of time for said adventure and insure you've got a nutrition plan in mind. You also have a document you can share with family and friends so they know your route and plan.

You can find the sample document I worked through above here.

No comments:

Post a Comment