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:
The procedure for using an SDO_GEOMETRY object in a function-based index is as follows:
Create the function that returns an SDO_GEOMETRY object.
The function must be declared as DETERMINISTIC.
If the spatial data table does not already exist, create it, and insert data into the table.
Update the USER_SDO_GEOM_METADATA view.
Create the spatial index.
For a function-based spatial index, the number of parameters must not exceed 32.
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.
In the following example, the input parameters to the function used for the function-based index are standard numeric values (longitude and latitude).
Assume that you want to create a function that returns the longitude and latitude of a point and to use that function in a spatial index. First, create the function, as in the following example that creates a function named GET_LONG_LAT_PT:
-- 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;
/
If the spatial data table does not already exist, create the table and add data to it, as in the following example that creates a table named LONG_LAT_TABLE:
create table LONG_LAT_TABLE (lon number, lat 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');
Update the USER_SDO_GEOM_METADATA view, using dot-notation to specify the schema name and function name. The following example specifies SCOTT.GET_LONG_LAT_PT(LON,LAT) as the COLUMN_NAME (explained in COLUMN_NAME) in the metadata view.
-- 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(lon,lat)',
sdo_dim_array(
sdo_dim_element('Longitude', -180, 180, 0.005),
sdo_dim_element('Latitude', -90, 90, 0.005)), 8307);
Create the spatial index, specifying the function name with parameters. For example:
create index LONG_LAT_TABLE_IDX on LONG_LAT_TABLE(get_long_lat_pt(lon,lat)) indextype is mdsys.spatial_index;
Perform queries on the data. The following example specifies the user-defined function in a call to the SDO_FILTER operator.
select name from LONG_LAT_TABLE a
where sdo_filter(
get_long_lat_pt(a.lon,a.lat),
sdo_geometry(2001, 8307, sdo_point_type(10,10,NULL), NULL, NULL)
)='TRUE';
NAME
--------------------------------
Place1In the following example, the input parameter to the function used for the function-based index is an object of a user-defined type that includes the longitude and latitude.
Assume that you want to create a function that returns the longitude and latitude of a point and to create a spatial index on that function. First, create the user-defined data type, as in the following example that creates an object type named LONG_LAT and its member function GetGeometry:
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;
/
If the spatial data table does not already exist, create the table and add data to it, as in the following example that creates a table named TEST_LONG_LAT:
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');
Update the USER_SDO_GEOM_METADATA view, using dot-notation to specify the schema name, table name, and function name and parameter value. The following example specifies SCOTT.LONG_LAT.GetGeometry(LOCATION) as the COLUMN_NAME (explained in COLUMN_NAME) in the metadata view.
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 the spatial index, specifying the column name and function name using dot-notation. For example:
create index test_long_lat_idx on test_long_lat(___location.GetGeometry()) indextype is mdsys.spatial_index;
Perform queries on the data. The following query performs a primary filter operation, asking for the names of geometries that are likely to interact spatially with point (10,10).
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';