Estadísticas a partir del track de una excursión.

He preparado este trabajo para aquellos que les gusta trastear con el GPS y que les gustaría poder hacer sus propias estadísticas de una ruta. De paso incluyo la posibilidad que tiene excel de crear nuestras propias funciones con VBasic.

• El primer paso consiste en pasar los datos de la ruta al libro excel.
• Hay muchas maneras de hacerlo, desde el copia pega desde BaseCamp (propiedades de la ruta) hasta salvar los datos a un fichero de texto (o un fichero csv) o, incluso, utilizar Ibpindex para obtener esos datos.
• A partir de estos datos puedo conocer directamente los incrementos de altura y de tiempos. Incremento = "Lo que hay" - "Lo que había"
• La distancia recorrida hay que calcularla.
• El rumbo hay que calcularlo.
• Los acumulados de tiempo, distancia y alturas son una suma.

¿Como paso los datos a mi libro excel?

Esta vez voy a utilizar la utilidad que tienen algunos editores de rutas, Oziexplorer, BaseCamp, MapSource y OruxMaps, entre otros, para volcar los datos, coordenadas, alturas y fechas a un fichero de texto (.txt o .csv).

• Oziexplorer, el editor de rutas mas antiguo que conozco, tiene la posibilidad de exportar a texto con varias, y distintas, posibilidades.

• El trabajo que he realizado lo he hecho exportando a texto (csv) un fichero GPX con BaseCamp.

• Abro directamente con excel el fichero csv.
•  Elimino todo aquello que no sea los puntos de la traza.
• Desde datos->Texto en columnas paso cada item a su columna.

• Pasados los campos a su columna copio los datos y los pego en el libro EstadísticasManuales.xls, hoja Datos.

Como mínimo, a partir de fecha, coordenadas y alturas puedo calcular:

• Distancia entre dos puntos y distancia acumulada.
• Tiempo entre dos puntos y duración total.
• Incremento de altura entre dos puntos y acumulados de subida y bajada.
• Velocidad de recorrido. Entre puntos y total.
• Tiempo en movimiento y de parada.
• Eliminar puntos que no cumplen o que cumplen unos determinados criterios. En el ejemplo tiempo mínimo entre puntos, distancia mínima entre puntos y velocidad máxima. Otro criterio que se puede incluir es el de velocidad vertical (de subida o bajada), etc..
• Para calcular tiempos y, dado que depende del tiempo, velocidades, hay que tener presente el sistema de fechas de excel. 

• Si en una celda escribimos una fecha y cambiamos el formato fecha a formato numérico vemos un número en vez de una fecha. Es el número de días trascurridos desde el 1/1/1900. Uno equivale a un día, la fracción de uno es la fracción de día. Este sistema no me acaba de gustar, es bastante imperfecto. 
• Las velocidades, en km/hora se calculan con distanciaEnMetros/(tiempo*24000). 1000 para pasar los metros a km y 24 para pasar los días, o su fracción, a horas.
• Las duraciones, sin embargo, se calculan restando sin mas  las fechas y sus acumulados sumando las duraciones.
• Los incrementos de altura son sencillamente una resta. Altura del punto2 menos la altura del punto1.
• El calculo de las distancias es lo único realmente complejo de todo esto. Hay varias posibilidades, incluida una manera aproximada de calcularla.

Distancia entre dos puntos:

Buscando en internet encontré, hace ya varios años, una fórmula que es la que utilizo desde que empecé a hacer mis propias estadísticas.

La fórmula es bastante compleja, no difícil convertirla a fórmula excel pero queda un formulón,  por lo que creo mi propia función en VBasic a partir de esa fórmula, la funcion DGeo (distancia geográfica), que es mucho mas sencilla de manejar:

Function DGeo(Lat1, Lon1, Lat2, Lon2)

Dim GaR, R, Lat1R, Lat2R, Lon1R, Lon2R

DGeo = -1

On Error Resume Next

'57.29577951 para convertir grados a radianes

'6378137 Radio de la tierra

GaR = 57.29577951

R = 6378137

Lat1R = Lat1 / GaR

Lat2R = Lat2 / GaR

Lon1R = Lon1 / GaR

Lon2R = Lon2 / GaR

'DGeo = R * Atn(Sqr((1 - (Sin(Lat1 / GaR) * Sin(Lat2 / GaR) + Cos(Lat1 / GaR) * Cos(Lat2 / GaR) * Cos(Lon2 / GaR - Lon1 / GaR)) ^ 2)) / _

(Sin(Lat1 / GaR) * Sin(Lat2 / GaR) + Cos(Lat1 / GaR) * Cos(Lat2 / GaR) * Cos(Lon2 / GaR - Lon1 / GaR)))

DGeo = R * Atn(Sqr((1 - (Sin(Lat1R) * Sin(Lat2R) + Cos(Lat1R) * Cos(Lat2R) * Cos(Lon2R - Lon1R)) ^ 2)) / _

(Sin(Lat1R) * Sin(Lat2R) + Cos(Lat1R) * Cos(Lat2R) * Cos(Lon2R - Lon1R)))

On Error GoTo 0

End Function

Esta función, mi función, la función añadida por mi, como fórmula excel queda:

=Radio* ATAN(RAIZ((1 - (SENO(D2*GaR) * SENO(D3*GaR) + COS(D2*GaR) * COS(D3*GaR) * COS(E3*GaR - E2*GaR)) ^ 2)) / (SENO(D2*GaR) * SENO(D3*GaR) + COS(D2*GaR) * COS(D3*GaR) * COS(E3*GaR - E2*GaR)))

Donde:

• Radio=radio de la tierra. Es una variable con nombre.
• De GaR sirve para convertir grados a radianes. Es una variable con nombre. Podía haber utilizado la función RADIANES().
• Columna D es la latitud. 
• Columna E es la longitud.

Entre ambas maneras de calcular distancias hay una pequeña diferencia.

Para distancias cortas he encontrado una manera aproximada de calcular la distancia entre dos puntos, columna I de la hoja "datos2". Empiricamente hay muy poca diferencia para puntos cercanos en metros y bastante diferencia para puntos muy separados. Creo que como estamos trabajando con la traza de una excursión a pie o en bici los puntos están muy cerca unos de otros podemos utilizar la siguiente fórmula:

• =RAIZ(((D2-D3)*M_G)^2+(M_G*COS(D3*GaR)*(E2-E3))^2), que tampoco se puede considerar una formulita.
• Columna D es la latitud. 
• Columna E es la longitud.
• M_G es una variable con nombre. Es el número de metros por grado en el Ecuador de la tierra.

Rumbo: El rumbo es el ángulo que forma nuestra dirección, la dirección que llevamos al movernos,  con el eje norte-sur, con una brújula graduada es fácil de ver. Es una información, también desde mi punto de vista, mas útil en náutica que sobre tierra, en tierra nuestros tramos rectos son pequeños y nuestro camino va por donde va, casi nunca podemos ni elegir el rumbo ni salirnos del camino. Hay varios casos mas en los que el ángulo entre una dirección y el eje norte-sur se puede utilizar, pero no vienen al caso.

• No he conseguido encontrar una fórmula exacta de calcular el rumbo, pero si una fórmula aproximada para puntos cercanos.
• Queda una fórmula compleja, por lo que ni siquiera intento pasarla a excel. Solo hago la fórmula en VBasic.
• En tierra solo he visto utilizar el rumbo para eliminar algunos puntos. Si tres puntos consecutivos tienen el mismo rumbo se puede eliminar el punto central sin perder demasiada información.

Código de Rumbo:

Function Rumbo(Lat1, Lon1, Lat2, Lon2)
Dim D1 As Double, D2 As Double, D3 As Double, ArcoTan As Double
'corregir casos 2 y 4
'111319.491
'Pi = 3.141592654

D1 = DGeo(Lat1, Lon1, Lat2, Lon2)
D2 = DGeo(Lat1, Lon1, Lat2, Lon1)
D2 = Abs(111319.491 * (Lat2 - Lat1))
D3 = DGeo(Lat2, Lon1, Lat2, Lon2)
D3 = Abs(111319.491 * (Lon2 - Lon1) * Cos(Lat2 * 2 * Pi() / 360))
On Error Resume Next

C = 0
ArcoTan = Atn(D3 / D2)
e = Err()
If (D2 > 0 And D3 > 0) Then
If Lat2 >= Lat1 And Lon2 <= Lon1 Then
Rumbo = (2 * Pi() - ArcoTan)

End If
If Lat2 >= Lat1 And Lon2 >= Lon1 Then
Rumbo = ArcoTan

End If
If Lat2 <= Lat1 And Lon2 <= Lon1 Then
Rumbo = Pi() + ArcoTan

End If
If Lat2 <= Lat1 And Lon2 >= Lon1 Then
Rumbo = (Pi() - ArcoTan)

End If
End If

If D2 = 0 Then
Rumbo = Pi() / 2
If Lon1 > Lon2 Then Rumbo = 1.5 * Pi()
End If

If D3 = 0 Then
Rumbo = 0
If Lat1 > Lat2 Then Rumbo = Pi()
End If

On Error GoTo 0
'f = "0." & Left("0000000000000", Dec)
'Rumbo = Format(Rumbo * 360 / (2 * Pi()), f) + 0
Rumbo = Rumbo * 360 / (2 * Pi())
End Function

Function Pi()
Pi = 3.141592654

End Function

En donde Lat1,Lon1,Lat2 y Lon2 son las coordenadas del punto de inicio y del punto de destino.