Advertise on Bikeforums.net



User Tag List

Results 1 to 6 of 6
  1. #1
    Senior Member
    Join Date
    Mar 2008
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    best way to get elevation/gradient chart?

    I've used mapmyride, but that doesn't have a color zone for anything over 10%.

    Is there a way to make my own chart in Excel? I can get a chart that shows the altitude profile, but I want to incorporate the gradient zones.

    I'm using a Mac, and Ascent Training software.

    The data is from my Garmin 705.

  2. #2
    Senior Member
    Join Date
    Nov 2008
    Location
    San Carlos, CA
    My Bikes
    '83 Miyata Two-Ten, '84 Raleigh Pestige, '09 Downtube 8H
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've done it, but there's a lot of manual fiddling involved, and it's not for the faint hearted, or those who don't have decent text editor (vi/vim or emacs) and excel skills.

    You can save a route from mapmyride as in a couple of different formats that are xml and contain sets of latitude, longitude and altitude (in meters). I took one of those and edited it with a lot of global search/replaces to turn it into just one line per location, with latitude, longitude, altitude with commas between each item. A good text editor like vi/vim or emacs is your friend here, Word or Wordpad would probably suck at this kind of search/replace using regular expressions. Using vim it only took me a few minutes, but I've been using it daily for 20 years.

    Then I imported that into excel. One problem I had is that some latitude/longitude sets didn't have altitudes with then, so I had to interpolate missing values between the surrounding values. The missing altitude points were in an download from mapmyride. I've also done it with data from bikely, and it had all of the altitude values, but some were clearly way off, with occasional values dozens of meters higher than their neighbors. I think I'd rather interpolate missing values than try and deal with spikes.

    Next, I found a webpage with this formula for getting the distance between lat/long pairs in excel:
    =RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(D71)-RADIANS(D72))/2)^2)+COS(RADIANS(D71))*COS(RADIANS(D72))*(SIN((RADIANS(E71)-RADIANS(E72))/2)^2)))))

    See the web page for explaination of what RadiusEarth needs to be and what values are in the referenced cells (D71, D72, E71 & E72).

    So now you can have a column that shows the distance between each point, and then you create a column that adds them up as it goes down, so you have a value for each point that is how far it is from the start. You can also calculate the %slope for each segment since you know the altitudes at the ends and the length of the segment. I added a column to convert the altitude to feet, since I'm used to thinking of altitude in feet, but if you were raised in a country that uses a reasonable measuring system, you can skip that.

    Now use the distance from start column and the altitude column as the data for an X/Y chart. I'm not sure it's possible to have the chart's colors be set depending on the slope of the segment, though. Hmmm, you could create a columns that have the end altitude only for segments with a range of slopes, say one for 0-4%, one for 4-8%, etc (something like "=if((G7>4)AND(G7<=8),G7,0)" would work, assuming the altitude is in column G). Then have multiple different colored lines on your chart, one for each of those columns. Hmmm, you'd have to just have it plot points, nit the lines between the points, I'm not sure how good it would look. It would only have colored points on the profile, not colored areas under the line like mapmyride has, but at least you'd be able to choose your own slope ranges.

    I think you might be able to get it to work with line segments, but it would require some trying stuff out.

    I did it because I wanted to be able to make the plot the size I wanted with distance ticks every 5 miles, instead of whatever maymyride or bikely decided to give me, so I didn't need to worry about different colors for different slopes. Once you have a column with slope values, you may even decide you don't care as much about showing them on the plot.

    Anyways, it's possible, but non-trivial. Good Luck. Hopefully somebody will post an easier solution.
    Keith Hearn

  3. #3
    urban biker
    Join Date
    Feb 2006
    Location
    Twin Cities, MN
    My Bikes
    Haro MTB for commuting and a LHT for everything else.
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Keith, that's some good work, thanks for posting.

    I've done something similar in the past using downloaded elevation data from mapmyride and using a simple Rise/Run = Slope to determine grade. Keep in mind that many maps, including mapmyride, don't take into consideration bridges and elevated roads, so be wary if you suddenly see large changes that could be attributed to dropping you in a lake and back up again.

  4. #4
    Senior Member
    Join Date
    Nov 2008
    Location
    San Carlos, CA
    My Bikes
    '83 Miyata Two-Ten, '84 Raleigh Pestige, '09 Downtube 8H
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't see that on the route I was mapping, but I don't think it has any bridges. I saw a number if lat/long pairs from mapmyride that had no elevations, and a number of anomalous points from bikely. Like one point at 571m, the next was .08 miles away and at 430m (-33% slope), the next was .022 miles and at 505m (66% slope), the next was .036 miles away and at 571m (35% slope), and the next was .05 miles and 430m (-53% slope). That could be a bridge over a 139m deep valley, but that sounds pretty deep.

    I just checked, and 9.5% of my segments have slopes greater that 20% or less than -20%. Hmmm, just looking at the >20% segments, they make up 2.16 miles. I hope that's way off, because I'm doing my first 200km brevet tomorrow on that course! Particularly bothersome is the .04 mile segment at 98% slope. I don't think I could walk my bike up that.
    Keith Hearn

  5. #5
    urban biker
    Join Date
    Feb 2006
    Location
    Twin Cities, MN
    My Bikes
    Haro MTB for commuting and a LHT for everything else.
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you're safe tomorrow

    Been playing with mapmyride some more and found that it does display large grades of 20% or more, which is good. However, the noted grade is not accurate. I did a small route over a local bridge which has almost no change in altitude as you cross it. However, when I do a route over the bridge, it drops me down 100, then back up again.

    Good luck on your first 200K tomorrow!

  6. #6
    Senior Member
    Join Date
    Nov 2008
    Location
    San Carlos, CA
    My Bikes
    '83 Miyata Two-Ten, '84 Raleigh Pestige, '09 Downtube 8H
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems to be showing the Jesus route across the river.
    Keith Hearn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •