Oracle spatial operator SDO_JOIN returns ORA-01722 invalid number

Question

Firstly, I have created metadata in user_sdo_geom_metadata for two tables 'test_katt' and 'test_del_ko'. Each table has a unique id (featureid). And one table has points geometry and the other has polygons.

--example

insert into user_sdo_geom_metadata (table_name, column_name, diminfo,srid) values (
    'test_katt',  --table name
    'geometry',         
    SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Y',365000,627000,0.005), --box
    SDO_DIM_ELEMENT('X',28000,195000,0.005)), --box
    null
  );

Secondly: I have created a spatial index for both tables:

create index test_katt_idx 
          on test_katt (geometry) indextype is mdsys.spatial_index;

Finally, I wanted to join data from points in 'test_katt' and data from polygons in 'test_del_ko' based on any spatial interaction. With the result: Error report - ORA-01722: invalid number

select *   
from test_del_ko dk, test_katt ka,  
                table (SDO_JOIN('test_del_ko', 'geometry',   
                'test_katt', 'geometry',   
                'mask = anyinteract') )a  
where a.rowid1 = dk.featureid  
and a.rowid2 = ka.featureid;

I haven't succeeded yet in creating a result with the SDO_JOIN, so any help with the procedure would be very helpful. We have oracle 12 database.

Answer 1

In your query, a.rowid1 and a.rowid2 are oracle rowids in varchar2 format, and you are comparing them to the featureid columns in your tables, and I suppose they are numbers. That's why you have an error. Use this query instead:

select *   
from test_del_ko dk, test_katt ka,  
                table (SDO_JOIN('test_del_ko', 'geometry',   
                'test_katt', 'geometry',   
                'mask = anyinteract') )a  
where a.rowid1 = dk.rowid  
and a.rowid2 = ka.rowid;