5 Indexing and Querying Spatial Data

5.1.1 Constraining Data to a Geometry Type

When you create or rebuild a spatial index, you can ensure that all geometries that are in the table or that are inserted later are of a specified geometry type. To constrain the data to a geometry type in this way, use the layer_gtype keyword in the PARAMETERS clause of the CREATE INDEX or ALTER INDEX REBUILD statement, and specify a value from the Geometry Type column of Table 2-1 in Section 2.2.1. For example, to constrain spatial data in a layer to polygons:

CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('layer_gtype=POLYGON');

The geometry types in Table 2-1 are considered as a hierarchy when data is checked:

• The MULTI forms include the regular form also. For example, specifying 'layer_gtype=MULTIPOINT' allows the layer to include both POINT and MULTIPOINT geometries.

• COLLECTION allows the layer to include all types of geometries.

5.1.2 Creating a Cross-Schema Index

You can create a spatial index on a table that is not in your schema. Assume that user B wants to create a spatial index on column GEOMETRY in table T1 under user A's schema. Follow these steps:

1. Connect to the database as a privileged user (for example, as SYSTEM), and execute the following statement:

   GRANT create table, create sequence to B;
   

2. Connect as a privileged user or as user A (or have user A connect), and execute the following statement:

   GRANT select, index on A.T1 to B;
   

3. Connect as user B and execute a statement such as the following:

   CREATE INDEX t1_spatial_idx on A.T1(geometry)
     INDEXTYPE IS mdsys.spatial_index;
   

5.1.3 Using Partitioned Spatial Indexes

You can create a partitioned spatial index on a partitioned table. This section describes usage considerations specific to Oracle Spatial. For a detailed explanation of partitioned tables and partitioned indexes, see Oracle Database Administrator's Guide.

A partitioned spatial index can provide the following benefits:

• Reduced response times for long-running queries, because partitioning reduces disk I/O operations

• Reduced response times for concurrent queries, because I/O operations run concurrently on each partition

• Easier index maintenance, because of partition-level create and rebuild operations

  Indexes on partitions can be rebuilt without affecting the queries on other partitions, and storage parameters for each local index can be changed independent of other partitions.

• Parallel query on multiple partition searching

  The degree of parallelism is the value from the DEGREE column in the row for the index in the USER_INDEXES view (that is, the value specified or defaulted for the PARALLEL keyword with the CREATE INDEX, ALTER INDEX, or ALTER INDEX REBUILD statement).

• Improved query processing in multiprocessor system environments

  In a multiprocessor system environment, if a spatial operator is invoked on a table with partitioned spatial index and if multiple partitions are involved in the query, multiple processors can be used to evaluate the query. The number of processors used is determined by the degree of parallelism and the number of partitions used in evaluating the query.

The following restrictions apply to spatial index partitioning:

• The partition key for spatial tables must be a scalar value, and must not be a spatial column.

• Only range partitioning is supported on the underlying table. All other kinds of partitioning are not currently supported for partitioned spatial indexes.

To create a partitioned spatial index, you must specify the LOCAL keyword. (If you do not specify the LOCAL keyword, a nonpartitioned spatial index is created on the data in all table partitions.) The following example creates a partitioned spatial index:

CREATE INDEX counties_idx ON counties(geometry)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX LOCAL;

In this example, the default values are used for the number and placement of index partitions, namely:

• Index partitioning is based on the underlying table partitioning. For each table partition, a corresponding index partition is created.

• Each index partition is placed in the default tablespace.

If you do specify parameters for individual partitions, the following considerations apply:

• The storage characteristics for each partition can be the same or different for each partition. If they are different, it may enable parallel I/O (if the tablespaces are on different disks) and may improve performance.

• The sdo_indx_dims value must be the same for all partitions.

• The layer_gtype parameter value (see Section 5.1.1) used for each partition may be different.

To override the default partitioning values, use a CREATE INDEX statement with the following general format:

CREATE INDEX <indexname> ON <table>(<column>) 
  INDEXTYPE IS MDSYS.SPATIAL_INDEX 
     [PARAMETERS ('<spatial-params>, <storage-params>')] LOCAL 
     [( PARTITION <index_partition> 
        PARAMETERS ('<spatial-params>, <storage-params>') 
     [, PARTITION <index_partition> 
        PARAMETERS ('<spatial-params>, <storage-params>')] 
     )] 

Queries can operate on partitioned tables to perform the query on only one partition. For example:

SELECT * FROM counties PARTITION(p1)
    WHERE ...<some-spatial-predicate>;

Querying on a selected partition may speed up the query and also improve overall throughput when multiple queries operate on different partitions concurrently.

When queries use a partitioned spatial index, the semantics (meaning or behavior) of spatial operators and functions is the same with partitioned and nonpartitioned indexes, except in the case of SDO_NN (nearest neighbor). With SDO_NN, the requested number of geometries is returned for each partition that is affected by the query. (See the description of the SDO_NN operator in Chapter 19 for more information.)

For example, if you request the 5 closest restaurants to a point and the spatial index has 4 partitions, SDO_NN returns up to 20 (5*4) geometries. In this case, you must use the ROWNUM pseudocolumn (here, WHERE ROWNUM <=5) to return the 5 closest restaurants, and the ORDER BY clause to sort the results by distance in miles. Example 5-1 returns the 5 nearest restaurants from a partitioned spatial index.

SELECT * FROM
(
SELECT r.name, r.location, SDO_NN_DISTANCE(1) distance_in_miles
FROM restaurants_part_table r
WHERE SDO_NN(r.location,
      MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(-110,35,Null),Null,Null),
     'SDO_NUM_RES=5 distance=2  unit=MILE', 1) = 'TRUE'
ORDER BY distance_in_miles
)
WHERE ROWNUM<=5;

For a cross-schema query when a table has a partitioned spatial index, the user must be granted SELECT privilege on both the spatial table and the index table (MDRT_xxx) for the spatial index that was created on the spatial table. For more information and an example, see "Cross-Schema Invocation of SDO_JOIN" in the Usage Notes for the SDO_JOIN operator in Chapter 19.

5.1.4 Exchanging Partitions Including Indexes

You can use the ALTER TABLE statement with the EXCHANGE PARTITION ... INCLUDING INDEXES clause to exchange a spatial table partition and its index partition with a corresponding table and its index. For information about exchanging partitions, see the description of the ALTER TABLE statement in Oracle Database SQL Language Reference.

This feature can help you to operate more efficiently in a number of situations, such as:

• Bringing data into a partitioned table and avoiding the cost of index re-creation.

• Managing and creating partitioned indexes. For example, the data could be divided into multiple tables. The index for each table could be built one after the other to minimize the memory and tablespace resources needed during index creation. Alternately, the indexes could be created in parallel in multiple sessions. The tables (along with the indexes) could then be exchanged with the partitions of the original data table.

• Managing offline insert operations. New data can be stored in a temporary table and periodically exchanged with a new partition (for example, in a database with historical data).

To exchange partitions including indexes with spatial data and indexes, the two spatial indexes (one on the partition, the other on the table) must have the same dimensionality (sdo_indx_dims value). If the indexes do not have the same dimensionality, an error is raised. The table data is exchanged, but the indexes are not exchanged and the indexes are marked as failed. To use the indexes, you must rebuild them

5.1.5 Export and Import Considerations with Spatial Indexes and Data

If you use the Export utility to export tables with spatial data, the behavior of the operation depends on whether or not the spatial data has been spatially indexed:

• If the spatial data has not been spatially indexed, the table data is exported. However, you must update the USER_SDO_GEOM_METADATA view with the appropriate information on the target system.

• If the spatial data has been spatially indexed, the table data is exported, the appropriate information is inserted into the USER_SDO_GEOM_METADATA view on the target system, and the spatial index is built on the target system. However, if the insertion into the USER_SDO_GEOM_METADATA view fails (for example, if there is already a USER_SDO_GEOM_METADATA entry for the spatial layer), the spatial index is not built.

If you use the Import utility to import data that has been spatially indexed, the following considerations apply:

• If the index on the exported data was created with a TABLESPACE clause and if the specified tablespace does not exist in the database at import time, the index is not built. (This is different from the behavior with other Oracle indexes, where the index is created in the user's default tablespace if the tablespace specified for the original index does not exist at import time.)

• If the import operation must be done by a privileged database user, and if the FROMUSER and TOUSER format is used, the TOUSER user must be granted the CREATE TABLE and CREATE SEQUENCE privileges before the import operation, as shown in the following example (and enter the password for the SYSTEM account when prompted):

  sqlplus system
  SQL> grant CREATE TABLE, CREATE SEQUENCE to CHRIS;
  SQL> exit;
  imp system file=spatl_data.dmp fromuser=SCOTT touser=CHRIS
  

For information about using the Export and Import utilities, see Oracle Database Utilities.

5.1.6 Distributed Transactions and Spatial Index Consistency

In a distributed transaction, different branches of the transaction can execute in different sessions. The branches can detach from their current session and migrate to another within the transaction scope. To maintain the consistency of Spatial indexes in distributed transactions, you must follow the usage guidelines in this section.

When the first insert, update, or delete operation on a spatial table (one with a spatial index) is performed in a distributed transaction, all subsequent insert, update, or delete operations on the table, as well as any prepare to commit operation (the first branch to prepare a commit), in the transaction should happen in the same session as the first operation. The branches performing these subsequent operations will first have to connect to the session in which the first operation was performed.

For more information about distributed transactions, see Oracle Database Administrator's Guide.

5.1.7 Rollback Segments and Sort Area Size

This section applies only if you (or the database administrator) are not using the automatic undo management feature or the PGA memory management feature, or both, of Oracle Database. Automatic memory management and PGA memory management are enabled by default, and their use is highly recommended. For explanations of these features, see:

• The section about automatic undo management and undo segments in Oracle Database Concepts

• The section about PGA memory management in Oracle Database Concepts

If you are not using automatic undo management and if the rollback segment is not large enough, an attempt to create a spatial index will fail. The rollback segment should be 100*n bytes, where n is the number of rows of data to be indexed. For example, if the table contains 1 million (1,000,000) rows, the rollback segment size should be 100,000,000 (100 million) bytes.

To ensure an adequate rollback segment, or if you have tried to create a spatial index and received an error that a rollback segment cannot be extended, review (or have a DBA review) the size and structure of the rollback segments. Create a public rollback segment of the appropriate size, and place that rollback segment online. In addition, ensure that any small inappropriate rollback segments are placed offline during large spatial index operations.

If you are not using the PGA memory management feature, the system parameter SORT_AREA_SIZE affects the amount of time required to create the index. The SORT_AREA_SIZE value is the maximum amount, in bytes, of memory to use for a sort operation. The optimal value depends on the database size, but a good guideline is to make it at least 1 million bytes when you create a spatial index. To change the SORT_AREA_SIZE value, use the ALTER SESSION statement. For example, to change the value to 20 million bytes:

ALTER SESSION SET SORT_AREA_SIZE = 20000000;

5.2.1 Spatial Query

In a spatial R-tree index, each geometry is represented by its minimum bounding rectangle (MBR), as explained in Section 1.7.1. Consider the following layer containing several objects in Figure 5-1. Each object is labeled with its geometry name (geom_1 for the line string, geom_2 for the four-sided polygon, geom_3 for the triangular polygon, and geom_4 for the ellipse), and the MBR around each object is represented by a dashed line.

Figure 5-1 Geometries with MBRs

Description of "Figure 5-1 Geometries with MBRs"

A typical spatial query is to request all objects that lie within a query window, that is, a defined fence or window. A dynamic query window refers to a rectangular area that is not defined in the database, but that must be defined before it is used. Figure 5-2 shows the same geometries as in Figure 5-1, but adds a query window represented by the heavy dotted-line box.

Figure 5-2 Layer with a Query Window

Description of "Figure 5-2 Layer with a Query Window"

In Figure 5-2, the query window covers parts of geometries geom_1 and geom_2, as well as part of the MBR for geom_3 but none of the actual geom_3 geometry. The query window does not cover any part of the geom_4 geometry or its MBR.

SDO_FILTER(geometry1 SDO_GEOMETRY, geometry2 SDO_GEOMETRY, param VARCHAR2)

SELECT A.Feature_ID FROM TARGET A  WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
                                       SDO_elem_info_array(1,1003,3),
                                       SDO_ordinate_array(x1,y1, x2,y2))
                           ) = 'TRUE';   

SELECT A.Feature_ID FROM TARGET A  WHERE sdo_filter(A.shape, :theWindow) = 'TRUE';   

SELECT A.Feature_ID FROM TARGET A, WINDOWS B  WHERE B.ID = 'WINS_1' AND    sdo_filter(A.shape, B.shape) = 'TRUE';      

SDO_RELATE(geometry1  SDO_GEOMETRY,
           geometry2  SDO_GEOMETRY,
           param      VARCHAR2)

SELECT A.Feature_ID FROM TARGET A
   WHERE sdo_relate(A.shape, SDO_geometry(2003,NULL,NULL,
                                       SDO_elem_info_array(1,1003,3),
                                      SDO_ordinate_array(x1,y1, x2,y2)),                         'mask=anyinteract') = 'TRUE';

SELECT A.Feature_ID FROM TARGET A, WINDOWS B  WHERE B.ID = 'WINS_1' AND          sdo_relate(A.shape, B.shape,
          'mask=anyinteract') = 'TRUE';   

SDO_WITHIN_DISTANCE(geometry1  SDO_GEOMETRY, 
                    aGeom      SDO_GEOMETRY,
                    params     VARCHAR2);

SELECT A.Feature_ID
  FROM TARGET A
  WHERE SDO_WITHIN_DISTANCE( A.shape, :theWindow, 'distance=1.35') = 'TRUE';

CREATE TABLE cosine_bufs UNRECOVERABLE AS
   SELECT SDO_BUFFER (A.SHAPE, B.DIMINFO, 1.35)
     FROM COSINE A, USER_SDO_GEOM_METADATA B
     WHERE TABLE_NAME='COSINES' AND COLUMN_NAME='SHAPE';

SELECT /*+ ordered */ a.gid, b.gid 
  FROM TABLE(SDO_JOIN('PARKS', 'SHAPE', 
                      'COSINE_BUFS', 'SHAPE',
                      'mask=ANYINTERACT')) c,
       parks a, 
       cosine_bufs b
  WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid;

SDO_NN(geometry1  SDO_GEOMETRY, 
       geometry2  SDO_GEOMETRY,
       param      VARCHAR2
       [, number  NUMBER]);

SELECT /*+ INDEX(cola_markets cola_spatial_idx) */
 c.mkt_id, c.name  FROM cola_markets c  WHERE SDO_NN(c.shape,
   SDO_geometry(2001, NULL, SDO_point_type(10,7,NULL), NULL,
   NULL),  'sdo_num_res=2') = 'TRUE'; 

5.2.2 Spatial Join

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 geometries of one layer to all geometries of another layer. This is unlike a query window, which 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 SDO_GEOMETRY)
HIGHWAYS( GID VARCHAR2(32), SHAPE SDO_GEOMETRY) 

To perform a spatial join, use the SDO_JOIN operator, which is described in Chapter 19. The following spatial join query, to list the GID column values of highways and parks where a highway interacts with a park, performs a primary filter operation only ('mask=FILTER'), and thus it returns only approximate results:

SELECT /*+ ordered */ a.gid, b.gid 
  FROM TABLE(SDO_JOIN('PARKS', 'SHAPE', 
                      'HIGHWAYS', 'SHAPE',
                      'mask=FILTER')) c,
       parks a, 
       highways b
  WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid;

The following spatial join query requests the same information as in the preceding example, but it performs both primary and secondary filter operations ('mask=ANYINTERACT'), and thus it returns exact results:

SELECT /*+ ordered */ a.gid, b.gid 
  FROM TABLE(SDO_JOIN('PARKS', 'SHAPE', 
                      'HIGHWAYS', 'SHAPE',
                      'mask=ANYINTERACT')) c,
       parks a, 
       highways b
  WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid;

5.2.3 Data and Index Dimensionality, and Spatial Queries

The elements of a spatial query can, in theory, have the following dimensionality:

• The base table geometries (or geometry1 in Spatial operator formats) can have two, three, or more dimensions.

• The spatial index created on the base table (or geometry1) can be two-dimensional or three-dimensional.

• The query window (or geometry2 in Spatial operator formats) can have two, three, or more dimensions.

Some combinations of dimensionality among the three elements are supported and some are not. Table 5-1 explains what happens with the possible combinations involving two and three dimensions.