Release 8.1.7
Part Number A85337-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Spatial User's Guide and Reference Release 8.1.7 Part Number A85337-01 |
|
This chapter describes the operators used when working with the spatial object data type. The operators are listed in Table 6-1.
Table 6-1 Spatial Usage OperatorsSDO_FILTER(geometry1, geometry2, params);
Uses the spatial index to identify either the set of spatial objects that are likely to interact spatially with a given object (such as an area of interest), or pairs of spatial objects that are likely to interact spatially. Objects interact spatially if they are not disjoint.
This operator performs only a primary filter operation. The secondary filtering operation, performed by the SDO_RELATE operator, can be used to determine with certainty if objects interact spatially.
The expression SDO_FILTER(arg1, arg2, arg3) = `TRUE' evaluates to TRUE for object pairs that are non-disjoint, and FALSE otherwise.
The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form SDO_FILTER(arg1, arg2, arg3) = `TRUE'.
If querytype is WINDOW, geometry2 can come from a table or be a transient SDO_GEOMETRY object (such as a bind variable or SDO_GEOMETRY constructor).
If querytype is JOIN:
The following example selects the GID values from the POLYGONS table where the GEOMETRY column objects are likely to interact spatially with the GEOMETRY column object in the QUERY_POLYS table that has a GID value of 1.
SELECT A.gid FROM Polygons A, query_polys B WHERE B.gid = 1 AND SDO_FILTER(A.Geometry, B.Geometry, 'querytype = WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with the geometry stored in the aGeom variable.
Select A.Gid FROM Polygons A WHERE SDO_FILTER(A.Geometry, :aGeom, 'querytype=WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).
Select A.Gid FROM Polygons A WHERE SDO_FILTER(A.Geometry, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,3), mdsys.sdo_ordinate_array(x1,y1,x2,y2)), 'querytype=WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with any GEOMETRY column object in the QUERY_POLYS table. In this example, the ORDERED optimizer hint is used and QUERY_POLYS (geometry2) table is specified first in the FROM clause, because multiple geometries from geometry2 are involved (see the Usage Notes)
SELECT /*+ ORDERED */ A.gid FROM query_polys B, polygons A WHERE SDO_FILTER(A.Geometry, B.Geometry, 'querytype = WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is likely to interact spatially with any GEOMETRY column object in the QUERY_POLYS table. In this example, the QUERY_POLYS.GEOMETRY column must be spatially indexed.
SELECT A.gid FROM Polygons A, query_polys B WHERE SDO_FILTER(A.Geometry, B.Geometry, 'querytype = JOIN') = 'TRUE';
SDO_NN(geometry1, geometry2, param);
Uses the spatial index to identify the nearest neighbors for a geometry.
This operator returns the sdo_num_res number of objects from geometry1 that are closest to geometry2 in the query. In determining how close two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.
The operator must always be used in a WHERE clause, and the condition that includes the operator should be an expression of the form SDO_NN(arg1, arg2, 'sdo_num_res=<some_val>') = 'TRUE'.
You should not make any assumptions about the order of the returned results. For example, the first of several returned objects is not guaranteed to be the one closest to geometry2.
If two or more objects from geometry1 are an equal distance from geometry2, any of the objects can be returned on any call to the function. For example, if item_a, item_b, and item_c are closest to and equally distant from geometry2, and if SDO_NUM_RES=2, two of those three objects are returned, but they can be any two of the three.
SDO_NN is not supported for spatial joins.
In some situations the SDN_NN operator will not use the spatial index unless an optimizer hint forces the index to be used. This can occur when a query involves a join; and if the optimizer hint is not used in such situations, an internal error occurs. To prevent such errors, you should always specify an optimizer hint to use the spatial index with the SDO_NN operator, regardless of how simple or complex the query is. For example, the following excerpt from a query specifies to use the COLA_SPATIAL_IDX index that is defined on the COLA_MARKETS table:
SELECT /*+ INDEX(cola_markets cola_spatial_idx) */ c.mkt_id, c.name, ... FROM cola_markets c, ...;
For detailed information about using optimizer hints, see Oracle8i Performance Guide and Reference.
The following example finds the two objects from the shape column in the COLA_MARKETS table that are closest to a specified point (10,7). (The example uses the definitions and data from Section 2.1.)
SELECT /*+ INDEX(cola_markets cola_spatial_idx) */ c.mkt_id, c.name FROM cola_markets c WHERE SDO_NN(c.shape, mdsys.sdo_geometry(2001, NULL, mdsys.sdo_point_type(10,7,NULL), NULL, NULL), 'sdo_num_res=2') = 'TRUE'; MKT_ID NAME ---------- -------------------------------- 4 cola_d 2 cola_b
None.
SDO_RELATE(geometry1, geometry2, params);
Uses the spatial index to identify either the spatial objects that have a particular spatial interaction with a given object such as an area of interest, or pairs of spatial objects that have a particular spatial interaction.
This operator performs both primary and secondary filter operations.
|
geometry1 |
Specifies a geometry column in a table. The column must be spatially indexed. |
|
geometry2 |
Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) |
|
PARAMS |
Determines the behavior of the operator. |
|
Keyword |
Description |
|
mask |
Specifies the topological relation of interest. This is a required parameter. Valid values are one or more of the following in the 9-intersection pattern: TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT. Multiple masks are combined with a the logical Boolean operator OR, for example, 'mask=inside+touch'; however, see the Usage Notes for an alternative syntax using UNION ALL that may result in better performance. See Section 1.8 for an explanation of the 9-intersection relationship pattern. |
|
querytype |
Valid query types are: WINDOW or JOIN. This is a required parameter if geometry2 is from another table, but it is not a required parameter if geometry2 is a literal or a host variable. WINDOW is recommended in almost all cases. WINDOW implies that a query is performed for every geometry1 candidate geometry to be compared with geometry2. WINDOW can be used to compare a single geometry (geometry2) to all the geometries in a column (geometry1). JOIN is rarely used. Use JOIN when you want to compare all the geometries of a column to all the geometries of another column. JOIN implies that geometry2 refers to a table column that must have a spatial index built on it. (See the Usage Notes for additional requirements.) |
|
idxtab1 |
Specifies the name of the index table, if there are multiple spatial indexes, for geometry1. |
|
idxtab2 |
Specifies the name of the index table, if there are multiple spatial indexes, for geometry2. Only valid for 'querytype = JOIN'. |
|
layer_gtype |
Specifies special processing for point data.
If the columns you are comparing have only point data, set this parameter to POINT for optimal performance. |
The expression SDO_RELATE(geometry1,geometry2, 'mask = <some_mask_val> querytype = <some_querytype>') = 'TRUE' evaluates to TRUE for object pairs that have the topological relationship specified by <some_mask_val>, and FALSE otherwise.
The operator must always be used in a WHERE clause, and the condition that includes the operator should be an expression of the form SDO_RELATE(arg1, arg2, 'mask = <some_mask_val> querytype = <some_querytype>') = 'TRUE'.
If querytype is WINDOW, geometry2 can come from a table or be a transient SDO_GEOMETRY object (such as a bind variable or SDO_GEOMETRY constructor).
If querytype is JOIN:
Unlike with the SDO_GEOM.RELATE function, DISJOINT and DETERMINE masks are not allowed in the relationship mask with the SDO_RELATE operator. This is because SDO_RELATE uses the spatial index to find candidates that may interact, and the information to satisfy DISJOINT or DETERMINE is not present in the index.
Although multiple masks can be combined using the logical Boolean operator OR, for example, 'mask=inside+coveredby', better performance may result if the spatial query specifies each mask individually and uses the UNION ALL syntax to combine the results. This is due to internal optimizations that Spatial can apply under certain conditions when masks are specified singly rather than grouped within the same SDO_RELATE operator call. For example, the following query using the logical Boolean operator OR to group multiple masks:
SELECT a.gid FROM polygons a, query_polys B WHERE B.gid = 1 AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=inside+coveredby querytype=WINDOW') = 'TRUE';
may result in better performance if it is expressed thus, using UNION ALL to combine results of multiple SDO_RELATE operator calls, each with a single mask:
SELECT a.gid FROM polygons a, query_polys B WHERE B.gid = 1 AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=inside querytype=WINDOW') = 'TRUE' UNION ALL SELECT a.gid FROM polygons a, query_polys B WHERE B.gid = 1 AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=coveredby querytype=WINDOW') = 'TRUE';
The following examples are similar to those for the SDO_FILTER operator; however, they identify a specific type of interaction (using the mask parameter), and they determine with certainty (not mere likelihood) if the spatial interaction occurs.
The following example selects the GID values from the POLYGONS table where the GEOMETRY column objects have any spatial interaction with the GEOMETRY column object in the QUERY_POLYS table that has a GID value of 1.
SELECT A.gid FROM Polygons A, query_polys B WHERE B.gid = 1 AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with the geometry stored in the aGeom variable.
Select A.Gid FROM Polygons A WHERE SDO_RELATE(A.Geometry, :aGeom, 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).
Select A.Gid FROM Polygons A WHERE SDO_RELATE(A.Geometry, mdsys.sdo_geometry(2003,NULL,NULL, mdsys.sdo_elem_info_array(1,1003,3), mdsys.sdo_ordinate_array(x1,y1,x2,y2)), 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object has any spatial interaction with any GEOMETRY column object in the QUERY_POLYS table. In this example, the ORDERED optimizer hint is used and QUERY_POLYS (geometry2) table is specified first in the FROM clause, because multiple geometries from geometry2 are involved (see the Usage Notes).
SELECT /*+ ORDERED */ A.gid FROM query_polys B, polygons A WHERE SDO_RELATE(A.Geometry, B.Geometry, 'querytype = WINDOW') = 'TRUE';
The following example selects the GID values from the POLYGONS table where a GEOMETRY column object has any spatial interaction with any GEOMETRY column object in the QUERY_POLYS table. In this example, the QUERY_POLYS.GEOMETRY column must be spatially indexed.
SELECT A.gid
FROM Polygons A, query_polys B WHERE SDO_RELATE(A.Geometry, B.Geometry, 'mask=ANYINTERACT querytype=JOIN') = 'TRUE';
SDO_WITHIN_DISTANCE(T.column, aGeom, params);
Uses the spatial index to identify the set of spatial objects that are within some specified Euclidean distance of a given object (such as an area of interest or point of interest).
The expression SDO_WITHIN_DISTANCE(arg1, arg2, arg3) = 'TRUE' evaluates to TRUE for object pairs that are within the specified distance, and FALSE otherwise.
Distance between two extended objects (nonpoint objects such as lines and polygons) is defined as the minimum distance between these two objects. The distance between two adjacent polygons is zero.
The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form:
SDO_WITHIN_DISTANCE(arg1, arg2, 'distance = <some_dist_val>') = 'TRUE'
T.column must have a spatial index built on it.
SDO_WITHIN_DISTANCE is not supported for spatial joins. See Section 4.2.3 for a discussion on how to perform a spatial join within-distance operation.
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is within 10 distance units of the geometry stored in the aGeom variable.
SELECT A.GID
FROM POLYGONS A
WHERE
SDO_WITHIN_DISTANCE(A.Geometry, :aGeom, 'distance = 10') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GEOMETRY column object is within 10 distance units of the specified rectangle having the lower-left coordinates (x1,y1) and the upper-right coordinates (x2, y2).
SELECT A.GID
FROM POLYGONS A
WHERE
SDO_WITHIN_DISTANCE(A.Geometry, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(x1,y1,x2,y2)),
'distance = 10') = 'TRUE';
The following example selects the GID values from the POLYGONS table where the GID value in the QUERY_POINTS table is 1 and a POLYGONS.GEOMETRY object is within 10 distance units of the QUERY_POINTS.GEOMETRY object.
SELECT A.GID
FROM POLYGONS A, Query_Points B
WHERE B.GID = 1 AND
SDO_WITHIN_DISTANCE(A.Geometry, B.Geometry, 'distance = 10') = 'TRUE';
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|