Attention spreadsheet geeks!
Posted: Thu Feb 27, 2014 9:33 pm
I have built a database for the Wind River Mountains, as part of eventually putting out a second edition of my guidebook. I thought spreadsheet geeks would be interested in what I did. Although building the database was tedious, now that it is done it is a snap to configure thousands of trips or reverse a trip to see if that works better. It took several weeks almost full time to put together the database.
First, I drew and labeled 950 logical connected route segments (most about 1-2 miles in length) using the TOPO software. I did the segments in geographical blocks. After drawing each segment I used the "build profile" feature to get miles, starting elevation, ending elevation, elevation gain and loss. Database columns are: segment number, start name, endpoint name, trail or x-c, difficulty rating (three trail difficulties A B C and three x-c ratings -G, PG, R) segment miles, starting elevation, ending elevation, elevation gain, elevation loss.
For travel in the opposite direction, I use a negative segment number and use formulas to adjust all data for traveling the opposite direction. I also have a column for estimated travel rate and two columns that I use to verify/check elevation gains/losses. A lot of my end point elevations are taken from a lake elevation or manually looking at the topo map. I found that what TOPO calculates on its profile is sometimes significantly wrong! So I have a formula that corrects the TOPO errors. I also find my own errors when doing the check. Sometimes I goof and put the elevation gain in the "loss" column.
The database is the first sheet. The other sheets are used for routes. Actually, I use the other sheets for book chapters with several routes on each sheet. These are set up to match the columns in the data base sheet and to match column widths that will copy into WORD that is set up in my book format. I just hide columns that I do not want to show up in the book.
So now when I want to devise a trip, all I have to do is look at my master TOPO map with segment codes and make a list of segment numbers (some positive and some negative depending on which way I want to travel). I am currently just manually linking the endpoint and then copy "as formula" the remainder of the columns of information. I also have columns where I calculate estimated travel time based on mph x miles + 1 hr/1000 feet gain.
I now have my entire route broken into small segments. I then decide base mostly on where I want to camp and total travel time, which segments I add up for each days travel. It is very easy to see if my desired day is too long and adjust it. Trip planning for the Rockies, where afternoon thunderstorms are the norm, is a bit more complicated because each pass or high altitude section has to be done in the morning hours. If any of you have my guidebook, this is how I make those travel plan tables.
If I want to see if reversing the rout makes more logistical sense, I use the sort function to reverse the segment numbers and multiply by -1 (this changes the direction). Then I manually re-link to the database. It only takes about 15 minutes to do this. Then it is just a matter of copying formulas.
You could eliminate the manual linking by using lookup table functions. I hesitate to do this because I have done this quite a bit in the past (at my real job) because lookup tables of this sort can be touchy.
I am not sure I will try to build a similar database for the Sierra. Maybe after I get the second edition of my guidebook out (a few years away) doing the same for the Sierra could be my winter's project.
First, I drew and labeled 950 logical connected route segments (most about 1-2 miles in length) using the TOPO software. I did the segments in geographical blocks. After drawing each segment I used the "build profile" feature to get miles, starting elevation, ending elevation, elevation gain and loss. Database columns are: segment number, start name, endpoint name, trail or x-c, difficulty rating (three trail difficulties A B C and three x-c ratings -G, PG, R) segment miles, starting elevation, ending elevation, elevation gain, elevation loss.
For travel in the opposite direction, I use a negative segment number and use formulas to adjust all data for traveling the opposite direction. I also have a column for estimated travel rate and two columns that I use to verify/check elevation gains/losses. A lot of my end point elevations are taken from a lake elevation or manually looking at the topo map. I found that what TOPO calculates on its profile is sometimes significantly wrong! So I have a formula that corrects the TOPO errors. I also find my own errors when doing the check. Sometimes I goof and put the elevation gain in the "loss" column.
The database is the first sheet. The other sheets are used for routes. Actually, I use the other sheets for book chapters with several routes on each sheet. These are set up to match the columns in the data base sheet and to match column widths that will copy into WORD that is set up in my book format. I just hide columns that I do not want to show up in the book.
So now when I want to devise a trip, all I have to do is look at my master TOPO map with segment codes and make a list of segment numbers (some positive and some negative depending on which way I want to travel). I am currently just manually linking the endpoint and then copy "as formula" the remainder of the columns of information. I also have columns where I calculate estimated travel time based on mph x miles + 1 hr/1000 feet gain.
I now have my entire route broken into small segments. I then decide base mostly on where I want to camp and total travel time, which segments I add up for each days travel. It is very easy to see if my desired day is too long and adjust it. Trip planning for the Rockies, where afternoon thunderstorms are the norm, is a bit more complicated because each pass or high altitude section has to be done in the morning hours. If any of you have my guidebook, this is how I make those travel plan tables.
If I want to see if reversing the rout makes more logistical sense, I use the sort function to reverse the segment numbers and multiply by -1 (this changes the direction). Then I manually re-link to the database. It only takes about 15 minutes to do this. Then it is just a matter of copying formulas.
You could eliminate the manual linking by using lookup table functions. I hesitate to do this because I have done this quite a bit in the past (at my real job) because lookup tables of this sort can be touchy.
I am not sure I will try to build a similar database for the Sierra. Maybe after I get the second edition of my guidebook out (a few years away) doing the same for the Sierra could be my winter's project.