在Oracle 資料庫使用 Google Geocoding (地址定位)

Google Geocoding API 的說明網頁請參考：http://code.google.com/intl/zh-TW/apis/maps/documentation/geocoding/
我所關心的是輸入中文(台灣)的地址，Google Geocoding回傳座標，取得經緯度之後，轉換成Oracle SDO_GEOMETRY資料型態，更新該欄位。
首先，測試以下URL，以確定必要的資訊都已輸入
http://maps.googleapis.com/maps/api/geocode/xml?address=台北市忠孝西路一段66號&language=zh-tw&region=tw&sensor=true
其回應如下：

紅色框的地方就是我要的座標(預設為WGS84)。
確定了使用Google Geocoding的傳遞參數之後，接下來就要在資料庫中撰寫一函式，使用HTTP呼叫Google Geocoding API來取得座標並轉化成SDO_GEOMETRY資料型態。
在資料庫內要能執行HTTP的呼叫，必需進行以下的設定：

以SYS登入資料庫賦予使用者(這裏使用'MVDEMO'))能夠執行UTL_HTTP程式套件的權限
 grant execute on utl_http to mvdemo;

以SYSTEM登入建立對外存取網站ACL(Access Control List)清單
connect system/password
-- Step 1: Create the Access Control List and Its Privilege Definitions
 BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'maps_googleapis_com.xml',  -- ACL XML檔案，自己定義一個名稱
  description  => 'Google Maps API ACL', -- 說明該ACL XML檔案的用途
  principal    => 'MVDEMO',  -- 指定管理的使用者或角色(role)，在這裏是以mvdemo使用者
  is_grant     => TRUE,
  privilege    => 'connect', -- 對外網站的權限
  start_date   => null,
  end_date     => null);
END;
/

將要可以對外連結的網站加入ACL
Step 2: Assign the Access Control List to One or More Network Hosts
BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'maps_googleapis_com.xml',   -- 要對應上一步剛剛建立的ACL XML檔案名稱
  host        => 'maps.googleapis.com',   -- Google Geo-coding的網站位址
  lower_port  => null, -- 允許對外連結的網路埠(port)的範圍；NULL表示不設限
  upper_port  => null);
END;
如果會透過 PROXY SERVER對外連接，不要忘了要加入允許存取網站的清單
BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl         => 'maps_googleapis_com.xml',
  host        => 'proxy.mycompany.com',  -- HTTP Proxy Server (http://host[:port]/)
  lower_port  => null,
  upper_port  => null);
END;

建立取得地址定位的函式
create or replace
function google_geocoding
( p_address in varchar2 default '台北市忠孝西路一段66號')
return sdo_geometry -- 回傳 Oracle 空間資料型態
deterministic
as
  http_req_url varchar2(32767);
  http_req utl_http.req;
  http_resp utl_http.resp;
  http_resp_html varchar2(32767);
  resp_xml XMLType;
  latitude number;
  longitude number;
begin
  UTL_HTTP.SET_PROXY (' proxy.mycompany.com ');  -- 設定 HTTP Proxy Server
  http_req_url:='http://maps.googleapis.com/maps/api/geocode/xml?language=zh-tw&region=tw&sensor=true&address=' || p_address; -- 準備送的URL參數
  http_req:= utl_http.begin_request(http_req_url); -- 開始進行HTTP 的傳送要求，預設方式為GET
-- 設定 HTTP Header
  UTL_HTTP.SET_HEADER(http_req, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.SET_HEADER(http_req,'Content-Type','text/xml;charset=UTF-8');
  http_resp:= utl_http.get_response(http_req); -- 取得 HTTP 回傳
  utl_http.read_text(http_resp, http_resp_html); -- 讀取 HTTP 回傳內容
  utl_http.end_response(http_resp); -- 結束HTTP的請求
  -- 將讀取的HTTP 內容轉換成XML 文件
  resp_xml:= XMLType.createXML(http_resp_html);
  -- 讀取 Latitude, Longitude 的內容並將其轉換成數字
  latitude:= resp_xml.extract('/GeocodeResponse/result/geometry/location/lat/text()').GETNUMBERVAL();
  longitude:= resp_xml.extract('/GeocodeResponse/result/geometry/location/lng/text()').GETNUMBERVAL();
  return point(longitude,latitude); -- 傳回"點"空間位置
  EXCEPTION
    WHEN others THEN
      return point(NULL,NULL);
end;

測試：
select google_geocoding() from dual;
GOOGLE_GEOCODING()
---------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(121.515241,25.046139,NULL),NULL,NULL)


select google_geocoding('台北市政府') from dual;
GOOGLE_GEOCODING('台北市政府')
-----------------------------------------------------------------------------------------MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(121.5644722,25.0372787,NULL),NULL,NULL)


select google_geocoding('不知道的地方') from dual;
GOOGLE_GEOCODING('不知道的地方')
----------------------------------------------------------------------------MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(NULL,NULL,NULL),NULL,NULL)