Convertir des adresses postales en coordonnées GPS

Bonjour à tous,

Je fais appelle à la communauté pour m'aider. Je précise avoir déjà lu pas mal de post ici et ailleurs sans trouver de réponses appropriées.

Je souhaite faire évoluer mon outil de travail principal (sous Excel 365).

Le tableau :

Il s'agit d'un tableau avec des informations divers dont des salariés avec leurs adresses, CP et ville ainsi que les coordonnées GPS de ces adresses.

Voici à quoi cela ressemble exactement sur une ligne et concernant uniquement la partie du tableau qui nous intéresse (beaucoup de lignes/colonnes, tableau structuré).

Mon projet :

Les données étant de plus en plus nombreuses et récurrentes, je souhaite simplement gagner du temps de saisie.

Je souhaite automatiser désormais (via VBA) la recherche de lattitude/longitude (en rouge dans le tableau) selon l'adresse saisie manuellement/CP/Ville (en vert) plutôt que d'aller chercher tout cela manuellement à chaque fois ( via bing maps, google maps).

==> Je précise ne pas vouloir utiliser l'API google maps suite à l'évolution tarifaire (aucun budget pour cela)

Auriez-vous des idées ?

Je vous remercie!

Bonjour,

Vous pourriez utiliser PowerQuery. Ci-joint un exemple avec OpenStreetMap.

Juste 1 point : en général les api gratuites ont un nombre limité de calls, donc l'idéal serait d'appeler l'API lors de l'entrée des données (recherche de 1 adresse) et non sur la table "déjà faite" (beaucoup d'adresses). C'est pour ça que l'exemple est construit comme ceci.

Onglet données > connexions pour rafraichir si vous changez l'adresse en B2.

Hello,

Une proposition powerquery juste avec une clef API google maps, ça a l'air gratuit, je n'ai pas vu ou je devais payer en tout cas

let

    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    AddCustom = Table.AddColumn(Source, "Coordinates", each Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?key=TA_CLEF_API&address=" & [Adresse]))),
    ExpandCoordinates = Table.ExpandRecordColumn(AddCustom, "Coordinates", {"results"}),
    #"results développé" = Table.ExpandListColumn(ExpandCoordinates, "results"),
    ExpandResults = Table.ExpandRecordColumn(#"results développé", "results", {"geometry"}),
    ExpandGeometry = Table.ExpandRecordColumn(ExpandResults, "geometry", {"location"}),
    ExpandLocation = Table.ExpandRecordColumn(ExpandGeometry, "location", {"lat", "lng"})
in
    ExpandLocation

@+

Merci SABOH1261 : ta solution gratuite fonctionne mais après l'avoir testé et si j'ai bien compris : il conviendrait donc de séparer ma BDD de cet outil de conversion d'adresse pour éviter de tout réinterroger à chaque fois, c'est bien ça ? Idéalement j'aimerais intégrer cela directement dans le tableau principal (+ 1000 lignes d'adresse dont des doublons) pour éviter un copier-coller des coordonnées GPS. J'aimerais tout laisser sur place les adresses de la table principale et la requête pour récup les coordonnées GPS.

Merci BAROUTE78, cette solution m'intéresse aussi, mais j'ai bien regardé une nouvelle fois l'inscription à l'API ce midi : elle semble "gratuite" mais demande quand même une CB (a priori en cas de dépassement des requêtes mensuelles limitées à 10 000 si j'ai bien suivi). Pas possible de mettre une CB dans mon environnement pro.

Un compromis (gratuit et sur la table principale) de vos deux solutions serait parfait...

Hum,

Moi j'ai mis ma carte virtuelle Lydia avec 0€ dessus via mon compte google perso et ensuite j'ai utilisé la clé sur mon pc pro.

Tu ne peux pas reproduire ce schéma ?

@+

De ce que je lis sur le net, OpenStreetMap permet maxi 1 call/seconde (gratuit). Donc en l'appliquant sur tout un tableau on risque d'avoir des (mauvaises) surprises. Je ne sais pas si il y a d'autres API plus permissives.

La requete de @Baroute pourrait etre complétée en ne lui faisant rechercher que les lignes sans latitude/longitude par exemple. Mais le problème reste le meme : si + 1 ligne vide, on ne peut pas prévoir le comportement.

Sinon il y a VBA, mais bon les appels à des API moi je ne sais pas trop faire.

Une petite alternative amusante, puisque vous êtes sur 365 on peut aussi utiliser une formule combinant WEBSERVICE et les expressions regex. Entrez une adresse en A1 et en C1 mettez ceci :

=LET(_apiCall;SERVICEWEB("https://nominatim.openstreetmap.org/search?q=" & A1 & "&format=json&limit=1");
CNUM(REGEX.REMPLACER(REGEX.EXTRAIRE(_apiCall; """lat""\s*:\s*""([^""]+)"",""lon""\s*:\s*""([^""]+)""";2);"\.";"\,")))

Ce pourrait être une solution, il me semble que les formules ne sont pas réévaluées… Mais à l'ouverture du fichier je ne sais pas. Sinon il faudrait mettre le calcul du classeur en manuel, au moins à l'ouverture.

Merci pour vos retours rapides !

- BAROUTE : Pas mal l'idée mais je n'ai pas de carte virtuelle Lydia ou autre. Je ne maitrise pas non plus le sujet. J'irais regarder ça plus tard du coup.

-SABOH12617 : Merci pour la nouvelle solution. J'ai mis ça en C1 dans un nouveau classeur et j'obtiens un #NOM?. J'imagine avoir raté quelque chose dans la syntaxe de la formule.

NikonvsWild a écrit :

Merci pour vos retours rapides !

- BAROUTE : Pas mal l'idée mais je n'ai pas de carte virtuelle Lydia ou autre. Je ne maitrise pas non plus le sujet. J'irais regarder ça plus tard du coup.

-SABOH12617 : Merci pour la nouvelle solution. J'ai mis ça en C1 dans un nouveau classeur et j'obtiens un #NOM?. J'imagine avoir raté quelque chose dans la syntaxe de la formule.

oui désolé j'avais oublié de traduire la fonction en français. C'est corrigé dans le message précédent.

Je te remercie saboh12617 ta solution semble fonctionner sur quelques lignes d'essai.

EDIT : A priori, je dois passer par la création d'une nouvelle colonne CONCATENER, pour réunir adresse+cp+ville.

Je vais tester de la propager sur une copie de mon doc pour voir comment cela réagit à l 'ouverture du fichier.

Je reviens pour vous tenir informé (mais on s'approche du but )

D'accord. Pour info changer d'API (si tu arrivais à avoir une clé GMaps par exemple) est possible de cette manière également. L'avantage si j'ai bien compris c'est qu'on a moins de restrictions sur les appels avec Google.

Afin de simplifier ton utilisation future, je recommanderai de passer par 1 (ou 2) fonction(s) LAMBDA dans le gestionnaire de noms, pour avoir quelque chose comme

=LATITUDE(mon_adresse)
=LONGITUDE(mon_adresse)

Les fonctions LATITUDE et LONGITUDE encapsulant le charabia peu appétissant ci-dessus.

Le seul souci que je vois avec cette méthode c'est qu'il faut 2 calls/adresse (latitude + longitude). Si tu travailles en tableau structuré (TS) ça me semble quasi-inévitable (hors PQ évidemment). Si on a ces données calculées hors du TS par contre on peut profiter de la propagation et faire 1 call pour les 2 valeurs.

Toujours pour limiter les calls API, une astuce à laquelle je pense serait [utile uniquement si répétitions d'adresses et/ou recalcul à l'ouverture] de sauvegarder sur une autre feuille une base de données des adresses déjà évaluées (via VBA/PQ) et dans la formule de faire un SI qui va d'abord chercher cette BDD pour une correspondance, et sinon fait un nouveau call API. D'ailleurs, cette solution s'adapterait assez bien dans une approche PQ.

Bonjour,

ci-joint un exemple en utilisant l'API "nominatim.openstreetmap.org"

Attention : l'adresse ne doit pas comporter d'accents : compatibilité anglo-saxonne oblige.

Merci THEV pour la solution avec l'API openstreetmap

Ca semble top sur le principe.

Problème, il y a énormément de déchet (il trouve 50% des adresses que je tape sur le site web d'openstreetmap)

exemple d'une adresse (prise au pif) que je trouve avec le site et pas avec la formule :

Est-ce mieux avec l'API google maps à ce niveau ?

Merci en tout cas! Est-ce qu'une copie de mon ficher vous serez utile (si je l'anonymise) ?

Bonne journée et merci pour votre aide

Je teste les autres solutions de SABOH et BAROUTE dès que possible également (webservice et la formule powerquery : j'y connais rien en powerquery ceci dit mais je tenterai).

Bonjour,

Le problème vient de l'API malheureusement. Si vous pouviez tester celle de Google Maps ce serait top.

Sinon en dernier recours je pense à une solution VBA qui "ferait une recherche" sur Google Maps et renverrait les coordonnées trouvées, mais c'est très "lourd" comme méthode et pas vraiment adapté quand il existe des API pour faire ce travail.

Bonsoir,

ci-jointe nouvelle version avec modification de l'API

A noter que certaines adresses renvoient plusieurs géocodes. Seul le premier est restitué.

pour m'amuser et en ajoutant le pays ... C'est mieux de modifier la séquence ? (Pays, Ville, adresse)

Merci à tous, je vais garder cette dernière version. Elle est précise et gratuite.

Merci pour toutes vos réponses!

@NikonvsWild,

la solution est celle de @Thev, moi, j'ai juste ajouté quelque plaisanteries