9 Extending Spatial Indexing Capabilities

9.1 SDO_GEOMETRY Objects in User-Defined Type Definitions

The SDO_GEOMETRY type can be embedded in a user-defined data type definition. The procedure is very similar to that for using the SDO_GEOMETRY type for a spatial data column:

1. Create the user-defined data type.

2. Create a table with a column based on that data type.

3. Insert data into the table.

4. Update the USER_SDO_GEOM_METADATA view.

5. Create the spatial index on the geometry attribute.

6. Perform queries on the data.

For example, assume that you want to follow the cola markets scenario in the simplified example in Section 2.1, but want to incorporate the market name attribute and the geometry attribute in a single type. First, create the user-defined data type, as in the following example that creates an object type named MARKET_TYPE:

CREATE OR REPLACE TYPE market_type AS OBJECT 
  (name VARCHAR2(32), shape SDO_GEOMETRY);
/

Create a table that includes a column based on the user-defined type. The following example creates a table named COLA_MARKETS_2 that will contain the same information as the COLA_MARKETS table used in the example in Section 2.1.

CREATE TABLE cola_markets_2 (
  mkt_id NUMBER PRIMARY KEY,
  market MARKET_TYPE);

Insert data into the table, using the object type name as a constructor. For example:

INSERT INTO cola_markets_2 VALUES(
  1,
  MARKET_TYPE('cola_a',
    SDO_GEOMETRY(
      2003,  -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
      SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
            -- define rectangle (lower left and upper right)
      )
  )
);

Update the USER_SDO_GEOM_METADATA view, using dot-notation to specify the column name and spatial attribute. The following example specifies MARKET.SHAPE as the COLUMN_NAME (explained in Section 2.6.2) in the metadata view.

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'cola_markets_2',
  'market.shape',
  SDO_DIM_ARRAY(   -- 20X20 grid
    SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  NULL   -- SRID
);

Create the spatial index, specifying the column name and spatial attribute using dot-notation. For example.

CREATE INDEX cola_spatial_idx_2
ON cola_markets_2(market.shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Perform queries on the data, using dot-notation to refer to attributes of the user-defined type. The following simple query returns information associated with the cola market named cola_a.

SELECT c.mkt_id, c.market.name, c.market.shape 
  FROM cola_markets_2 c
  WHERE c.market.name = 'cola_a';

The following query returns information associated with all geometries that have any spatial interaction with a specified query window, namely, the rectangle with lower-left coordinates (4,6) and upper-right coordinates (8,8).

SELECT c.mkt_id, c.market.name, c.market.shape
  FROM cola_markets_2 c
  WHERE SDO_RELATE(c.market.shape,
            SDO_GEOMETRY(2003, NULL, NULL,
              SDO_ELEM_INFO_ARRAY(1,1003,3),
              SDO_ORDINATE_ARRAY(4,6, 8,8)),
            'mask=anyinteract' = 'TRUE';

9.2 SDO_GEOMETRY Objects in Function-Based Indexes

A function-based spatial index facilitates queries that use locational information (of type SDO_GEOMETRY) returned by a function or expression. In this case, the spatial index is created based on the precomputed values returned by the function or expression.

If you are not already familiar with function-based indexes, see the following for detailed explanations of their benefits, options, and requirements, as well as usage examples:

• Oracle Database Application Developer's Guide - Fundamentals

• Oracle Database Administrator's Guide

The procedure for using an SDO_GEOMETRY object in a function-based index is as follows:

1. Create the function that returns an SDO_GEOMETRY object.

   The function must be declared as DETERMINISTIC.

2. If the spatial data table does not already exist, create it, and insert data into the table.

3. Update the USER_SDO_GEOM_METADATA view.

4. Create the spatial index.

   For a function-based spatial index, the number of parameters must not exceed 32.

5. Perform queries on the data.

The rest of this section describes two examples of using function-based indexes. In both examples, a function is created that returns an SDO_GEOMETRY object, and a spatial index is created on that function. In the first example, the input parameters to the function are a standard Oracle data type (NUMBER). In the second example, the input to the function is a user-defined object type.

-- Create a function to return a point geometry (SDO_GTYPE = 2001) with
-- input of 2 numbers: longitude and latitude (SDO_SRID = 8307, for
-- "Longitude / Latitude (WGS 84)",  probably the most widely used 
--  coordinate system, and the one used for GPS devices.
-- Specify DETERMINISTIC for the function.

create or replace function get_long_lat_pt(longitude in number, 
                                           latitude in number)
return SDO_GEOMETRY deterministic is
begin
     return sdo_geometry(2001, 8307, 
                sdo_point_type(longitude, latitude, NULL),NULL, NULL);
end;
/

create table LONG_LAT_TABLE 
(longitude number, latitude number, name varchar2(32));

insert into LONG_LAT_TABLE values (10,10, 'Place1');
insert into LONG_LAT_TABLE values (20,20, 'Place2');
insert into LONG_LAT_TABLE values (30,30, 'Place3');

-- Set up the metadata entry for this table.
-- The column name sets up the function on top
-- of the two columns used in this function,
-- along with the owner of the function.
insert into user_sdo_geom_metadata values('LONG_LAT_TABLE',
 'scott.get_long_lat_pt(longitude,latitude)',
 sdo_dim_array(
   sdo_dim_element('Longitude', -180, 180, 0.005),
   sdo_dim_element('Latitude', -90, 90, 0.005)), 8307);

create index LONG_LAT_TABLE_IDX on 
   LONG_LAT_TABLE(get_long_lat_pt(longitude,latitude))
   indextype is mdsys.spatial_index;

-- First query: call sdo_filter with an SDO_GEOMETRY constructor
select name from LONG_LAT_TABLE  a
   where sdo_filter(get_long_lat_pt(a.longitude,a.latitude), 
      sdo_geometry(2001, 8307, 
        sdo_point_type(10,10,NULL), NULL, NULL)
      )='TRUE';

-- Second query: call sdo_filter with the function that returns an sdo_geometry
select name from LONG_LAT_TABLE  a
   where sdo_filter(get_long_lat_pt(a.longitude,a.latitude), 
     get_long_lat_pt(10,10)
     )='TRUE';

create type long_lat as object ( 
   longitude number, 
   latitude number, 
member function GetGeometry(SELF in long_lat) 
RETURN SDO_GEOMETRY DETERMINISTIC) 
/ 

create or replace type body long_lat as 
  member function GetGeometry(self in long_lat) 
  return SDO_GEOMETRY is 
    begin 
       return sdo_geometry(2001, 8307, 
           sdo_point_type(longitude, latitude, NULL), NULL,NULL); 
    end; 
end; 
/ 
  

create table test_long_lat 
   (location long_lat, name varchar2(32)); 

insert into test_long_lat values (long_lat(10,10), 'Place1'); 
insert into test_long_lat values (long_lat(20,20), 'Place2'); 
insert into test_long_lat values (long_lat(30,30), 'Place3'); 

insert into user_sdo_geom_metadata values('test_long_lat', 
 'scott.long_lat.GetGeometry(location)', 
 sdo_dim_array( 
   sdo_dim_element('Longitude', -180, 180, 0.005),
   sdo_dim_element('Latitude', -90, 90, 0.005)), 8307);

create index test_long_lat_idx on test_long_lat(location.GetGeometry()) 
  indextype is mdsys.spatial_index;

SELECT a.name FROM test_long_lat a
  WHERE SDO_FILTER(a.location.GetGeometry(),
            SDO_GEOMETRY(2001, 8307,
                SDO_POINT_TYPE(10,10,NULL), NULL, NULL)
            ) = 'TRUE';