ST_MULTILINESTRING….ESRI Bug?

Recently our client found a bug They were testing the services and tried to create a long linear location – suddenly the server returned a nasty oracle error. Everything had worked fine up until the application server tried to write the geometry as WKT into the database at which point things went astray. To resolve this issue we built a small test which uses ODP.Net:

As a bit of background I’ve been working on a project which delivers a set location translation web services – basically the organisation collect data using a number of methods some of which have a linear aspect. They want to be able to translate between these methods as well as validate the locations to ensure a consistent data quality and finally enrich each location through spatial analysis. In effect we’ve been developing an advanced linear referencing locator.

We made a decision with the solution that we would utilise as much functionality at the database level as possible and that means we use ST_Geometry where we can. Unlike some implementations the ESRI ST implementation does not provide linear referencing functions therefore the web services which are served out from an application server communicates with our spatial server hosted within ArcGIS Server. The spatial server component provides dynamic segmentation and some other spatial functions. For linear locations the dynamic segmentation operations return ArcObjects geometry which is parsed and returned to the application server as WKT.

All has been fine until last week when our client found a bug They were testing the services and tried to create a long linear location – suddenly the server returned a nasty oracle error. Everything had worked fine up until the application server tried to write the geometry as WKT into the database at which point things went astray. To resolve this issue we built a small test which uses ODP.Net:

//wkt
string WKT = "some well known text"
 
//create parameters
OracleParameter p_wkt  = new OracleParameter();
p_wkt.OracleDbType = OracleDbType.Clob;
p_wkt.Value = WKT;
 
OracleParameter p_srid  = new OracleParameter();
p_srid.OracleDbType = OracleDbType.Int32;
p_srid.Value = 0;
 
//create command
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.Parameters.Add(p_wkt);
cmd.Parameters.Add(p_srid);
 
//sql
string sql = "INSERT INTO GEOMETRY_TESTBED VALUES(1, SDE.MULTILINESTRING(:p_wkt, :p_srid))";
 
//execute
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

So basically Oracle was spitting a dummy when I was inserting a lengthy piece of text as the WKT parameter, returning an ORA-01461: can bind a LONG value only for insert into a LONG column. Basically when the WKT was truncated to less than 4000 characters long the insert was working. I wanted to try and insert a very long geometry so I selected (ST_ASTEXT & ST_LENGTH) the geometry from the database with the highest number of points (ST_NUMPOINTS) which returned a 400,000+ character string using the ST_LINESTRING M constructor, I updated the code to:

string sql = "INSERT INTO GEOMETRY_TESTBED VALUES(1, SDE.LINESTRING M(:p_wkt, :p_srid))";

Insert worked fine. So lets try another geometry – this time using ST_NUMGEOMETRIES > 1 I selected the longest multi part with the highest number of verticies. This time the database returned a geometry with the ST_MULTILINESTRING M constructor, I updated the code to:

string sql = "INSERT INTO GEOMETRY_TESTBED VALUES(1, SDE.MULTILINESTRING M(:p_wkt, :p_srid))";

Insert worked fine! Ok then, now I tried my geometry as returned from my ArcObjects parser – this time amended to have a dummy M value appended to each coordinate, and using the ST_MULTILINESTRING M constructor. Worked!

Is this a bug with the ST_MULTILINESTRING constructor within the ESRI ST implementation on Oracle? Somewhere under the hood is the UDT using a varchar when it shouldn’t be?

Has anyone else seen this issue?