Le portail francophone de la géomatique

Bonsoir,

PG en tant que tel n'est pas outillé pour accéder à des URL, des API.
Mais il supporte de nombreux langages qui permettent ces accès, à travers des fonctions écrites dans ces langages.

Une solution classique est de développer un serveur d'application (java, javascript, python, php, ...) qui fait les accès à l'API et le lien vers PG/Pgis pour stocker ou manipuler les données.

Je préfère souvent une solution intégrée à PG, soit à travers une fonction qui réalise les accès, soit à travers un FDW (foreign data wrapper). Je vous laisse regarder le FDW Unicorn, par ex, qui permet en python de développer une interface entre le service et PG, permettant d'accèder à l'API à travers du sql (select * from foreign_table_qui_appelle_lapi).

Ca se met en place assez facilement et convient bien si ces appels à l'API doivent etre nombreux et/ou fréquents.

Il existe aussi des FDW génériques qui savent "taper" sur une API ou un web service avec un peu de conf.

Avec une fonction, c'est encore plus simple: vous développez la fonction (par ex en python, langage vraiment pratique pour manipuler les données et disposant de nombreuses API) qui appelle l'API et traite les données.

Voici un exemple de fonction permettant de géocoder des adresses dans PG. Elle se base sur le geocodeur en ligne BANO, prend une requete SQL formatée avec certaines colonnes (rue, num, ville, cp, ...), transmet ces données à l'API du geocodeur, récupère les données (format CSV), les convertit en JSON et les renvoie à PG.
La concision du code Python pour faire tout cela (ca doit pouvoir etre bien plus concis: je suis nul en python...) est remarquable je trouve.

(le code execute la requête passée en param, formate le tout en CSV (en mémoire), puis envoie ce CSV sur l'URL du geocodeur.
Le résultat (CSV) est converti en JSON et renvoyé par la fonction: on peut alors convertir les info JSON en geométries postgis.)

create or replace function geocode_add(query text)
  RETURNS setof jsonb as
$$
import csv
import io
from collections import OrderedDict
import requests
import json

with io.BytesIO(b"") as stream:
    writer = csv.writer(stream, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    writer.writerow(["ids", "voie", "ville", "cp"])

    for row in plpy.cursor(query):
        # writes row in csv to csv stream
        # Write CSV Header, If you dont need that, remove this line
        writer.writerow([row['ids'], row['voie'], row['ville'], row['cp']])

    requests_session = requests.Session()
    kwargs = {
        'data': OrderedDict([
            ('columns', ['voie', 'ville', 'cp']),
            ('postcode', 'cp')
        ]),
        'method': 'post',
        'files': OrderedDict([
            ('data', ('addpg.csv',stream.getvalue()))
        ]),
        'stream': True,
        'url': 'https://api-adresse.data.gouv.fr/search/csv/'
    }

    response = requests_session.request(**kwargs)
    for row in csv.DictReader(response.text.encode('utf-8').splitlines()):
        yield json.dumps(row)

  $$ language plpythonu VOLATILE;

Elle s'utilise comme cela:

select * from geocode_add('select array_agg(s.id) as ids, voie, ville, cp from table_adresse');

Nicolas

Je n'ai pas précisé, mais la création d'une fonction en python nécessite d'installer l'extension plpython3u.

Nico

Je vous avais pas mis le bon lien :
https://db-app-builder.gitlab.io/docume … s/doc-api/
J'ai corrigé aussi dans mon premier message

Dernière modification par AlineC (Sat 05 December 2020 18:34)

Bon,

Pour l'instant je rame encore pour bien comprendre comment ça fonctionne.
C'était tellement simple de se connecter avec tous types de bases de données avec des connexions ODBC ou autres ...
Je sais pas où on met le token ?
Est ce que je peux facilement me connecter via par exemple power bi (je cherche avec les outils que j'ai sous la main).
Mon collègue est arrivé à se connecter à une table mais je croyais qu'avec une API on pouvait aussi "charger" la structure des données ?

Donc, même si j'ai l'air un peu niaise sur le coup, n'hésitez pas à m'apporter quelques compléments d'info, je vais finir pas comprendre !
Merci d'avance

Merci Nicolas,

Donc je comprends qu'il y a API et API !
Qu'il faut regarder la documentation au début plutôt qu'après avoir ramé un moment (on l'a fait l'autre jour et à la fin j'ai vu que le connecteur REST trouvé avait un "sauf" donc ça marchait pas)
On va continuer à faire des tests mais on est sur la bonne voie

A +