advertisement

Print

Mapping Geometric Data with Oracle Spatial
Pages: 1, 2

Querying

Oracle Spatial uses a two-tier process for querying spatial data. The first part of the query selects candidate rows based only on their spatial index. The second step of the query looks at each candidate and determines if it meets the query exactly. This two-tier approach allows for quick querying by leveraging the spatial indexes.



The first part of the query uses an Oracle Spatial function called SDO_FILTER, which is defined below.

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

The first geometry argument, geometry1, is a column name of spatially indexed geometries. The second geometry argument, geometry2, may or may not come from a table and does not need to be indexed spatially. The final argument defines how the filtering works. If the final argument is querytype=WINDOW, filtering is done in memory and works very well when geometry2 doesn't come from a table. If the final argument is querytype=JOIN, geometry2 must come from a table and performance depends on the spatial index type of the two tables. SDO_FILTER returns a string of TRUE if a successful filtering occurs or FALSE otherwise. An example query using SDO_FILTER follows. 

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';

This query selects all boats that have a 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.

— Oracle Spatial User Guide and Reference 9.2

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

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'

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 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 provides other powerful querying functions beside SDO_FILTER and SDO_RELATE. They include:

QueryDescription
SDO_NNNearest neighbor
SDO_SDO_WITHIN_DISTANCEAll geometries with a certain distance
FunctionsDescription
SDO_GEOM.SDO_MBRThe minimum bounding rectangle for a geometry
SDO_GEOM.SDO_DISTANCEThe distance between two geometries
SDO_GEOM.SDO_INTERSECTIONProvides the intersection point of two geometries

There are many more query operators and functions in Oracle Spatial. A complete list and explanation may be found in reference section below.

Conclusion

Oracle Spatial makes it possible to combine the relational power of a database with spatial data. The ability to use indexes, various queries, and functions means complex spatial calculations may be pushed back onto large database servers. As mobile applications and technologies increase, so will the demands to store and analyze spatial data in a transactional setting.

Reference

Matt Bauer is the founder of MetroRacer, a Minneapolis based firm developing software for professional and amateur athletes.

Return to the O'Reilly Network.