Oracle Spatial基礎(基于11g)
本過程來源于上學期課程《空間資料庫》課餘設計,Oracle對空間資料的支援使得其在GIS方向有其獨有的優勢,由于近期有用到Oracle的需求,故重新整理了一下之前的文檔以備用及分享,如有錯誤請留言指出。
教材基于Oracle11g,網上關于Oracle的資源教程較少且多基于此版本,是以筆者推薦11g用于熟悉工作。
轉載請注明來源。
文章目錄
- Oracle Spatial基礎(基于11g)
-
- @[toc]
- 一、資料導入
-
- 1.前期準備:
- 2.測試資料導入
-
- 2.1 教材參考資料導入(from .dmp格式)
- 2.2 通過shapefile檔案導入
- 二、了解空間中繼資料
-
- 1.什麼是中繼資料
- 2.字典視圖說明
-
- 2.1 diminfo屬性說明
- 2.2 srid屬性說明
- 3. 示例——填充空間中繼資料
- 三、*sdo_geometry資料類型
-
- 1.可存儲哪些幾何資料?
- 2.geometry的邏輯實作
- 3.geometry的類型、屬性和值的描述
-
- 3.1 gtype——次元和形狀的确定
- 3.2 srid——坐标系的确定
- 3.3 sdo_point——确定單點坐标
- 3.4 element_info和ordinates——線、面(多點)及複雜幾何體
- 四、空間資料的加載
- 五、sql語句操作Geometry資料——舉例
-
- 1.讀取幾何資訊
- 2.oracle數組(array)基礎
-
- 2.1 定義固定長度的一維數組
- 2.2 定義可變長度的一維數組
- 2.3 定義多元數組
- 2.4 Oracle調用函數傳回的數組
- 2.5 強制修改已經被引用的自定義type
- 2.6 oracle數組屬性和函數
- 3.建立幾何體的函數
-
- 3.1建立點
- 3.2 建立矩形
- 3.3 建立線段
- 4.修改幾何體
-
- 4.1 從線段删除某點
- 4.2 追加點
- 5.構造通用函數——從幾何體中提取資訊
-
- 5.1 計算幾何體中的點數
- 5.2 從線串中某位置擷取一點
- 5.3 擷取幾何體中的一個點
- 六、sql執行個體
-
- 1.道格拉斯撲克simplify——線壓縮
-
- 1.1 測試結果
- 1.2 代碼
- 2.線面緩沖區生成buffer
-
- 2.1 結果-點、線緩沖區
- 2.2 代碼
- 3.*ArcGIS-smooth功能粗實作
-
- 3.1 算法簡介
-
- 3.1.1 三次B樣條曲線方程基函數
- 3.1.2 線平滑
- 3.1.3 面平滑
- 3.2 實作政策
- 3.3 處理面的代碼
- 3.4 結果
- 4.Python連接配接及操作oracle
- @[toc]
- 一、資料導入
-
- 1.前期準備:
- 2.測試資料導入
-
- 2.1 教材參考資料導入(from .dmp格式)
- 2.2 通過shapefile檔案導入
- 二、了解空間中繼資料
-
- 1.什麼是中繼資料
- 2.字典視圖說明
-
- 2.1 diminfo屬性說明
- 2.2 srid屬性說明
- 3. 示例——填充空間中繼資料
- 三、*sdo_geometry資料類型
-
- 1.可存儲哪些幾何資料?
- 2.geometry的邏輯實作
- 3.geometry的類型、屬性和值的描述
-
- 3.1 gtype——次元和形狀的确定
- 3.2 srid——坐标系的确定
- 3.3 sdo_point——确定單點坐标
- 3.4 element_info和ordinates——線、面(多點)及複雜幾何體
- 四、空間資料的加載
- 五、sql語句操作Geometry資料——舉例
-
- 1.讀取幾何資訊
- 2.oracle數組(array)基礎
-
- 2.1 定義固定長度的一維數組
- 2.2 定義可變長度的一維數組
- 2.3 定義多元數組
- 2.4 Oracle調用函數傳回的數組
- 2.5 強制修改已經被引用的自定義type
- 2.6 oracle數組屬性和函數
- 3.建立幾何體的函數
-
- 3.1建立點
- 3.2 建立矩形
- 3.3 建立線段
- 4.修改幾何體
-
- 4.1 從線段删除某點
- 4.2 追加點
- 5.構造通用函數——從幾何體中提取資訊
-
- 5.1 計算幾何體中的點數
- 5.2 從線串中某位置擷取一點
- 5.3 擷取幾何體中的一個點
- 六、sql執行個體
-
- 1.道格拉斯撲克simplify——線壓縮
-
- 1.1 測試結果
- 1.2 代碼
- 2.線面緩沖區生成buffer
-
- 2.1 結果-點、線緩沖區
- 2.2 代碼
- 3.*ArcGIS-smooth功能粗實作
-
- 3.1 算法簡介
-
- 3.1.1 三次B樣條曲線方程基函數
- 3.1.2 線平滑
- 3.1.3 面平滑
- 3.2 實作政策
- 3.3 處理面的代碼
- 3.4 結果
- 4.Python連接配接及操作oracle
一、資料導入
1.前期準備:
- Oracleg11g:部署基本環境(https://blog.csdn.net/m0_37768631/article/details/89164329);
- sqlplus、sqlDeveloper:用于管理及操作空間資料庫、管理及操作資料,後者為圖形化操作界面;本組主要進行幾何形狀展示,未涉及屬性,否則需借助Mapbuilder或Geoserver.
- Mapbuilder:用于顯示攜帶空間資訊資料表,可用于代碼測試及結果展示;更友善地可通過sqldeveloper或PLSQL地圖視圖檢視幾何資訊并編輯,可在oracle官網下載下傳對應版本;
- 教材參考——《Oracle Spatial空間資訊管理:Oracle Database 11g-清華大學出版社》,十分經典實用的教材。
- 連結:https://pan.baidu.com/s/1dDKd6yVjQT6BzW__gXeLEQ ,提取碼:s3n4
- 内容有侵權請聯系删除
- 關于Oracle Spatial自帶的一些函數可用看這個參考及教程,文末我使用其中的緩沖區及道格拉斯函數進行了簡單的示例,可自行參考
2.測試資料導入
2.1 教材參考資料導入(from .dmp格式)
我首先通過系統賬号SYS/SYSTEM建立了新賬戶Spatial(且我設定我的密碼為spatial)并授予管理權限,因為如果使用系統賬戶登入,其自帶大量表格不便于查詢及管理。具體檢視電子書前言部分
- 通過SqlPlus導入(cmd中輸入):–imp **賬号
- 教材提供資料齊全,包含美國地區點、線、面資料及部分全球資料表,部分表含人口等統計資料可用。
2.2 通過shapefile檔案導入
此處導入有很多種方式,最簡單易行的是通過mapbuild工具直接導入
- 打開mapbuild并連接配接資料庫
- tools——import shapefile
二、了解空間中繼資料
1.什麼是中繼資料
狹隘的說,可以了解為對二進制表的某一字段(列)的限制格式,如我需要限制某表日期這一列為年月日格式且日期處在1998年1月1日之後,就是其“中繼資料”
- 攜帶空間資訊的表通常将其幾何對象存儲在
列中,這是專用于存儲空間資料的資料類型,下章會介紹;這一列資料的所有對象即作為一個空間層。SDO_GEOMETRY
- 是以:要對該列幾何對象進行操作(驗證、建索引、查詢等)需隻指定中繼資料
- 包含資訊,該資訊以字典形式存儲在:其他使用者→MDSYS→視圖→user_sdo_geometry_metadata中,可自行檢視,如下
- 維數
- 次元的邊界
- 容差
- 坐标系
2.字典視圖說明
-
用于唯一辨別每個空間層table_name 和 column_name
-
用于存儲次元資訊diminfo
-
此屬性很重要,用于存儲坐标系的相關資訊srid
2.1 diminfo屬性說明
!也就是說若該列為點——經緯度坐标,則會有兩個
類型,一個經度一個次元element
- sdo_dim_array說明
- 一個名稱、一個大小範圍、一個容差,容差一般預設,主要影響涉及要素距離的查詢精度
2.2 srid屬性說明
- 該字段一般用數字代碼指定坐标系,見示例
3. 示例——填充空間中繼資料
- 此表為customers Table
INSERT INTO USER_SDO_GEOM_METADATA VALUES ( 'CUSTOMERS', 表名 'LOCATION', 指定列名 SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance存儲次元資訊的屬性字段 ( //兩個ELEMENT,說明為(x,y)形式 SDO_DIM_ELEMENT //這是第一個元素 ( 'LONGITUDE', -- DIMENSION NAME for first dimension -180, -- SDO_LB for the dimension 180, -- SDO_UB for the dimension 0.5 -- Tolerance of 0.5 meters //指定名稱、上下限和容差,下同 ), SDO_DIM_ELEMENT //這是第二個 ( 'LATITUDE', -90, 90, 0.5 ) ), 8307 -- SRID代表大地坐标系 );
三、*sdo_geometry資料類型
1.可存儲哪些幾何資料?
- 可以存儲點線面及其集合
- 針對不同的幾何體不展開描述,本身也不難了解
- 我們更加關心的是如何實作?——不難想到,用數組
2.geometry的邏輯實作
- 下圖為幾何類型的構造圖及關系,對于我們而言應該很眼熟(在AE開發中常用)
- 簡單來講就是一個坐标的标準+N個坐标,如我建立一個笛卡爾坐标系,然後用數組存儲即可
對于更複雜的形體則通過如下列關系層層包含嵌套存儲:如:[(x1,y1,z1), (x2,y2, z2), (x3, y3, z3),...]
3.geometry的類型、屬性和值的描述
3.1 gtype——次元和形狀的确定
3.2 srid——坐标系的确定
- *這部分要展開内容會很豐富,但實際中不需要深究、有需要可看教材詳解
3.3 sdo_point——确定單點坐标
很容易了解,就是存儲 ( X , Y , Z ) ( X,Y,Z\ ) (X,Y,Z )坐标對,其資料類型的描述如下- 以下示例為插入一個點到已知表中
INSERT INTO geometry_examples (name, description, geom) VALUES
(
'POINT',
'2-dimensional Point at coordinates (-79,37) with srid set to 8307',
SDO_GEOMETRY
(
2001, -- SDO_GTYPE format: D00T. Set to 2001 for a 2-dimensional point
8307, -- SDO_SRID (geodetic)
SDO_POINT_TYPE
(
-79, -- ordinate value for Longitude
37, -- ordinate value Latitude
NULL -- no third dimension (only 2 dimensions)
),
NULL,
NULL
)
);//五個元素的填充,點、後兩個預設為空
- 另外,我們可以通過更為通俗易懂的方式(使用構造函數)構造一個點,如下
3.4 element_info和ordinates——線、面(多點)及複雜幾何體
這裡簡單描述:- ordinates為一系列的坐标點,應數組存儲如:
- element_info用于指定這些點如何組成幾何體
- 示例:見下一節
四、空間資料的加載
- 往SDO_GEOMETRY列中插入資料,以面資料為例,代碼可自行在資料中擷取
五、sql語句操作Geometry資料——舉例
1.讀取幾何資訊
- 以下代碼(來自資料)展示建立一個新的店鋪(分店)位置,計算店鋪能銷售客戶的區域範圍,并建立一個運輸的路徑,且當貨車運作時對線路進行延伸
DECLARE
b_long NUMBER;
b_lat NUMBER;
new_long NUMBER;
new_lat NUMBER;
new_branch_loc SDO_GEOMETRY;
sales_region SDO_GEOMETRY;
route SDO_GEOMETRY;
//變量聲明
BEGIN
-- Obtain Old location for branch id=1
SELECT br.location.sdo_point.x, br.location.sdo_point.y
INTO b_long, b_lat
FROM branches br
WHERE id=1;
//将擷取的x,y指派給經緯度變量
-- Compute new coordinates: say the location is displaced by 0.0025 degrees
new_long := b_long+ 0.0025;
new_lat := b_lat + 0.0025;
//生成新的點——作為新點的位置
-- Create new branch location using old location
new_branch_loc :=
point
(
X=> new_long,
Y=> new_lat,
SRID=> 8307
) ;
//用上述擷取的新點建立新分店
-- Compute sales region for this branch
sales_region :=
rectangle
(
CTR_X=> new_long,
CTR_Y=> new_lat,
EXP_X=> 0.005,
EXP_Y=> 0.0025,
SRID=> 8307
) ;
//計算銷售區域(範圍)
-- Create Delivery Route
route :=
line
(
FIRST_X=> -122.4804,
FIRST_Y=> 37.7805222,
NEXT_X=> -123,
NEXT_Y=> 38,
SRID=> 8307
) ;
//建立運輸路線
-- Update Delivery Route by adding new point
route :=
add_to_line
(
GEOM=> route,
POINT => POINT(-124, 39, 8307)
) ;
//通過加點調整運輸路線
-- Perform additional analysis such as length of route,
-- or # of customers in sales region (we will see examples in Chapters 8 and 9)
-- ...
-- Update geometry in branches table
UPDATE branches SET LOCATION = new_branch_loc WHERE id=1;
//重新整理
END;
2.oracle數組(array)基礎
- 相比于正常的語言sql數字操作顯得較為麻煩,需要輸血
-
是可以嵌套使用的,并且其内部有序array
- 先通過一個較為綜合的示例進行講解,後續展開
SET SERVEROUTPUT ON
DECLARE
-- Declare a type for the VARRAT
TYPE MY_ARRAY_TYPE IS VARRAY(10) OF NUMBER;
//聲明一個自定義的數組的類型
-- Declare a varray variable
V MY_ARRAY_TYPE;
//定義一個數組V
-- Other variables
I NUMBER;
K NUMBER;
L NUMBER;
ARRAY_CAPACITY NUMBER;
N_ENTRIES NUMBER;
//其他變量定義
BEGIN
-- Initialize the array
V := MY_ARRAY_TYPE (1,2,3,4);
//數組初始化
-- Get the value of a specific entry
DBMS_OUTPUT.PUT_LINE('* Values for specific array entries');
K := V(3);
DBMS_OUTPUT.PUT_LINE('V(3)='|| V(3));
I := 2;
L := V(I+1);
DBMS_OUTPUT.PUT_LINE('I=' || I);
DBMS_OUTPUT.PUT_LINE('V(I+1)=' || V(I+1));
//下表通路取值,輸出列印
-- Find the capacity of a VARRAY:
DBMS_OUTPUT.PUT_LINE('* Array capacity');
ARRAY_CAPACITY := V.LIMIT();
DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
N_ENTRIES := V.COUNT();
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
//列印(輸出)目前數組的容量
-- Range over all values in a VARRAY
DBMS_OUTPUT.PUT_LINE('* Array Content');
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')=' || V(I));
END LOOP;
//使用for循環周遊數組中的值
FOR I IN V.FIRST()..V.LAST() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')=' || V(I));
END LOOP;
//first()和last()分别傳回array[]最小和最大的下标
I := V.COUNT();
WHILE I IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')=' || V(I));
I := V.PRIOR(I);
END LOOP;
//prior(x)和next(x)傳回前一個和後一個下标,如上用來後向周遊
-- Extend the VARRAY
DBMS_OUTPUT.PUT_LINE('* Extend the array');
I := V.LAST();
V.EXTEND(2);
V(I+1) := 5;
V(I+2) := 6;
//給數組append值
DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')='|| V(I));
END LOOP;
-- Shrink the VARRAY
DBMS_OUTPUT.PUT_LINE('* Trim the array');
V.TRIM();
//彈出trim()
DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')='|| V(I));
END LOOP;
-- Delete all entries from the VARRAY
DBMS_OUTPUT.PUT_LINE('* Empty the array');
V.DELETE();
//删除數組所有元素
DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')='|| V(I));
END LOOP;
END;
2.1 定義固定長度的一維數組
type type_array is varray(10) of varchar2(20);
sey
1、varray(10)表示定義長度為10的數組
2、varchar2(20)表示數組為字元型,且元素字元串長度不超過20
2.2 定義可變長度的一維數組
type type_array is table of varchar2(20) index by binary_integer;
1、oracle數組的索引從1開始,而不是從0開始2、count為數組的長度1、table表示可變長度 2、index by binary_integer 表示以符号整數為索引 一維數組的初始化 /*初始化為空數組*/ var_array type_array := type_array(); /*初始化為六個元素數組*/ var_array type_array := type_array('ggs','jjh','wsb','csl','dd','bb'); /*直接對各個元素進行指派*/ var_array.extend(3); var_array(1) = '1'; var_array(2) = '2'; var_array(3) = '3'; /*通過周遊數組元素方式進行初始化操作*/ for i in 1..var_array.count loop var_array(i) = to_char(i); end loop;
2.3 定義多元數組
type type_array is table of Tbl_User % rowtype index by binary_integer;
說明:Tbl_User 為基礎表
多元數組的初始化 select * bulk collect into var_array from t_user; 說明:bulk collect 表示将批量查詢資料直接插入collection中,而不是通過cursur一條條插入 多元數組的讀取方法 for i in 1..var_array.count loop dbms_output.put_line(var_array(i).user_id); dbms_output.put_line(var_array(i).username); end loop;
2.4 Oracle調用函數傳回的數組
方法一declare aa Tbl_StrSplit := Tbl_StrSplit(); begin aa := PKG_TCH_COURSE_INIT.Fn_GetTermDate('root', 2); dbms_output.put_line(aa(1)); dbms_output.put_line(aa(2)); end; 方法二 Select Fn_GetTermDate(P_SchoolKey, P_TermKey) Into Tbl_TermDate From dual;
2.5 強制修改已經被引用的自定義type
- 修改自定義type類型的OBJTYPE_VAR時失敗
OBJTYPE_VAR原有結構 CREATE OR REPLACE TYPE "OBJTYPE_VAR" AS OBJECT ( field0 VARCHAR2(1000), field1 VARCHAR2(1000) ) 修改後的新結構 CREATE OR REPLACE TYPE "OBJTYPE_VAR" AS OBJECT ( serialNo Number, field0 VARCHAR2(1000), field1 VARCHAR2(1000) ) 執行修改時提示錯誤 “cannot drop or replace a type with type or table dependents” 原因是已經在其他地方使用了OBJTYPE_VAR,oracle不允許直接修改修改或删除被引用的OBJTYPE_VAR,但可以通過加上force關鍵字強制執行。 強制重新建立 CREATE OR REPLACE TYPE "OBJTYPE_VAR" FORCE AS OBJECT ( serialNo NUMBER, field0 VARCHAR2(1000), field1 VARCHAR2(1000) ) 強制删除掉OBJTYPE_VAR drop type OBJTYPE_VAR force 定義一維可變數組 CREATE OR REPLACE TYPE "TBLTYP_VAR" Is Table Of VARCHAR2 (32767); CREATE OR REPLACE TYPE "TBLTYP_Int" Is Table Of Pls_Integer; 定義三維可變數組 CREATE OR REPLACE TYPE "OBJTYPE_VAR" FORCE AS OBJECT ( serialNo NUMBER, field0 VARCHAR2(1000), field1 VARCHAR2(1000) )
2.6 oracle數組屬性和函數
- COUNT 傳回集合中元素的個數
- DELETE 删除集合中所有元素
- DELETE(x) 删除元素下标為x的元素 對VARRAY非法
- DELETE(x,y) 删除元素下标從X到Y的元素 對VARRAY非法
- EXIST(x) 如果集合元素x已經初始化,則傳回TRUE, 否則傳回FALSE
- EXTEND 在集合末尾添加一個元素 對Index_by非法
- EXTEND(x) 在集合末尾添加x個元素 對Index_by非法
- EXTEND(x,n) 在集合末尾添加元素n的x個副本 對Index_by非法
- FIRST 傳回集合中的第一個元素的下标号,對于VARRAY集合始終傳回1。
- LAST 傳回集合中最後一個元素的下标号, 對于VARRAY傳回值始終等COUNT.
- LIMIT 傳回VARRY集合的最大的元素個數 Index_by集合和嵌套表無用
- NEXT(x) 傳回在第x個元素之後及緊挨着它的元素值,如果x是最後一個元素,傳回null.
- PRIOR(x) 傳回在第x個元素之前緊挨着它的元素的值,如果x是第一個元素,則傳回null。
- TRIM 從集合末端開始删除一個元素 對于index_by不合法
- TRIM(x) 從集合末端開始删除x個元素
3.建立幾何體的函數
3.1建立點
CREATE OR REPLACE FUNCTION point (
x NUMBER, y NUMBER, srid NUMBER DEFAULT 8307)
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
BEGIN
RETURN SDO_GEOMETRY (
2001, srid, SDO_POINT_TYPE (x,y,NULL), NULL, NULL);
END;
3.2 建立矩形
CREATE OR REPLACE FUNCTION rectangle (
ctr_x NUMBER, ctr_y NUMBER, exp_x NUMBER, exp_y NUMBER, srid NUMBER)
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
r SDO_GEOMETRY;
BEGIN
r := SDO_GEOMETRY (
2003, srid, NULL,
SDO_ELEM_INFO_ARRAY (1, 1003, 3),
SDO_ORDINATE_ARRAY (
ctr_x - exp_x, ctr_y - exp_y,
ctr_x + exp_x, ctr_y + exp_y));
RETURN r;
END;
3.3 建立線段
CREATE OR REPLACE FUNCTION line (
first_x NUMBER, first_y NUMBER, next_x NUMBER, next_y NUMBER, srid NUMBER)
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
l SDO_GEOMETRY;
BEGIN
l := SDO_GEOMETRY (
2002, srid, NULL,
SDO_ELEM_INFO_ARRAY (1, 2, 1),
SDO_ORDINATE_ARRAY (
first_x, first_y,
next_x, next_y));
RETURN l;
END;
4.修改幾何體
以下示例均可以從資料中擷取,涉及到添加或移除點時需要注意各點在數組中存儲的實際位置
4.1 從線段删除某點
--移除點的函數
create or replace FUNCTION remove_point (
geom SDO_GEOMETRY, point_number NUMBER
) RETURN SDO_GEOMETRY
IS
g MDSYS.SDO_GEOMETRY; -- Updated Geometry
d NUMBER; -- Number of dimensions in geometry
p NUMBER; -- Index into ordinates array
i NUMBER; -- Index into ordinates array
BEGIN
d := SUBSTR (geom.SDO_GTYPE, 1, 1);
IF point_number = 0 THEN
p := geom.SDO_ORDINATES.COUNT() - d + 1;
ELSE
p := (point_number-1) * d + 1;
END IF;
IF p > geom.SDO_ORDINATES.COUNT() THEN
RETURN NULL;
END IF;
g := geom;
FOR i IN p..g.SDO_ORDINATES.COUNT()-d LOOP
g.SDO_ORDINATES(i) := g.SDO_ORDINATES(i+d);
END LOOP;
g.SDO_ORDINATES.TRIM (d);
RETURN g;
END;
4.2 追加點
- note:線的開始插入一點則傳入1,最後一點則傳入0
create or replace FUNCTION add_to_line (
geom SDO_GEOMETRY,
point SDO_GEOMETRY,
point_number NUMBER DEFAULT 0
) RETURN SDO_GEOMETRY
IS
g SDO_GEOMETRY; -- Updated geometry
d NUMBER; -- Number of dimensions in line geometry
t NUMBER; -- Geometry type
p NUMBER; -- Insertion point into ordinates array
i NUMBER;
BEGIN
d := SUBSTR (geom.SDO_GTYPE, 1, 1);
IF point_number = 0 THEN
p := geom.SDO_ORDINATES.COUNT() + 1;
ELSE
p := (point_number-1) * d + 1;
END IF;
IF point_number <> 0 THEN
IF p > geom.SDO_ORDINATES.LAST()
OR p < geom.SDO_ORDINATES.FIRST() THEN
RAISE_APPLICATION_ERROR (-20000, 'Invalid insertion point');
END IF;
END IF;
g := geom;
g.SDO_ORDINATES.EXTEND(d);
FOR i IN REVERSE p..g.SDO_ORDINATES.COUNT()-d LOOP
g.SDO_ORDINATES(i+d) := g.SDO_ORDINATES(i);
END LOOP;
g.SDO_ORDINATES(p) := point.SDO_POINT.X;
g.SDO_ORDINATES(p+1) := point.SDO_POINT.Y;
IF d = 3 THEN
g.SDO_ORDINATES(p+2) := point.SDO_POINT.Z;
END IF;
RETURN g;
END;
5.構造通用函數——從幾何體中提取資訊
5.1 計算幾何體中的點數
- –擷取點的個數函數
create or replace FUNCTION get_num_points (
g SDO_GEOMETRY)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
RETURN g.SDO_ORDINATES.COUNT() / SUBSTR(g.SDO_GTYPE,1,1);
END;
5.2 從線串中某位置擷取一點
- Getting the First,Middle, and Last Points of a Line String
-- Getting the first point of a line string
SELECT get_point(geom) p
FROM us_interstates
WHERE interstate='I95';
-- Getting the last point of a line string
SELECT get_point(geom, get_num_points(geom)) p
FROM us_interstates
WHERE interstate='I95';
-- Getting the middle point of a line string
SELECT get_point(geom, ROUND(get_num_points(geom)/2)) p
FROM us_interstates
WHERE interstate='I95';
5.3 擷取幾何體中的一個點
- Function to Extract a Point from a Geometry `CREATE OR REPLACE FUNCTION get_point (` `geom SDO_GEOMETRY, point_number NUMBER DEFAULT 1` `) RETURN SDO_GEOMETRY` `IS` `g SDO_GEOMETRY; -- Updated Geometry` `d NUMBER; -- Number of dimensions in geometry` `p NUMBER; -- Index into ordinates array` `px NUMBER; -- X of extracted point` `py NUMBER; -- Y of extracted point` `BEGIN` `-- Get the number of dimensions from the gtype` `d := SUBSTR (geom.SDO_GTYPE, 1, 1);` `-- Verify that the point exists` `IF point_number < 1` `OR point_number > geom.SDO_ORDINATES.COUNT()/d THEN` `RETURN NULL;` `END IF;` `-- Get index in ordinates array` `p := (point_number-1) * d + 1;` `-- Extract the X and Y coordinates of the desired point` `px := geom.SDO_ORDINATES(p);` `py := geom.SDO_ORDINATES(p+1);` `-- Construct and return the point` `RETURN` `SDO_GEOMETRY (` `2001,` `geom.SDO_SRID,` `SDO_POINT_TYPE (px, py, NULL),` `NULL, NULL);` `END;`
六、sql執行個體
以下執行個體,尤其是第三個,作業實作,涉及到自建函數、資料源等等,脫離本機環境則無法運作,僅供參考
其中待改進的地方頗多(包括方法政策),有錯請忽略
為了便于多次測試且不影響資料庫原資料,代碼中都有先删除表再建立臨時表的操作
1.道格拉斯撲克simplify——線壓縮
1.1 測試結果
- 原圖
- 壓縮效果 ,門檻值分别為10km 30km 100km
1.2 代碼
drop table copy_us_interstates;
create table copy_us_interstates as (select * from us_interstates);
delete from user_sdo_geom_metadata where table_name='COPY_US_INTERSTATES';
INSERT INTO user_sdo_geom_metadata VALUES (
'copy_us_interstates',
'geom',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)
),8307 );
declare
threshold number;
n number;
begin
threshold := 60000;
select count(*) into n from copy_us_interstates;
for i in 1..n loop
update copy_us_interstates set GEOM = SDO_UTIL.SIMPLIFY(GEOM,threshold,0.0000005)
WHERE id = i;
end loop;
commit;
end;
2.線面緩沖區生成buffer
2.1 結果-點、線緩沖區
2.2 代碼
--腳本1
drop table buffer_table;
CREATE TABLE buffer_table AS SELECT NAME,
SDO_GEOM.SDO_BUFFER(A.geometry, 0.5, 0.5, 'arc_tolerance=0.005 unit=mile') geom FROM points_dtz A;
--添加空間資訊中繼資料(可選)
delete from user_sdo_geom_metadata where table_name='buffer_table';
INSERT INTO user_sdo_geom_metadata VALUES (
'buffer_table',
'geometry',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)
),
8307 );
--測試,點緩沖區,地圖視圖運作
SELECT * FROM buffer_table;
``SELECT * FROM points_dtz;
--腳本2
`drop table buffer_table;` `CREATE TABLE buffer_table AS SELECT ID,` `SDO_GEOM.SDO_BUFFER(A.geometry, 0.5, 0.5, 'arc_tolerance=0.005 unit=mile') geom FROM lineforbuffer A;` `--添加空間資訊中繼資料(可選)` `delete from user_sdo_geom_metadata where table_name='buffer_table';` `INSERT INTO user_sdo_geom_metadata VALUES (` `'buffer_table',` `'geometry',` `MDSYS.SDO_DIM_ARRAY(` `MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),` `MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)` `),` `8307 );` `--測試,線緩沖區,地圖視圖運作` `SELECT * FROM buffer_table;` `SELECT * FROM lineforbuffer;`
3.*ArcGIS-smooth功能粗實作
3.1 算法簡介
簡介,更詳細的請自行百度
通過使用三次B樣條曲線方程對線條進行平滑處理,而平滑處理可分為近似拟合和插值拟合兩種。其中近似拟合是不過特征點,求出近似的平滑曲線;而插值拟合是通過特征點,需要經過反算得到各特征點的控制點,再通過近似拟合獲得過特征點的平滑曲線。- B樣條曲線的總方程為: P ( t ) = ∑ i = 0 n P i F i , k ( t ) \mathrm{P}(\mathrm{t})=\sum_{i=0}^{n} P_{i} F_{i, k}(t) P(t)=∑i=0nPiFi,k(t) ①
- 其中 P i j P_{ij} Pij是控制曲線的特征點 F i , k ( t ) F_{i, k}(t) Fi,k(t)則是 K 階 B K階B K階B樣條基函數。
3.1.1 三次B樣條曲線方程基函數
F i , k ( t ) = 1 k ! ∑ m = 0 k − i ( − 1 ) m ( m k + 1 ) ( t + k − m − j ) k F_{i, k}(t)=\frac{1}{k !} \sum_{m=0}^{k-i}(-1)^{m}\left(\begin{array}{c}{m} \\ {k+1}\end{array}\right)(t+k-m-j)^{k} Fi,k(t)=k!1∑m=0k−i(−1)m(mk+1)(t+k−m−j)k ②
其中 ( m k + 1 ) \left(\begin{array}{c}{m} \\ {k+1}\end{array}\right) (mk+1)表示階乘,化為本算法中式子為:
F 0 , 3 ( t ) = 1 6 ( 1 − t ) 3 F_{0,3}(t)=\frac{1}{6}(1-t)^{3} F0,3(t)=61(1−t)3
F 1 , 3 ( t ) = 1 6 ( 3 t 3 − 6 t 2 + 4 ) F_{1,3}(t)=\frac{1}{6}\left(3 t^{3}-6 t^{2}+4\right) F1,3(t)=61(3t3−6t2+4)
F 2 , 3 ( t ) = 1 6 ( − 3 t 3 + 3 t 2 + 3 t + 4 ) F_{2,3}(t)=\frac{1}{6}\left(-3 t^{3}+3 t^{2}+3 t+4\right) F2,3(t)=61(−3t3+3t2+3t+4)
F 3 , 3 ( t ) = 1 6 t 3 F_{3,3}(t)=\frac{1}{6} t^{3} F3,3(t)=61t3
将以上基函數代入①中,就是:
P ( t ) = P 0 ∗ F 0 , 3 ( t ) + P 1 ∗ F 1 , 3 ( t ) + P 2 ∗ F 2 , 3 ( t ) + P 3 ∗ F 3 , 3 ( t ) P(t)=P_{0} * F_{0,3}(t)+P_{1} * F_{1,3}(t)+P_{2} * F_{2,3}(t)+P_{3} * F_{3,3}(t) P(t)=P0∗F0,3(t)+P1∗F1,3(t)+P2∗F2,3(t)+P3∗F3,3(t) ③
3.1.2 線平滑
(1)近似拟合
使用遊标周遊資料點,每四個點為一組,計算每個點的拟合時需要用到它的三個後繼點,代入式③中可求得其一段三次B樣條曲線,起始點在P_0,終點在P_1,每條曲線中等間隔取11個點用于繪制曲線,隻計算到倒數第四個點。
(2)插值拟合
同樣使用遊标周遊資料點,反求每個點的控制點,需要每個點的前驅一個點和後繼兩個點作為計算資料。計算第一個點的控制點是把其本身作為前驅進行計算;計算最後兩個點時,缺少後繼時将其本身順位作為後繼進行計算。求得控制點後,再根據控制點進行線要素的近似拟合便可獲得經過特征點的平滑結果。
3.1.3 面平滑
(1)近似拟合
使用遊标周遊要素點,每四個點為一組,計算每個點的拟合時需要用到它的三個後繼點,代入式③中可求得其一段三次B樣條曲線,起始點在P_0,終點在P_1,每條曲線中等間隔取11個點用于繪制曲線;四個遊标初始化時,提前儲存前三個點的資料,當遊标走到倒數三個點時,使用初始三個點進行輔助運算。
(2)插值拟合
同樣使用遊标周遊資料點,反求每個點的控制點,需要每個點的前驅一個點和後繼兩個點作為計算資料。因為面是閉合幾何體,采用近似拟合類似的方法存儲前三個點的資料,對所有點進行計算得控制點。求得控制點後,再根據控制點進行面要素的近似拟合便可獲得經過特征點的平滑結果。
3.2 實作政策
幾何資料處理不便,同時為了比對組員寫的數學計算算法(小組分工實作),采用以下一個較為笨重繁瑣的流程實作了該過程:- 算法政策圖(解釋略-看代碼)
3.3 處理面的代碼
線的類似、備援多,略
`drop table temptable;` `create table temptable` `(` `id number primary key,` `x float,` `y float,` `geom sdo_geometry` `);` `` `drop table test;` `create table test as (select * from testsmooth);` `` `update test set geometry = feature_to_table(geometry)` `WHERE id = 0;` `--select * from temptable` `--select * from to_temp;` `--插值計算過程` `drop table to_temp;` `create table to_temp(` `id number primary key,` `x binary_double,` `y binary_double` `) ;` `declare` `-- Local variables here` `t float := 0;` `to_id number := 1;` `a1 float := 0;` `a2 float := 0;` `a3 float := 0;` `a4 float := 0;` `lx1 temptable.x%type;` `lx2 temptable.x%type;` `lx3 temptable.x%type;` `ly1 temptable.x%type;` `ly2 temptable.x%type;` `ly3 temptable.x%type;` `/*初始四個遊标推進時将前面幾個點存儲*/` `x temptable.x%type;` `y temptable.y%type;` `x1 temptable.x%type;` `x2 temptable.x%type;` `x3 temptable.x%type;` `x4 temptable.x%type;` `y1 temptable.y%type;` `y2 temptable.y%type;` `y3 temptable.y%type;` `y4 temptable.y%type;` `cursor cur1 is` `select x, y from temptable;` `cursor cur2 is` `select x, y from temptable;` `cursor cur3 is` `select x, y from temptable;` `cursor cur4 is` `select x, y from temptable;` `begin` `-- Test statements here` `open cur4;` `fetch cur4` `into x4, y4;` `lx1 := x4;` `ly1 := y4;` `fetch cur4` `into x4, y4;` `lx2 := x4;` `ly2 := y4;` `fetch cur4` `into x4, y4;` `lx3 := x4;` `ly3 := y4;` `fetch cur4` `into x4, y4;` `open cur3;` `fetch cur3` `into x3, y3;` `fetch cur3` `into x3, y3;` `fetch cur3` `into x3, y3;` `open cur2;` `fetch cur2` `into x2, y2;` `fetch cur2` `into x2, y2;` `open cur1;` `fetch cur1` `into x1, y1;` `/*遊标就位和初始值指派*/` `while cur1%found loop` `if cur4%notfound then` `if cur3%notfound then` `if cur2%notfound then` `/*計算最後一個點*/` `t := 0;` `FOR int in 1 .. 11 LOOP` `a1 := POWER((1 - t), 3) / 6;` `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;` `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;` `a4 := POWER(t, 3) / 6;` `x := a1 * x1 + a2 * lx1 + a3 * lx2 + a4 * lx3;` `y := a1 * y1 + a2 * ly1 + a3 * ly2 + a4 * ly3;` `insert into to_temp (id, x, y) values (to_id, x, y);` `to_id := to_id + 1;` `t := t + 0.1;` `end loop;` `else` `/*計算倒數第二個點*/` `t := 0;` `FOR int in 1 .. 11 LOOP` `a1 := POWER((1 - t), 3) / 6;` `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;` `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;` `a4 := POWER(t, 3) / 6;` `x := a1 * x1 + a2 * x2 + a3 * lx1 + a4 * lx2;` `y := a1 * y1 + a2 * y2 + a3 * ly1 + a4 * ly2;` `insert into to_temp (id, x, y) values (to_id, x, y);` `to_id := to_id + 1;` `t := t + 0.1;` `end loop;` `end if;` `else` `/*計算倒數第三個點*/` `t := 0;` `FOR int in 1 .. 11 LOOP` `a1 := POWER((1 - t), 3) / 6;` `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;` `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;` `a4 := POWER(t, 3) / 6;` `x := a1 * x1 + a2 * x2 + a3 * x3 + a4 * lx1;` `y := a1 * y1 + a2 * y2 + a3 * y3 + a4 * ly1;` `insert into to_temp (id, x, y) values (to_id, x, y);` `to_id := to_id + 1;` `t := t + 0.1;` `end loop;` `end if;` `else` `/*計算一般點*/` `t := 0;` `FOR int in 1 .. 11 LOOP` `a1 := POWER((1 - t), 3) / 6;` `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;` `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;` `a4 := POWER(t, 3) / 6;` `x := a1 * x1 + a2 * x2 + a3 * x3 + a4 * x4;` `y := a1 * y1 + a2 * y2 + a3 * y3 + a4 * y4;` `insert into to_temp (id, x, y) values (to_id, x, y);` `to_id := to_id + 1;` `t := t + 0.1;` `end loop;` `end if;` `fetch cur1` `into x1, y1;` `fetch cur2` `into x2, y2;` `fetch cur3` `into x3, y3;` `fetch cur4` `into x4, y4;` `end loop;` `commit;` `end;` `\--` `declare` `n1 number;` `n2 number;` `px number;` `py number;` `BEGIN` `select get_num_points(geometry) into n1 from test WHERE id =0;` `select count(*) into n2 from to_temp;` `for i in 1..n1 loop` `update test set geometry = remove_point(geometry,0) where id =0;` `end loop;` `for i in 1..n2 loop` `select x into px from to_temp where id=i;` `select y into py from to_temp where id=i;` `update test set geometry = add_to_line(geometry,point(px,py),0) where id =0;` `end loop;` `end;` `` `delete from user_sdo_geom_metadata where table_name='TEST';` `INSERT INTO user_sdo_geom_metadata VALUES (` `'TEST', ---含有空間字段的表名` `'geometry', ---字段名(列名)` `MDSYS.SDO_DIM_ARRAY(` `MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),` `MDSYS.SDO_DIM_ELEMENT('Y', 0, 90, 0.05)` `),` `8307` `);` `--drop index testindex;` `--create INDEX testindex ON test(geometry) INDEXTYPE IS MDSYS.SPATIAL_index;`
3.4 結果
- 面平滑後的圖
- 線平滑後的圖
4.Python連接配接及操作oracle
以下不完善,待續,且僅能通過簡單語句/腳本測試
`user = input("請輸入使用者名: ");` `password = input("輸入密碼:");` `Filepath = input(“請添加檔案路徑:”);` `try:` `os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8'` `db = cx_Oracle.connect(user, password, 'localhost/orcl')` `c = db.cursor()` `with open('buffer.sql', 'r+') as f:` `sql_list = f.read().split(';')[:-1];` `sql_list = [x.replace('\n', ' ') if '\n' in x else x for x in sql_list] # 将每段sql裡的換行符改成空格` `for sql_item in sql_list:` `print(sql_item)` `c.execute(sql_item)` `except cx_Oracle.Error as e:` `print(e)` `finally:` `c.close()` `db.commit()` op;` `end;`
`delete from user_sdo_geom_metadata where table_name='TEST';` `INSERT INTO user_sdo_geom_metadata VALUES (` `'TEST', ---含有空間字段的表名` `'geometry', ---字段名(列名)` `MDSYS.SDO_DIM_ARRAY(` `MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),` `MDSYS.SDO_DIM_ELEMENT('Y', 0, 90, 0.05)` `),` `8307` `);` `--drop index testindex;` `--create INDEX testindex ON test(geometry) INDEXTYPE IS MDSYS.SPATIAL_index;` ### 4.Python連接配接及操作oracle > 以下不完善,待續,且僅能通過簡單語句/腳本測試 ```python `user = input("請輸入使用者名: ");` `password = input("輸入密碼:");` `Filepath = input(“請添加檔案路徑:”);` `try:` `os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8'` `db = cx_Oracle.connect(user, password, 'localhost/orcl')` `c = db.cursor()` `with open('buffer.sql', 'r+') as f:` `sql_list = f.read().split(';')[:-1];` `sql_list = [x.replace('\n', ' ') if '\n' in x else x for x in sql_list] # 将每段sql裡的換行符改成空格` `for sql_item in sql_list:` `print(sql_item)` `c.execute(sql_item)` `except cx_Oracle.Error as e:` `print(e)` `finally:` `c.close()` `db.commit()` `db.close()`