发布时间 : 星期二 文章空间数据库实习三个实验更新完毕开始阅读
) );
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'