3 ORACLE 10g的安装与使用-spatial实例

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Oracle 10g,的安装和使用,及,Oracle Spatial,实例分析,1,、从安装程序安装,Oracle 10g,2.,利用,Database Configuration Assistant,创,建数据库,3,、还可以从,Universal Installer,安装,Oracle 10g,4,、,Oracle 10g,网络管理,Net manager,5,、,Oracle 10g,数据库管理,6,、,Oracle Spatial,实例分析,Oracle Spatial,实例分析,drop INDEX,cola_spatial_idx,;,drop TABLE,cola_markets,;,Delete from USER_SDO_GEOM_METADATA;,CREATE TABLE,cola_markets,(,mkt_id,NUMBER PRIMARY KEY,name VARCHAR2(32),shape MDSYS.SDO_GEOMETRY);,INSERT INTO,cola_markets,VALUES(,1,cola_a,MDSYS.SDO_GEOMETRY(,2003, - 2-dimensional polygon,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), - one rectangle (1003 = exterior),MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7) - only 2 points needed to,);,INSERT INTO,cola_markets,VALUES(,2,cola_b,MDSYS.SDO_GEOMETRY(,2003, - 2-dimensional polygon,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),- one polygon (exterior polygon ring),MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1),);,INSERT INTO,cola_markets,VALUES(,3,cola_c,MDSYS.SDO_GEOMETRY(,2003, - 2-dimensional polygon,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),- one polygon (exterior polygon ring),MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3),);,INSERT INTO,cola_markets,VALUES(,2,cola_b,MDSYS.SDO_GEOMETRY(,2003, - 2-dimensional polygon,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), - one polygon (exterior polygon ring),MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1),);,INSERT INTO,cola_markets,VALUES(,3,cola_c,MDSYS.SDO_GEOMETRY(,2003, - 2-dimensional polygon,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), - one polygon (exterior polygon ring),MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3),);,INSERT INTO,cola_markets,VALUES(,4,cola_d,MDSYS.SDO_GEOMETRY(,2003, - 2-dimensional polygon,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4), - one circle,MDSYS.SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11),),);,-,更新原数据表,INSERT INTO USER_SDO_GEOM_METADATA,VALUES (,cola_markets,shape,MDSYS.SDO_DIM_ARRAY,( - 20X20 grid,MDSYS.SDO_DIM_ELEMENT(X, 0, 20, 0.005),MDSYS.SDO_DIM_ELEMENT(Y, 0, 20, 0.005),),NULL - SRID,);,-,创建空间索引,-,CREATE INDEX,cola_spatial_idx,ON,cola_markets(shape,),INDEXTYPE IS MDSYS.SPATIAL_INDEX;,-,空间查询,-,SELECT,SDO_GEOM.SDO_INTERSECTION(c_a.shape,c_c.shape, 0.005),FROM,cola_markets,c_a,cola_markets,c_c,WHERE,c_a.name,= ,cola_a, AND,c_c.name,= ,cola_c,;,- Do two geometries have any spatial relationship?,SELECT,SDO_GEOM.RELATE(c_b.shape, ,anyinteract,c_d.shape, 0.005),FROM,cola_markets,c_b,cola_markets,c_d,WHERE,c_b.name,= ,cola_b, AND,c_d.name,= ,cola_d,;,- Return the areas of all cola markets.,SELECT name,SDO_GEOM.SDO_AREA(shape, 0.005) FROM,cola_markets,;,- Return the area of just,cola_a,.,SELECT,c.name,SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM,cola_markets,c,WHERE,c.name,= ,cola_a,;,- Return the distance between two geometries.,SELECT,SDO_GEOM.SDO_DISTANCE(c_b.shape,c_d.shape, 0.005),FROM,cola_markets,c_b,cola_markets,c_d,WHERE,c_b.name,= ,cola_b, AND,c_d.name,= ,cola_d,;,- Is a geometry valid?,SELECT,c.name,SDO_GEOM.VALIDATE_GEOMETRY(c.shape, 0.005),FROM,cola_markets,c WHERE,c.name,= ,cola_c,;,- Is a layer valid? (First, create the results table.),CREATE TABLE,validation_results,(,mkt_id,number, result varchar2(10);,EXECUTE SDO_GEOM.VALIDATE_LAYER(COLA_MARKETS, SHAPE, MKT_ID,VALIDATION_RESULTS);,SELECT * from,validation_results,;,