03 aout 2020

Calculer une distance entre deux coordonnées GPS dans Excel

Je suis un grand fan des énigmes ! En 2019, j’ai même crée avec ma femme la société Zupple, team building cérébral à Lyon. Nous sommes aussi des joueurs réguliers, et dans un jeu, on a eu besoin de calculer des ditances à partir de coordonnées GPS.

Le tableur (que ce soit Microsoft Excel, Libre Office Calc ou Google Sheets) est l'outil à tout faire. Quand on est dans une chasse au trésor, on s'en sert pour garder une trace de toutes nos découvertes, de nos pistes et de nos idées. C'est un document facile à partager pour qu'un co-équipier puisse vérifier nos calculs. Il me manquait une fonctionnalité importante : pouvoir estimer la distance entre deux coordonnées GPS.

Après un peu de recherche, je suis tombé sur la formule de Haversine. Elle permet de déterminer la distance entre deux points d'une sphère à partir de leurs longitudes et latitudes. Bingo !

Étape 1

Capture d'écran d'un fichier Excel avec le calcul de la distance entre deux coordonnées — L'objectif est de pouvoir calculer la distance entre deux coordonnées.

La formule de Haversine en langage tableur

=2 * 6371 * ASIN(
  SQRT(
    (
      SIN((
        RADIANS(C2) -
        RADIANS(A2)
      )/2)
    )^2
    +
    COS(RADIANS(C2)) *
    COS(RADIANS(A2)) *
    (
      SIN((
        RADIANS(D2) -
        RADIANS(B2)
      )/2)
    )^2
  )
)

I did it!

J'étais trop heureux d'arriver à faire fonctionner cette formule dans mon tableur 🥳. Mais elle n'est pas très pratique car elle n'utilise pas un format très standard pour les coordonnées.

Étape 2

Capture d'écran d'un fichier Excel qui calcul la distance entre deux coordonnées GPS — L'objectif est de calculer la distance entre deux coordonnées au format GPS, sans séparer latitude et longitude.

J'ai donc deux soucis avec ma formule précédente. Mon premier problème est que j'ai la longitude et la latitude de chaque lieu dans la même cellule. Il me faut donc extraire ces données, pour cela on a besoin des fonctions LEFT(), RIGHT(), FIND(), et LEN().

Récupérer la longitude de la cellule A2

=LEFT(A2; FIND(","; A2) - 1)

Récupérer la latitude de la cellule A2

=RIGHT(A2; LEN(A2) - FIND(","; A2))

Mon deuxième problème est que les longitudes et latitudes utilisent par convention le point pour séparer les décimales. Hors, les formules mathématiques s'attendent à une virgule comme séparateur. C'est la fonction SUBSTITUTE() qui va nous dépanner cette fois-ci :

Récupérer la longitude de la cellule A2, au bon format

=SUBSTITUTE( LEFT(A2; FIND(","; A2) - 1); "."; ",")

La formule complète pour calculer la distance entre les coordonnées GPS contenues dans les cellules A2 et B2

=2 * 6371 * ASIN(
  SQRT(
    (
      SIN((
        RADIANS( SUBSTITUTE( LEFT(A2; FIND(","; A2) - 1); "."; ",")) -
        RADIANS( SUBSTITUTE( LEFT(B2; FIND(","; B2) - 1); "."; ","))
      )/2)
    )^2
    +
    COS( RADIANS( SUBSTITUTE( LEFT(A2; FIND(","; A2) - 1); "."; ","))) *
    COS( RADIANS( SUBSTITUTE( LEFT(B2; FIND(","; B2) - 1); "."; ","))) *
    (
      SIN((
        RADIANS( SUBSTITUTE( RIGHT(A2; LEN(A2) - FIND(","; A2)); "."; ",")) -
        RADIANS( SUBSTITUTE( RIGHT(B2; LEN(B2) - FIND(","; B2)); "."; ","))
      )/2)
    )^2
  )
)

La formule complète est donc complètement illisible, mais l'important c'est que ça marche, non ?

Bonne chasse à tous, Thomas.