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.

CURL implementation in Oracle

Have you ever wondered, you wish you had an Oracle CURL function, no problem, your wish is fulfilled.
PS: This will only work for text files and you can one for binary files too.

CREATE OR REPLACE FUNCTION CURL(i_URL VARCHAR2) RETURN CLOB IS
    -- HTTP Portion
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_clob           CLOB;
    l_text           VARCHAR2(32767);
BEGIN
    -- Initialize the CLOB.
    DBMS_LOB.createtemporary(l_clob, FALSE);
    -- Make a HTTP request and get the response.
    l_http_request  := UTL_HTTP.begin_request(i_URL);
    l_http_response := UTL_HTTP.get_response(l_http_request);
    -- Copy the response into the CLOB.
    BEGIN
        LOOP
            UTL_HTTP.read_text(l_http_response, l_text, 32766);
            DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
        END LOOP;
    EXCEPTION  WHEN UTL_HTTP.end_of_body THEN
        NULL;
    END;
    UTL_HTTP.END_RESPONSE(l_http_response);

    RETURN l_clob;

END CURL;
/