oracle 存储过程 空间查询.docxVIP

Oracle的空间要素查询空间数据类型为sdo_geometry 准备环境：windowsxp系统，安装Arcgis 10.1，oracle11g32位，plsql。数据：通过arcgis直连数据库，导入测试数据，导入时数据类型选择SDO_GEOMETRY。测试数据包含点线面，点数据MAP_USER_POINT, 线数据MAP_USER_LINE, 面数据MAP_USER_POLY。在arcmap中显示如下：创建存储过程添加几何要素创建函数创建几何要素create or replace FUNCTION FUN_CreateGeometry( wkt IN VARCHAR2,srid IN NUMBER) RETURN sdo_geometry ASBEGIN RETURN SDO_GEOMETRY(wkt,srid);END FUN_CreateGeometry;创建存储过程向矢量表插入要素create or replacePROCEDURE 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;调用存储过程创建几何，注意srid必须一致Oracle SQL Developter中调用：DECLARE OBJECTID NUMBER; WKT VARCHAR2(200); SRID NUMBER;BEGINOBJECTID := 100;WKT := POINT (118.731963311458 32.0661417793197) ;SRID := 4326;ADDNEWPOINT(OBJECTID, WKT, SRID); END;Sql plus中调用： EXECUTE ADDNEWPOINT(1,’POINT(118 32)’, 4326)Commit空间查询Sql语句给定要素500缓冲区之内的要素，可变部分为输入要素wkt（点，线，面），查询图层，缓存距离，示例如下：创建包，定义一个cursorCREATE OR REPLACEPACKAGE PACKAGE_GEOMETRY AS TYPE GEOMETRY_CURSOR IS ref cursor; /* TODO enter package declarations (types, exceptions, methods etc) here */END PACKAGE_GEOMETRY;创建存储过程，返回查询结果cursorcreate or replacePROCEDURE SEARCHGEOMETRY( WKT VARCHAR2,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;调用存储过程查询要素Oracle SQL Developter或plSql中调用：DECLARE BUFLENGTH VARCHAR2(25); WKT VARCHAR2(200); SRID NUMBER; RESULT_CUR PACKAGE_GEOMETRY.CURSOR_GEOMETRY; OBJECTID NUMBER; OBJNAME VARCHAR2(200);BEGINWKT := POINT (118.731963311458 32.0661417793197) ;SRID