Oracle Spatial Queries

Table Creation in Oracle Spatial (DDL Command)

CREATE TABLE mylake ( feature_id NUMBER PRIMARY KEY, name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);

Insert (DML Command) in Oracle Spatial

INSERT INTO mylake VALUES( 10, -- feature_id 'Lake Calhoun', -- name MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),MDSYS.SDO_ORDINATE_ARRAY(0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4)
));

Querying

SELECT name boat_nameFROM mylake tWHERE feature_id = 12AND 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';

This query selects all boats that have geometry with an indexed grid square within the polygon defined. This doesn't necessarily mean the returned boats are within the rectangle or if they are just touching the defined rectangle. To obtain an exact query, a second function called SDO_RELATE must be executed. SDO_RELATE looks at two geometries and determines if they interact in a specified way. It is important to note that SDO_RELATE only works on two-dimensional data. It is defined below.

SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)

SDO_RELATE arguments are the same as those for SDO_FILTER with the exception of the last argument. The params argument has a masktype value in addition to the querytype value. The masktype value can take the values listed below.

• 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.

To select all boats that are inside a defined rectangle the following query would work:

SELECT name boat_nameFROM mylake tWHERE feature_id = 12AND 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'

It is also possible to combine masktypes to select sites that are inside or touching the defined polygon with the query below.

SELECT feature_id idFROM mylake tWHERE feature_id = 12AND 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'