[postgis-users] Geocoding using www_fdw and Google APIs

[Jeremy]

Hi all,

I've got a question on how to define a server & foreign table to pull
back latitude and longitude from a street address. I've been working
from examples like twitter or google searches - all of which return a
fairly flat data structure. The structure of the geocode return is
quite different, however. For instance, it contains a field 'geometry'
which in turn contains a field 'location' which finally contains
values for 'lat' and 'long'.

The only information I'm interested in is the geometry:location:lat,
geometry:location:long and the geometry:locaiton_type

I'm not sure how to structure the fields in the foreign table
www_fdw_google_geocode so that this will work. Any advice would be
greatly appreciated. Please see end of message for code, command and
sample xml response.

Thanks in advance,

Jeremy.


-Code I have so far----------------------------------------------

DROP EXTENSION IF EXISTS www_fdw CASCADE;
CREATE EXTENSION www_fdw;

CREATE SERVER www_fdw_server_google_geocode FOREIGN DATA WRAPPER www_fdw
    OPTIONS (uri 'http://maps.google.com/maps/api/geocode/xml');
CREATE USER MAPPING FOR current_user SERVER www_fdw_server_google_geocode;

CREATE FOREIGN TABLE www_fdw_google_geocode (
    /* parameters used in request */
    sensor text,
    address text,

    /* fields in response */
    /****************************************************/
	/* NOT SURE HOW TO STRUCTURE THIS PART OF THE TABLE */
	/****************************************************/
    /* The response data is not flat. Please see sample data at end */

) SERVER www_fdw_server_google_geocode;

-/Code I have so far----------------------------------------------

-Command I intend to use------------------------------------------
postgres=# SELECT latitude, longitude, location_type from
www_fdw_google_geocode where sensor='false' AND address='2 Some
street, somewhere, somewhereville' LIMIT 1;
-/Command I intend to use-----------------------------------------


-Sample response from geocoding web service----------------------

<?xml version="1.0" encoding="UTF-8"?>
<GeocodeResponse>
<status>OK</status>
 <result>
  <type>street_address</type>
  <formatted_address>2 Southbank Blvd, Southbank VIC 3006,
Australia</formatted_address>
  <address_component>
   <long_name>2</long_name>
   <short_name>2</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Southbank Blvd</long_name>
   <short_name>Southbank Blvd</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Southbank</long_name>
   <short_name>Southbank</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Victoria</long_name>
   <short_name>VIC</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Australia</long_name>
   <short_name>AU</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>3006</long_name>
   <short_name>3006</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-37.8224465</lat>
    <lng>144.9630868</lng>
   </location>
   <location_type>ROOFTOP</location_type>
   <viewport>
    <southwest>
     <lat>-37.8237955</lat>
     <lng>144.9617378</lng>
    </southwest>
    <northeast>
     <lat>-37.8210975</lat>
     <lng>144.9644358</lng>
    </northeast>
   </viewport>
  </geometry>
  <partial_match>true</partial_match>
 </result>
 [possibly more results after the first - we only want the first one]
</GeocodeResponse>

-/Sample response from geocoding web service---------------------