Most of the information found online is about sdo_geometry and adding features through SQL statements, querying features, and so on. No examples of stored procedures were found, so I had to do it myself.
Get ready
Environment:windowsxp system, install Arcgis 10.1,oracle11g32 bit,plsql.
Data: Import test data through ArcGIS Direct attached database, select Sdo_geometry data type when importing .
The test data includes point line surface, point data map_user_point, line data map_user_line, polygon data map_user_poly. The following are shown in ArcMap:
Create a stored procedure add a geometric feature creation function to create a geometric feature
Create or replace FUNCTION fun_creategeometry
(Wkt in VARCHAR2
, Srid in number
) RETURN Sdo_geometry as
BEGIN
RETURN sdo_geometry (Wkt,srid);
END Fun_creategeometry;
Creating a stored procedure inserting features into a vector table
Create or replace
PROCEDURE Addnewpoint (
OBJECTID in number
WKT in VARCHAR2
, SRID in number
) as
V_return Sdo_geometry;
BEGIN
V_return: = Fun_creategeometry (
WKT = WKT,
Srid = Srid
);
INSERT into map_user_8332 (OBJECTID, SHAPE)
VALUES
(OBJECTID, V_return);
END Addnewpoint;
Call the stored procedure to create the geometry, noteThe Srid must be consistent
Called in Oracle SQL developter:
DECLARE
OBJECTID number;
WKT VARCHAR2 (200);
SRID number;
BEGIN
OBJECTID: = 100;
WKT: = ' point (118.731963311458 32.0661417793197) ';
SRID: = 4326;
Addnewpoint (OBJECTID, WKT, SRID);
END;
Called in SQL Plus:
EXECUTE addnewpoint (1, ' point (118) ', 4326)
Commit
Spatial query
The SQL statement gives the features within the feature 500 buffer, the variable part is the input features wkt (point, Line, polygon), query layer, cache distance, examples are as follows:
Create a package that defines acursor
CREATE OR REPLACE
Package Package_geometry AS
TYPE geometry_cursor is ref CURSOR;
/* TODO Enter package declarations (types, exceptions, methods etc) here */
END Package_geometry;
Create a stored procedure that returns query resultscursor
Create or replace
PROCEDURE Searchgeometry (
WKT VARCHAR2
, the SRID number
, Buflength VARCHAR2
, p_cursor in Out Package_geometry. Cursor_geometry) as
Srarch_geom Sdo_geometry;
Buffercondition VARCHAR2 (35);
BEGIN
Srarch_geom: = Fun_creategeometry (
WKT = WKT,
Srid = Srid
);
Buffercondition: = ' distance= ' | | Buflength;
Open p_cursor for SELECT c_c.objectid,c_c.name objname from map_user_0000008332 c_c
WHERE sdo_within_distance (C_c.shape, Srarch_geom, buffercondition) = ' TRUE ';
END Searchgeometry;
Calling stored procedure query features
Called in Oracle SQL developter or plsql:
DECLARE
Buflength VARCHAR2 (25);
WKT VARCHAR2 (200);
SRID number;
Result_cur Package_geometry. Cursor_geometry;
OBJECTID number;
ObjName VARCHAR2 (200);
BEGIN
WKT: = ' point (118.731963311458 32.0661417793197) ';
SRID: = 4326;
Buflength: = ' 500 ';
Searchgeometry (WKT, Srid,buflength, result_cur);
Loop
Fetch Result_cur
into Objectid,objname;
Exit when Result_cur%notfound;
Dbms_output.put_line (OBJECTID);
Dbms_output.put_line (objname);
End Loop;
CLOSE result_cur;
END;
Spatial Query test query point aroundpoint data within 500 meters
Select a point within the point data to get its wkt coordinates:
SELECT CC. SHAPE. Get_wkt () from Map_user_point CC WHERE cc.name= ' Lake Garden '
Point (118.748698730662 32.035395102502)
Modify the Searchgeometry query in the table named Map_user_point,
Query Result:
8
Station District
9
Style West Village
7
Bud Community
11
Ying-bin Village
10
Cha Ting cun
12
Garden
19
Park Central Garden Apartment
ArcMap Query Results:
Plsql query results are consistent with ArcMap query results.
Around the query pointline data within 500 meters
Select a point within the point data to get its wkt coordinates:
SELECT CC. SHAPE. Get_wkt () from Map_user_point CC WHERE cc.name= ' Vanke Golden Home '
Point (118.759385095746 32.0420345318682)
Modify the query parameter wkt to ' point (118.759385095746 32.0420345318682)', modify the stored procedure searchgeometry query in the table named MAP_ User_line.
Query Result:
Line 2 is selected, thebuffer query for that point is made in ArcMap, and Segment 2 is selected. Modify the query buffer size to 700 meters, Segment 2,3 is selected.
Plsql query results are consistent with ArcMap query results.
Around the query pointpolygon data within 500 meters
Select a point within the point data to get its wkt coordinates:
SELECT CC. SHAPE. Get_wkt () from Map_user_point CC WHERE cc.name= ' Lake Residence '
Point (118.759724313726 32.0354580244683)
Modify the query parameter wkt to ' point (118.759724313726 32.0354580244683)', modify the stored procedure searchgeometry query in the table named Map_user_ POLY.
Query Result:
2
Face2
6
Face6
7
Face7
Polygons 2,6,7 are selected, and the500-meter buffer query for that point is made in ArcMap , with polygons 2,6, and7 selected.
Plsql query results are consistent with ArcMap query results.
Around the enquiry linepoint data within 500 meters
Select a segment within the line data to get its wkt coordinates:
SELECT CC. SHAPE. Get_wkt () from Map_user_line CC WHERE cc.name= ' Line3 '
Wkt of the query obtained :
LINESTRING (118.763605759798 32.0391445982447, 118.763755695669 32.0386259340397, 118.763874074329 32.0381089704527, 118.763949962721 32.0377700573423, 118.764039433573 32.0374028695468, 118.764145034666 32.0369562671181, 118.764232941597 32.0365690568166, 118.76430597554 32.0362041074337, 118.764344298351 32.0359930356501, 118.764388237427 32.0357066978059, 118.764487386784 32.0350466349943, 118.764565439843 32.0345753407794, 118.76460084975 32.0343352406788, 118.764633191169 32.0341192388119, 118.764646064065 32.0339927329783, 118.764660872302 32.0337829885941, 118.764663602643 32.0336185295715, 118.764666619869 32.0334691063143, 118.764654318942 32.0330732652191, 118.764633596763 32.032851987929, 118.764589327635 32.0325688795502, 118.764551264729 32.0323638844857, 118.764504484694 32.0321339440263, 118.764435108294 32.0317689748584, 118.764351598148 32.0313420450993, 118.764308733761 32.0311321028643, 118.764242330519 32.0307380145478, 118.76422386474 32.0305728504567)
Modify the query parameter wkt to the wkt that you queried in the previous step , and modify the stored procedure searchgeometry query in the table named Map_user_point.
Query Result:
5
Bi Hong Court
6
Grassroots habitat
22
June Yuan
23
Mo Sorrow East Residence community
24
Famous Lake Residence
25
Vanke Golden Home
36
2nd Luo Lang Lane , residential district
37
Universal Xuan District
38
Zhongxing Village
39
Stop Horse Camp Community
44
Tao Li Wang Xiang Xin yuan
45
Hanzhong Court Property Management District
46
Hanzhong Court Property Management District
47
Stop Ma Cun
48
Thong Woo Garden
49
Han Jiayuan District
50
Han Jiayuan District
Query the point features in ArcMap through the Line3 500-meter buffer, with query results such as:
Plsql query results are consistent with ArcMap query results.
Around the enquiry lineline data within 500 meters
Plsql query results are consistent with ArcMap query results.
Around the enquiry linepolygon data within 500 meters
Plsql Query Results:
1
Face1
3
Face3
6
Face6
7
Face7
ArcMap query results such as:
Plsql query results are consistent with ArcMap query results.
Oracle spatial query with data type Sdo_geometry