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.

Cambios de base. De decimal a binario y de decimal a hexadecimal.

Estoy trabajando, en mi blog de cacharreo, con un reproductor de mp3, el DFPlayer Mini y un Arduino Uno. El aparato se puede programar directamente, según la documentación del fabricante, o se puede programar siguiendo alguna de las librerías preparadas al efecto. La muy escasa documentación del fabricante sobre este aparato hace referencia a unos comandos escritos directamente en hexadecimal, aunque en la práctica ese hexadecimal se convierte en una base de ocho bits, los ocho bits de un carácter ascii.

Como apoyo a lo que estoy haciendo, por un lado hablar el mismo idioma (hexadecimal) del pdf del fabricante y por otro trabajar, de hecho, con una base de 8 bits, he preparado un libro excel que me permite estar seguro, antes de entrar en la programación del Arduino, de lo que estoy haciendo.

• La primera conversión, líneas 6 y 7 es la de decimal a binario. En este caso no la utilizo para nada, pero es la base de la electrónica digital.
• Hago una conversión de decimal a hexadecimal en las líneas 21 y 22.
• Hago una conversión de decimal a una base byte, una de dos caracteres u una de cuatro caracteres.
• Por último hago una conversión de decimal a decimal, como comprobación de la idea.
• Normalmente, en nuestro sistema de numeración, el número de menor peso es el de más a derecha y el de mas peso es el de mas a la izquierda.
• Independientemente de la base de numeración, cada una de las unidades  de una cifra vale el número de valores que toma la base de numeración elevado a un exponente, cero el de mas a derecha, uno el siguiente, dos, tres y así sucesivamente.
• Esto es fácil de seguir, partiendo de cero, sumando sucesivamente uno, en binario pasamos de cero a uno y de uno a 10. En decimal pasamos de 9 a 10. En hexadecimal pasamos de 0F a a 10.
• Para pasar de una cifra a otra debemos incrementar n (número base, contando el cero) veces la cifra de la derecha.
• Si continuamos el razonamiento, para llegar a la tercera cifra hemos tenido que incrementar n^2 veces el cero inicial. En decimal 100, tres cifras, es 99 +1, es 10^2 cada unidad de esa tercera cifra vale 10^2. Lo mismo para 1000, cuatro cifras, 999+1, cada unidad de esa cifra vale 10^3.
• Este razonamiento es válido para el resto de las posibles bases de numeración que se nos ocurra.
• En la conversión de decimal a otra base hay que introducir el concepto resto. Vamos a calcular la cifra N (empezando en cero). Debemos quitar el valor de las cifras de mas a la izquierda, a partir de N+1, esto se hace hallando el resto de dividir el valor decimal por la base elevada a N+1.
• El entero de dividir ese resto por la base elevada a N nos da el valor de esa cifra.
• Repitiendo ese proceso hallamos todas las cifras de la conversión.

Esto, quizás parezca un poco lioso, pero el la práctica resulta sencillo.