0

I am using ArcSDE and Oracle with the ST_GEOMETRY spatial type. I'm writing C# code using SqlGeoemtry types. What i want is to be able to request an intersect but use wkb instead of wkt. I know it works with wkt but if my feature has many vertices I get an error from Oracle about the string literal being too large ( i guess it is 2000 characters or so). I also know that i can chunk that large string into a CLOB and send the chunks in and have the intersect operation work.

What I would like would be to use the binary format and avoid all of these issues. But i'm having trouble with the syntax. Here is what works for wkt:

oracleCommand.CommandText = string.Format("SELECT OBJECTID FROM {0} WHERE sde.st_intersects(shape, sde.st_polyfromtext('{1}', 3071)) = 1", selectionLayerName, unionedBuffer.ToString());

Here is what does not work right now:

oracleCommand.CommandText = string.Format("SELECT OBJECTID FROM {0} WHERE sde.st_intersects(shape, sde.st_polyfromwkb('{1}', 3071)) = 1", selectionLayerName, unionedBuffer.STAsBinary());

Obstacle's complaint is ORA-29900: operator binding does not exist What can i do to get Oracle to accept the incoming feature in a Binary format?

VBAHole
  • 1,508
  • 2
  • 24
  • 38
  • for reference here is the esri doc - http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/st-polyfromwkb.htm – VBAHole Dec 21 '16 at 15:44

1 Answers1

0

I figured it out. I had to create an Oracle parameter as a Blob then load in the byte array from the SqlGeometry call:

oracleCommand.CommandText = string.Format("SELECT OBJECTID FROM {0} WHERE sde.st_intersects(shape, sde.st_polyfromwkb(:THEBLOB, 3071)) = 1", selectionLayerName);
OracleParameter param = oracleCommand.Parameters.Add(new OracleParameter(":THEBLOB", OracleDbType.Blob));
param.Value = unionedBuffer.STAsBinary().Value;
VBAHole
  • 1,508
  • 2
  • 24
  • 38