Oracle Spatial構建點、線、面

標籤：

在ArcGIS中通過ArcSDE儲存空間資料到Oracle中有多種儲存方式，分別有：二進位Long Raw 、ESRI的ST_Geometry以及基於Oracle Spatial的SDO_Geometry等等。

最近我主要是學習基於Oracle Spatial的儲存方式，通過這種儲存方式的幾何列Shape的欄位類型為mdsys.sdo_geometry類型。

關於Oracle Spatial定義的SDO_GEOMETRY類型，我將單獨在一章裡來說明，個人覺得關於操作空間資料的SQL語句來說，相交於PostgreSQL、MS SQLServer麻煩一些，下面將給出在Oracle庫中構建點、線、面等幾何體的SQL語句：

------------單點

DECLARE

geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2001,---2001代表單點
null,
sdo_point_type
(
63918.6936868593,
39300.6724619204,
null),
null,
null
);

execute immediate ‘update zd_test set shape=:gm where objectid=227944‘ using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227944

--------------多點

geom sdo_geometry;
BEGIN
geom:=sdo_geometry
(2005,---2005代表多點
null,
null,
sdo_elem_info_array(1,1,7),---"7"代表有七個點的座標資料
sdo_ordinate_array( 63918.6936868593, 39300.6724619204,63918.7296493314, 39302.5029543953,63918.7576130323, 39304.3335865351, 63918.777577428, 39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709 )
);
execute immediate ‘update zd_test set shape=:gm where objectid=227945‘ using geom;
END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227945

 

-----------------折串線：由直線組成的折串線

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2002,---2002代表單線或曲線
null,
null,
sdo_elem_info_array(1,2,1),
sdo_ordinate_array(63918.6936868593, 39300.6724619204, 63918.7296493314, 39302.5029543953, 63918.7576130323, 39304.3335865351, 63918.777577428, 39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709, 63918.7774366272, 39313.4876188202, 63918.7574018344, 39315.3183549036, 63918.7293677405, 39317.1489859668)
);

execute immediate ‘update zd_test set shape=:gm where objectid=227943‘ using geom;

END;

select objectid,shape from zd_test where objectid=227943

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227943

 

---------------單曲線

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2002,
null,
null,
sdo_elem_info_array(1,2,2),----2,2 代表由曲線片段組成的多義線，每一線段由三個點來描述：起點、任意在曲線段上的一點，終點，且前一個曲線終點是下一個曲線的起點
sdo_ordinate_array(40602883.52196759, 3497165.8231581002, 40602944.9893748, 3497397.0575331002, 40602901.083979294, 3497657.5621717945)
);

execute immediate ‘update zd_test set shape=:gm where objectid=227937‘ using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227937

---------------z折串線

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2006,---2006代表多線或多曲線
null,
null,
sdo_elem_info_array(1,2,1,17,2,1),--"17"代表第二條直線第一個點的座標在座標序列中的位置
sdo_ordinate_array(63918.6936868593, 39300.6724619204, --第一條線
63918.7296493314, 39302.5029543953, 63918.7576130323, 39304.3335865351, 63918.777577428, 39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709,
63824.5720349896, 39287.4658036968, --第二條線
63824.4568945635, 39285.8607059794, 63824.3337761012, 39284.2562004966, 63824.3191716341, 39284.0725430048, 63818.6178073972, 39264.4770559283, 63818.3871845309, 39262.8932601417, 63818.1486480711, 39261.3106368899, 63817.9022039774, 39259.7292257135, 63817.6478584071, 39258.1490661232
)
);

execute immediate ‘update zd_test set shape=:gm where objectid=227947‘ using geom;

END;

select objectid,shape from zd_test where objectid=227947

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227947

--------------複雜多義線
DECLARE
geom sdo_geometry;
BEGIN

geom:=sdo_geometry(
2002,
NULL,
NULL,
sdo_elem_info_array(1,4,2, 1,2,1, 3,2,2), -- 複雜多義線

---有三個三元組，其中後兩個是SDO_ELEM_INFO屬性元素三元組，前一個為表述組合。具體參考 圖4。
---第一個三元組【1,4,2】，根據4可以得到是個描述三元組，2表示有兩個幾何元素組成，即後兩個三元組描述各自的幾何A和幾何B。
---第二個三元組【1,2,1】，為SDO_ELEM_INFO屬性元素三元組，它描述的是幾何A。根據 圖4可以得到它是一條直線段，且該直線段的最後一個節點還是下一個幾何B的開始點，即幾何A和幾何B有幾何節點重合。
---第二個三元組【3,2,2】，為SDO_ELEM_INFO屬性元素三元組，它描述的是幾何B。根據 圖4可以得到它是一條曲線段，該幾何B的起點和幾何A終點節點重合。
sdo_ordinate_array( 40602944.9893748, 3497397.0575331002,40602883.52196759, 3497165.8231581002,40602356.658564195, 3497423.4007337987, 40602421.052973494, 3497730.7377698943)
--sdo_ordinate_array(10,10, 10,14, 6,10, 14,10)
);

execute immediate ‘update zd_test set shape=:gm where objectid=218950‘ using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=218950

--------------圓

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,4),
sdo_ordinate_array(40603430.7963257,3497241.6246947944,40603417.9331055,3497762.5902709961,40604307.223693892,3498210.6824950948)
);

execute immediate ‘update zd_test set shape=:gm where objectid=242137‘ using geom;

END;

select objectid,shape from zd_test where objectid=242137
select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=242137

--------------簡單面

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,1),
sdo_ordinate_array(58184.2949999999, 39390.5210000016, 58208.6500000013, 39291.8900000025, 58499.0099999998, 39310.700000003, 58482.4699999997, 39409.8360000011, 58184.2949999999, 39390.5210000016)
);

execute immediate ‘update zd_test set shape=:gm where objectid=242137‘ using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=242137

---------多邊形：環（也叫帶“洞”的多邊形）

DECLARE

geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,1,15,2003,1),-----第一個“1”代表外多邊形的第一個座標在座標數組中的位置，最後一個“1”代表該多邊形是由直線組成的多邊形
-----其中1003表明對應的座標序列組成的幾何A為外多邊形環(順時針)；而2003表明對應的座標序列組成的幾何B為內多邊形環(逆時針)
----15 表示幾何B座標序列開始的位置，也就是說從15開始的幾何座標組成幾何B，而1到18組成幾何A
sdo_ordinate_array(40604250.220275894,3498887.5979003981,40602839.3861084,3498132.3029174954,40602696.877685592,3497070.6145019978,40603886.8234863,3496137.183898896,40604549.488098189,3496557.5839232951,40604984.139099091,3497947.0416869968,40604250.220275894,3498887.5979003981,40604214.5930786,3496977.9838866964,40603430.7963257,3496956.6077270955,40603430.7963257,3497241.6246947944,40603417.9331055,3497762.5902709961,40604307.223693892,3498210.6824950948,40604556.613525391,3497483.8892821968,40604214.5930786,3496977.9838866964)
);

execute immediate ‘update zd_test set shape=:gm where objectid=234137‘ using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=234137

----------多邊形：多面

DECLARE

geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2007,--多面或多個多邊形組成
null,
null,
sdo_elem_info_array(1,1003,1,9,1003,1),
---sdo_elem_info_array(1,1003,1,15,1003,1,29,1003,1),-----由三個面組成的元素組合
---sdo_ordinate_array(40602883.52196759, 3497165.8231581002, 40602944.9893748, 3497397.0575331002, 40602901.083979294, 3497657.5621717945, 40602684.4847361, 3497678.0513685942, 40602543.9877269, 3497408.7655408978, 40602605.455134094, 3497154.1149671972, 40602883.52196759, 3497165.8231581002, 40602356.658564195, 3497423.4007337987, 40602421.052973494, 3497730.7377698943, 40602301.045160994, 3497824.4023818, 40602116.642939195, 3497839.0373914987, 40602005.416376695, 3497640.0001600981, 40602107.861933395, 3497285.8309706002, 40602356.658564195, 3497423.4007337987, 40601762.4737498, 3497379.4953382984, 40601823.941157, 3497537.5545424, 40601900.043757096, 3497751.226783596, 40601815.1601512, 3497964.8991468996, 40601692.2253366, 3497959.0451429933, 40601490.260981292, 3497593.1677626, 40601616.1227365, 3497376.5683362931, 40601762.4737498, 3497379.4953382984 )
sdo_ordinate_array(63918.6936868593, 39300.6724619204, 63918.7296493314, 39302.5029543953, 63918.7576130323, 39304.3335865351,63918.6936868593, 39300.6724619204, 63918.777577428 ,39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709,63918.777577428, 39306.1643233876)
);

execute immediate ‘update zd_test set shape=:gm where objectid=234169‘ using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=234169

 

Oracle Spatial構建點、線、面