Geocode from PostGIS using Python

geocode-addok-python.sql

CREATE TYPE geocoded_address AS (
  city text,
  citycode text,
  name text,
  postcode text,
  score text,
  type text,
  lon numeric,
  lat numeric
);

CREATE FUNCTION addok_geocoder_set(param_addr text)
  RETURNS SETOF geocoded_address
AS $$
import requests

r = requests.get('http://api-adresse.data.gouv.fr/search/?q=%s' % param_addr)

if len(r.json()['features']) > 0:
    features = []
    for feat in r.json()['features']:
        props = feat['properties']
        city, citycode, name, postcode, score, type = props['city'], props['citycode'], props['name'], props['postcode'], props['score'], props['type']
        (latitude,longitude) = feat['geometry']['coordinates']
        features.append([city, citycode, name, postcode, score, type, latitude,longitude])
    return list(features)
$$
LANGUAGE 'plpython3u';


-- Try it with
SELECT * FROM addok_geocoder_set('rue de malville, Nantes');

-- You can also look at the project 'pg_frapi' with same intent but based on plsh https://github.com/adauhr/pg_frapi

You should cache that in a table to avoid having each session performing a synchronous call to the webservice in parallel

Each table has an implied composite type matching its definition in PostgreSQL. So you just have to create a table instead of a type and modify the function to have it look inside the table for a matching record before trying the webservice.