[postgis-users] Geocoding using www_fdw and Google APIs

[Paul Ramsey]

Wow, fdw_www, that's even crazier than my idea...

http://blog.opengeo.org/2012/04/20/http-for-postgresql/

However, my post includes a working example of geocoding off of
Google. It's still a crazy scheme, running a blocking request (curl
HTTP call) inside your database transaction.

P.

On Mon, Oct 8, 2012 at 10:15 PM, Jeremy <jeremy001 at gmail.com> wrote:
> 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---------------------
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users