Comment convertir une adresse postale en coordonnées GPS sur Excel (Latitude et longitude) ?
Dans ce tutoriel, je vais vous montrer comment obtenir des coordonnées GPS (latitude et longitude) à partir d’une adresse postale. Nous verrons que pour cela, il sera nécessaire de construire une petite fonction personnalisée qui repose sur l’utilisation du service web OpenStreetMap.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Comment convertir des adresses postales en coordonnées GPS
Comme nous venons de le voir dans l’introduction de ce tutoriel, nous allons voir comment convertir une série d’adresses en coordonnées GPS :
Pour pouvoir convertir une adresse postale en coordonnées GPS, le moyen le plus rapide et le plus simple est d’utiliser un service web, que nous pourrons ensuite analyser pour extraire la portion d’information qui nous intéresse.
Il existe de nombreux services sur internet qui propose de réaliser cette conversion, le plus célèbre étant incontestablement celui de Google.
Celui-ci propose en effet une API spécifique qui fournit en autres choses ce genre de service.
Une API est une interface de programmation d’application, comprenez par là un ensemble d’outils à destination principale des développeurs.
Pour simplifier, et notamment pour éviter d’avoir à souscrire à cet API auprès de Google, nous allons donc découvrir une solution alternative.
Nous allons donc utiliser les services du principal concurrent de Google Maps, à savoir OpenStreetMap, que nous retrouvons à l’adresse suivante :
https://www.openstreetmap.org/
Nous commençons donc par nous y rendre, puis nous saisir la première adresse de notre liste, afin d’analyser le comportement opéré :
Tout d’abord, nous pouvons constater qu’OpenStreetMap a bien identifié l’adresse demandée.
En plus de cela, nous pouvons nous apercevoir que cette adresse se retrouve directement saisie dans la barre d’adresse de la page :
Nous pourrons donc facilement appeler cette adresse en utilisant le lien suivant :
https://www.openstreetmap.org/search?query=5%20Avenue%20Anatole%20France%2075007%20Paris
Si nous recopions uniquement cette partie dans la barre d’adresse, nous pourrons constater que la page sera automatiquement redirigée à l’adresse :
La partie ajoutée correspond justement à ce qui nous intéresse, c’est-à-dire les coordonnées GPS.
2. La fonction COORDONNEESGPS()
Pour générer un équivalent de cette adresse dans Excel, nous pouvons simplement concaténer les différents éléments en utilisant une esperluette :
="https://www.openstreetmap.org/search?query="&A8
Cela étant fait, nous allons pouvoir analyser l’URL affichée dans la barre d’adresse pour obtenir les coordonnées GPS.
Malheureusement, il ne sera pas possible d’obtenir cette information directement avec une fonction Excel.
Mais pas d’inquiétude, nous allons développer notre propre fonction directement en VBA.
Et pour que ce tutoriel soit accessible à tout le monde, nous allons revenir dans le détail sur aspect de cette dernière.
Si vous souhaitez aller plus loin dans la découverte du langage VBA, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.
Pour commencer, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.
Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :
VBE s’ouvre alors et nous allons commencer par créer un nouveau module.
Un module est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.
Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :
Une fois le module inséré, nous pouvons créer notre fonction, en utilisant le mot-clé Function, suivi du nom de la fonction :
Function coordonneesGPS()
End Function
Excel ajoute ensuite la dernière ligne (End Function), et tout ce que nous viendrons saisir entre ces deux lignes sera exécuté lorsque nous appellerons cette fonction.
Pour commencer, nous allons passer un argument à la fonction, en le saisissant simplement entre les parenthèses.
Ici, il s’agira de venir saisir l’adresse de la page web, nous appelons donc cet argument URL et nous le typons en tant que String, c’est-à-dire une chaine de caractères :
Function coordonneesGPS(URL As String)
End Function
3. L’objet VBA Internet Explorer
Maintenant que la fonction est préparée, nous allons pouvoir lancer la page correspondante à l’URL afin d’analyser la redirection (la nouvelle adresse d’accès à la page dans laquelle OpenStreetMap ajoute les coordonnées GPS).
Pour ce faire, nous allons devoir créer un objet qui va permettre de lancer la page web.
Cet objet, nous allons l’appeler web, et le définir en tant que « InternetExplorer.Application » :
Dim web As Object
Set web = CreateObject("InternetExplorer.Application")
Pour lancer la navigation, nous utilisons maintenant la méthode navigate de cet objet web avec pour paramètre l’URL cible :
web.navigate URL
Il ne reste plus qu’à attendre le chargement complet de la page :
Do While web.busy And web.readyState <> 4
DoEvents
Loop
Ensuite, nous pouvons récupérer la redirection de l’adresse avec la propriété locationUrl :
coordonneesGPS = web.locationUrl
Pour simplifier la lecture du résultat, nous ne récupérons ici que la partie qui a été ajoutée à la fin, en supprimant tout ce qui se trouve tout d’abord tout ce qui se trouve avant le symbole dièse :
coordonneesGPS = Right(coordonneesGPS, Len(coordonneesGPS) - InStr(coordonneesGPS, "#"))
Et dans un deuxième temps, tout ce qui se trouve avant le slash :
coordonneesGPS = Right(coordonneesGPS, Len(coordonneesGPS) - InStr(coordonneesGPS, "/"))
Nous ne pouvons pas extraire directement ce qui se trouve après le slash, étant donné que nous en retrouvons un grand nombre dans l’adresse complète.
Par contre, avant que la redirection ne soit effective, il est nécessaire d’attendre un peu de temps.
Pour cela, nous insérons une petite pause d’une seconde en utilisant la procédure que nous avions mise en place lors du tutoriel dédié :
Sub pause(duree As Double)
Dim finPause As Double
finPause = Timer + duree
Do While Timer < finPause
DoEvents
Loop
End Sub
Puis, nous pouvons insérer cette pause d’une seconde juste avant d’affecter la valeur de web.locationUrl à la fonction :
pause (1)
Attention, la durée de cette pause est à adapter en fonction des conditions de travail : débit internet et ressources disponibles sur poste de travail.
En effet, le but de celle-ci est de permettre à OpenStreetMap de recentrer la page, ce qui peut demander un certain temps.
Et enfin, pour finir, nous pouvons fermer la session d’internet explorer avec un web.Quit :
web.Quit
Nous pouvons maintenant tester la fonction directement sur la feuille de calcul en l’appelant comme n’importe quelle autre fonction :
=coordonneesGPS("https://www.openstreetmap.org/search?query="&A8
Bien entendu, le temps d’exécution de ces fonctions va dépendre de plusieurs éléments :
- La qualité de la connexion internet,
- Les ressources disponibles
Sachant que chaque requête demandera au moins une seconde, qui correspond à la pause imposée.
4. Extraire la latitude et la longitude
Maintenant, nous pouvons extraire les informations de ce résultat pour obtenir la latitude et la longitude en fonction des adresses correspondantes.
Pour la latitude tout d’abord, il suffit d’extraire la partie située sur la gauche du slash, nous utiliserons donc les fonction GAUCHE() et TROUVE() :
=GAUCHE(B8;TROUVE("/";B8)-1)
Ensuite, pour la longitude, nous utiliserons bien évidemment la fonction DROITE() :
=DROITE(B8;NBCAR(B8)-TROUVE("/";B8))
5. Utiliser Google Maps (Mise à jour 24/01/2023)
La solution que nous venons de découvrir qui repose sur le service https://www.openstreetmap.fr/, n'étant plus fonctionnel en l'état, nous allons donc modifier la macro que nous puissions utiliser cette fois-ci https://www.google.fr/maps. Ce qui nous permettra de continuer de récupérer les coordonnées GPS à partir d'une simple adresse postale.
En effet, le problème de la méthode que nous avions mis en place, c'est que celle-ci reposait à la fois sur le service https://www.openstreetmap.fr/ (OSM) et à la fois sur Internet Explorer. Seulement, comme nous allons le voir juste après ces derniers sont devenus incompatibles l’un avec l’autre.
Si nous réutilisons la fonction coordonneesGPS() mise en place dans le tutoriel précédent, nous pourrons en effet nous rendre compte qu’effectivement Excel à bien lancé OSM sur IE, sauf qu’ici la recherche d’adresse échoue il n’est donc pas possible d’obtenir les coordonnées GPS de celle-ci.
Pour corriger ce problème, nous allons donc aller au plus simple en passant outre OSM et nous allons donc utiliser plutôt le service Google Maps.
5.1. Modifier la fonction coordonneesGPS() pour utiliser Google Maps à la place d'Open Street Map
Pour cela, nous allons commencer par modifier l’argument utilisé dans la fonction coordonneesGPS pour pointer directement sur le bon service.
Nous allons donc remplacer :
=coordonneesGPS("https://www.openstreetmap.org/search?query="&A8)
Par :
=coordonneesGPS("https://www.google.fr/maps/place/"&A8)
Lorsque nous validons, nous pouvons constater qu’ Excel après avoir identifié l'adresse, a bien affiché la fenêtre d’Internet Explorer, mais n’est pas encore en mesure de récupérer les coordonnées GPS de celle-ci.
Nous allons maintenant modifier la fonction coordonnesGPS().
5.2. Ajuster le temps de pause
Pour modifier la macro, nous allons lancer l’éditeur de macro en appuyant sur les touches [Alt]+[F11] du clavier.
Nous allons retrouver la fonction coordonnesGPS() mis en place précédemment dans le « Module1 » :
Nous n’allons pas revenir sur le fonctionnement de celle-ci, vous retrouver le détail de sa construction ici.
Pour commencer, nous allons nous intéresser à la ligne :
pause (2)
Il s’agit en effet de la ligne permettant d’attendre que Google Maps mette en place la redirection de l’URL de la page internet avec les coordonnées GPS. Nous avions vu un peu plus tôt que le temps alloué à cette pause n’est pas suffisant. Nous allons donc la passer directement à 6 secondes en modifiant l’argument de celle-ci :
pause (6)
Bien entendu, je vous invite à effectuer des tests sur votre propre poste de travail étant donné que le temps nécessaire va dépendre à la fois de la vitesse de la connexion internet, mais également de configuration de la machine sur laquelle la macro est exécutée.
5.3. Récupérer les coordonnées GPS de l'adresse postale
Une fois la redirection effectuée, nous récupérons l’adresse affichée dans la barre d’URL d’Internet Explorer et nous la stockons dans la variable que nous appelons « redirection ».
Cette variable prend alors la valeur :
« https://www.google.fr/maps/place/5+Av.+Anatole+France,+75007+Paris/@48.8581951,2.2946885,17z/data=!3m1!4b1!4m5!3m4!1s0x47e66fe18ec97eb9:0x2eb4ebbfd111b16c!8m2!3d48.8581951!4d2.2946885 »
La partie qui nous intéresse est celle qui se trouve entre l’arobase et la barre oblique qui suit : « @48.8581951,2.2946885,17z/ »
Pour réduire la chaîne à ces valeurs, nous allons effectuer deux opérations :
- En premier lieu, nous allons souhaiter ne conserver que la partie située après l’arobase. Pour cela, nous allons pouvoir utiliser la fonction Right() de VBA comme nous le verrons juste après.
- Ensuite, nous allons splitter la chaîne de caractères ainsi obtenue en utilisant la virgule comme caractère de séparation. La fonction Split() que nous allons utiliser permet en effet de transformer une chaîne de caractères en un tableau qui contiendra plusieurs sous-chaînes de caractères. Ce tableau, nous allons le stocker dans la variable « redirectionSplitee », laquelle est typée en tant que Variant.
Nous remplaçons donc les lignes :
redirection = Right(redirection, Len(redirection) - InStr(redirection, "#"))
redirection = Right(redirection, Len(redirection) - InStr(redirection, "/"))
Par :
redirection = Right(redirection, Len(redirection) - InStr(redirection, "@"))
Dim redirectionSplitee As Variant
redirectionSplitee = Split(redirection, ",")
Il ne nous reste ensuite plus qu’à stocker les deux premiers éléments du tableau (avec les index « 0 » et « 1 ») dans la variable « redirection » en remplaçons le signe de séparation des décimales US utilisés par Google dans l’adresse générée par celui reconnu par votre version d’Excel (en principe le point « . » si vous travaillez sur une version française d’Excel) en utilisant la fonction VBA Replace. Nous n’oublierons pas non plus d’intercaler une barre oblique entre la latitude et la longitude :
redirection = Replace(redirectionSplitee(0) & "/" & redirectionSplitee(1), ".", ",")
Et voilà, la fonction coordonneesGPS() permet maintenant d’obtenir une chaîne de caractère identique à celle que nous avions mise en place dans la première partie, et que nous pourrons maintenant exploiter avec dans les colonnes « Latitude » et « Longitude » de notre tableau :
