-- creation fonction BAN postgis
-- Alain from pasq.fr
-- Date: 16 juil. 2023
-- Time: 11:19:49

-- Extension requise : plpython3u

-- DROP EXTENSION plpython3u;
CREATE EXTENSION plpython3u SCHEMA "pg_catalog";

-- accès à internet requis, adresse.gouv.fr

-- creation fonction de geocodage dans postgis
CREATE OR REPLACE FUNCTION public.geocode(address text)
RETURNS geometry
AS $$
  import requests
  try:
    payload = {'q' : address}
    base_geocode = 'https://api-adresse.data.gouv.fr/search/'
    r = requests.get(base_geocode, params = payload)
    coords = r.json()['features'][0]['geometry']['coordinates']
    lon = coords[0]
    lat = coords[1]
    geom = f'SRID:4326;POINT({lon} {lat})'
  except Exception as e:
    plpy.notice(f'address failed: {address}')
    geom = None
  return geom
$$
LANGUAGE 'plpython3u';

-- geocodage inverse
CREATE OR REPLACE FUNCTION geocode_reverse(lon text,lat text)
RETURNS TEXT
AS $$
  import requests
  try:
    payload = {'lon':lon, 'lat':lat}
    base_geocode = 'https://api-adresse.data.gouv.fr/reverse/'
    r = requests.get(base_geocode, params = payload)
    ad = r.json()['features'][0]['properties']['label']
  except Exception as e:
    ad = 'erreur'
  return ad
$$
LANGUAGE 'plpython3u';
