How to do it...

Carry out the following steps:

1. As the first test, open your favorite SQL client (psql or pgAdmin), and write a very basic PL/Python function, just using the GoogleV3 geocoding API with geopy. The function will accept the address string as an input parameter and, after importing geopy, it will instantiate a geopy Google Geocoder, run the geocode process, and then return the point geometry, using the ST_GeomFromText function and the geopy output:

        CREATE OR REPLACE FUNCTION chp08.Geocode(address text)
          RETURNS geometry(Point,4326) AS $$
          from geopy import geocoders
          g = geocoders.GoogleV3()
          place, (lat, lng) = g.geocode(address)
          plpy.info('Geocoded %s for the address: %s' % (place, address))
          plpy.info('Longitude is %s, Latitude is %s.' % (lng, lat))
          plpy.info("SELECT ST_GeomFromText('POINT(%s %s)', 4326)" 
                    % (lng, lat))
          result = plpy.execute("SELECT ST_GeomFromText('POINT(%s %s)',
                                4326) AS point_geocoded" % (lng, lat))
          geometry = result[0]["point_geocoded"]
          return geometry 
        $$ LANGUAGE plpythonu; 

2. After creating the function, try to test it:

      postgis_cookbook=# SELECT chp08.Geocode('Viale Ostiense 36, Rome');
      INFO:  Geocoded Via Ostiense, 36, 00154 Rome, 
      Italy for the address: Viale Ostiense 36, Rome
      CONTEXT:  PL/Python function "geocode"
      INFO:  Longitude is 12.480457, Latitude is 41.874345.
      CONTEXT:  PL/Python function "geocode"
      INFO:  SELECT ST_GeomFromText('POINT(12.480457 41.874345)', 4326)
      CONTEXT:  PL/Python function "geocode"
                       geocode 
      ---------------------------------------------------- 
      0101000020E6100000BF44BC75FEF52840E7357689EAEF4440 
      (1 row) 

3. Now, you will make the function a little bit more sophisticated. First, you will add another input parameter to let the user specify the geocode API engine (defaulting to GoogleV3). Then, using the Python try...except block, you will try to add some kind of error management in case the geopy Geocoder cannot manage to return valid results for any reason:

        CREATE OR REPLACE FUNCTION chp08.Geocode(address text,
                                                 api text DEFAULT 'google')
        RETURNS geometry(Point,4326) AS $$
        from geopy import geocoders
        plpy.info('Geocoing the given address using the %s api' % (api))
        if api.lower() == 'geonames':
          g = geocoders.GeoNames()
        elif api.lower() == 'geocoderdotus':
          g = geocoders.GeocoderDotUS()
        else: # in all other cases, we use google
          g = geocoders.GoogleV3()
        try:
          place, (lat, lng) = g.geocode(address)
          plpy.info('Geocoded %s for the address: %s' % (place, address))
           plpy.info('Longitude is %s, Latitude is %s.' % (lng, lat))
          result = plpy.execute("SELECT ST_GeomFromText('POINT(%s %s)',
                                4326) AS point_geocoded" % (lng, lat))
          geometry = result[0]["point_geocoded"]
          return geometry
        except:
          plpy.warning('There was an error in the geocoding process,
                        setting geometry to Null.')
          return None 
        $$ LANGUAGE plpythonu; 

4. Test the new version of your function without specifying the parameter for the API. In such a case, it should default to the Google API:

      postgis_cookbook=# SELECT chp08.Geocode('161 Court Street, 
                         Brooklyn, NY');
      INFO:  Geocoing the given address using the google api
      CONTEXT:  PL/Python function "geocode2"
      INFO:  Geocoded 161 Court Street, Brooklyn, NY 11201, 
             USA for the address: 161 Court Street, Brooklyn, NY
      CONTEXT:  PL/Python function "geocode2"
      INFO:  Longitude is -73.9924659, Latitude is 40.688665.
      CONTEXT:  PL/Python function "geocode2"
      INFO:  SELECT ST_GeomFromText('POINT(-73.9924659 40.688665)', 4326)
      CONTEXT:  PL/Python function "geocode2"
                       geocode2
      ----------------------------------------------------
      0101000020E61000004BB9B18F847F52C02E73BA2C26584440 
      (1 row) 

5. If you test it by specifying a different API, it should return the result processed for the given API. For example:

      postgis_cookbook=# SELECT chp08.Geocode('161 Court Street, 
                         Brooklyn, NY', 'GeocoderDotUS');
      INFO:  Geocoing the given address using the GeocoderDotUS api
      CONTEXT:  PL/Python function "geocode2"
      INFO:  Geocoded 161 Court St, New York, NY 11201 for the address: 161 
             Court Street, Brooklyn, NY
      CONTEXT:  PL/Python function "geocode2"
      INFO:  Longitude is -73.992809, Latitude is 40.688774.
      CONTEXT:  PL/Python function "geocode2"
      INFO:  SELECT ST_GeomFromText('POINT(-73.992809 40.688774)', 4326)
      CONTEXT:  PL/Python function "geocode2"
                       geocode2
      ----------------------------------------------------
      0101000020E61000002A8BC22E8A7F52C0E52A16BF29584440 
      (1 row)

6. As a bonus step, create a table in PostgreSQL with street addresses, and generate a new point PostGIS layer storing the geocoded points returned by the Geocode function.