Calculating hiking verticals using spreadsheets | High Sierra Topix  

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

Calculating hiking verticals using spreadsheets

Postby SSSdave » Sat Aug 17, 2013 6:27 pm

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

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

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
SSSdave
Topix Fanatic
 
Posts: 1965
Joined: Thu Nov 17, 2005 11:18 pm
Location: Silicon Valley
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby rlown » Sat Aug 17, 2013 7:07 pm

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
User avatar
rlown
Topix Junkie
 
Posts: 5352
Joined: Thu Oct 25, 2007 5:00 pm
Location: Petaluma and Wilton, CA
Experience: Level 4 Explorer

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby sparky » Sun Aug 18, 2013 9:54 pm

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.
There is a million ways to be human, all are worthwhile.

True happiness is the absence of striving for happiness.
-Chuang Tzu.
User avatar
sparky
Topix Expert
 
Posts: 880
Joined: Tue Jul 20, 2010 10:01 am
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby SSSdave » Mon Aug 19, 2013 8:21 am

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

Image
User avatar
SSSdave
Topix Fanatic
 
Posts: 1965
Joined: Thu Nov 17, 2005 11:18 pm
Location: Silicon Valley
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby TehipiteTom » Mon Aug 19, 2013 9:08 am

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
TehipiteTom
Founding Member
 
Posts: 814
Joined: Thu Aug 24, 2006 8:42 am
Experience: Level 4 Explorer

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby Wandering Daisy » Tue Aug 20, 2013 8:33 am

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
Wandering Daisy
Topix Junkie
 
Posts: 2610
Joined: Sun Jan 24, 2010 8:19 pm
Location: Fair Oaks CA (Sacramento area)
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby SSSdave » Tue Aug 20, 2013 9:26 am

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

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
SSSdave
Topix Fanatic
 
Posts: 1965
Joined: Thu Nov 17, 2005 11:18 pm
Location: Silicon Valley
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby longri » Tue Aug 20, 2013 10:47 am

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
longri
Topix Expert
 
Posts: 505
Joined: Mon Aug 13, 2012 9:13 am
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby TehipiteTom » Tue Aug 20, 2013 11:00 am

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
TehipiteTom
Founding Member
 
Posts: 814
Joined: Thu Aug 24, 2006 8:42 am
Experience: Level 4 Explorer

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby longri » Tue Aug 20, 2013 11:40 am

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?
User avatar
longri
Topix Expert
 
Posts: 505
Joined: Mon Aug 13, 2012 9:13 am
Experience: N/A

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby TehipiteTom » Tue Aug 20, 2013 1:13 pm

longri wrote:
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?

More or less. It's certainly close enough for my purposes.
User avatar
TehipiteTom
Founding Member
 
Posts: 814
Joined: Thu Aug 24, 2006 8:42 am
Experience: Level 4 Explorer

User avatar

Re: Calculating hiking verticals using spreadsheets

Postby Jimr » Tue Aug 20, 2013 4:16 pm

I used to hand plot these in the '80's, now I don't even bother. And I'm an excel guru. Still don't bother.
What?!
User avatar
Jimr
Topix Fanatic
 
Posts: 1141
Joined: Fri Jul 31, 2009 2:14 pm
Location: Redondo Beach
Experience: Level 4 Explorer


Return to Backpacking / Hiking / Camping



Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 13 guests