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.
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:
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.