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

707
2
06-09-2014 09:55 AM
PeterLen
Occasional Contributor
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
0 Kudos
2 Replies
MarkBaird
Esri Regular Contributor
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
0 Kudos
SeanNakasone
New Contributor II
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.
0 Kudos