Archive for August 5, 2008

Quick Geocoding Using Google

August 5, 2008 10 comments

Location based services are all the rage these days.  Everyone is including it into their applications and there are many ways to accomplish this.  Oracle supports geocoding in the database but you need to own the geocoding dataset.  To get that dataset, you would probably need to purchase it from some provider like NAVTEQ.

But there are services out there, like Google, that allows us common folk to use its data, but you have to know how to ask for it.  Google offers a ton of Javascript APIs for mapping and geocoding, but that does nothing for us working in the database with PL/SQL.  I routinely want to geocode the addresses in a table on insert and update via a trigger.  Javascript is not going to help you here.

Luckily, Google supports geocoding via HTTP and I have written a simple function to access it.  ( Oracle also supports a geocoding solution via HTTP and has a hosted site to support it.  I will blog about it in the future. )

create or replace
function google_geocode( p_address varchar2 ) return sdo_geometry is
 l_http_req  utl_http.req;
 l_http_resp utl_http.resp;
 l_response long;
 l_latlon long;

 l_http_req := utl_http.begin_request(
   url => '' ||
          '?q=' || utl_url.escape( p_address ) ||  -- address to geocode
          '&output=csv' ||                         -- simplest return type
          '&key=abcdef' );                         -- Google API site key

 l_http_resp := utl_http.get_response( l_http_req );
 utl_http.read_text( l_http_resp, l_response );
 utl_http.end_response( l_http_resp );
 l_latlon := substr( l_response, instr( l_response, ',', 1, 2 ) + 1 );

 return sdo_geometry(
          2001, 8307,
          sdo_point_type( to_number( substr( l_latlon, instr( l_latlon, ',' )+1 )),
                          to_number( substr( l_latlon, 1, instr( l_latlon, ',' )-1 )),
                          null ),
          null, null );
end google_geocode;

That’s it.  It’s not that complicated.  You just need to send via HTTP the address and you will get back a comma separated string which we parse out the latitude and longitude and stuff into a sdo_geometry type.

I’ll quickly dissect the code.

Line 9 – Initializing the HTTP call.  The URL includes 3 parameters ( q, output and key )

  • q is the address string to be geocoded.
  • output is the format that we want the out returned in.  I chose csv.  It’s the simplest for this example.
  • key is the your Google API key.  ( Read more about getting a Google API key ).

Lines 15-17 – Sending the request and fetching the result via HTTP.  The result that the comes back is in the form of:

  • HTTP_return_code,accuracy,latitude,longitude

Line 18 – Substring the latitude and longitude.

Line 20 – Creating and returning the SDO_GEOMETRY

Now you can call this from your pl/sql code in the database, or code in your ApEx app ( since that too is stored and run in the database ) and geocode any address.  Just like this:

SQL> select google_geocode( '1910 Oracle Way, Reston' ) g
  2    from dual;
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-77.351976, 38.954872, NULL), NULL, NULL)

I tried to set up a quick demo of this on here, but I need to get the access to run UTL_HTTP via setting up an ACL. I am trying to see if I can get the admins to allow that for me.

If you want to read more about the options Google offers for geocoding via HTTP, you can read about it here.

As always, if you have any questions, just ask.

Categories: PL/SQL Tags: , , ,