API et géocodage dans Libre Office Calc
J’avais remarqué depuis longtemps qu’il était possible de faire des appels vers des API depuis Google Spreadsheet pour géocoder (voir cet article). En cherchant un peu j’ai vu l’équivalent avec Excel (non, ne me demandez pas de faire du VBA, j’ai des boutons)
Je me suis posé la question comment faire avec LibreOffice
La recette est assez simple: il faut se reposer sur une extension nommée LibreOfficeGetRestPlugin pour faire les appels vers une API qui retourne du JSON (je n’aborderais pas le XML). On l’installe en suivant le tuto en anglais.
En français, cela donne:
- récupérez le fichier https://gist.github.com/ThomasG77/4ed97370af8355feabf50cb2909198a0/raw/65417d9d71b719205575958da44a8753b3b19d8c/LibreOfficeGetRestPlugin.oxt (Parce que l’extension n’est plus disponible sur le site de LibreOffice https://extensions.libreoffice.org/en/extensions/show/libreoffice-getrest-plugin-1#comment-4187)
- ouvrez LibreOffice Calc
- allez dans Outils > Gestionnaire des extensions
- cliquez sur Ajouter puis parcourir pour pointer vers le fichier LibreOfficeGetRestPlugin.oxt
- cliquez sur Fermer de la fenêtre Gestionnaire des extensions
Le scénario est de récupérer le code INSEE depuis le nom d’une commune et son code postal. Cela donne pour Nantes dont le code postal peut être 44000, 44100, 44200 ou 44300 l’url https://geo.api.gouv.fr/communes?nom=Nantes&boost=population&fields=code,nom,centre&codePostal=44100
On va prendre 2 colonnes en entrée, code_postal et nom_commune dans notre tableur puis mettre à jour une troisième colonne code_insee. On suppose que vous avez déjà des données comme illustré ci-dessous.
On ajoute maintenant le nom de colonne code_insee dans la cellule C1 puis on va coller dans la cellule C2 la formule suivante
=CONCATENER("https://geo.api.gouv.fr/communes?nom=";ENCODEURL(B2);"&boost=population&fields=code,nom,centre&codePostal=";A2)On fait appel à la fonction CONCATENER (attention, la formule peut être CONCATENATE si votre LibreOffice n’est pas en Français) qui permet de concaténer des chaînes en fonction des colonnes et à la fonction ENCODEURL pour encoder le nom de la commune. Encoder dans ce cas signifie remplacer certains caractères qui ne sont pas autorisés pour faire appel à une URL et qui dans ce cas planterons les appels au service web.
Ensuite, entourez l’expression précédente par GET qui est une des fonctions apportées par LibreOfficeGetRestPlugin et qui permet de faire des appels web. La fonction sera maintenant
=GET(CONCATENER("https://geo.api.gouv.fr/communes?nom=";ENCODEURL(B2);"&boost=population&codePostal=";A2))
Il nous reste maintenant à récupérer le code INSEE de la commune. L’API peut retourner plusieurs résultats et on a un tableau qui nous est retourné. On va faire au plus simple en disant de prendre le premier objet à l’intérieur du tableau et récupérer pour cet objet la clé “code” qui est le code INSEE de la commune. C’est ce qu’on fait en utilisant la fonction PARSEJSON. Notre fonction devient ainsi
=PARSEJSON(GET(CONCATENER("https://geo.api.gouv.fr/communes?nom=";ENCODEURL(B2);"&boost=population&fields=code,nom,centre&codePostal=";A2));"get(0).code")"get(0).code" veut dire prend-moi le premier élément (en informatique, le premier élément est souvent noté 0) en utilisant “get(0)” puis on dit qu’on veut la clé “code”.
On peut ensuite “étendre la formule” pour appliquer le traitement à plusieurs lignes. Attention, en faisant cela car le plugin n’est pas aussi souple que du code, on ne peut pas limiter la fréquence entre les appels à l’API. Le traitement risque d’être long et/ou le serveur risque de ne plus répondre…
Le résultat est le suivant:
On peut vouloir aussi récupérer les coordonnées. Pour cela, on va récupérer la longitude (le X si vous pensez Maths) avec
=PARSEJSON(GET(CONCATENER("https://geo.api.gouv.fr/communes?nom=";ENCODEURL(B2);"&boost=population&fields=code,nom,centre&codePostal=";A2));"get(0).centre.coordinates.get(0)")Pour faire de même pour récupérer la latitude (le Y)
=PARSEJSON(GET(CONCATENER("https://geo.api.gouv.fr/communes?nom=";ENCODEURL(B2);"&boost=population&fields=code,nom,centre&codePostal=";A2));"get(0).centre.coordinates.get(0)")On peut maintenant si on le souhaite appliquer la recette en utilisant par exemple Nominatim (attention ce dernier peut retourner du XML ou du JSON, vous devrez veiller à utiliser du JSON). Vous trouverez ci-dessous quelques exemples. Attention, j’ai limité les résultats à la France avec la chaîne “&country=fr”
Pour la latitude dans Nominatim
=PARSEJSON(GET(CONCATENER("https://nominatim.openstreetmap.org/search?q=";ENCODEURL(B2);"&format=json&county=fr"));"get(0).lat")Pour la longitude dans Nominatim
=PARSEJSON(GET(CONCATENER("https://nominatim.openstreetmap.org/search?q=";ENCODEURL(B2);"&format=json&county=fr"));"get(0).lon")Pour le “display_name” dans Nominatim
=PARSEJSON(GET(CONCATENER("https://nominatim.openstreetmap.org/search?q=";ENCODEURL(B2);"&format=json&county=fr"));"get(0).display_name")Pour la “class” dans Nominatim
=PARSEJSON(GET(CONCATENER("https://nominatim.openstreetmap.org/search?q=";ENCODEURL(B2);"&format=json&county=fr"));"get(0).class")Pour le “type” dans Nominatim
=PARSEJSON(GET(CONCATENER("https://nominatim.openstreetmap.org/search?q=";ENCODEURL(B2);"&format=json&county=fr"));"get(0).type")Vous pouvez récupérez l’exemple prêt à utiliser sur http://labs.webgeodatavore.com/partage/sample-libreoffice-getrest-plugin-api-geo-gouv-fr.ods
Il est aussi possible de faire les mêmes opérations avec les adresses en s’appuyant sur l’API de adresse.data.gouv.fr. N’hésitez pas à lire la doc de la fonction PARSEJSON pour pouvoir accéder à l’objet JSON, la structure de ces services variant par rapport à notre exemple.
