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');