Geo Coding address from Oracle

Have you ever dreamed about Geo Coding an address using Google Geo Location services through Oracle, your dream has come true.


WITH GEOCODE AS(
    SELECT UTL_HTTP.REQUEST('http://maps.googleapis.com/maps/api/geocode/xml?address='|| replace('1 Main St,Trumbull,CT-06611',' ','+') || '&sensor=false') GEO_XML
     FROM DUAL
)
SELECT EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/status') STATUS
     , EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/result[position()=1]/geometry/location/lat') LATITUDE
     , EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/result[position()=1]/geometry/location/lng') LONGITUDE
     , GEO_XML
  FROM GEOCODE
WHERE EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/status') = 'OK'

Warning 1: You have to substitute spaces with '+' in the address, otherwise the request will fail.

Warning 2: Some time Google may return bigger XML, however UTL_HTTP.REQUEST will return only the first 2000 characters and it will thrown an error. To over come this issue, replace UTL_HTTP.REQUEST with my dirty version of Oracle CURL function(See my prior blog)

Warning 3: Read the licensing requirements before using Google geocoding services, you might need an business license to use this service.