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