Oracle Spatial查詢數(shù)據(jù)包括二個處理過程:
1.只通過索引查詢候選項。通過函數(shù)SDO_FILTER實現(xiàn):
SDO_FILTER(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
geometry1:必須是被索引的幾何數(shù)據(jù)
geometry2:不一定是表中的空間字段,也不要求被索引
params:Filter類型
querytype=WINDOW:geometry2不要求來自表
querytype=JOIN:geometry2必須來自表
SELECT name boat_name
FROM mylake 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';
2.再檢查每個候選項是否和條件精確匹配。通過函數(shù)SDO_RELATE實現(xiàn):
SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
params:masktype類型
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.
// 選擇在定義矩形內(nèi)的所有小船
SELECT name boat_name
FROM mylake 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)),
'masktype=INSIDE querytype=WINDOW') = 'TRUE'
// masktype可聯(lián)合使用
SELECT feature_id id
FROM mylake 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)),
'masktype=INSIDE+TOUCH querytype=WINDOW') = 'TRUE'
Oracle Spatial 提供的其他查詢函數(shù):
Query Description
SDO_NN Nearest neighbor
SDO_SDO_WITHIN_DISTANCE All geometries with a certain distance
Functions Description
SDO_GEOM.SDO_MBR The minimum bounding rectangle for a geometry
SDO_GEOM.SDO_DISTANCE The distance between two geometries
SDO_GEOM.SDO_INTERSECTION Provides the intersection point of two geometries