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 = SDO_POINT2D) with
-- input of 2 numbers: longitude and latitude (SDO_SRID = 4326, 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
     IF (longitude IS NULL) OR (latitude IS NULL) THEN
         RETURN NULL;
     END IF;
     RETURN SDO_GEOMETRY(longitude, latitude);
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)), 4326);

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_v2;

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(10,10)
  )='TRUE';

NAME
--------------------------------
Place1

In 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 
       IF (longitude IS NULL) OR (latitude IS NULL) THEN
         RETURN NULL;
       END IF;
       RETURN SDO_GEOMETRY(longitude, latitude); 
    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)), 4326);

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_V2;

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(10,10)
            ) = 'TRUE';