Gratefully making use of what others have provided, i.e. geopy, Google and plpythonu.
Type to hold result of geocoding:
CREATE TYPE geocoding AS ( place text, latitude DOUBLE PRECISION, longitude DOUBLE PRECISION ); |
Function that does the actual geocoding (to be extended with more vendors. Hint: look at geopy wiki). Takes an (arbitrary) input string to be geocoded:
CREATE OR REPLACE FUNCTION python_geocode ( input text, vendor text DEFAULT 'google' ) RETURNS SETOF geocoding AS $$ import time from geopy import geocoders # https://code.google.com/p/geopy/wiki/GettingStarted time.sleep(0.2) # TODO: Add other available vendors, e.g. Yahoo. if vendor.lower() == 'google': geocoder = geocoders.GoogleV3() else: raise ValueError("Invalid geocoder: %s" % vendor) try: for res in geocoder.geocode(input, exactly_one=False): yield {'place': res[0], 'latitude': res[1][0], 'longitude': res[1][1]} except: pass $$ LANGUAGE plpythonu VOLATILE; |
Example:
SELECT place, ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) FROM python_geocode('Kostas'); |