Home > PL/SQL > Quick Geocoding Using Google

Quick Geocoding Using Google

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;
begin

 l_http_req := utl_http.begin_request(
   url => 'http://maps.google.com/maps/geo' ||
          '?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;
G(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
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 apex.oracle.com 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.

Advertisements
Categories: PL/SQL Tags: , , ,
  1. August 10, 2008 at 1:22 pm

    Shows again how simple it is to make a HTTP callout from PL/SQL. The database offers so many possibilities, developers just have to use them or know about them! 🙂

    Patrick

  2. Dan
    August 10, 2008 at 6:41 pm

    Patrick,

    Great post! I’ll be using this a lot!

    Regards,
    Dan

  3. girl-next-door
    August 11, 2008 at 1:31 pm

    Pretty cool! I’m sure we can make use of this in our apps!!

  4. Stew
    August 13, 2008 at 5:28 pm

    Very cool! Thanks for sharing this. I especially appreciate that you provided all the source code on apex.oracle.com too!

  5. April 9, 2009 at 5:30 pm

    Chris

    Greate Apex post! I am trying to get google geocoding to work with at least static maps, for my existing hosted by oracle apex site. All I need is the lat,long for a UK postcode.

    Did the apex.oracle.com admins come back to you? Will they ever let access to 3rd party APIs. It is a shame we are locked up from the world in apex.oracle.com.

    Any news from them, what did they say?

    Many thanks

    Kind Regards

    Kubilay

    • Christopher Beck
      April 9, 2009 at 8:52 pm

      Thanks. I have made use of this a few times in apps that I have written. Getting google to display its dynamic maps in APEX is just as easy too.

      As for the UK geocoding, should not be an issue. Just try out my app with your UK address and you should get back the proper lat/long.

  6. November 13, 2009 at 7:28 am

    G’day Christopher,

    I was also trying to experiment with the google geocode API in apex.oracle.com, but came across the ORA-24247 issue.

    I’ve searched the OTN forums for information on this to no avail. I was wondering what feedback you got?

    Cheers.

    • Christopher Beck
      November 13, 2009 at 11:48 am

      Scott,

      The admins have informed me that http from apex.oracle.com is blocked for everyone. There are just too many possible security and performance issues if it was wide open for the general public to use. Same goes for all the network protocols ( ftp, smtp, … ) out of the database hosted on apex.oracle.com

      chris.

  7. willian
    November 25, 2010 at 6:36 pm

    Hi Chris, congrats for the post man!!

    i´m trying to use this function, i´ve created the function passsing all the parameters to the url,
    but i´m facing a problem: when i query it i get the following error

    ORA-06512: em “SYS.GOOGLE_GEOCODE”, line 17
    ORA-06512: em line 7

    i don´t know what i am doing wrong…

    thanks for the feedback!

    • Christopher Beck
      November 27, 2010 at 5:03 pm

      Without more info, I can not determine what is going on. But I am alarmed that you installed the function into the SYS schema. That is a huge no-no! You should never install anything into that schema. You need to make a different schema, install it into that schema and make sure that that schema has to proper privileges to make HTTP calls. Once all that is set up, then we can try and debug and figure out why you are getting errors.

  1. No trackbacks yet.

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: