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:

A graph with a line AI-generated content may be incorrect.

.

Na zona que aparenta ter a maior distância entre nós, temos:

A screenshot of a computer AI-generated content may be incorrect. A white rectangle with blue and pink text AI-generated content may be incorrect.

.

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.

A screenshot of a computer code AI-generated content may be incorrect.

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)

.

A table with numbers and letters AI-generated content may be incorrect.

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

A screenshot of a table AI-generated content may be incorrect.

.

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

.

A screenshot of a table AI-generated content may be incorrect.

.

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

A screenshot of a table AI-generated content may be incorrect.

.

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))

.

A table with numbers and numbers AI-generated content may be incorrect.

.

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:

.

A screenshot of a computer AI-generated content may be incorrect.

.

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 😊

.