空间数据库实习三个实验

) );

INSERT INTO liufei VALUES( 12,

'Blue Crest', MDSYS.SDO_GEOMETRY( 2003, NULL, NULL,

MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),

MDSYS.SDO_ORDINATE_ARRAY(7,7, 8,7, 8,7, 7,8, 7,7) ) );

运行SELECT * FROM liufei，并记录运行结果，并说明每条记录代表什么。

Step5. 查询

Oracle Spatial查询数据包括二个处理过程： 只通过索引查询候选项。通过函数SDO_FILTER实现： SDO_FILTER(geometry1

MDSYS.SDO_GEOMETRY, params VARCHAR2)

geometry1： 必须是被索引的几何数据

geometry2：不一定是表中的空间字段，也不要求被索引 params：Filter类型

querytype=WINDOW：geometry2不要求来自表 querytype=JOIN：geometry2必须来自表

运行下列SQL语句，并记录结果，并说明这个结果这代表什么意义？ SELECT name boat_name FROM liufei t WHERE feature_id = 11

AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,1),

mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)), 'querytype=WINDOW') = 'TRUE';

MDSYS.SDO_GEOMETRY, geometry2

湖中没有船，而且两者不想交

再检查每个候选项是否和条件精确匹配。通过函数SDO_RELATE实现：

SDO_RELATE(geometry1 params：masktype类型

MDSYS.SDO_GEOMETRY, geometry2

MDSYS.SDO_GEOMETRY, params VARCHAR2)

DISJOINT — the boundaries and interiors do not intersect TOUCH — the boundaries intersect but the interiors do not

intersect

OVERLAPBDYDISJOINT — the interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.

OVERLAPBDYINTERSECT — the boundaries and interiors of the two objects intersect

EQUAL — the two objects have the same boundary and interior CONTAINS — the interior and boundary of one object is completely contained in the interior of the other object

COVERS — the interior of one object is completely contained in the interior of the other object and their boundaries intersect

INSIDE — the opposite of CONTAINS. A INSIDE B implies B CONTAINS A.

COVEREDBY — the opposite of COVERS. A COVEREDBY B implies B

COVERS A.

ON — the interior and boundary of one object is on the boundary of the other object (and the second object covers the first object). This relationship occurs, for example, when a line is on the boundary of a polygon.

ANYINTERACT — the objects are non-disjoint.

运行下列SQL语句，并记录结果，并说明这个结果这代表什么意义？ // 选择在定义矩形内的所有小船 SELECT name boat_name FROM liufei t WHERE feature_id = 12

AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,1),

mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),

'querytype=WINDOW') = 'TRUE'

AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,1),

mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)), 'mask=INSIDE querytype=WINDOW') = 'TRUE' 运行结果和分析：

未选定行，因为湖里没有船

// masktype可联合使用 SELECT name boat_name FROM liufei t WHERE feature_id = 11

AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,1),

mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)), 'querytype=WINDOW') = 'TRUE'

AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,1),

mdsys.sdo_ordinate_array(1,1, 5,1, 5,5, 1,5, 1,1)), 'mask=INSIDE+TOUCH querytype=WINDOW') = 'TRUE'