Oracle spatial constructs points, lines, polygons

There are several ways to store data in ArcGIS from ARCSDE storage to Oracle, including binary long Raw, Esri St_geometry, and sdo_geometry based on Oracle spatial.

Recently I have mostly studied the storage method based on Oracle spatial, where the type of Geometry column shape is mdsys.sdo_geometry type.

With regard to the Sdo_geometry types defined by Oracle spatial, I will explain in a separate chapter that individuals feel that the SQL statements about manipulating spatial data intersect with PostgreSQL, MS SQL Server is troublesome, The following is an SQL statement that constructs the geometry of points, lines, polygons, and so on in the Oracle library:

------------Single Point

DECLARE

Geom Sdo_geometry;

BEGIN

Geom:=sdo_geometry
(2001,---2001 stands for a single point
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

--------------Multi-point

Geom Sdo_geometry;
BEGIN
Geom:=sdo_geometry
(2005,---2005 stands for Multi-point
Null
Null
Sdo_elem_info_array (1,1,7),---"7" represents the coordinate data with seven points
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

-----------------polyline String: A polyline string that consists of straight lines

DECLARE
Geom Sdo_geometry;

BEGIN

Geom:=sdo_geometry
(2002,---2002 for single or curved lines
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

---------------Single Curve

DECLARE
Geom Sdo_geometry;

BEGIN

Geom:=sdo_geometry
(2002,
Null
Null
Sdo_elem_info_array (1,2,2),----2, 2 represents a polyline composed of curved fragments, each of which is described by three points: The starting point, any point on the curve segment, the end point, and the previous curve end is the starting point of the next curve.
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-line string

DECLARE
Geom Sdo_geometry;

BEGIN

Geom:=sdo_geometry
(2006,---2006 for multi-line or multi-curve
Null
Null
Sdo_elem_info_array (1,2,1,17,2,1),--"17" represents the position of the coordinates of the first point in the second line in the coordinate sequence
Sdo_ordinate_array (63918.6936868593, 39300.6724619204,--First line
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,--second line
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

--------------Complex Multiline lines
DECLARE
Geom Sdo_geometry;
BEGIN

Geom:=sdo_geometry (
2002,
Null
Null
Sdo_elem_info_array (1,4,2, 1,2,1, 3,2,2),--complex multiline

---has three triples, the last of which is the Sdo_elem_info attribute element ternary, and the previous one is the expression combination. Refer to Figure 4 for details.
---The first ternary "1,4,2", according to 4 can be obtained is a description of ternary group, 2 means that there are two geometric elements, that is, the latter two triples describe their respective geometry A and Geometry B.
---the second ternary "1,2,1", which is the Sdo_elem_info attribute element ternary, which describes the geometry A. As shown in Figure 4, it is a straight line segment, and the last node of the line segment is the starting point of the next Geometry B, where geometry A and geometry B have geometric nodes coincident.
---the second ternary "3,2,2", which is the Sdo_elem_info attribute element ternary, which describes geometry B. As shown in Figure 4, it is a curved segment that coincides with the start point of the Geometry B and the end node of the geometry 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

--------------Round

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

--------------Simple Faces

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

---------Polygon: Ring (also called Polygon with "hole")

DECLARE

Geom Sdo_geometry;

BEGIN

Geom:=sdo_geometry
(2003,
Null
Null
Sdo_elem_info_array (1,1003,1,15,2003,1),-----the first "1" represents the position of the first coordinate of the outer polygon in the coordinate array, and the last "1" means that the polygon is a polygon composed of straight lines
-----where 1003 indicates that the corresponding coordinate sequence consists of an outer polygon ring (clockwise), while 2003 indicates that the corresponding coordinate sequence consists of a geometry B as an inner polygon ring (counterclockwise)
----15 represents the starting position of the geometry B-coordinate sequence, that is, the geometric coordinates starting at 15 are composed of Geometry B, and 1 to 18 make up geometry a
Sdo_ordinate_array ( 40604250.220275894,3498887.5979003981,40602839.3861084,3498132.3029174954,40602696.877685592,3497070.6145019978,40603886.8 234863,3496137.183898896,40604549.488098189,3496557.5839232951,40604984.139099091,3497947.0416869968,40604250.220275894,3 498887.5979003981,40604214.5930786,3496977.9838866964,40603430.7963257,3496956.6077270955,40603430.7963257,3497241.624694 7944,40603417.9331055,3497762.5902709961,40604307.223693892,3498210.6824950948,40604556.613525391,3497483.8892821968,4060 4214.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

----------Polygon: Multi-sided

DECLARE

Geom Sdo_geometry;

BEGIN

Geom:=sdo_geometry
(A,--polygon or multiple polygons
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),-----an element combination of three polygons
---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 constructs points, lines, polygons