Skip to content

Instantly share code, notes, and snippets.

@danicarrion
Last active August 29, 2015 14:21
Show Gist options
  • Save danicarrion/3ae87e618e54f59fd9c4 to your computer and use it in GitHub Desktop.
Save danicarrion/3ae87e618e54f59fd9c4 to your computer and use it in GitHub Desktop.
Reverse geocoding after adding a point to a table on CartoDB

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();
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment