20.6 SDO_FILTER
Format
SDO_FILTER(geometry1, geometry2, param);
Description
Identifies 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.
Keywords and Parameters
Value | Description |
---|---|
geometry1 |
Specifies a geometry column in a table. A spatial index on this column is recommended. Data type is SDO_GEOMETRY. |
geometry2 |
Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY. |
param |
Optionally specifies either or both of the The The |
Returns
The expression SDO_FILTER(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that are non-disjoint, and FALSE otherwise.
Usage Notes
Note:
SDO_FILTERoperator is not supported for 3D data in Oracle Autonomous Database Serverless deployments.
The SDO_FILTER 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) = 'TRUE'. (The expression must not equate to any value other than 'TRUE'.)
geometry2
can come from a table or be a transient SDO_GEOMETRY object, such as a bind variable or SDO_GEOMETRY constructor.
-
If the
geometry2
column is not spatially indexed, the operator indexes the query window in memory and performance is very good. -
If two or more geometries from
geometry2
are passed to the operator, the ORDERED optimizer hint must be specified, and the table ingeometry2
must be specified first in the FROM clause.
If geometry1
and geometry2
are based on different coordinate systems, geometry2
is temporarily transformed to the coordinate system of geometry1
for the operation to be performed, as described in Different Coordinate Systems for Geometries with Operators and Functions.
Note:
If the DBMS_RLS.ADD_POLICY procedure has been used to add a fine-grained access control policy to a table or view, and if the specified policy function uses a spatial operator, the operator must be SDO_FILTER. No other spatial operators are supported in that context.
For information about 3D support with spatial operators (which operators do and do not consider all three dimensions in their computations), see Three-Dimensional Spatial Objects.
Examples
The following example selects the geometries that are likely to interact with a query window (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8). (The example uses the definitions and data described and illustrated in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.)
SELECT c.mkt_id, c.name FROM cola_markets c WHERE SDO_FILTER(c.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)) ) = 'TRUE'; MKT_ID NAME ---------- -------------------------------- 2 cola_b 1 cola_a 4 cola_d
The following example is the same as the preceding example, except that it includes only geometries where at least one side of the geometry's MBR is equal to or greater than 4.1. In this case, only cola_a
and cola_b
are returned, because their MBRs have at least one side with a length greater than or equal to 4.1. The circle cola_d
is excluded, because its MBR is a square whose sides have a length of 4.
SELECT c.mkt_id, c.name FROM cola_markets c WHERE SDO_FILTER(c.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)), 'min_resolution=4.1' ) = 'TRUE'; MKT_ID NAME ---------- -------------------------------- 2 cola_b 1 cola_a
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) = '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) = '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, sdo_geometry(2003,NULL,NULL, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(x1,y1,x2,y2)) ) = '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 the 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) = 'TRUE';
Related Topics
Parent topic: Spatial Operators