Home > PL/SQL > More Google Geocoding

More Google Geocoding

Ok, for those who liked the prior post on Google geocoding via HTTP, you will enjoy this as well.  It builds on what was done in that example but retrieves even more information from google.

Before we used an output type of CSV and that only gave us back the LATITUDE and LONGITUDE.  But there is more information that we can derive from the geocoding service if we use a different output type.  If we request the result in XML, then we are given the complete address as well as the longitude and latitude.  But we have to do a little extra work to parse out the results.

I have written a PL/SQL object type to do just that.  Why an object type and not a Package?  Personal preference.  In this case, it just seemed to feel right to make it an object.  With some simple code factoring, it could easily be converted into a package.  But since most PL/SQL developers don’t code object types, thought it might be interesting.

Let’s take a look at the object’s specification.


create or replace
type google_geocode_type as object (

  -- Attributes
  response xmlType,
  search varchar2(32767),

  -- Constructor Methods
  constructor function google_geocode_type(
    p_search varchar2 default null ) return self as result,

  -- Execute Geocode
  member procedure execute(
    p_search varchar2 default null ),

  -- Getter Routines
  member function get_result_count return number,

  member function get_geometry(
    p_sequence number default 1 ) return sdo_geometry,

  member function get_latitude(
    p_sequence number default 1 ) return number,

  member function get_longitude(
    p_sequence number default 1 ) return number,

  member function get_address(
    p_sequence number default 1 ) return varchar2,

  member function get_street(
    p_sequence number default 1 ) return varchar2,

  member function get_city(
    p_sequence number default 1 ) return varchar2,

  member function get_state(
    p_sequence number default 1 ) return varchar2,

  member function get_zipcode(
    p_sequence number default 1 ) return varchar2,

  member function get_county(
    p_sequence number default 1 ) return varchar2,

  member function get_country(
    p_sequence number default 1 ) return varchar2

);

The object’s body is a bit long and I will not post all of it here but will make it all available for download.  But I will post a few of the member functions and talk about what they are doing.


constructor function google_geocode_type(
  p_search varchar2 default null ) return self as result is
begin
  self.search := p_search;
  return;
end;

For those who are not familiar with PL/SQL object types, a constructor function is the method called when you create a new instance of the object (just like java).  Every object has a default constructor, but you can define your own so to manage the object’s creation as well as defaulting certain object attributes.  For me, I did not want the use to have to supply a value for the attribute RESPONSE so I defined my own.

The EXECUTE() method is very similar to the the prior example.  I made a minor change to handle the case where we get back more than 32K of data.

member procedure execute( p_search varchar2 default null ) is
  l_http_req  utl_http.req;
  l_http_resp utl_http.resp;
  l_response long;
  l_clob clob;
begin
  if p_search is not null then
    self.search := p_search;
  end if;
  l_http_req := utl_http.begin_request(
                  url => 'http://maps.google.com/maps/geo' ||
                  '?q=' || utl_url.escape( self.search ) ||  -- address to geocode
                  '&output=xml' ||  -- XML return type
                  '&key=abcdef' );  -- site key
  l_http_resp := utl_http.get_response( l_http_req );
  begin
    loop
      utl_http.read_text( l_http_resp, l_response );
      l_clob := l_clob || l_response;
    end loop;
  exception
    when utl_http.end_of_body then
     null;
   end;
  utl_http.end_response( l_http_resp );
  self.response := sys.xmlType.createXML( l_clob );
end execute;

Now depending on how specific the address you supply, you may get more than one answer.  I sent in a value of “1600 Pennsylvania Ave” and it returned 10 results.  If I made that search more specific, say “1600 Pennsylvania Ave, DC”, then I get just a single result.  But since we don’t know how many results we will get, we need to parse out all the results.  So the first thing we need to know is how many results came back.

member function get_result_count return number is
  l_count number;
begin
  select count(*)
    into l_count
    from table( XMLSequence( extract(
                 self.response,
                 '//Placemark',
                 'xmlns="http://earth.google.com/kml/2.0"' ) ) );
  return l_count;
end get_result_count;

This method calculates how many results are in the XML returned by finding each <Placemark> section,  and then counting them using the XMLSequence and Table functions in SQL.

Now we know how many results, we can begin to ask for the individual values.

member function get_zipcode(
  p_sequence number default 1 ) return varchar2 is
begin
  return self.response.extract(
           '//Placemark[@id="p' || p_sequence || '"]',
           'xmlns="http://earth.google.com/kml/2.0"' ).extract(
             '//PostalCodeNumber/text()',
             'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"' ).getStringVal();
end get_zipcode;

Here in the GET_ZIPCODE() method, we use the EXTRACT() method on the XMLType variable RESPONSE and XPATH searching to retrieve the proper zipcode.  I had to do and extract() of an extract() because the relavant peices of data were in different namespaces in the XML.  (My XPATH searching ability is not that strong so there may be an easier way?  If you know of one, let me know.)

All the other get methods are similiar to this example.

Now let’s see how we would use this.

  1  declare
  2    g google_geocode_type := new google_geocode_type();
  3  begin
  4    g.execute( '1910 Oracle way, reston' );
  5      dbms_output.put_line( 'lat/lon: ' ||
  6                            g.get_latitude()||'/'||
  7                            g.get_longitude() );
  8      dbms_output.put_line( 'Address: ' || g.get_address() );
  9      dbms_output.put_line( 'County: ' || g.get_county() );
 10* end;
SQL> /
lat/lon: -77.351976/38.954872
Address: 1910 Oracle Way, Reston, VA 20190, USA
County: Fairfax County

PL/SQL procedure successfully completed.

Given just the street and the city, google was able to determine the full address and the latitude and longitude.

Now let’s see what happens when we supply even less information

  1  declare
  2    g google_geocode_type := new google_geocode_type();
  3  begin
  4    g.execute( '1600 Pennsylvania Ave' );
  5    for i in 1 .. g.get_result_count() loop
  6      dbms_output.put_line( 'lat/lon: ' ||
  7                            g.get_latitude(i)||'/'||
  8                            g.get_longitude(i) );
  9      dbms_output.put_line( 'Address: ' || g.get_address(i) );
 10    end loop;
 11* end;
SQL> /
lat/lon: -90.229033/38.617594
Address: 1600 Pennsylvania Ave, St Louis, MO 63104, USA
lat/lon: -76.880242/42.031789
Address: 1600 Pennsylvania Ave, Pine City, NY 14871, USA
lat/lon: -82.984848/42.36331
Address: 1600 Pennsylvania St, Detroit, MI 48214, USA
lat/lon: -76.634388/39.30307
Address: 1600 Pennsylvania Ave, Baltimore, MD 21217, USA
lat/lon: -96.77534/32.759033
Address: 1600 Pennsylvania Ave, Dallas, TX 75215, USA
lat/lon: -81.620803/38.360844
Address: 1600 Pennsylvania Ave, Charleston, WV 25302, USA
lat/lon: -80.27183/40.687529
Address: 1600 Pennsylvania Ave, Monaca, PA 15061, USA
lat/lon: -79.8573/40.362383
Address: 1600 Pennsylvania Ave, West Mifflin, PA 15122, USA
lat/lon: -117.32709/34.084866
Address: 1600 Pennsylvania Ave, Colton, CA 92324, USA
lat/lon: -75.185584/40.121061
Address: 1600 Pennsylvania Ave, Oreland, PA 19075, USA

PL/SQL procedure successfully completed.

There sure are a lot of 1600 Pennsylvania Ave’s.  But if you notice, the most famous one is not even in the list?  Why?  I have NO idea???  But if we make our search a bit more specific…

  1  declare
  2    g google_geocode_type := new google_geocode_type();
  3  begin
  4    g.execute( '1600 Pennsylvania Ave, dc' );
  5    for i in 1 .. g.get_result_count() loop
  6      dbms_output.put_line( 'lat/lon: ' ||
  7                            g.get_latitude(i)||'/'||
  8                            g.get_longitude(i) );
  9      dbms_output.put_line( 'Address: ' || g.get_address(i) );
 10    end loop;
 11* end;
SQL> /
lat/lon: -77.036698/38.897102
Address: 1600 Pennsylvania Ave NW, Washington, DC 20006, USA

PL/SQL procedure successfully completed.

… we find that famous one that will be getting a new family moving in this January.

I hope this was interesting and helpful.  Again, if you want to get the code and try it out yourself, its right here.  And as always, if you have any questions, just ask.

Advertisements
Categories: PL/SQL Tags: , , , ,
  1. August 16, 2008 at 1:51 pm

    Your blog is interesting!

    Keep up the good work!

  2. Levi
    February 18, 2009 at 10:24 pm

    how compile whith &ouput if this not variable. But he think is.

    what i do?

    • Christopher Beck
      February 19, 2009 at 2:59 am

      Well As you seem to know, using an ‘&’ in a SQL script causes sqlplus to treat it as a substitution variable and promt the user for a value. You can override this defaut functionality by either

      1. redefining the substitution indicator
      set define [some new character]

      -OR-

      2. turning off variable substitution
      set define off

      In this case, I would suggest the second option since we don’t need to do any substitution for this script to work.

      So just issue the sqlplus command set define off before you run the script and it should then compile without issue.

      Hope this helps.

      chris.

  3. Mette Stephansen
    June 13, 2010 at 6:28 pm

    Hi there

    I just love this type and the code – but it has a little flaw. I’m in Denmark, using . as thousand sep and , as decimal point.

    I have changed the get_latitude, get_longitude and get_geometry to have a to_number with a format of “99.9999999”. Otherwise I get a numeric error. Now it works lika a charm.

    And another strange thing:

    It does not accept national chars in the streetnames – try “Blåbærhaven” – no response, but when I try “Blaabaerhaven” – i get “blåbærhaven” ?

    I assume that it’s google :-))

    ANd when I try just “Holbæk” (which is a city) I get an XML error ora-31011.

    But thanks again for the code.

    regards
    Mette

  4. January 25, 2013 at 7:57 pm

    I’m not sure where you are getting your info, but good topic. I needs to spend some time learning much more or understanding more. Thanks for magnificent info I was looking for this info for my mission.

  5. January 27, 2013 at 9:38 pm

    I’m truly enjoying the design and layout of your blog. It’s a very easy on the eyes which makes it much more enjoyable
    for me to come here and visit more often. Did you hire out a designer to create your theme?

    Superb work!

  1. October 20, 2008 at 10:30 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: