topic Re: Join two feature class tables using STIntersects problems..... what am I missing in ArcObjects SDK Questions

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

PeterLen — Mon, 09 Jun 2014 16:55:39 GMT

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

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

MarkBaird — Tue, 10 Jun 2014 09:04:36 GMT

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

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

SeanNakasone — Thu, 12 Jun 2014 17:47:30 GMT

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.