Introdução
Há algum tempo, numa prova de trail, um amigo deixou-me bem para trás e deixei de o ver, mas mais à frente voltei a apanhá-lo. Fiquei com curiosidade para saber que distância ele me tinha ganho e que eu tinha conseguido recuperar.
Uma vista de olhos rápida pelas ferramentas e sites que conhecia não me permitiu chegar a nenhuma conclusão e achei que era uma boa altura para testar as funções geográficas do SQL Server para tentar responder a essa pergunta.
Não queria nada muito sofisticado e no final o gráfico abaixo permitiu-me ter uma ideia da resposta que queria:
Na zona que aparenta ter a maior distância entre nós, temos:
Portanto, cheguei a estar com quase 700 metros de atraso em relação a ele.
Este é o resultado final, como lá chegamos vem a seguir.
Importar e ler o GPX
Primeiro, temos de carregar o GPX dentro SQL. O GPX é um ficheiro XML e o SQL tem funções e tipos específicos para tratar dados XML.
Começamos por criar uma tabela para albergar os dados. Algo simples como:
CREATE TABLE [dbo].[tbTrailsPorAtleta2]( [id] [int] IDENTITY(1,1) NOT NULL, [gpx] [xml] NOT NULL, [atleta] [nvarchar](50) NOT NULL, [prova] [nvarchar](50) NOT NULL)
Com a tabela criada é só carregar os dados:
INSERT INTO [dbo].[tbTrailsPorAtleta] ([gpx], [atleta], [prova]) SELECT x.*,'Cláudio Tereso','Torres Novas Night Trail' FROM OPENROWSET( BULK 'C:\DADOS\Torres_Novas_Night_Trail_Claudio.gpx',SINGLE_BLOB) as x
Depois de carregado o GPX temos de o ler. O GPX é na sua forma mais simples composto por um elemento trkseg que por sua vez é composto por elementos trkpt, um para registo gravado normalmente de X em X segundos conforme a configuração do dispositivo.
Cada elemento tem dois atributos que indicam a latitude e a longitude além de dois elementos que também precisamos: a elevação e a hora de registo. A elevação é importante para ajustar a distância.ir das coordenadas A para as coordenadas B não é a mesma distância se ambos estiverem à mesma altitude ou altitudes diferentes. Para cada registo a diferença será de centímetros, mas ao fim de milhares de registos teremos algumas centenas de metros.
Para extrair os dados do XML, usamos o seguinte código, que além de nos dar os nós, indica a que prova e atleta correspondem:
select atleta,prova,
nos.value('@lat','nvarchar(max)') AS latitude,nos.value('@lon','nvarchar(max)') AS longitude ,
nos.value('*:time[1]','datetime') AS hora, nos.value('*:ele[1]','float') AS altitude
from tbTrailsPorAtleta dados CROSS APPLY dados.gpx.nodes('/*:gpx/*:trk/*:trkseg/*:trkpt') A(nos)
Agrupar e Ordenar
Para obter os resultados que pretendemos temos de saber a distância percorrida entre dois pontos consecutivos para cada atleta na respectiva prova, por isso vamos precisar de ordenar e agrupar os registos o que podemos fazer com a função over partition sobre os dados previamente obtidos:
select row_number() over(partition by atleta,prova order by hora) as num, * from
(select atleta,prova,
nos.value('@lat','nvarchar(max)') AS latitude,nos.value('@lon','nvarchar(max)') AS longitude ,
nos.value('*:time[1]','datetime') AS hora,
nos.value('*:ele[1]','float') AS altitude
from tbTrailsPorAtleta dados CROSS APPLY dados.gpx.nodes('/*:gpx/*:trk/*:trkseg/*:trkpt') A(nos)
) dados
Já temos os dados ordenados e agrupados, agora passamos para o passo seguinte: comparar dois registos consecutivos do mesmo atleta/prova e obter a distância percorria entre eles, não sem antes arrumar a casa.
Primeiro criamos uma view para o código que já temos:
CREATE view [dbo].[vwDados]
As
….
Segundo, quando tentei efectuar o passo seguinte directamente nesta view, foram necessários mais de 10 minutos para obter os resultados para os 4 percursos que tinha carregado. Passei os dados para uma tabela e o tempo diminuiu para 3 segundos. Na primeira versão os dados estão numa coluna XML e para cada cálculo o SQL é obrigado tem de obter os campos necessários procurando dentro do XML, quando se passa para uma tabela, cada valor tem a sua própria coluna.
Por isso para resolver o problema, copiamos os resultados da nossa view para uma tabela:
DROP TABLE IF EXISTS tblDados
GO
SELECT [num],[atleta],[prova],[latitude],[longitude],[hora],[altitude]
INTO tblDados FROM [dbo].[vwDados]
GO
Distâncias e Tempo
Estamos prontos para obter a informação que queremos. A distância e o tempo decorridos entre o momento X e o momento X+1 para todos os momentos da prova.
Como temos todos os registos ordenados e numerados só precisamos de combinar os dados do registo com o número X com o registo com o número X+1:
SELECT antes.num,antes.atleta,antes.prova,antes.hora,agora.num,agora.atleta,agora.prova,agora.hora FROM [dbo].[tblDados] antes inner join [dbo].[tblDados] agora on antes.num=agora.num-1 and antes.prova=agora.prova and antes.atleta=agora.atleta
Agora podemos fazer contas. Para obtermos o tempo decorrido entre um momento e o seguinte:
datediff(second,antes.hora,agora.hora)
E, finalmente!, podemos usar as funções geométricas para calcular a distância entre os dois pontos:
GEOGRAPHY::Point(antes.latitude, antes.longitude,4326).STDistance(GEOGRAPHY::Point(agora.latitude, agora.longitude, 4326))
Vamos por partes, Geography é um data type e para usarmos métodos de um data type usamos :: . Geography é usado para pontos no globo terrestre e existe Geometry para projeções planas (os terraplanistas devem usar sempre o Geometry …)
Usamos o método Point para obter um objecto do tipo ponto, mas para isso temos de indicar o SRID (Spatial Reference System Identifier). Para coordenadas GPS, o SRID é 4326.
Os objecto do tipo Point tem o método STDistance que nos dá a distância em metros em os dois pontos. Aplicando ao nosso exemplo:
SELECT antes.num,antes.atleta,antes.prova,antes.hora,agora.num,
datepart(second,agora.hora-antes.hora) tempo,
GEOGRAPHY::Point(antes.latitude, antes.longitude, 4326).STDistance(GEOGRAPHY::Point(agora.latitude, agora.longitude, 4326)) distancia
FROM [dbo].[tblDados] antes
inner join [dbo].[tblDados] agora
on antes.num=agora.num-1 and antes.prova=agora.prova and antes.atleta=agora.atleta
Falta-nos a diferença de altitude. Vamos considerar que a distância horizontal e a distância vertical são os catetos do triangulo rectângulo da distância percorrida que é a hipotenusa, e usando o teorema de Pitágoras usamos para a distância:
sqrt(square(GEOGRAPHY::Point(antes.latitude, antes.longitude, 4326).STDistance(GEOGRAPHY::Point(agora.latitude, agora.longitude, 4326)))+square(agora.altitude-antes.altitude))
As distâncias são mínimas, mas vão-se acumulando.
Já temos mais um passo para o resultado final, para facilitar vamos guardar esta consulta como uma view:
CREATE view [dbo].[vwDadosTratados] AS ...
Acumular distancia e tempo
Temos para cada momento o tempo e a distância percorrida, mas queremos esses dados acumulados, ou seja, para cada momento quanto tempo e que distância já passou.
Vamos mais uma vez usar o over partition:
select num, prova,atleta, sum (metros) over (partition by prova,atleta order by num) distancia, sum (tempo) over (partition by prova,atleta order by num) segundos from vwDadosTratados
o que nos vai dar os dados que necessitamos para poder passar às visualizações:
O resultado já vimos no início, basta consumir estes dados no PowerBi e fazer um gráfico de linhas ou um gráfico de dispersão.
A realização deste projecto foi engraçada e além de ter obtido a resposta que pretendia aprendi bastante sobre SQL. Espero que também tenham aprendido alguma coisa 😊