Géolocalisation avec Google Maps et Excel

Google Maps est désormais très répandu : visualisation de plans, de rues avec Street View ou même consultation d'itinéraires.
Mais derrière l'interface utilisateur, Google Maps c'est aussi la possibilité de traiter des requêtes en masse. Et ainsi de récupérer toutes les informations utiles pour se lancer dans le géomarketing.
Dans cet article, nous allons voir comment Excel peut communiquer avec Google Maps pour créer ces requêtes.


 

Que peut-on faire avec Google Maps ?

Planifions nos trajets en voiture

Pour ceux d'entre vous qui n’auraient jamais utilisé Google Maps, c'est un peu comme une carte routière, un plan de quartier et un GPS réunis. On peut visualiser la carte d'une région ou d'une ville, entrer un itinéraire et en suivre les instructions.

C'est justement cette dernière fonctionnalité que l'on va utiliser. En rentrant 2 adresses, Google Maps va nous calculer le trajet idéal entre ces deux points et nous allons récupérer les informations utiles :

  • La durée du trajet
  • La distance
  • Les coordonnées GPS des points de départ et d'arrivée.

Google Maps Itinéraire
 

Ces dernières informations sont disponibles dans l’URL générée par Google.

Google Maps URL

Si l'opération est déjà possible en l'état, elle risque d'être fastidieuse sur une longue liste d'adresses. Pour ceci, nous allons donc utiliser l'API de Google Maps. Pour faire simple, une API (Application Programming Interface) est un moyen d'accéder aux services d'un programme sans passer par l'interface standard. Dans notre cas, c'est donc via Excel que nous allons nous connecter à Google Maps.

Pour y accéder, il nous faut utiliser la syntaxe suivante :
http://maps.google.fr/maps/api/directions/json?origin= Adresse de départ &destination= Adresse d’arrivée &sensor=false

Si on rentre cette URL dans un navigateur web, on obtient le résultat au format JSON (article Wikipédia pour une présentation rapide du format). Il s'agit d'un format texte dans lequel nous retrouvons toutes les informations nécessaires.

Google Maps JSON navigateur

Première étape réussie : nous savons désormais comment obtenir toutes les données d'un trajet Google Maps.

 

Soyons écolo, prenons les transports

Pour être complet, les itinéraires sont calculés pour des trajets en voiture par défaut. La requête utilisée ci-dessus renvoie ce type de résultat.
Mais Google Maps nous permet aussi de consulter le même itinéraire en transports en commun. Sur l'interface, c'est en cliquant sur le symbole du train que l'on affiche ces résultats.

Google Maps itineraire transports commun

L'API traite aussi bien les itinéraires routiers qu'en transports en commun, avec les mêmes informations de départ. Et les résultats sont sensiblement au même format.
Le seul inconvénient, c'est qu'il faut préciser dans la requête le mode de transport voulu. Ainsi, il faut lancer deux requêtes différentes pour obtenir l'itinéraire aussi bien en voiture qu'en transports.
Voici la syntaxe utilisée pour une requête en transports en commun :
http://maps.google.fr/maps/api/directions/json?origin= Adresse de départ &destination= Adresse d’arrivée &mode=transit

Dans la prochaine section, nous allons nous concentrer sur un itinéraire routier. Nous verrons plus tard comment obtenir de la même façon les résultats en transports.

 

Comment traiter ces données dans Excel ?

Pour importer le résultat dans Excel, enregistrons la page web avec un clic droit puis Enregistrer sous... sur la page du navigateur.

Ouvrons le fichier dans Excel. Il n'est pas dans un format reconnu mais on le retrouve dans la liste de tous les fichiers, en passant par le menu Fichier > Ouvrir. L'assistant d'importation vient nous aider, sélectionnons un fichier de type Délimité mais sans aucune délimitation à l’étape 2.

Import JSON Excel

Les lignes sont bien séparées et tout le contenu est dans la première colonne. Il ne reste plus qu'à repérer les balises ‘start_address’ et ‘end_address’ pour trouver les adresses et coordonnées des deux points du trajet. La balise 'legs' nous indique la distance et la durée.
Les balises 'distance' et 'duration' se trouvent aussi dans chaque étape de la section 'steps' qui détaille le trajet. Il est donc plus prudent de se fier à la balise 'legs' qui récapitule le trajet.

Import JSON Excel termine

L'avantage d'une API c'est que le format de restitution est toujours le même. Avec 'start_address' vous trouvez l'adresse de départ, 2 lignes en-dessous figure la latitude, 3 lignes en-dessous la longitude.
Ce sera le cas pour toutes les réponses JSON, il suffit d'isoler les libellés en début de lignes ("lat" : ou "long" : par exemple) pour ne conserver que ce qui nous intéresse.

Deuxième étape réussie : on sait comment retraiter le résultat de la requête dans Excel.

 

Comment géolocaliser facilement une série d’adresses ?

Après cette explication, venons-en à l'essentiel : récupérer les informations en masse sans passer par la même manipulation à chaque fois.

Commencez par télécharger le fichier que vous pourrez ensuite réutiliser selon vos besoins. Cliquez sur ce lien ou sur le bouton ci-dessous.

logo telecharger fichier
 

Que fait le fichier ?

Dans l'onglet Itinéraires, lorsqu'on clique sur le bouton Lancer le calcul des itinéraires, une macro s'exécute. Elle va lire la première ligne et créer une requête Google Maps avec les adresses renseignées.

Donnees calculs itineraires geolocalisation excel

Le résultat est importé dans la feuille Requête Google Maps. La procédure recherche les balises 'start_address', 'end_address' et 'legs' puis extrait toutes les informations et les reporte dans le tableau sur la ligne en cours de traitement.

Resultats calculs itineraires geolocalisation excel

La procédure passe ensuite à la ligne suivante et recommence le même process et ce jusqu'à la fin du tableau.

 

Comment l'utiliser ?

Pour une utilisation optimale, retrouvez toutes les consignes et conseils dans l'onglet Guide.
Pour automatiser cette tâche, une macro a été nécessaire. Pensez donc à les activer pour que le fichier fonctionne.

Renseignez dans la feuille de calcul Données, une liste avec, à chaque fois, un point de départ et un d'arrivée : soit via les coordonnées géographiques soit via l’adresse (code postal et ville obligatoires). La priorité est donnée aux coordonnées si elles sont complètes.

Avant de lancer les calculs, on choisit le mode de transport voulu à l'aide des deux boutons, le train symbolisant les transports en commun, quels qu'ils soient.
Tous les itinéraires seront alors calculés avec ce mode de transport.

Google Maps choix mode transport

En fin de procédure, pensez à contrôler les adresses dans les résultats pour vérifier qu’elles ont été correctement reconnues.
Si une des adresses n’est pas identifiable, un commentaire le précise dans la colonne prévue.

 

Quelles sont les contraintes ?

Google Maps offre gratuitement 2 500 requêtes par tranche de 24 heures. Au-delà de cette limite, vous devrez soit attendre 24 heures pour la remise à zéro de votre quota soit souscrire à une offre payante. Google vous propose un forfait à partir de 0,50$ pour 1 000 requêtes supplémentaires.
La géolocalisation est un travail de longue haleine, en fonction de la volumétrie de vos données, vous pouvez sans doute planifier toutes vos requêtes sur une plage de plusieurs jours.

Lorsque vous atteignez la limite de 2 500 requêtes, la procédure s'arrête et un commentaire vous le signale sur la ligne en question.

Le nombre de requêtes est aussi limité, en théorie, à 50 par seconde. Un commentaire sera de nouveau laissé pour vous signaler le dépassement de quota.
Mais si la première limite est plutôt fiable, celle-ci semble beaucoup plus aléatoire. Elle est parfois atteinte après seulement une ou deux requêtes. Pour contourner ce problème, une pause de 250 millisecondes est ajoutée entre chaque interrogation pour être sûr de ne pas dépasser le quota. Comptez alors en moyenne 2 lignes traitées par secondes, soit un peu plus de 20 minutes pour exécuter le maximum de 2 500 requêtes.
Si malgré cette pause, vous obtenez le message d'erreur, vous avez la possibilité de régler vous-même le délai d'attente entre deux requêtes. Entrez le temps de pause souhaité, en millisecondes, dans le champ situé au-dessus de la liste des adresses.

Temps pause requete Google Maps

Pour plus d'informations ou pour souscrire à une offre Google, consultez cette page.

 

Exploiter les données

Les données liées aux trajets sont exploitables directement, on a une valeur précise du temps et de la distance séparant deux adresses. Selon vos besoins, utilisez la valeur la plus pertinente.
Le temps de parcours est parfois plus judicieux, notamment s'il s'agit de prévoir des tournées clients.

Les coordonnées géographiques ont également leur intérêt. Comme nous venons de le voir, les requêtes Google Maps ont quelques contraintes. Pour les contourner, on peut lancer une première analyse en calculant une distance à vol d'oiseau entre deux points.
Le résultat est forcément moins précis qu'une distance routière ou un temps de trajet mais il peut-être obtenu à partir d'une simple formule Excel et des coordonnées GPS.
On peut ainsi opérer un premier tri et affiner ensuite l'analyse avec des requêtes Google Maps sur une liste d'itinéraires plus réduite.
Nous avons donc tout intérêt à stocker les latitudes et longitudes pour les adresses que nous sommes susceptibles de réutiliser. Cela pourrait nous faire gagner un temps précieux la prochaine fois !

Pour savoir comment calculer des distances à vol d'oiseau à partir des coordonnées, consultez cet article. Vous y retrouverez un autre fichier que vous pourrez réutiliser avec vos propres données.

N'hésitez pas à nous dire commentaires quelle est votre approche sur ces questions de géomarketing.
Si vous avez des questions ou des problèmes sur le fonctionnement du fichier, posez-les également en commentaires !

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , , .Mettre en favori le Permaliens.

19 réponses à Géolocalisation avec Google Maps et Excel

  1. Armand dit :

    Bonjour, merci pour le doc. Malheureusement, c’est écrit « Lieu(x) introuvable(s) » lorsque je lance la macro, sans changer les adresses de l’exemple. Une raison particulière ?

  2. El Mehdi EDDARHRI dit :

    Hello

    Merci pour votre travail, il a l’air tourneé tres bien pour vous sauf que ça ne fonctionne pas pour moi, au début ca m’affichait un msg comme quoi j’ai dépassé la limite autorisée j’ai résolu ça en ajoutant une clé « api google directions » a la fin des liens du code VBA,
    Actuellement les lieux sont introuvables par l’outil, que pourrai-je faire pour remédier à ce pb?

    D’avance merci pour votre aide 🙂

  3. suziwan dit :

    , c’est une macro très utile. J’aimerais bien avoir la possibilité d’entrer les noms de villes sans les codes postaux, même si évidemment, les requêtes aboutiront moins souvent.

  4. Marseillan dit :

    Bonjour Jean-Rémy.
    Enfin un VBA (après de longues heures de recherche) qui fonctionne sur mon Mac (Excel 16.11.1). Vous venez de me faire gagner un temps précieux. Merci +++ infiniment.
    Astuce: Le partage de connexion avec son téléphone permet de lancer une nouvelle série de requêtes.
    Cordialement. A.M

  5. Delphine dit :

    Bonjour,

    Merci pour votre outil et tout le mode d’emploi. Malheureusement quand je lance le calcul d’itinéraire, le message suivant s’affiche « erreur de compilation : membre de méthode ou de données introuvable » J’imagine qu’il y a un problème dans ma liste d’adresse mais je ne vois pas lequel…. Si jamais vous avez une idée pour m’aider, merci beaucoup !

    Delphine

  6. david dit :

    Bonjour,
    Merci pour vos super outils, je me demandais, pour permettre de conserver les resultats des requetes antérieurs ayant aboutit, serait il possible d’intégrer une boucle de programmation qui reprendrait le travail à la suite de la dernière requete aboutie .

    Une alternative disons à la limitation google, qu’en dites vous ?

  7. Camby OfMars dit :

    Bonsoir. Bravo pour votre modèle super utile. Cependant j’aurai besoin d’utiliser d’autres paramètres et notamment calculer une estimation de temps à une date future.
    J’ai ajouté les paramètres &departure_time=1343643432&traffic_model=pessimistic
    Aucune conséquence au niveau du résultat.
    J’ai lu sur les aides dev https://developers.google.com/maps/documentation/directions/intro que ces paramètres ne sont disponibles que si on précise une ID API (ou un code client payant Premium mais cela ne semble pas obligatoire).
    Je me suis créé cet ID mais quand je passe ce paramètre en plus cela ne fonctionne plus (erreur « lieux introuvables ».
    La syntaxe que j’ai utilisé : &key=AIzaSyBJs6cfkw0dNaAymMpoWO6D11FEZBfAg2g
    Avez vous une idée ?
    Merci d’avance pour votre aide.

  8. Romain dit :

    Bonjour,
    Un grand merci pour ce document dynamique extrêmement utile.
    Question, est-il possible de sélectionner la clé API sur laquelle est effectuée la requête ? Quelle est celle utilisée par défaut ?

    Merci pour votre aide,
    Bien à vous

  9. cedric dit :

    Bonjour Jean-Rémy,
    Super travail réalisé, cela dit j’ai un soucis de limite de requêtes atteintes aléatoire à chaque fois que je lance le calcul des itinéraires, sur 100 itinéraires le calcul peut s’arrêter tantôt à la 10ème ligne puis à la 15éme puis à la 5ème…
    Est ce que cela peut être la pause que vous avez imposé toute les 250 ms qu’il faudrait passer à une autre valeur?

    • paolobendin dit :

      Bonjour Jean-Rémy,

      bravo pour ce super travail, très utile et facile à utiliser.
      Par contre en effet, comme Cédric, j’ai moi aussi un soucis de limite de requêtes atteinte aléatoire.

      Avez vous une idée de comment résoudre ce problème?
      Merci d’avance

      • Jean-Rémy dit :

        Bonjour Paolo,

        Merci pour votre message.
        Comme vous et comme Cédric, il m’arrive d’avoir le même dépassement de quota après seulement une ou deux requêtes lancées.
        Je n’ai pas vraiment de solution miracle à vous proposer, le comportement de Google me semble aléatoire dans ce cas.

        Je peux vous proposer de télécharger une nouvelle version du fichier, j’ai simplement ajouté un champ au-dessus du tableau des adresses dans lequel vous pouvez régler vous-même le temps de pause entre deux requêtes.
        Parfois, l’augmenter (à 500 ou 1000) permet d’améliorer l’enchaînement. Il se peut que cela fonctionne aussi sans aucune pause.

        J’espère que cela peut corriger en partie le problème.

        Bonne soirée
        Jean-Rémy

  10. Clement dit :

    Bonjour Jean-Remy,

    J’ai une petite question un peu délicate :
    Le temps de trajet estimé est celui au moment de la requête ?
    Si oui y’a t’il un moyen d’obtenir un trajet moyen (optimiste ou pessimiste?)

    Merci pour ce super outil!!!

  11. Jérémie dit :

    Merci pour ce super article et la diffusion du fichier Excel.
    J’ai besoin d’afficher au clic de souris sur une cellule contenant code postal et nom de la ville, la page internet géolocalisant ladite ville.
    Pourriez-vous me dire comment réaliser cela s’il vous plait.
    Merci d’avance.

    • Jean-Rémy dit :

      Bonjour Jérémie,

      Pour afficher un lien, vous pouvez utiliser la fonction LIEN_HYPERTEXTE. Comme c’est une fonction, le résultat est dynamique et change si jamais vous modifiez la ville ou si vous l’utilisez avec une série de valeurs.
      Dans la première partie, vous placez le lien vers la page et dans la seconde, le texte à afficher.

      Voici un exemple avec Google Maps, le code postal figure en A1 et le nom de la ville en A2. Les deux infos sont insérées dans l’URL Google Maps, il suffit alors de cliquer sur le lien pour l’ouvrir dans une nouvelle page du navigateur Internet.

      Lien Google Maps fonction LIEN_HYPERTEXTE

      La formule prend en charge les éventuels espaces dans le nom et les remplace par des tirets dans l’adresse URL, la syntaxe globale de l’adresse est celle que vous pouvez trouver en cherchant un lieu manuellement dans Google Maps.

      J’espère que cela répond à votre demande.
      Bien à vous
      Jean-Rémy

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.