Vorige week heb ik een fietstocht georganiseerd voor een groep collega’s; mooi weer, wat heuvels, prachtige omgeving, gezellige mensen: wat wil een mens nog meer?
Nou, wat minder discussie over het aantal gefietste kilometers en de geklommen hoogtemeters zou prettig zijn!
Zeker nu steeds meer fietsers gebruik maken van een GPS (al dan niet op de smartphone) en de kilometers op Strava worden gezet, eindigt een fietstocht vaak in een langdurige twist over de exacte cijfers van de geleverde prestatie.
Trouwens niet alleen bij het fietsen gebeurt dit; ik ken een fanatieke groep wandelaars waar na een tocht flink wordt overlegd welk aantal kilometers aan het thuisfront kan worden gemeld (het hoogste natuurlijk!).
GPX
Om te onderzoeken waar de verschillen door worden veroorzaakt, kwam ik er al snel achter, dat dit inherent is aan het gebruik van een GPS. De verbinding met de satellieten is niet altijd optimaal, waardoor verstoringen kunnen optreden. Zeker wanneer het gaat over het meten van de hoogte, laat menige GPS nog wel eens een steek vallen. Of ineens is de ontvangst beter en geeft de GPS een stijging (of daling) aan terwijl je stil staat!
Om de resultaten beter te kunnen vergelijken heb ik de tocht, die ik op Strava had gezet, geëxporteerd naar een GPX-bestand. Dit type bestand wordt bij navigatie-software meestal als standaard voor uitwisseling gebruikt. Soms is een dergelijk bestand direct van de GPS of smartphone te downloaden; anders kun je het omzetten met behulp van een programma als bijvoorbeeld Basecamp.
Nader onderzoek (met het alom gewaardeerde hulpmiddel Kladblok) leverde op, dat het eigenlijk een XML-bestand is.
XML
XML begint de Haarlemmermeerolie voor bestandsuitwisseling te worden. Deze standaard wordt door steeds meer pakketten ondersteund, waaronder zeker ook al jaren door Excel!
Waarom ik als titel voor deze blog “GPX? XML! dus Excel” heb gebruikt mag nu duidelijk zijn.
GPX importeren in Excel
Het voert te ver om hier alles rond XML uit de doeken te doen, dus ik geef een verkorte handleiding hoe de GPX-file in te lezen in Excel:
- Open Excel, zorg dat er geen bestand geopend is
- download een GPX-bestand (van Strava, GPS, internet)
- sleep het GPX-bestand naar het Excel-symbool op de werkbalk, Excel klapt dan open, sleep het GPX-bestand nu in Excel (de cursor krijgt dan een plusje) en laat de muis-button los
- Excel geeft een foutmelding, maar die negeren we gewoon, dus op J
a klikken - in het volgende dialoogscherm kies je de eerste optie (Als een XML-tabel)
- 2 keer waarschuwingen negeren door OK te klikken en daar is onze route!
Excel maakt er direct een zogenaamde tabel van; in latere verwijzingen zie je de naam Tabel2 terugkomen. Binnenkort toch eens wat vertellen over de (nieuwe) mogelijkheden van tabellen!
Niet alle kolommen zijn voor ons interessant, maar E en F leveren de coördinaten (lat=latitude/breedtegraad, lon=longitude/lengtegraad), G de hoogte (ele=elevation), H een samengepakte vorm van de dag en het bijbehorende tijdstip en I de temperatuur op dat moment.
GPX-analyse
We gaan dit bestandje nu verder analyseren: download het Voorbeeldbestand.
In het eerste werkblad staan de gegevens van Strava. Ik heb een tweede werkblad aangemaakt (Berek), waarin we diverse berekende kolommen zien:
- in kolom A ‘knippen’ we het tijdstip
los van de datum door een DEEL er van te pakken. Aangezien dit dan een tekst is, maken we er via TIJDWAARDE een echte tijd van. - in B3 staat de formule
=AFRONDEN(6370973,27862*((2*BOOGSIN(WORTEL((SIN((RADIALEN(Strava!E2)-RADIALEN(Strava!E3))/2)^2)+COS(RADIALEN(Strava!E2))*COS(RADIALEN(Strava!E3))*(SIN((RADIALEN(Strava!F2)-RADIALEN(Strava!F3))/2)^2)))));0)
Het voert te ver om deze door te spitten, maar uit de coördinaten van het tijdstip uit regel 3 en die van de vorige regel, wordt de afstand daartussen bepaald (in meters). - in C gaan we die meters gecumuleerd volgen (als kilometers)
- D bevat de verstreken tijd tussen twee metingen en E de gecumuleerde tijd
- en in F staat de snelheid: B gedeeld door D.
Wel even opletten: we willen de snelheid in km/uur, dus de getallen in B moeten gedeeld worden door 1.000; aangezien in Excel een tijd intern als fractie van een dag wordt vastgelegd (dus 6 uur wordt in Excel 0,25 etc) vermenigvuldigen we de getallen in D nog met 24 om er uren van te maken.
Bestudering van de resultaten verklaart al wat van de vreemde snelheidsgrafieken in Strava en andere software: tussen veel metingen zitten soms maar enkele seconden en slechts luttele meters (toch eens harder gaan fietsen!). Aangezien we hier dan met relatief grote afrondingen zitten, kan het resultaat (de snelheid) ook behoorlijk variëren.
Om dit effect te verkleinen heb ik nog wat kolommen toegevoegd, waarin ik de snelheid niet tussen 2 opeenvolgende metingen bepaal, maar de intervallen vergroot. Hierdoor wordt het afrondingsprobleem voor een gedeelte uitgemiddeld.
Google Maps
Om makkelijk te kunnen analyseren waarom de snelheid op sommige stukken zo hoog (of laag) is heb ik in kolom K nog een hyperlink naar Google-maps toegevoegd:
=HYPERLINK(“http://maps.google.nl/maps?q=”&SUBSTITUEREN(Tabel2[@lat];”,”;”.”)&”,”&SUBSTITUEREN(Tabel2[@lon];”,”;”.”))
Meestal zoek je in Google-maps via een plaatsnaam en/of adres, maar je kunt ook coördinaten opgeven.
Aangezien Google een punt als decimaal scheidingsteken verwacht (de 2 coördinaten worden door een komma gescheiden), voeren we op de lat en lon een substitutie uit. Het resultaat kan er dan als volgend uitzien:
http://maps.google.nl/maps?q=50.867841,5.99487
Grafieken
Omdat zoveel detail-informatie zoals weergegeven in het werkblad Berek zich uitstekend leent om grafisch weer te geven, heb ik een werkblad Grafieken toegevoegd.
De bovenste grafiek komt uit Strava; in de tweede is duidelijk te zien, dat de berekende snelheid (met een interval van 4 metingen) heel goed aansluit bij Strava!
In de derde grafiek zijn de hoogte en de snelheid niet tegen de afstand maar tegen de tijd uitgezet. 
Dan valt direct een vreemd stuk op in de grafiek. Bij de afstand-grafiek liep de lijn door; bij de tijd zit een onderbreking??
Hier is goed te zien hoe slecht de (mijn?) GPS met hoogtes omgaat: na de pauze waren we echt niet gestegen (alleen koffie gedronken!).
LET OP: bij het maken van dergelijke grafieken (waar de gegevens voor de x-as niet gelijkmatig zijn verdeeld; de ene keer 1 sec, dan weer 5 sec), kun je geen gewone Lijn-grafiek nemen; kies dan Spreiding als grafiektype.