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 <jeremy...@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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users