Release 8.1.5
A67295-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Spatial User's Guide and Reference Release 8.1.5 A67295-01 |
|
This chapter describes how the structures of an object-relational model Spatial layer are used to resolve spatial queries and spatial joins. For the sake of clarity, the examples all use fixed-size tiling, but hybrid indexing is actually recommended for the object model.
Spatial uses a two-tier query model to resolve spatial queries and spatial joins. The term is used to indicate that two distinct operations are performed in order to resolve queries. If both operations are performed, the exact result set is returned.
The two operations are referred to as primary and secondary filter operations.
An important concept in the spatial data model is that each geometry is represented by a set of exclusive and exhaustive tiles. This means that no tiles overlap each other (exclusive), and the tiles fully cover the object (exhaustive).
Consider the following layer containing several objects in Figure 4-1. Each object is labeled with its SDO_GID. The relevant tiles are labeled with `Tn'.
A typical spatial query is to request all objects that lie within a defined fence or window. A query window is shown in Figure 4-2 by the dotted-line box. A dynamic query window refers to a fence that is not defined in the database, but that must be defined prior to using it.
Spatial release 8.1 provides a new operator named SDO_FILTER( ). This implements the primary filter portion of the two-step process involved in the product's query processing model. The primary filter uses the index data only to determine a set of candidate object pairs that may interact. The syntax is as follows:
SDO_FILTER(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY,
params VARCHAR2)
Where:
The following examples perform a primary filter operation only. They will return all the geometries shown in Figure 4-2 that have an index tile in common with one of the index tiles that approximates the query window: tiles T1, T2, T2, and T4. The result of the following examples are geometries with IDs 1013, 1243, 12, and 501.
As mentioned previously, these examples are performed with fixed-size tiles, which is not the recommended indexing method for the object model. If hybrid indexing was used, the selectivity would improve.
Example 4-1 performs a primary filter operation without inserting the query window into a table. The window will be indexed in memory and performance will be very good.
SELECT A.Feature_ID FROM TARGET A
WHERE mdsys.sdo_filter(A.shape, mdsys.sdo_geometry(3,NULL,NULL, mdsys.sdo_elem_info(1,3,3), mdsys.sdo_ordinates(x1,y1, x2,y2)),
'querytype=window') = 'TRUE';
Note that (x1,y1) and (x2,y2) are the lower left and upper right corners of the query window.
In Example 4-2, a transient instance of type SDO_GEOMETRY was constructed for the query window instead of specifying the window parameters in the query itself.
SELECT A.Feature_ID FROM TARGET A
WHERE mdsys.sdo_filter(A.shape, :theWindow,'querytype=window') = `TRUE';
Example 4-31 assumes the query window was inserted into a table called WINDOWS, with an ID of 'WINS_1'.
SELECT A.Feature_ID FROM TARGET A, WINDOWS B
WHERE B.ID= 'WIN_1' AND
mdsys.sdo_filter(A.shape, B.shape,'querytype=window') = `TRUE';
If the B.shape column is not spatially indexed, the SDO_FILTER( ) operator indexes the query window in memory and performance is very good.
If the B.shape column is spatially indexed with the same SDO_LEVEL value as the A.shape column, the SDO_FILTER( ) operator reuses the existing index, and performance is very good or better.
If the B.shape column is spatial indexed with a different SDO_LEVEL value than the A.shape column, the SDO_FILTER( ) operator reuses the existing index, but performance degrades.
The SDO_RELATE( ) operator performs both the primary and secondary filter stages when processing a query. The syntax of the operator is as follows:
SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY,
geometry2 MDSYS.SDO_GEOMETRY,
params VARCHAR2)
Where:
params is a quoted string of keyword value pairs that determines the behavior of the operator. See the SDO_RELATE operator in Chapter 9 for a list of parameters.
The following examples perform both primary and secondary filter operations. They return all the geometries in Figure 4-2 that lie within or overlap the query window. The result of these examples is objects 1243 and 1013.
Example 4-4 performs both primary and secondary filter operations without inserting the query window into a table. The window will be indexed in memory and performance will be very good.
SELECT A.Feature_ID FROM TARGET A WHERE mdsys.sdo_relate(A.shape, mdsys.sdo_geometry(3,NULL,NULL, mdsys.sdo_elem_info(1,3,3),
mdsys.sdo_ordinates(x1,y1, x2,y2)),
'mask=anyinteract querytype=window') = 'TRUE';
Note that (x1,y1) and (x2,y2) are the lower left and upper right corners of the query window.
Example 4-5 assumes the query window was inserted into a table called WINDOWS, with an ID of 'WINS_1'.
SELECT A.Feature_ID FROM TARGET A, WINDOWS B
WHERE B.ID= 'WIN_1' AND
mdsys.sdo_relate(A.shape, B.shape, 'mask=anyinteract querytype=window') = `TRUE';
If the B.shape column is not spatially indexed, the SDO_RELATE( ) operator indexes the query window in memory and performance is very good.
If the B.shape column is spatially indexed with the same SDO_LEVEL value as the A.shape column, the SDO_RELATE( ) operator reuses the existing index, and performance is very good or better.
If the B.shape column is spatial indexed with a different SDO_LEVEL value than the A.shape column, the SDO_RELATE( ) operator reuses the existing index, but performance degrades.
The SDO_WITHIN_DISTANCE( ) operator is used to determine the set of objects in a table that are within N Euclidean distance units from a reference object aRefGeom. The reference object may be a transient or persistent instance of MDSYS.SDO_GEOMETRY (such as a temporary query fence or a permanent geometry stored in the database.) The syntax is as follows:
SDO_WITHIN_DISTANCE(geometry1 MDSYS.SDO_GEOMETRY,
aRefGeom MDSYS.SDO_GEOMETRY,
params VARCHAR2)
Where:
params is a a quoted string of keyword value pairs that determines the behavior of the operator. See the SDO_WITHIN_DISTANCE operator in Chapter 9 for a list of parameters.
The following example selects any objects within 1.35 distance units from the query window:
SELECT A.Feature_ID FROM TARGET A WHERE MDSYS.SDO_WITHIN_DISTANCE( A.shape, :theWindow, 'distance=1.35') = 'TRUE';
The distance units are based on the geometry coordinate system in use. If your data consists of latitude and longitude pairs, then one distance unit corresponds to the length of one degree. For city, county, state, and even country-wide applications, this is probably acceptable. Unfortunately, one degree of longitude at the equator is much different than one degree at the poles. As mentioned previously, the Spatial product treats the coordinate space as a flat Cartesian grid. It currently does not take projections into account. Projections are left up to the application.
This operator is not suitable for performing spatial joins. That is, a query like `Find all parks that are within 10 distance units from coastlines' will not be processed as an index-based spatial join of the COASTLINES and PARKS tables. Instead, it will be processed as a nested loop query in which each COASTLINE instance is in turn a reference object that is buffered, indexed, and evaluated against the PARKS table. Thus the SDO_WITHIN_DISTANCE( ) operation is performed N times if there are N rows in the COASTLINES table.
There is an efficient way to accomplish a spatial join that involves buffering all the geometries of a layer. This method does not use the SDO_WITHIN_DISTANCE( ) operator. First, create a new table COSINE_BUFS as follows:
CREATE TABLE cosine_bufs UNRECOVERABLE AS SELECT SDO_BUFFER (A.SHAPE, B.DIMINFO, 1.35) FROM COSINE A, SDO_GEOM_METADATA B WHERE TABLE_NAME='COSINES' AND COLUMN_NAME='SHAPE';
Next, create a spatial index on the SHAPE column of COSINE_BUFS. Then you can perform the following query:
SELECT a.gif, b.gid FROM parks A cosine_bufs B WHERE SDO_Relate(A.shape, B.shape, 'mask=ANYINTERACT querytype=JOIN') ='TRUE';
A spatial join is the same as a regular join except that the predicate involves a spatial operator. In Spatial, a spatial join takes place when you compare all the geometries of one layer to all the geometries of another layer. This is unlike a query window that only compares a single geometry to all geometries of a layer.
Spatial joins can be used to answer questions such as, "which highways cross national parks?"
The following table structures illustrate how the join would be accomplished for this example:
PARKS( GID VARCHAR2(32), SHAPE MDSYS.SDO_GEOMETRY) HIGHWAYS( GID VARCHAR2(32), SHAPE MDSYS.SDO_GEOMETRY)
The primary filter would identify pairs of PARK GIDs and HIGHWAY GIDs that interact in their index entries. The query that performs the PRIMARY filter join is:
SELECT A.GID, B.GID
FROM PARKS A, HIGHWAYS B
WHERE mdsys.sdo_filter(A.shape, B.shape, 'querytype = join') = 'TRUE';
The original question, asking about highways that cross national parks, requires the secondary filter operator because we need to find the exact relation between highways and parks.
The query that performs this join using both PRIMARY and SECONDARY filters is:
SELECT A.GID, B.GID FROM parks A, highwaysB WHERE mdsys.sdo_relate(A.shape, B.shape,
'mask = ANYINTERACT querytype = join');
1
A limitation in SQLPLUS may result in an error if Example 4-3 is run in SQLPLUS. This error results when both tables are indexed, and it can occur with any Spatial operator (primary or secondary filter). The error does not occur with any other interface than SQLPLUS. The limitation will be fixed in a future release.
