Oracle spatial query with data type Sdo_geometry

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