Use SQL to directly manipulate featureclass (Oracle)

Use SQL to directly manipulate featureclass (Oracle). Here we mainly talk about the element class with the storage type st_geometry.
The featureclass operations mainly include the following:
1. Data insertion, deletion, and update
The insert statement is directly used for data insertion. Two methods can be used to construct st_geometry:
1) WKT Encoding
2) WKB Encoding
The above two types of encoding are both OGC standard encoding methods. They are converted from WKT or WKB to st_geometry through st_polyfromtext (), st_pointfromwkb (), and a series of similar functions.
The following are all related st functions:
ST_GeomFromText-Creates an st_geometry from a text representation of any geometry type
ST_PointFromText-Creates an st_point from a point text Representation
ST_LineFromText-Creates an st_linestring from a linestring text Representation
ST_PolyFromText-Creates an st_polygon from a polygon text Representation
ST_MPointFromText-Creates an st_multipoint from a multipoint Representation
ST_MLineFromText-Creates an st_multilinestring from a multilinestring Representation
ST_MPolyFromText-Creates an st_multipolygon from a multipolygon Representation
ST_AsText-converts an existing geometry into a text Representation
ST_GeomFromWKB-Creates an st_geometry from a WKB representation of any geometry type
ST_PointFromWKB-Creates an st_point from a point WKB Representation
ST_LineFromWKB-Creates an st_linestring from a linestring WKB Representation
ST_PolyFromWKB-Creates an st_polygon from a polygon WKB Representation
ST_MPointFromWKB-Creates an st_multipoint from a multipoint WKB Representation
ST_MLineFromWKB-Creates an st_multilinestring from a multilinestring WKB Representation
ST_MPolyFromWKB-Creates an st_multipolygon from a multipolygon WKB Representation
ST_AsBinary-converts an existing geometry value into well-known binary representation.
The encoding methods of WKT and WKB are described in the next section.
The following SQL statement is used to insert data and execute the following SQL statement:
Insert into sensitive_areas (area_id, name, area_size, type, zone, FID)
Values (1, 'summerhill elementary school ', 67920.64, 'school', SDE. st_polyfromtext ('polygon
(52, 58, 52) ', 0 ));
Insert a record in the sensitive_areas table.

Open ArcMap and load the featureclass to view the newly added record.

Update record
Updatesensitive_areas
Set zone = SDE. st_pointfromtext ('polygon (52 30,58 30,58 23,50 23,50 28) ', 0 ))
Where area_id = 1;
Delete record
Delete from sensitive_areas where names
(Select SA. Names
From sensitive_areas SA, hazardous_sites HS
Where SDE. st_overlaps (SA. Zone, SDE. st_buffer (HS. Location,. 01) = 1 );
2. Query
Indexes are used for spatial queries, where infomix uses the r tree index, PostgreSQL uses the generalized search tree (GIST) R-tree index, and Oracle and DB2 uses the grid index.
To query in Oracle, perform the following steps:
1) First, compare the grid and query range to find all the grids within the query range.
2) Find all the elements in these grids.
3) Compare the outer envelope rectangle of these elements with the query range to find all the elements that are within the query range and that are at the intersection of the query range.
4) use the st function for final filtering (generally st_intersects or st_contains) to find elements that fully comply with the conditions.
The following is an example of space query:
Select SA. Name "sensitive areas", HS. Name "hazardous sites"
From sensitive_areas SA, hazardous_sites HS
Where SDE. st_overlaps (SA. Zone, SDE. st_buffer (HS. Location,. 01) = 1;
3. Create an index
Create index sa_idx on sensitive_areas (zone)
Indextype is SDE. st_spatial_index
Parameters ('st _ grids = 1, 3, 0 st_srid = 0 ');

4. Create a space Reference System
ESRI recommends that you use ArcGIS Desktop to create a spatial reference system, but you can also use SQL statements to create a spatial reference system. Execute the following SQL statement:
Insert into SDE. st_spatial_references values (
Gcs_north_american_1983,
1,
-400,
-400,
1000000000,
-100000,
10000,
-100000,
10000,
9.999e35,
-9.999e35,
9.999e35,
-9.999e35,
9.999e35,
-9.999e35,
9.999e35,
-9.999e35,
4269,
'Gcs _ north_american_1983 ',
'Projected ',
Null,
'Geogcs ["gcs_north_american_1983", datum ["d_north_american_1983", Spheroid
["Grs_1980", 6378137.0, 298.257222101], primem ["Greenwich", 0.0], Unit
["Degree", 0.0174532925199433] ',
'Arcsde spref'
);