Database Software

hello,

I'm using a function-based spatial index, which I've set up in a way similar to Oracle's examples in the Spatial documentation. Here's the basic idea:

create table places (
place_id not null
primary key,
latitude number,
longitude number
);

-- This function is used to create an MDSYS.SDO_GEOMETRY object
-- as part of the Oracle Spatial indexing of the latitude and
-- longitude.
create or replace function get_long_lat_pt(
longitude in number,
latitude in number)
return MDSYS.SDO_GEOMETRY deterministic
is
begin
if latitude is null or longitude is null
then
return null;
else
return mdsys.sdo_geometry(2001, 8307,
mdsys.sdo_point_type(longitude, latitude, NULL), NULL, NULL);
end if;
end;
/
show errors

INSERT INTO USER_SDO_GEOM_METADATA
VALUES ('PLACES', 'schema_name.GET_LONG_LAT_PT(LONGITUDE,LATITUDE)',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 0.00000005),
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 0.00000005)),
8307
);

-- Create Oracle Spatial function-based index
CREATE INDEX places_long_lat_idx ON places(get_long_lat_pt(longitude, latitude))
INDEXTYPE IS mdsys.spatial_index PARAMETERS('layer_gtype="POINT"');


All of this works just fine. But then I try to do a nearest neighbor calculation:

select /*+ INDEX(places places_long_lat_idx) */ place_id
FROM places WHERE
SDO_NN(
get_long_lat_pt(longitude,latitude), -- table values
get_long_lat_pt(-71.1482, 42.3367), -- an arbitrary point
'sdo_num_res=2') = 'TRUE'

and I get this:

ERROR at line 1:
ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 27

It's as though Oracle isn't using the function-based index inside my SDO_NN call, even with a INDEX hint. I am stumped.

Just for kicks I tried to do a query using SDO_FILTER instead. I get:

ERROR at line 1:
ORA-13268: error obtaining dimension from USER_SDO_GEOM_METADATA
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 365

I am using Oracle 9.0.1.0.0, and I set QUERY_REWRITE_ENABLED=TRUE and QUERY_REWRITE_INTEGRITY=TRUSTED as noted in the Spatial documentation.

Any clues?

Thanks,
Greg