Forgot to record half a bike ride. Thoughts on my solution?

NorthCascades
NorthCascades Posts: 10,968 Member
in Fitness and Exercise
Last night I got sent to the grocery store, and went by bike. There was some chaos outside the store when I left, and I wound up forgetting to record the ride home on my Garmin. I use the Gear Tracking feature to know when to do all kinds of maintenance, and also like having the number of miles and feet of elevation gain in my reports, and an odometer reading for my bike, so I want to correct this as best I can. Luckily, I took the same route home.

Here's what I did:

* Export the first half of my ride as a TCX file
* Use SQL Server to reverse the order of the points in the route & add 15 minutes
* Save the result as a GPX
* Upload manually to Garmin Connect

It works. Unfortunately it only works if the file I create has a time stamp on each point. So it looks like I flew up the hills and crawled down. Also, I'm throwing HR and power away because those values would be completely different on the way home.

Is this the best I can do, or is there a better way?

Replies

  • NorthCascades
    NorthCascades Posts: 10,968 Member
    I'm trying to accomplish knowing how many miles I have on all of the parts that wear and need maintenance, like I said in the OP.
  • webtracer
    webtracer Posts: 142 Member
    That's probably the best you can do, unless you duplicate the ride on a different day, then change the dates and upload that overwriting the ride you messed up
  • jessicapk
    jessicapk Posts: 574 Member
    Sounds like you did exactly what you intended to accomplish on the mileage. Make notes on that ride that the second half is just a reversal so you know in case you go back looking over performance data. Otherwise, the mileage should be accurate enough for maintenance purposes.
  • queenliz99
    queenliz99 Posts: 15,317 Member
    You must be a numbers geek!! High five!!
  • minizebu
    minizebu Posts: 2,716 Member
    How much biking do you do on average?

    In the grand scheme of things, how much of a difference will this one half-bike-ride make? If you are a once daily rider, we are talking about something that comprises less than 0.2% of your annual mileage, right?

    (1/365 = 0.00273972602 This is just absolute number of rides. We aren't even talking about actual mileage.)

    If you ride even more often than that, then we are talking about even less of a hit.

    I understand your desire for precision. I like to be precise as well. However, sometimes you've got to let go of the trees and focus on the forest.
  • CincyNeid
    CincyNeid Posts: 1,249 Member
    I could get on Endomondo, Create and log the route on Endomondo then export the .gpx to your pc. Then upload it to your ecosystem of choice.

    I know Garmin, Strava, and Ride With GPS will allow you to create route. But not log them.... Endo will allow you to create then log out that route.
  • NorthCascades
    NorthCascades Posts: 10,968 Member
    This is the query I used, if anybody else ever finds the need for it. Took me about an hour to come up with this.
    Declare @tcx xml
Set @tcx = N'
... ride data export goes here ...
'

Create Table #Track (Ele decimal(15, 10), Lat decimal(15, 10), Lon decimal(15, 10), Time DateTime, ID int identity not null)

Insert #Track (Ele, Lat, Lon, Time)
Select
	r.value('*:AltitudeMeters[1]', 'decimal(15, 10)') AltitudeMeters,
	r.query('*:Position').query('//*:LatitudeDegrees[1]').value('*:LatitudeDegrees[1]', 'decimal(15, 10)') LatitudeDegrees,
	r.query('*:Position').query('//*:LongitudeDegrees[1]').value('*:LongitudeDegrees[1]', 'decimal(15, 10)') LongitudeDegrees,
	DateAdd(minute, 15, r.value('*:Time[1]', 'dateTime')) Time
From
	@tcx.nodes('//*:Trackpoint') Trackpoint(r)

Declare @m int, @startTime nvarchar(max), @xml nvarchar(max)
Select @m = Max(ID) From #Track
Set @startTime = '2016-08-26T03:05:00.000Z'

Select @xml = (
	Select
		lon '@lon',
		lat '@lat',
		ele,
		DateAdd(second, -ID, @startTime) time
	From
		#Track
	Order By
		ID Desc
	For Xml Path('trkpt'), Root('trkseg')
)

Select Cast('<gpx creator="Garmin Connect" version="1.1"
  xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/11.xsd"
  xmlns="http://www.topografix.com/GPX/1/1"
  xmlns:ns3="http://www.garmin.com/xmlschemas/TrackPointExtension/v1"
  xmlns:ns2="http://www.garmin.com/xmlschemas/GpxExtensions/v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<metadata>
		<link href="connect.garmin.com">
			<text>Garmin Connect</text>
		</link>
		<time>' + @startTime + '</time>
	</metadata>
	<trk>
		<name>Seattle Cycling</name>
		<type>cycling</type>' + @xml + '  </trk>
</gpx>
' as xml)

Drop Table #Track
  • LazSommer
    LazSommer Posts: 1,851 Member
    Very cool nerdpoints but remembering to add an extra 10 miles to your wear and tear totals seems a bit easier. like a post it note.
  • rybo
    rybo Posts: 5,424 Member
    I bet you schedule meetings for late Friday afternoon & ask questions after presentations when everyone else just wants to leave.
  • niblue
    niblue Posts: 339 Member
    I'm a numbers geek as well but even I wouldn't bother about the odd 10 miles here or there!
  • Hornsby
    Hornsby Posts: 10,322 Member
    My thought would be to forget about it. 10 miles means nothing really.

    Since you have already done the work though, I would just go with what you did and fugggetabout.
  • NorthCascades
    NorthCascades Posts: 10,968 Member
    rybo wrote: »
    I bet you schedule meetings for late Friday afternoon & ask questions after presentations when everyone else just wants to leave.

    Wow, that was hostile for no reason. Having a bad day?

    For what it's worth I'm the one trying to sneak out early on Friday. What made you think a person who cares about their own hobbies is the same kind of person who has no hobbies and wants to punish everyone else?

    I think this is one of those times you should have thought a little bit before you clicked "post reply."
  • rybo
    rybo Posts: 5,424 Member
    NorthCascades wrote: »
    rybo wrote: »
    I bet you schedule meetings for late Friday afternoon & ask questions after presentations when everyone else just wants to leave.

    Wow, that was hostile for no reason. Having a bad day?

    For what it's worth I'm the one trying to sneak out early on Friday. What made you think a person who cares about their own hobbies is the same kind of person who has no hobbies and wants to punish everyone else?

    I think this is one of those times you should have thought a little bit before you clicked "post reply."

    Was a total joke man. I figured it was so over the top that no one would think I was serious. My apologies.
  • rankinsect
    rankinsect Posts: 2,238 Member
    SQL? Pfft, get back to me when you can do that entire thing in one regex statement :wink:
  • daweasel
    daweasel Posts: 68 Member
    Where are you logging? Can't you just add a manual activity? That's what I do, especially if you know the distance.

Categories