Oracle7 Spatial Data Option User's Guide and Reference

Library

Product

Contents

Index

Querying Spatial Data

• 3.1 Query Model
• 3.2 Spatial Data Model
• 3.3 Spatial Query
• 3.4 Spatial Join

This chapter describes how the structures of a Spatial Data Option layer are used to resolve spatial queries and spatial joins. For the sake of clarity, the examples assume that all of the tiles covering all of the elements in a layer are the same size, although this is not required by the Spatial Data Option.

3.1 Query Model

The two operations are referred to as primary and secondary filter operations.

• The primary filter permits fast selection of a small number of candidate records to pass along to the secondary filter. The primary filter uses approximations in order to reduce computational complexity and is considered a lower cost filter.

• The secondary filter applies exact computational geometry to the result set of the primary filter. These exact computations yield the final answer to a query.The secondary filter operations are computationally more expensive, but they are only applied to the relatively small result set from the primary filter.

Figure 3¯1 Query Model

A function used as a secondary filter is SDO_GEOM.RELATE() which determines the spatial relationship between two given geometries, such as whether they touch, overlap, or if one is inside the other.

3.2 Spatial Data Model

Consider the following layer containing several objects. Each object is labeled with its SDO_GID. The relevant cover tiles are labeled with `Tn'.

Figure 3¯2 Tessellated Layer With Multiple Objects

Table 3¯1 <layer>_SDOLAYER

SDO_ORDCNT (NUMBER)

4

Figure 3¯3 Tessellated Layer With a Query Fence

SQL> EXECUTE SDO_WINDOW.CREATE_WINDOW_LAYER (fencelayer, DIMNUM1, LB1, UB1, 
TOLERANCE1, DIMNAME1, DIMNUM2, LB2, UB2, TOLERANCE2, DIMNAME2)

SQL> EXECUTE DBMS_OUTPUT.PUTLINE(TO_CHAR(SDO_WINDOW.BUILD_WINDOW (comp_user, 
fencelayer, SDO_ETYPE, NUM_TILES, X1, Y1, X2, Y2, X3, Y3, X4, Y4, X1, Y1)));

SELECT DISTINCT A.SDO_GID, B.SDO_GID

FROM <layer1>_SDOINDEX A, <fencelayer>_SDOINDEX B

WHERE	 		A.SDO_CODE = B.SDO_CODE 

and B.SDO_GID = {GID returned from SDO_WINDOW.BUILD_WINDOW};

	

 { 1013,501,1243,12 }

SELECT SDO_GID

   FROM (

          SELECT DISTINCT A.SDO_GID

             FROM <layer1>_SDO_INDEX A

                  <fence'>_SDO_INDEX B

             WHERE A.SDO_CODE = B.SDO_CODE         

         )

   WHERE SDO_GEOM.INTERACT('<layer1>', GID1, '<fence>', 1) = 'TRUE';

{1243,1013}

3.4 Spatial Join

Example 3¯1

Spatial joins can be used to answer questions such as, "which highways cross national parks?"

This query could be resolved by joining a layer that stores national park geometries with one that stores highway geometries.The following table structures will be used to illustrate how the join would be accomplished for this example:

SELECT DISTINCT A.SDO_GID, B.SDO_GID 

FROM PARKS_SDOINDEX A, HIGHWAYS_SDOINDEX B 

WHERE A.SDO_CODE BETWEEN B.SDO_CODE AND B.SDO_MAXCODE

OR B.SDO_CODE BETWEEN A.SDO_CODE AND A.SDO_MAXCODE;

The result set of the PRIMARY filter must be passed through the SECONDARY filter to get the exact set of PARKS/HIGHWAYS GID pairs that cross. The query that performs the SECONDARY filtering is:

Suppose the original query had asked, "which 4-lane highways cross national parks." You could modify the above SQL statement to join back to the HIGHWAYS table where HIGHWAYS.WIDTH=4. This combination of spatial and relational attributes in a single query is one of the primary reasons for using the Spatial Data Option.

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index