Old 03-06-09 | 04:09 PM
  #2  
khearn
Senior Member
 
Joined: Nov 2008
Posts: 187
Likes: 0
From: San Carlos, CA

Bikes: '83 Miyata Two-Ten, '84 Raleigh Pestige, '09 Downtube 8H

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.
khearn is offline  
Reply