Calculating hiking verticals using spreadsheets

If you've been searching for the best source of information and stimulating discussion related to Spring/Summer/Fall backpacking, hiking and camping in the Sierra Nevada...look no further!
User avatar
SSSdave
Topix Addict
Posts: 3523
Joined: Thu Nov 17, 2005 11:18 pm
Experience: N/A
Location: Silicon Valley
Contact:

Calculating hiking verticals using spreadsheets

Post by SSSdave »

I added a small information feature to my website this afternoon presenting basics for using a spreadsheet for calculating route verticals from USGS topographic maps for purposes of hiking and backpacking. For many on this board that are already familiar with spreadsheets and topographic maps this may be a yawn as the math is very basic. However there are many others out there that are not so and with information like this can open a few eyes to what is possible with only modest work. At the page bottom is a jpg screen capture of what my trip spreadsheets actually look like.

At top right on my home page"

http://www.davidsenesac.com" onclick="window.open(this.href);return false;

Select:

Tips, Tools, Information Page ...

Then select:

Calculating route verticals from USGS topographic Maps

or simply go to this link:

http://www.davidsenesac.com/Information ... icals.html" onclick="window.open(this.href);return false;

Beyond the above, I know some of the rest of you also use spreadsheets for outdoor stuff and probably the obvious is for making gear checklists. But like the above there may be other purposes? Maybe can talk about that or do a screen capture and post a jpg of what your use looks like.
User avatar
rlown
Topix Docent
Posts: 8225
Joined: Thu Oct 25, 2007 5:00 pm
Experience: Level 4 Explorer
Location: Wilton, CA

Re: Calculating hiking verticals using spreadsheets

Post by rlown »

Or..

You could buy sw (like garmin mapsource; i think they updated it since but i'll buy the new version soon) that allows you to plot your path, and shows you a profile. (sorry, much of my work is with spreadsheets, and they actually suck large) YMMV.
Capture.JPG
It's a nice spreadsheet if you like to spend hours transferring numbers from a map to a program. I'd rather trace my route and then let the program show me the result.

In the end it doesn't really matter. If you want to go somewhere, you're gonna go.

russ
You do not have the required permissions to view the files attached to this post.
User avatar
sparky
Topix Fanatic
Posts: 1029
Joined: Tue Jul 20, 2010 10:01 am
Experience: Level 4 Explorer

Re: Calculating hiking verticals using spreadsheets

Post by sparky »

I am not so computer oriented, as I do the same thing with pen & paper. Writing things down like that really imprints information in my brain. Typing into a computer doesnt have the same effect.

Same with gear lists, it is all done on paper. Now if you asked me to find any of those papers, that would be another adventure! I just simply re-do them when I am actually executing a pre planned route.
User avatar
SSSdave
Topix Addict
Posts: 3523
Joined: Thu Nov 17, 2005 11:18 pm
Experience: N/A
Location: Silicon Valley
Contact:

Re: Calculating hiking verticals using spreadsheets

Post by SSSdave »

Noted example from my site sized for standard 8.5x11 sheet.

Image
User avatar
TehipiteTom
Founding Member
Posts: 953
Joined: Thu Aug 24, 2006 8:42 am
Experience: Level 4 Explorer

Re: Calculating hiking verticals using spreadsheets

Post by TehipiteTom »

rlown wrote:Or..

You could buy sw (like garmin mapsource; i think they updated it since but i'll buy the new version soon) that allows you to plot your path, and shows you a profile.
Yup. I have Topo! for the state of California, and I use it for printing all my maps as well as for calculating elevation gain.

I do remember a time (20 years ago or so) when I had to do it all on a spreadsheet. Not going back.
User avatar
Wandering Daisy
Topix Docent
Posts: 6640
Joined: Sun Jan 24, 2010 8:19 pm
Experience: N/A
Location: Fair Oaks CA (Sacramento area)
Contact:

Re: Calculating hiking verticals using spreadsheets

Post by Wandering Daisy »

I find that TOPO overestimates elevation gains and underestimates miles. This is not a fault of the program, but the way we "draw" the route line. At least, my hand is not steady enough to exactly follow the trail line. All the wiggles I draw show up as elevation gains and losses! Switchbacks are also hard to draw exactly. That is where I tend to come up short on miles. In an area of really tight contour lines, I use TOPO, but also count contours on the paper map.

I always distinguish between "map miles" and route miles. I estimate 2 miles per "map mile" as my hiking rate on trails - realizing that the miles I get off TOPO are usually short. The map does not show all the small twists and turns of the actual trail.

I am also really "old school" with metric. I hate metric! I love that TOPO does the conversion for me. Intellectually I know the English-metric conversion, but gut feel is all English! When I see contour lines of a certain density, my mind "sees" the 40-foot contour interval. I always FEEL that I am working real hard to gain elevation on a metric map.

As for spreadsheets, I love spreadsheets! All my trip plans are on spreadsheets. I can either input the elevation gain I get off TOPO or input actual elevations. I also calculate travel time and pay more attention to daily travel time than miles. I record travel on trips so after several years I now have my travel rates over various terrain estimated quite accurately. (It also verifies that I am slowing down with old age! Ugh!)

Anyway, thanks for showing us the spreadsheet. I always like to see what others are doing with spreadsheets used for trip planning.
User avatar
SSSdave
Topix Addict
Posts: 3523
Joined: Thu Nov 17, 2005 11:18 pm
Experience: N/A
Location: Silicon Valley
Contact:

Re: Calculating hiking verticals using spreadsheets

Post by SSSdave »

The original TOPO! CD resident products had an elevation and mileage plotting tool but I found it to be quite inaccurate so contacted their software engineering group and conversed via email a bit. The issues at that time may have since been improved but there are some basic issues that actually takes more intelligence than capable with a simple algorithm.

At that time they were using one of the USGS digital elevation model products which is metric based with elevation contour lines less accurate than the typical 40 foot per line on 7.5 degree maps. The algorithm probably added/subtracted elevation whenever a route (say a trail) crossed from one side of a line to the other. However that can be prone to error if for example a trail is generally traversing along a contour line for some distance without going up or down more than a few feet. The tool might see it crossing that contour line slightly ten times racking up considerable vertical while the actual trail having been designed by an intelligent hiking oriented person that tried to engineer the trail efficiently might actually go up and down little.

One issue with estimating mileage from topographic map dashed trail lines is the result of simply measuring the dash paths can never be any more accurate than the accuracy of what the USGS had drawn onto their maps. Trail markings on maps have often been simply some estimate and generally don't show small scale changes in direction. Even trails that could have been drawn by analyzing satellite photos are often grossly inaccuate. Here is a thread I created on summitpost.org correcting the lower Symmes Creek switchbacks that Bob Burd then verified from a stored GPS track.

http://www.summitpost.org/phpBB3/viewtopic.php?t=64395" onclick="window.open(this.href);return false;

Another trail in the Sierra that is very inaccurate and an example of how little the map designers deal with trails is out of Mineral King between Groundhog Meadow and the Crystal Lake junction even though the same inaccurate dashed lines have been used on a few generations of both USGS and SEKI NP and SNF maps. My own manual estimation of trail mileage where I've never hiked requires consideration of the gradient, geology, landscape features. I've found it closely matches mileages generated by the Wilderness Press classic Sierra North and Sierra South books where they probably used a similar process.

WD >>>"I am also really "old school" with metric. I hate metric! I love that TOPO does the conversion for me."

Yeah easily converted with a spreadsheet too. Per my link above, I put elevations sequentially in rows of a column that in the example was in column A. Suppose one had a metric elevation of 3160 meters in cell A1 and other trail change elevations at A2, A3 etc. What one can do at an unused column on a spreadsheet say at column Q, is create a temporary formula in the adjacent cell R1:
=3.2808*Q1

Then copy paste all the column A values into column Q. Drag cell R1 down through other R column rows. Then simply copy paste the R column results as text back into column A.
User avatar
longri
Topix Fanatic
Posts: 1082
Joined: Mon Aug 13, 2012 9:13 am
Experience: N/A

Re: Calculating hiking verticals using spreadsheets

Post by longri »

Wandering Daisy wrote:I find that TOPO overestimates elevation gains and underestimates miles. This is not a fault of the program, but the way we "draw" the route line.
That's true. But recently I discovered that TOPO will generate a different profile depending on the display resolution. Try it. Draw a route and generate a profile. Now zoom in or zoom out or magnify the display and generate the profile again. You'll get a different total gain/loss. That's the fault of the program.

Wandering Daisy wrote:I am also really "old school" with metric. I hate metric! I love that TOPO does the conversion for me.
But unfortunately some of their maps are in metric. So when you print out a map in TOPO it could be in feet, meters or a combination of the two.
User avatar
TehipiteTom
Founding Member
Posts: 953
Joined: Thu Aug 24, 2006 8:42 am
Experience: Level 4 Explorer

Re: Calculating hiking verticals using spreadsheets

Post by TehipiteTom »

Wandering Daisy wrote:I find that TOPO overestimates elevation gains and underestimates miles. This is not a fault of the program, but the way we "draw" the route line. At least, my hand is not steady enough to exactly follow the trail line. All the wiggles I draw show up as elevation gains and losses! Switchbacks are also hard to draw exactly. That is where I tend to come up short on miles. In an area of really tight contour lines, I use TOPO, but also count contours on the paper map.
It's more accurate than the original release, but yes, there is some over on elevation and under on mileage. I adjust my estimate accordingly, and it's not really a problem.
I am also really "old school" with metric. I hate metric!
Me too. On my Kearsarge Pass trip I got very good at mentally multiplying elevation by 3.28 so it would be in terms I understand. ;)
As for spreadsheets, I love spreadsheets!
I spend too much time with spreadsheets at work to really love them, but yeah, they can be very useful. When I was leading large groups and had to plan and shop for all the meals, Excel was my best friend. ;)
User avatar
longri
Topix Fanatic
Posts: 1082
Joined: Mon Aug 13, 2012 9:13 am
Experience: N/A

Re: Calculating hiking verticals using spreadsheets

Post by longri »

TehipiteTom wrote:I adjust my estimate accordingly, and it's not really a problem.
How? Do you just add 15% to the distance and subtract 10% from the gain or something like that?
Post Reply

Who is online

Users browsing this forum: Bing [Bot], bulaklakan and 112 guests