Reverse geocoding after adding a point to a table on CartoDB

README.md

Requirements

• A table on PostgreSQL/PostGIS (tested with CartoDB) with the following text fields: address, city, country, zipcode.
• Python functions are enabled.
• pygeocoder is available to the python function

Usage

Once the function has been created, the trigger must be created like this (assuming the geometry column is called the_geom:

CREATE TRIGGER geocode_trigger BEFORE INSERT OR UPDATE OF the_geom ON your_table FOR EACH ROW EXECUTE PROCEDURE geocode();

geocode.sql

CREATE OR REPLACE FUNCTION geocode()
RETURNS trigger AS
$$
from pygeocoder import Geocoder
if TD["new"]["the_geom"] is None:
    return
google_geocoder = Geocoder()
res = plpy.execute("select ST_X('" + TD["new"]["the_geom"] + "') as x, ST_Y('" + TD["new"]["the_geom"] + "') as y")
geoc = google_geocoder.reverse_geocode(res[0]["y"], res[0]["x"])   
TD["new"]["zipcode"] = geoc.postal_code
TD["new"]["city"] = geoc.city
TD["new"]["country"] = geoc.country
TD["new"]["address"] = geoc.street_address or geoc.route or geoc.intersection or geoc.political
return "MODIFY"
$$
LANGUAGE plpython2u SECURITY DEFINER VOLATILE;