在Oracle Spatial Developer Guide上，可以查到 SDO_UTIL.FROM_KMLGEOMETRY. 在說明上可以看到這個函數是將KML轉換成Oracle DSO_Geometry的資料型態。如果再細看一下範例，SDO_UTIL.FROM_KMLGEOMETRY 只支援 OGC KML 2.1文件，並不包括 Google extension namespace.
所以，像是拿到這樣子的KML，是沒有辦法簡單地運用SDO_UTIL.FROM_KMLGEOMETRY直接轉換成SDO_Geometry。
<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>freeway</name>
<description />
<Folder>
<Placemark>
<name>nfb0001</name>
<description />
<styleUrl>#style24</styleUrl>
<LineString>
<tessellate>1</tessellate>
<coordinates>
121.735260,25.122271,0.000000
121.734543,25.121410,0.000000
121.733559,25.120380,0.000000
121.731918,25.118900,0.000000
121.730164,25.117439,0.000000
121.729736,25.117041,0.000000
121.728851,25.115971,0.000000
121.728119,25.114820,0.000000
</coordinates>
</LineString>
</Placemark>
<Placemark>
<name>nfb0003</name>
<description />
<styleUrl>#style90</styleUrl>
<LineString>
<tessellate>1</tessellate>
<coordinates>
121.728111,25.114799,0.000000
121.727661,25.113951,0.000000
121.724380,25.106050,0.000000
121.723717,25.105150,0.000000
121.723061,25.104570,0.000000
121.722137,25.104031,0.000000
121.720886,25.103621,0.000000
</coordinates>
</LineString>
</Placemark>
</Folder>
</Document>
</kml>
所以我的想法是：先將"KML"載入Oracle XMLTable，再使用XPath截取所要的欄位，放入對應的表格與欄位，而相關的空間資訊再用SDO_UTIL.FROM_KMLGEOMETRY轉成SDO_Geometry
觀察上面範例，我會建立一表格名稱"FREEWAY"其中有欄位"NAME", "DESCRIPTION", "STYLEURL"以及"GEOM"。GEOM的資料型態為SDO_GEOMETRY。
CREATE TABLE FREEWAY
(
NAME VARCHAR2(7) NOT NULL
, STYLEURL VARCHAR2(8)
, DESCRIPTION VARCHAR2(4000)
, GEOM MDSYS.SDO_GEOMETRY
);
步驟：
將KML載入XML Table
可參考Load XML Documents
CREATE TABLE KML_TMP OF XMLType
XMLTYPE STORE AS BINARY XML;
CREATE DIRECTORY xmldir AS path_to_folder_containing_XML_file;（略）
使用SYS授予使用者讀取XMLDIR的權限
GRANT READ, WRITE ON DIRECTORY TO username;
登入該使用者，載入KML 至KML_TMP表格
INSERT INTO KML_TMP VALUES (XMLType(bfilename('XMLDIR', 'freeway.kml'), nls_charset_id('AL32UTF8')));
轉換對應至表格相關的欄位與SDO_GEOMETRY
INSERT INTO freeway(name, description, styleurl, geom)
SELECT kml.name, kml.description, kml.styleurl, SDO_UTIL.FROM_KMLGEOMETRY(TO_CLOB(kml.geom)) as geom
FROM kml_tmp,
XMLTable('declare default element namespace "http://earth.google.com/kml/2.0"; (: :)
/kml/Document/Folder/Placemark'
PASSING OBJECT_VALUE
COLUMNS
name VARCHAR2(7) PATH 'declare default element namespace "http://earth.google.com/kml/2.0"; (: :) name',
description VARCHAR2(4000) PATH 'declare default element namespace "http://earth.google.com/kml/2.0"; (: :) description',
styleurl VARCHAR2(8) PATH 'declare default element namespace "http://earth.google.com/kml/2.0"; (: :) styleUrl',
geom XMLTYPE PATH 'declare default element namespace "http://earth.google.com/kml/2.0"; (: :) LineString'
) kml
;
從KML本身並不描述座標系統，所以轉入SDO_GEOMETRY之後，座標系的值為NULL。所以需要將轉入SDO_GEOMETRY的座標(SDO_SRID)進行設定(異動)；在Oracle DB WGS 84 Geography 3D 的 SDO_SRID 的代號是4327。(請注意所提供的KML為3維空間座標)
UPDATE freeway f
set f.geom.SDO_SRID=4327;
COMMIT;
配合使用Google Map，我們需要是2維的平面，建議設定的SDO_SRID的值為4055
我使用SDO_CS.MAKE_2D來進行轉換
先增加一2維欄位"GEOM2D"
ALTER TABLE FREEWAY
ADD (GEOM2D MDSYS.SDO_GEOMETRY );
UPDATE FREEWAY
SET GEOM2D = SDO_CS.MAKE_2D(GEOM, 4055);
COMMIT;
更新Metadata以及建立空間索引(略)
驗証是否為合法的幾何圖形，發現13356的錯誤 -- points in a geometry are redundant
修正方式:
UPDATE FREEWAY
SET GEOM2D = SDO_UTIL.REMOVE_DUPLICATE_VERTICES(GEOM2D, 0.0000005);
COMMIT;
Thursday, January 10, 2013
Tuesday, December 18, 2012
Linear Reference System 應用 II
之前的範例比較簡單, 只有考慮公車停靠站(Bus Stop). 如果您有注意到, 公車路線是由站與站之間以直線連接起來. 在大部分的情境下, 這樣的公車路線並不存在. 比較正常的路線應該是如下表所顯示的範例:
BUS_SERVICE: 表示服務的公車代號
ROUTE_NAME: 表示公車行駛的方向, 可能為"東向", "西向"; "去程", "回程"...這裏使用"busService1"表示
BUS_SERVICE+ROUTE_NAME 可視為一公車路徑的ID.
Point_Order: 表示路徑上點順序.
Point_Name: 有值的內容表示真正的站名(範例為stop1, stop2...), 沒有值(NULL)則表示為純綷為描述路徑上的站與站之間的銜接, 轉折..的點位
LAT, LON 則表示座標
為了更能夠彈性地表示/描述路徑應用, 我用三個資料表呈現. 其三個表之間ER-Diagram 概念範如下(請注意, 這裏的PK表示是有問題的, 真實的情況會隨需求而有變化):
ROUTES_INFO: 表示所有公車路徑的描述
STOPS_INFO: 表示真正有站的位置, 其值主要從資料表ROUTES_INFO中篩選.ROUTE_POINT_NAME 有值的資料(表示真正的站點). LRS_METERS則是計算距離公車路徑起點的距離.
LRS_ROUTES: 表示真正以LRS方弍記載的公車路徑. 由ROUTE_INFO的內容構建而成.
為方便操作, 這邊抄襲 "Pro Oracle Spatial"的二個函式function): point, add_to_line
desc point
引數名稱 類型 In/Out 預設
-------------- ------ ------ -------
<return value> OBJECT OUT unknown
LON NUMBER IN unknown
LAT NUMBER IN unknown
SRID NUMBER IN unknown
desc add_to_line
引數名稱 類型 In/Out 預設
-------------- ------ ------ -------
<return value> OBJECT OUT unknown
GEOM OBJECT IN unknown
POINT OBJECT IN unknown
POINT_NUMBER NUMBER IN unknown
以下的SQL為如何從ROUTES_INFO篩選真正有值的公車站點
insert into stops_info (bus_service, route_name, stop_name, road_name, lat,lon)
select bus_service, route_name, route_point_name, road_name, lat,lon from routes_info where route_point_name is not null;
接下來是以ROUTES_INFO構建LRS_ROUTES. 這邊的假設是在LRS_ROUTES已經有(BUS_SERVICE, ROUTE_NAME)=('138','busService1')的資料
Line 1 ~ 18: 為一 PL/SQL block
Line 3 ~ 6 : 宣告一cursor, 其中直接使用point 來構建SDO_GEOMETRY 資料型態的點
Line 9 ~ 12: 為 FOR...LOOP
Line 11: 使用add_to_line 將點加入到線段中, 也就是將所有點, 以直線方式連接成一路徑的線段
Line 13 ~ 15: 為一UPDATE 指令, 異動BUS_SERVICE='138' and ROUTE_NAME='busService1' 的 ROUTE_GEOMETRY
Line 14: SDO_LRS.CONVERT_TO_LRS_GEOM為Oracle Spatial LRS所提供的功能, 主要是將一般平面的線段轉變成LRS的線段.
更新/異動空間資料庫的詮釋資料(Metadata)與建立空間資料索引
-- Update the Spatial metadata.
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'lrs_routes',
'route_geometry',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', -180, 180, 0.00005),
SDO_DIM_ELEMENT('Y', -90, 90, 0.00005),
SDO_DIM_ELEMENT('M', 0, 100000, 0.000005) -- meters
),
8307 -- SRID
);
-- Create the spatial index.
CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
將資料表STOPS_INFO中的LRS_METERS值算出並填滿
首先先建立函數GET_LRS_METERS
CREATE OR REPLACE
FUNCTION GET_LRS_METERS
(
LON IN NUMBER
, LAT IN NUMBER
) RETURN NUMBER AS
GET_METERS number;
BEGIN
SELECT SDO_LRS.GET_MEASURE(
SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
SDO_GEOMETRY(3301, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(LON, LAT, NULL)) ),
m.diminfo )
INTO GET_METERS
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
AND a.BUS_SERVICE = '138' and a.route_name = 'busService1';
RETURN GET_METERS;
END GET_LRS_METERS;
以GET_LRS_METERS來異動資料表STOPS_INFO中的LRS_METERS欄位
update stops_info
set lrs_meters = get_lrs_meters(lon,lat);
因為資料表的名稱, 欄位都有變動. 所以查詢的範例就會改變成:
查詢公車(103.974676, 1.374007)距離各站還有多少距離
SELECT STOP_NAME, LRS_METERS - GET_LRS_METERS(103.974676, 1.374007) AS METERS_TO_NEXT_STOP
FROM STOPS_INFO
where BUS_SERVICE='138' and ROUTE_NAME='busService1' ORDER BY 2;
查詢公車(103.974676, 1.374007)偏離公車路徑的距離
SELECT
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.975298, 1.374007, NULL)))
FROM LRS_ROUTES
WHERE BUS_SERVICE='138' and ROUTE_NAME='busService1';
ROUTE_NAME: 表示公車行駛的方向, 可能為"東向", "西向"; "去程", "回程"...這裏使用"busService1"表示
BUS_SERVICE+ROUTE_NAME 可視為一公車路徑的ID.
Point_Order: 表示路徑上點順序.
Point_Name: 有值的內容表示真正的站名(範例為stop1, stop2...), 沒有值(NULL)則表示為純綷為描述路徑上的站與站之間的銜接, 轉折..的點位
LAT, LON 則表示座標
為了更能夠彈性地表示/描述路徑應用, 我用三個資料表呈現. 其三個表之間ER-Diagram 概念範如下(請注意, 這裏的PK表示是有問題的, 真實的情況會隨需求而有變化):
STOPS_INFO: 表示真正有站的位置, 其值主要從資料表ROUTES_INFO中篩選.ROUTE_POINT_NAME 有值的資料(表示真正的站點). LRS_METERS則是計算距離公車路徑起點的距離.
LRS_ROUTES: 表示真正以LRS方弍記載的公車路徑. 由ROUTE_INFO的內容構建而成.
為方便操作, 這邊抄襲 "Pro Oracle Spatial"的二個函式function): point, add_to_line
desc point
引數名稱 類型 In/Out 預設
-------------- ------ ------ -------
<return value> OBJECT OUT unknown
LON NUMBER IN unknown
LAT NUMBER IN unknown
SRID NUMBER IN unknown
desc add_to_line
引數名稱 類型 In/Out 預設
-------------- ------ ------ -------
<return value> OBJECT OUT unknown
GEOM OBJECT IN unknown
POINT OBJECT IN unknown
POINT_NUMBER NUMBER IN unknown
以下的SQL為如何從ROUTES_INFO篩選真正有值的公車站點
insert into stops_info (bus_service, route_name, stop_name, road_name, lat,lon)
select bus_service, route_name, route_point_name, road_name, lat,lon from routes_info where route_point_name is not null;
接下來是以ROUTES_INFO構建LRS_ROUTES. 這邊的假設是在LRS_ROUTES已經有(BUS_SERVICE, ROUTE_NAME)=('138','busService1')的資料
Line 3 ~ 6 : 宣告一cursor, 其中直接使用point 來構建SDO_GEOMETRY 資料型態的點
Line 9 ~ 12: 為 FOR...LOOP
Line 11: 使用add_to_line 將點加入到線段中, 也就是將所有點, 以直線方式連接成一路徑的線段
Line 13 ~ 15: 為一UPDATE 指令, 異動BUS_SERVICE='138' and ROUTE_NAME='busService1' 的 ROUTE_GEOMETRY
Line 14: SDO_LRS.CONVERT_TO_LRS_GEOM為Oracle Spatial LRS所提供的功能, 主要是將一般平面的線段轉變成LRS的線段.
更新/異動空間資料庫的詮釋資料(Metadata)與建立空間資料索引
-- Update the Spatial metadata.
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'lrs_routes',
'route_geometry',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', -180, 180, 0.00005),
SDO_DIM_ELEMENT('Y', -90, 90, 0.00005),
SDO_DIM_ELEMENT('M', 0, 100000, 0.000005) -- meters
),
8307 -- SRID
);
-- Create the spatial index.
CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
將資料表STOPS_INFO中的LRS_METERS值算出並填滿
首先先建立函數GET_LRS_METERS
CREATE OR REPLACE
FUNCTION GET_LRS_METERS
(
LON IN NUMBER
, LAT IN NUMBER
) RETURN NUMBER AS
GET_METERS number;
BEGIN
SELECT SDO_LRS.GET_MEASURE(
SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
SDO_GEOMETRY(3301, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(LON, LAT, NULL)) ),
m.diminfo )
INTO GET_METERS
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
AND a.BUS_SERVICE = '138' and a.route_name = 'busService1';
RETURN GET_METERS;
END GET_LRS_METERS;
以GET_LRS_METERS來異動資料表STOPS_INFO中的LRS_METERS欄位
update stops_info
set lrs_meters = get_lrs_meters(lon,lat);
因為資料表的名稱, 欄位都有變動. 所以查詢的範例就會改變成:
查詢公車(103.974676, 1.374007)距離各站還有多少距離
SELECT STOP_NAME, LRS_METERS - GET_LRS_METERS(103.974676, 1.374007) AS METERS_TO_NEXT_STOP
FROM STOPS_INFO
where BUS_SERVICE='138' and ROUTE_NAME='busService1' ORDER BY 2;
查詢公車(103.974676, 1.374007)偏離公車路徑的距離
SELECT
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.975298, 1.374007, NULL)))
FROM LRS_ROUTES
WHERE BUS_SERVICE='138' and ROUTE_NAME='busService1';
Tuesday, September 18, 2012
Linear Referencing System 應用
Linear Referencing System 可以運用於交通運輸相關的系統。以下的例子為預測到達時間。
假設有下面的資訊(簡化)
一條行車路徑，有14個站(含起迄站)，如下圖：
假設有一公車從"stop1" 開往 "stop14"，想知道到該公車到剩餘的每一站還要多少時間，LRS可以幫忙解決這樣的問題。
以下為將14個 bus stop轉成LRS
-- Create a table for routes (highways).
假設有下面的資訊(簡化)
一條行車路徑，有14個站(含起迄站)，如下圖：
假設有一公車從"stop1" 開往 "stop14"，想知道到該公車到剩餘的每一站還要多少時間，LRS可以幫忙解決這樣的問題。
以下為將14個 bus stop轉成LRS
-- Create a table for routes (highways).
CREATE TABLE lrs_routes (
route_id NUMBER PRIMARY KEY,
route_name VARCHAR2(32),
route_geometry SDO_GEOMETRY);
-- Populate table with just one route for this example.
INSERT INTO lrs_routes VALUES(
1,
'Route1',
SDO_GEOMETRY(
3302, -- line string, 3 dimensions: X,Y,M
8307,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
103.837624, 1.311726, 0, -- stop1
103.83462 , 1.309709, NULL, -- stop2
103.829298, 1.3007 , NULL, -- stop3
103.83153 , 1.295894, NULL, -- stop4
103.83728 , 1.295379, NULL, -- stop5
103.842387, 1.293835, NULL, -- stop6
103.844662, 1.298082, NULL, -- stop7
103.84552 , 1.299026, NULL, -- stop8
103.847431, 1.298721, NULL, -- stop9
103.848782, 1.298426, NULL, -- stop10
103.851485, 1.29628 , NULL, -- stop11
103.862472, 1.301429, NULL, -- stop12
103.863931, 1.307993, NULL, -- stop13
103.860734, 1.305588, 7347.42786 -- stop14 Assume From stop1 to stop14 is 7347.42786 meters
)));
-- Update the Spatial metadata.
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'lrs_routes',
'route_geometry',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', -180, 180, 0.00005),
SDO_DIM_ELEMENT('Y', -90, 90, 0.00005),
SDO_DIM_ELEMENT('M', 0, 100000, 0.000005) -- meters
),
8307 -- SRID
);
-- Create the spatial index.
CREATE INDEX lrs_routes_idx ON lrs_routes(route_geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--
-- 在 SQL*Plus 查看該LRS相關資訊， 請注意 measures 的部分皆為 NULL
--
SQL> select * from lrs_routes;
ROUTE_ID ROUTE_NAME
---------- --------------------------------
ROUTE_GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDIN
--------------------------------------------------------------------------------
1 Route1
SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
103.837624, 1.311726, 0, 103.83462, 1.309709, NULL, 103.829298, 1.3007, NULL,
103.83153, 1.295894, NULL, 103.83728, 1.295379, NULL, 103.842387, 1.293835, NUL
L, 103.844662, 1.298082, NULL, 103.84552, 1.299026, NULL, 103.847431, 1.298721,
NULL, 103.848782, 1.298426, NULL, 103.851485, 1.29628, NULL, 103.862472, 1.30142
9, NULL, 103.863931, 1.307993, NULL, 103.860734, 1.305588, 7347.42786))
SQL>
-- Test the LRS procedures.
--
DECLARE
geom_segment SDO_GEOMETRY;
line_string SDO_GEOMETRY;
dim_array SDO_DIM_ARRAY;
result_geom_1 SDO_GEOMETRY;
result_geom_2 SDO_GEOMETRY;
result_geom_3 SDO_GEOMETRY;
BEGIN
SELECT a.route_geometry into geom_segment FROM lrs_routes a
WHERE a.route_name = 'Route1';
SELECT m.diminfo into dim_array from
user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY';
-- Define the LRS segment for Route1. This will populate any null measures.
-- No need to specify start and end measures, because they are already defined
-- in the geometry.
SDO_LRS.DEFINE_GEOM_SEGMENT (geom_segment, dim_array);
END;
/
--
-- 計算點(X,Y)=(103.93232, 1.31996) 投射(project) 於 LRS 線段上的位置
-- 因為 GPS 回報的點並不一定(應該不會)剛好落在 LRS 線上，為計算距離, 所以先進行投射
--
SELECT SDO_LRS.PROJECT_PT(a.route_geometry,
SDO_GEOMETRY(3301, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(103.93232, 1.31996, NULL))
)
from lrs_routes a WHERE a.route_id = 1;
SQL> SELECT SDO_LRS.PROJECT_PT(a.route_geometry, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.93232, 1.31996, NULL)) ) from lrs_routes a WHERE a.route_id = 1; 2 3 4 5 6 SDO_LRS.PROJECT_PT(A.ROUTE_GEOMETRY,SDO_GEOMETRY(3301,8307,NULL,SDO_ELEM_INFO_AR -------------------------------------------------------------------------------- SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY( 103.863931, 1.307993, 6903.23056))
--
-- 計算點(X,Y)=(103.93232, 1.31996) 已經離 stop1 行駛了多少距離
--
SQL> SELECT SDO_LRS.GET_MEASURE(
2 SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
3 SDO_GEOMETRY(3301, 8307, NULL,
4 SDO_ELEM_INFO_ARRAY(1, 1, 1),
5 SDO_ORDINATE_ARRAY(103.93232, 1.31996, NULL)) ),
6 m.diminfo )
7 FROM lrs_routes a, user_sdo_geom_metadata m
8 WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
9 AND a.route_id = 1;
SDO_LRS.GET_MEASURE(SDO_LRS.PROJECT_PT(A.ROUTE_GEOMETRY,M.DIMINFO,SDO_GEOMETRY(3
--------------------------------------------------------------------------------
6903.23056
--
-- 接下來，我們要計算該公車距離各站還有多少距離
-- 因為是公車路線，各站(stop)皆為固定位置，所以我們要先算出各站離起點的距離
-- 計算距離為常用的用途，為方便計算我們先建立 GET_METERS 的函式
--
CREATE OR REPLACE
FUNCTION GET_LRS_METERS
(
X IN NUMBER
, Y IN NUMBER
) RETURN NUMBER AS
GET_METERS number;
BEGIN
SELECT SDO_LRS.GET_MEASURE(
SDO_LRS.PROJECT_PT(a.route_geometry, m.diminfo,
SDO_GEOMETRY(3301, 8307, NULL,
SDO_ELEM_INFO_ARRAY(1, 1, 1),
SDO_ORDINATE_ARRAY(X, Y, NULL)) ),
m.diminfo )
INTO GET_METERS
FROM lrs_routes a, user_sdo_geom_metadata m
WHERE m.table_name = 'LRS_ROUTES' AND m.column_name = 'ROUTE_GEOMETRY'
AND a.route_id = 1;
RETURN GET_METERS;
END GET_LRS_METERS;
--
-- 接下來 要在原來的表格(ROUTES_INFO TABLE) 增加 存放離起點距離的欄位
--
ALTER TABLE ROUTES_INFO
ADD (LRS_METERS NUMBER(12, 6) );
--
-- 更新 ROUTES_INFO(LRS_METERS) 的內容
--
update
ROUTES_INFO
SET lrs_meters = get_lrs_meters(X, Y);
--
-- 更新後的結果
--
--
-- 所以公車位置(X,Y)=(103.93232, 1.31996) 離各站的距離
--
SELECT STOP_ID, LRS_METERS - GET_LRS_METERS(103.93232, 1.31996) AS METERS_TO_NEXT_STOP
FROM ROUTES_INFO
where ROUTE_ID = 1;
SQL> SELECT STOP_ID, LRS_METERS - GET_LRS_METERS(103.93232, 1.31996) AS METERS_TO_NEXT_STOP
2 FROM ROUTES_INFO
3 where ROUTE_ID = 1;
STOP_I METERS_TO_NEXT_STOP
------ -------------------
stop1 -6903.2306
stop2 -6501.3476
stop3 -5342.4001
stop4 -4755.7882
stop5 -4113.3349
stop6 -3519.8822
stop7 -2986.3667
stop8 -2844.8971
stop9 -2629.5625
stop10 -2475.7104
stop11 -2092.5644
stop12 -743.75404
stop13 3.8952E-07
stop14 444.197298
14 rows selected.
SQL>
以上可以看出來，該車已接stop 13(或已到站)，下一站為stop 14
--
-- 查詢公車點(103.840048, 1.29494)偏離路線有多遠的距離
-- 使用SDO_LRS.FIND_OFFSET進行查詢 單位: meter
--
SELECT
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.840048, 1.29494, NULL)))
FROM LRS_ROUTES
WHERE ROUTE_ID = 1;
SQL> SELECT
2 SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY, SDO_GEOMETRY(3301, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(103.840048, 1.29494, NULL)))
3 FROM LRS_ROUTES
4 WHERE ROUTE_ID = 1;
SDO_LRS.FIND_OFFSET(ROUTE_GEOMETRY,SDO_GEOMETRY(3301,8307,NULL,SDO_ELEM_INFO_ARR
--------------------------------------------------------------------------------
42.1325652
SQL>
Friday, August 31, 2012
哦! Oracle Proxy User
這一篇解釋 Oracle Proxy User相當經典。http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:21575905259251。
目前大部分網路應用系統開發(Web Application)在連接資料庫時，會建立一位資料庫連線使用者(讓我們假設此使用者的資料庫username為"MIDTIER")，然後所用經過網路應用系統認証(authenticate)的網路用戶，就會以MIDTIER連進資料庫，進行資料存取。
為了讓使用者MIDTIER能順利存取所需要的資料，一般會採取以下幾種的授權(authorize)方式：
1. 所有要被存取的資料全部建立於MIDTIER的使用者名下
2. 資料的擁有者為其他資料庫使用者，由其他使用者授權給MIDTIER使用
就安全與實務上來說，方法2為比較適合的方式；也是建議的方法。其優點除安全之外，在實務上也比較方管理，應用程式也比較容易移植在其他環境。
有個問題，在這樣的方式下，MIDTIER要給夠足夠的授權，才能夠滿足前端網路應用系統使用者的需求。讓我們假設：我們有2個不同授權的網路使用者-MANAGER與CLERK。
MANAGER與CLERK一經被網路應用程式認證，就能透過MIDTIER存取資料，因此MIDTIER的授權是要同時為MANAGER與CLERK授權的聯集。這會導致MIDTIER被過份授權。如果要想達到適當的授權，Oracle Proxy User為一種解決方案。(這裏有一種隱含的假設--MANAGER與CLERK的認證是由網路應用系統所認證而且資料庫"相信"網路應用系統的認證)
以下為展示Oracle Proxy User的做法：我們會在Oracle資料庫下建立幾個不同的USER：
MIDTIER為網路應用系統與資料庫之間連線的使用者或有人用為中間件軟體(middleware)建立Connection Pool的使用者：在這裏我們只會給予最少的權限"CREATE SESSION"(只能夠建立資料庫連線)。
MANAGER與CLERK為模擬經過網路應用系統認證過的使用者(因為經過網路應用系統認證所以資料庫不再認證)。
SCOTT為我們熟悉的USER，是真正表格的擁有者，為簡化展示會分別給予CLERK SELECT的授權於表格DEPT, EMP而MANAGER SELECT授權於表格DEPT, EMP, BONUS, SALGRADE。
SQL> connect system/oracle@orcl
Connected.
SQL> create user MIDTIER identified by MIDTIER;
User created.
SQL> create user MANAGER identified by whateveryouwanatoset;
User created.
SQL> create user CLERK identified by whateveryouwanatoset;
User created.
SQL> grant CREATE SESSION to MIDTIER;
Grant succeeded.
SQL> grant CREATE SESSION to MANAGER;
Grant succeeded.
SQL> grant CREATE SESSION to CLERK;
Grant succeeded.
SQL> grant SELECT on SCOTT.DEPT to CLERK;
Grant succeeded.
SQL> grant SELECT on SCOTT.DEPT to MANAGER;
Grant succeeded.
SQL> grant SELECT on SCOTT.EMP to CLERK;
Grant succeeded.
SQL> grant SELECT on SCOTT.EMP to MANAGER;
Grant succeeded.
SQL> grant SELECT on SCOTT.BONUS to MANAGEAR;
grant SELECT on SCOTT.BONUS to MANAGEAR
*
ERROR at line 1:
ORA-01917: user or role 'MANAGEAR' does not exist
SQL> grant SELECT on SCOTT.BONUS to MANAGER;
Grant succeeded.
SQL> grant SELECT on SCOTT.SALGRADE to MANAGER;
Grant succeeded.
SQL>
接下來設定MANAGER與CLERK可以透過MIDTIER登入Oracle 資料庫--MIDTIER可以代表MANAGER與CLERK登入資料庫。
SQL> alter user CLERK grant connect through MIDTIER;
User altered.
SQL> alter user MANAGER grant connect through MIDTIER;
User altered.
SQL>
現在先使用MIDTIER連進Oracle 資料庫，並查看是否具備讀取SCOTT的資料表格。
SQL> connect MIDTIER/MIDTIER@ORCL
Connected.
SQL> select table_name from all_tables where owner='SCOTT';
no rows selected
當使用"connect MIDTIER[MANAGER]/MIDTIER@ORCL"，當登入成功之後，我就是(代表)MANAGER，我就被授權看到SCOTT的4個表格。
以上過程中(先排除討論MANAGER與CLERK如何被認証)，都是使用MIDTIER的使用者名稱與密碼，MANAGER與CLERK的密碼完全不必知道也不用被送出；而且資料庫知道真正的前端使用者是誰(SELECT USER FROM DUAL)，現在就容易做資料稽核了。
PROXY USER的機制被廣泛地運用在Web Application，利用MIDTIER建立connection pool，且MIDTIER除了資料庫連線能力之外，沒有其它的能力，保障了connection pool 資料庫連線的安全性與便利性。經過Web Application認証之後才以前端真正使用者的身份(如為MANAGER或CLEARK) 進行資料庫的操作；請注意我們並沒有重新進行資料庫連接，而是在取得資料庫connection pool的資源後，設定其真正的使用者，進行對資料庫的操作。
如此，我們可以保有使用connection pool的快速與方便而且同時具備了安全性；從資料庫的角度，也能稽核到真正進入資料庫的使用者。
Oracle JDBC connection pool Proxy User 使用程式範例，詳情可參考Proxy Authentication 於 Oracle® Database JDBC Developer's Guide
接下來面臨的問題：
如果面對的是大量的網際網路使用者，難到真的要將所有使用者都建立在資料庫上嗎?還是有其它的解決方案 -- 解決方案之一：Oracle Enterprise User Security
目前大部分網路應用系統開發(Web Application)在連接資料庫時，會建立一位資料庫連線使用者(讓我們假設此使用者的資料庫username為"MIDTIER")，然後所用經過網路應用系統認証(authenticate)的網路用戶，就會以MIDTIER連進資料庫，進行資料存取。
為了讓使用者MIDTIER能順利存取所需要的資料，一般會採取以下幾種的授權(authorize)方式：
1. 所有要被存取的資料全部建立於MIDTIER的使用者名下
2. 資料的擁有者為其他資料庫使用者，由其他使用者授權給MIDTIER使用
就安全與實務上來說，方法2為比較適合的方式；也是建議的方法。其優點除安全之外，在實務上也比較方管理，應用程式也比較容易移植在其他環境。
有個問題，在這樣的方式下，MIDTIER要給夠足夠的授權，才能夠滿足前端網路應用系統使用者的需求。讓我們假設：我們有2個不同授權的網路使用者-MANAGER與CLERK。
MANAGER與CLERK一經被網路應用程式認證，就能透過MIDTIER存取資料，因此MIDTIER的授權是要同時為MANAGER與CLERK授權的聯集。這會導致MIDTIER被過份授權。如果要想達到適當的授權，Oracle Proxy User為一種解決方案。(這裏有一種隱含的假設--MANAGER與CLERK的認證是由網路應用系統所認證而且資料庫"相信"網路應用系統的認證)
以下為展示Oracle Proxy User的做法：我們會在Oracle資料庫下建立幾個不同的USER：
MIDTIER為網路應用系統與資料庫之間連線的使用者或有人用為中間件軟體(middleware)建立Connection Pool的使用者：在這裏我們只會給予最少的權限"CREATE SESSION"(只能夠建立資料庫連線)。
MANAGER與CLERK為模擬經過網路應用系統認證過的使用者(因為經過網路應用系統認證所以資料庫不再認證)。
SCOTT為我們熟悉的USER，是真正表格的擁有者，為簡化展示會分別給予CLERK SELECT的授權於表格DEPT, EMP而MANAGER SELECT授權於表格DEPT, EMP, BONUS, SALGRADE。
SQL> connect system/oracle@orcl
Connected.
SQL> create user MIDTIER identified by MIDTIER;
User created.
SQL> create user MANAGER identified by whateveryouwanatoset;
User created.
SQL> create user CLERK identified by whateveryouwanatoset;
User created.
SQL> grant CREATE SESSION to MIDTIER;
Grant succeeded.
SQL> grant CREATE SESSION to MANAGER;
Grant succeeded.
SQL> grant CREATE SESSION to CLERK;
Grant succeeded.
SQL> grant SELECT on SCOTT.DEPT to CLERK;
Grant succeeded.
SQL> grant SELECT on SCOTT.DEPT to MANAGER;
Grant succeeded.
SQL> grant SELECT on SCOTT.EMP to CLERK;
Grant succeeded.
SQL> grant SELECT on SCOTT.EMP to MANAGER;
Grant succeeded.
SQL> grant SELECT on SCOTT.BONUS to MANAGEAR;
grant SELECT on SCOTT.BONUS to MANAGEAR
*
ERROR at line 1:
ORA-01917: user or role 'MANAGEAR' does not exist
SQL> grant SELECT on SCOTT.BONUS to MANAGER;
Grant succeeded.
SQL> grant SELECT on SCOTT.SALGRADE to MANAGER;
Grant succeeded.
SQL>
接下來設定MANAGER與CLERK可以透過MIDTIER登入Oracle 資料庫--MIDTIER可以代表MANAGER與CLERK登入資料庫。
SQL> alter user CLERK grant connect through MIDTIER;
User altered.
SQL> alter user MANAGER grant connect through MIDTIER;
User altered.
SQL>
上述的指令是說：MANAGER與CLERK可以透過MIDTIER所使用的資料連線連接資料庫：一旦前面的應用系統認證MANAGER或CLERK為其本人之後，Oracle資料庫就承認前端應用系統的認證並允許MIDTIER可以代表經過認證的MANAGER或CLERK，執行原本為MANAGER或CLERK所被授權的權利。
現在先使用MIDTIER連進Oracle 資料庫，並查看是否具備讀取SCOTT的資料表格。
SQL> connect MIDTIER/MIDTIER@ORCL
Connected.
SQL> select table_name from all_tables where owner='SCOTT';
no rows selected
接著使用PROXY USER連接的語法登入資料庫
SQL> connect MIDTIER[CLERK]/MIDTIER@ORCL
Connected.
SQL> select table_name from all_tables where owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
SQL> select user from dual;
USER
------------------------------
CLERK
SQL> connect MIDTIER[MANAGER]/MIDTIER@ORCL
Connected.
SQL> select table_name from all_tables where owner='SCOTT';
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
SQL> select user from dual;
USER
------------------------------
MANAGER
首先先解釋"connect MIDTIER[CLERK]/MIDTIER@ORCL" -- CLERK已經先被我驗證過了，CLERK就是CLERK沒有問題, 我就是CLERK(我代表CLERK來登入資料庫)，但是我是用MIDTIER的使用者與其密碼來登入資料庫；因為之前我們都已經同意，資料庫相信我對前端使用者的認証。因此當登入成功之後，我就是(代表)CLERK，我只被授權看到SCOTT的2個表格。
當使用"connect MIDTIER[MANAGER]/MIDTIER@ORCL"，當登入成功之後，我就是(代表)MANAGER，我就被授權看到SCOTT的4個表格。
以上過程中(先排除討論MANAGER與CLERK如何被認証)，都是使用MIDTIER的使用者名稱與密碼，MANAGER與CLERK的密碼完全不必知道也不用被送出；而且資料庫知道真正的前端使用者是誰(SELECT USER FROM DUAL)，現在就容易做資料稽核了。
PROXY USER的機制被廣泛地運用在Web Application，利用MIDTIER建立connection pool，且MIDTIER除了資料庫連線能力之外，沒有其它的能力，保障了connection pool 資料庫連線的安全性與便利性。經過Web Application認証之後才以前端真正使用者的身份(如為MANAGER或CLEARK) 進行資料庫的操作；請注意我們並沒有重新進行資料庫連接，而是在取得資料庫connection pool的資源後，設定其真正的使用者，進行對資料庫的操作。
如此，我們可以保有使用connection pool的快速與方便而且同時具備了安全性；從資料庫的角度，也能稽核到真正進入資料庫的使用者。
Oracle JDBC connection pool Proxy User 使用程式範例，詳情可參考Proxy Authentication 於 Oracle® Database JDBC Developer's Guide
接下來面臨的問題：
如果面對的是大量的網際網路使用者，難到真的要將所有使用者都建立在資料庫上嗎?還是有其它的解決方案 -- 解決方案之一：Oracle Enterprise User Security
Wednesday, February 22, 2012
Oracle Database 呼叫外部地址定位網路服務(Web Service)
先至Oracle Database Web Service 下載 Web Services Callout Utility
按照說明(10.htm 或11.htm)至少完成 步驟1. Preparing the Oracle Client and Oracle Database Server
在完成上述步驟之後, 可以在$ORACLE_HOME/bin下找到"jpub"的script，複製一份(我的環境是 Oracle 11g, $ORACLE_HOME/jdk的版本是"1.5.0_30")
$ cp jpub jpub1.5
修改 jpub1.5, 以下為jpub1.5的內容
#! /bin/sh
#
# jpub - shell for invoking the JPublisher tool
# Usage: jpub <-J-javavm-options> <-jpub-options>
#
# %W% %E%
#
unset JAVA_HOME
JAVA_HOME=$ORACLE_HOME/jdk
export JAVA_HOME
TMPCLASSPATH=$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$ORACLE_HOME/jdbc/lib/ojdbc5_g.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/jlib/orai18n-mapping.jar:$ORACLE_HOME/sqlj/lib/translator.jar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/sqlj/lib/dbwsa.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aqapi.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.jar:$CLASSPATH
export TMPCLASSPATH
TMP_C_CLASSPATH=$TMPCLASSPATH
export TMP_C_CLASSPATH LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$JAVA_HOME/lib
export LD_LIBRARY_PATH
switches=-Xmx1024M
JRE64FLAG=
export JRE64FLAG
files=
switcharg=
javavm=$JAVA_HOME/bin/java
while [ $# -gt 0 ]
do
case "$1" in
-J-classpath) TMPCLASSPATH="$2"
shift;;
-classpath) TMP_C_CLASSPATH="$2"
shift;;
-J-*) switcharg=`echo $1 | cut -c3-`
switches="$switches $switcharg";;
-vm=*) javavm=`echo $1 | cut -c5-` ;;
*) files="$files $1";;
esac
shift
done
exec $javavm $JRE64FLAG -classpath $TMPCLASSPATH $switches oracle.jpub.java.Main -C-classpath=$TMP_C_CLASSPATH $files
然後執行(為一行的指令，若看到分行為版本格式的緣故)
$ jpub1.5 -httpproxy=proxy.my_company.com:80 -proxywsdl=http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx?WSDL -endpoint=http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx
Multiple ports available. To publish a particular port, use -proxyopts=port:Multiple_MatchLocateSoap12, or -proxyopts=port:Multiple_MatchLocateSoap.
Use the default port Multiple_MatchLocateSoap12.
tmp/src/genproxy/Multiple_MatchLocateSoap12ClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
plsql_proxy.jar
Please run plsql_wrapper.sql in the user schema, load the generated jar file and run plsql_grant.sql in the SYS schema.
使用jpub(jpub操作手冊)的測試對象為台灣地址定位服務(內政部資訊中心提供的網路服務-Web Service-元件)，其WSDL的位址：
http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx?WSDL
Endpoint 網址：
http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx?
依jpub 所需要輸入的參數對應，則會產生幾個PL/SQL wrapper package(應用程式套件)及 SOAP proxy jar檔。
依照指示，在使用呼叫的使用者下("MVDEMO")執行"plsql_wrapper.sql"，並使用"loadjava"將"plsql_proxy.jar"載入資料庫：修改"plsql_grant.sql"，將"<USER>"置換成要呼叫的使用者名稱("MVDEMO")，再以SYS執行"plsql_grant.sql"。
以上就完成了從Oracle DB 呼叫外部Web Service的PL/SQL程式界面。
按照說明(10.htm 或11.htm)至少完成 步驟1. Preparing the Oracle Client and Oracle Database Server
在完成上述步驟之後, 可以在$ORACLE_HOME/bin下找到"jpub"的script，複製一份(我的環境是 Oracle 11g, $ORACLE_HOME/jdk的版本是"1.5.0_30")
$ cp jpub jpub1.5
修改 jpub1.5, 以下為jpub1.5的內容
#! /bin/sh
#
# jpub - shell for invoking the JPublisher tool
# Usage: jpub <-J-javavm-options> <-jpub-options>
#
# %W% %E%
#
unset JAVA_HOME
JAVA_HOME=$ORACLE_HOME/jdk
export JAVA_HOME
TMPCLASSPATH=$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$ORACLE_HOME/jdbc/lib/ojdbc5_g.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/sqlj/lib/runtime12.jar:$ORACLE_HOME/jlib/orai18n.jar:$ORACLE_HOME/jlib/orai18n-mapping.jar:$ORACLE_HOME/sqlj/lib/translator.jar:$ORACLE_HOME/javavm/lib/aurora.zip:$ORACLE_HOME/sqlj/lib/dbwsa.jar:$ORACLE_HOME/rdbms/jlib/xdb.jar:$ORACLE_HOME/lib/xsu12.jar:$ORACLE_HOME/jlib/jndi.jar:$ORACLE_HOME/rdbms/jlib/aqapi.jar:$ORACLE_HOME/rdbms/jlib/jmscommon.jar:$ORACLE_HOME/lib/xmlparserv2.jar:$CLASSPATH
export TMPCLASSPATH
TMP_C_CLASSPATH=$TMPCLASSPATH
export TMP_C_CLASSPATH LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$JAVA_HOME/lib
export LD_LIBRARY_PATH
switches=-Xmx1024M
JRE64FLAG=
export JRE64FLAG
files=
switcharg=
javavm=$JAVA_HOME/bin/java
while [ $# -gt 0 ]
do
case "$1" in
-J-classpath) TMPCLASSPATH="$2"
shift;;
-classpath) TMP_C_CLASSPATH="$2"
shift;;
-J-*) switcharg=`echo $1 | cut -c3-`
switches="$switches $switcharg";;
-vm=*) javavm=`echo $1 | cut -c5-` ;;
*) files="$files $1";;
esac
shift
done
exec $javavm $JRE64FLAG -classpath $TMPCLASSPATH $switches oracle.jpub.java.Main -C-classpath=$TMP_C_CLASSPATH $files
然後執行(為一行的指令，若看到分行為版本格式的緣故)
$ jpub1.5 -httpproxy=proxy.my_company.com:80 -proxywsdl=http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx?WSDL -endpoint=http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx
Multiple ports available. To publish a particular port, use -proxyopts=port:Multiple_MatchLocateSoap12, or -proxyopts=port:Multiple_MatchLocateSoap.
Use the default port Multiple_MatchLocateSoap12.
tmp/src/genproxy/Multiple_MatchLocateSoap12ClientJPub.java
plsql_wrapper.sql
plsql_dropper.sql
plsql_grant.sql
plsql_revoke.sql
plsql_proxy.jar
Please run plsql_wrapper.sql in the user schema, load the generated jar file and run plsql_grant.sql in the SYS schema.
使用jpub(jpub操作手冊)的測試對象為台灣地址定位服務(內政部資訊中心提供的網路服務-Web Service-元件)，其WSDL的位址：
http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx?WSDL
Endpoint 網址：
http://address.tgos.nat.gov.tw/ADDR_WS/Multiple_MatchLocate/Multiple_MatchLocate.asmx?
依jpub 所需要輸入的參數對應，則會產生幾個PL/SQL wrapper package(應用程式套件)及 SOAP proxy jar檔。
依照指示，在使用呼叫的使用者下("MVDEMO")執行"plsql_wrapper.sql"，並使用"loadjava"將"plsql_proxy.jar"載入資料庫：修改"plsql_grant.sql"，將"<USER>"置換成要呼叫的使用者名稱("MVDEMO")，再以SYS執行"plsql_grant.sql"。
以上就完成了從Oracle DB 呼叫外部Web Service的PL/SQL程式界面。
Thursday, February 16, 2012
在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®ion=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®ion=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)
我所關心的是輸入中文(台灣)的地址，Google Geocoding回傳座標，取得經緯度之後，轉換成Oracle SDO_GEOMETRY資料型態，更新該欄位。
首先，測試以下URL，以確定必要的資訊都已輸入
http://maps.googleapis.com/maps/api/geocode/xml?address=台北市忠孝西路一段66號&language=zh-tw®ion=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®ion=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)
Monday, February 13, 2012
建立 Oracle Spatial WFS Part II
Oracle Web Feature Service 架構
如上圖，要特別指出的是，在Application Server上提供WFS Server API，資料是儲存在資料庫上的"Spatial Tables"，所以要透過"Feature Mapping"或稱為Publish (發佈)提供原"Spatial Tables"額外的資訊以滿足WFS的要求，但是原"Spatial Tables"並不需要再多一份複製(所以是用"Feature Mapping"這個字眼)。
設定Oracle WFS 的步驟，如下圖
在這些設定步驟，我們會使用SDO_WFS_PROCESS與SDO_WFS_LOCK packages
1. Set capabilities info
設定 Server Capabilities 的方法有(任選一種方法就可以)
I. 將外部"WFScapabilitiesTemplate.xml" 載入資料庫
以SYSTEM登入資料庫，執行下列SQL (WFScapabilitiesTemplate.xml 放在'/u01/oracle/db/product/11.2.0.3/dbhome_1' 目錄下
create or replace directory WFS_XML
as '/u01/oracle/db/product/11.2.0.3/dbhome_1';
begin
SDO_WFS_PROCESS.insertCapabilitiesInfo(
xmltype(
bfilename('WFS_XML', 'WFScapabilitiesTemplate.xml'),
nls_charset_id('AL32UTF8')
)
);
end;
/
commit;
II. 直接使用PL/SQL載入WFScapabilitiesTemplate.xml 的內容
範例
WFS GetCapabilities 測試URL：
http://localhost:7001/SpatialWS-SpatialWS-context-root/xmlwfsservlet?request=GetCapabilities&service=WFS&version=1.0.0
正確的設定應該會出下面的結果
2. Enable the schema
這個步驟相對簡單，只要透過MDSYS, SYSTEM或SYS執行"SDO_WFS_PROCESS.GrantMDAccessToUser"
SQL> execute SDO_WFS_PROCESS.GrantMDAccessToUser('MVDEMO')
3. Publish Table
必須要用SYSTEM或SYS來執行(但不包括MDSYS)Publish Tables的預儲程序(stored procedure)
範例：
declare
featureDescriptorXML CLOB :=
'<?xml version="1.0" ?>
<FeatureType xmlns:mvdemons="http://www.myserver.com/mvdemo"
xmlns="http://www.opengis.net/wfs">
<Name> mvdemons:Cities</Name>
<Title>Cities</Title>
<SRS>SDO:8307</SRS>
</FeatureType>';
begin
SDO_WFS_PROCESS.publishFeatureType(
dataSrc => 'MVDEMO.CITIES',
ftNsUrl => 'http://www.myserver.com/mvdemo',
ftName => 'Cities',
ftNsAlias => 'mvdemons' ,
featureDesc => xmltype(featureDescriptorXML),
schemaLocation => null,
pkeyCol => 'CITY',
columnInfo => MDSYS.StringList('PointMemberType'),
pSpatialCol => 'LOCATION',
featureMemberNs => null,
featureMemberName => null,
srsNs => null,
srsNsAlias => null
);
end;
/
6. Grant access rights
設定Oracle WFS 的步驟，如下圖
1. Set capabilities info
設定 Server Capabilities 的方法有(任選一種方法就可以)
I. 將外部"WFScapabilitiesTemplate.xml" 載入資料庫
以SYSTEM登入資料庫，執行下列SQL (WFScapabilitiesTemplate.xml 放在'/u01/oracle/db/product/11.2.0.3/dbhome_1' 目錄下
create or replace directory WFS_XML
as '/u01/oracle/db/product/11.2.0.3/dbhome_1';
begin
SDO_WFS_PROCESS.insertCapabilitiesInfo(
xmltype(
bfilename('WFS_XML', 'WFScapabilitiesTemplate.xml'),
nls_charset_id('AL32UTF8')
)
);
end;
/
commit;
II. 直接使用PL/SQL載入WFScapabilitiesTemplate.xml 的內容
範例
WFS GetCapabilities 測試URL：
http://localhost:7001/SpatialWS-SpatialWS-context-root/xmlwfsservlet?request=GetCapabilities&service=WFS&version=1.0.0
正確的設定應該會出下面的結果
這個步驟相對簡單，只要透過MDSYS, SYSTEM或SYS執行"SDO_WFS_PROCESS.GrantMDAccessToUser"
SQL> execute SDO_WFS_PROCESS.GrantMDAccessToUser('MVDEMO')
3. Publish Table
必須要用SYSTEM或SYS來執行(但不包括MDSYS)Publish Tables的預儲程序(stored procedure)
範例：
declare
featureDescriptorXML CLOB :=
'<?xml version="1.0" ?>
<FeatureType xmlns:mvdemons="http://www.myserver.com/mvdemo"
xmlns="http://www.opengis.net/wfs">
<Name> mvdemons:Cities</Name>
<Title>Cities</Title>
<SRS>SDO:8307</SRS>
</FeatureType>';
begin
SDO_WFS_PROCESS.publishFeatureType(
dataSrc => 'MVDEMO.CITIES',
ftNsUrl => 'http://www.myserver.com/mvdemo',
ftName => 'Cities',
ftNsAlias => 'mvdemons' ,
featureDesc => xmltype(featureDescriptorXML),
schemaLocation => null,
pkeyCol => 'CITY',
columnInfo => MDSYS.StringList('PointMemberType'),
pSpatialCol => 'LOCATION',
featureMemberNs => null,
featureMemberName => null,
srsNs => null,
srsNsAlias => null
);
end;
/
其中 MVDEMO.CITIES
SQL> desc cities
Name Null? Type
----------------------------------------- -------- ----------------------------
LOCATION MDSYS.SDO_GEOMETRY
CITY VARCHAR2(42)
STATE_ABRV VARCHAR2(2)
POP90 NUMBER
RANK90 NUMBER
說明
featureDescriptorXML為當收到 GetCapabilities要求時，所回傳XML的內容
dataSrc => 'MVDEMO.CITIES' 表示所對應旳Schema與Table名稱
ftName => 'Cities' 表示WFS的Feature Name
pkeyCol => 'CITY' 表示該表的的 Primary Key
columnInfo => MDSYS.StringList('PointMemberType') 表示所對應GML 圖徵)feature)的型態
pSpatialCol => 'LOCATION' 表示表格為SDO_GEOMETRY欄位型態的欄位名稱
4. Register table for updates
Oracle Spatial WFS 支援 WFS-T。要能支援WFS-T需要將能提供此項功能的資料表格進行註冊。透過該表格的擁有者(Owner)執行SDO_WFS_LOCK.registerFeatureTable
範例：
SQL> execute SDO_WFS_LOCK.registerFeatureTable('MVDEMO','CITIES')
5. Notify WFS server
新增的異動並不動自動送到WFS server，透過MDSYS, SYSTEM或SYS執行SDO_WFS_PROCESS. insertFtMDUpdated來通知WFS有新的更新異動；WFS 則會每10秒(預設)(定義在<wfs_cache_sync_interval>10000</wfs_cache_sync_interval>)來檢查更新的時間，如果有變動，則會載入新的WFS定義
範例：
SQL> execute SDO_WFS_PROCESS.InsertFtMDUpdated('http://www.myserver.com/mvdemo','Cities', sysdate)
6. Grant access rights
給予"匿名使用者"對資料表格適當的存取權限。以本範來說，MVDEMO.CITIES需要給予SELECT、INSERT、UPDATE、DELETE(為滿足WFS-T的功能)。
SQL> grant SELECT, INSERT, UPDATE, DELETE on cities to SpatialWsXmlUser;
7. Set Configuration params
主要是修改 WEB-INF/conf/wsconfig.xml 的設定內容(基本上使用預設就可以了)
<wfs_cache_sync_interval>10000</wfs_cache_sync_interval>
設定WFS檢查異動同步的時間間隔
<wfs_query_timeout>10</wfs_query_timeout>
預設查詢鎖定時間(單位是分鐘)
<wfs_lock_expiry>4</wfs_lock_expiry>
預設異動鎖定時間(單位是秒)
<cached_feature_types>
<feature_type ns="http://www.myserver.com/mvdemo" name="Cities" />
<feature_type ns="http://www.myserver.com/mvdemo" name="Counties" />
</cached_feature_types>
設定需要放置於WFS memory cache 的Features
7. Set Configuration params
主要是修改 WEB-INF/conf/wsconfig.xml 的設定內容(基本上使用預設就可以了)
<wfs_cache_sync_interval>10000</wfs_cache_sync_interval>
設定WFS檢查異動同步的時間間隔
<wfs_query_timeout>10</wfs_query_timeout>
預設查詢鎖定時間(單位是分鐘)
<wfs_lock_expiry>4</wfs_lock_expiry>
預設異動鎖定時間(單位是秒)
<cached_feature_types>
<feature_type ns="http://www.myserver.com/mvdemo" name="Cities" />
<feature_type ns="http://www.myserver.com/mvdemo" name="Counties" />
</cached_feature_types>
設定需要放置於WFS memory cache 的Features
Subscribe to:
Posts (Atom)