Join two feature class tables using STIntersects problems..... what am I missing?

[PeterLen]

Hello - We are using ArcGIS10 and I am using ArcObjects for Java to do some tasks (we have SDE sitting on top of Oracle).  I have two tables which each have a Polygon feature.  All I want to be able to do is return data from Table1 when the Table1.SHAPE intersects with the Table2.Shape and Table2.name like 'A%'.  There is no equivalent of Table2.name in Table1 which is why I need to do this join.  I have found two different ways to do the "intersect" statement in the WHERE clause:

1) select count(*) from Table1 t1, table2 t2 where t1.Shape.STIntersects(t2.Shape) = 1 and t2.name like 'A%';
- This gives me an ORA-00904: "T1"."SHAPE"."STINTERSECTS": invalid identifier error.

2) select count(*) from Table1 t1, table2 t2 where sde.st_intersects(t1.Shape, t2.Shape) =1 and t2.name like 'A%';
- This gives me the error:
   ORA-29902: error in executing ODCIIndexStart() routine
   ORA-28595: Extproc agent : Invalid DLL Path
   ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 788
   ......

I ran both of these statements in my SQL Developer where I have full access to the tables listed as well as in my java application that uses ArcObjects, again with full access to those tables.  I don't have any problems doing other queries on these tables when only one is used in the query.

I didn't think that this was going to be that difficult, but ....

Any clues about what I am missing?


Thanks - Peter

[MarkBaird]

Hi Peter,

I'm moving your post to the ArcObjects forum (this forum is for ArcGIS Runtime for Java which isn't based on ArcObjects). 

Hopefully you'll get a better response here.

Good luck

Mark

[SeanNakasone]

I'm somewhat new to ArcObjects so forgive me if I lead you astray...

I would double-check that you're NOT using Sql Server commands for an Oracle database.

Oracle has an sdo_anyinteract() function, here's an example...

SELECT parcel.g3e_fid AS parcel_fid
  FROM hcc_parcel_ar parcel
WHERE sdo_anyinteract (
          parcel.g3e_geometry,
          SDO_GEOMETRY (2001,
                        NULL,
                        SDO_POINT_TYPE (x_coord, y_coord, NULL),
                        NULL,
                        NULL)) = 'TRUE'


Also, your sql statement has Shape in it.  I know sql server has a Shape object, which leads me to think that it's intended for Sql Server, but of course you could name a field Shape in Oracle as well.