最初写这个程序是应老大的要求解决“更新Oracle中的空间数据时会因为wkt字符串太长而报错”这个问题，之前的更新都是在程序中插入一条SQL语句来进行更新，由于SQL语句本身的一些限制，在wkt字符串中包含几万个以上的点时就会报“ORA-01074：字符串文字太长”错误，这里提出了两种解决方法：
第一种：将之前传入简单的SQL更新语句，改为传入存储过程；
DECLARE
geom sdo_geometry;
BEGIN
geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,1),
sdo_ordinate_array(58184.2949999999, 39390.5210000016, 58208.6500000013, 39291.8900000025, 58499.0099999998, 39310.700000003, 58482.4699999997, 39409.8360000011, 58184.2949999999, 39390.5210000016)
);
execute immediate 'update zd_test set shape=:gm where objectid=242137' using geom;
END;
select objectid,shape from zd_test
select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=242137
第二种：使用参数方法进行传值
这种方法主要是参考了http://www.cnblogs.com/Anders888/p/3542288.html这篇文章，最终问题解决，代码如下：
这里SdoGeometry 、SdoPoint都已经经过封装，可参考http://blog.csdn.net/rrrrssss00/article/details/22879719
最终传参使用：
//单点
string wkt = "POINT (63918.6936862222 39311.6724619204)";
string conn_str = "Data Source=ORCL162; User ID = gttest; Password = gttest";//连接Oracle数据库的字符串
GeoneOracleGeometry geo = new GeoneOracleGeometry();
geo.UpdateGeometryData(conn_str, "zd_test", "objectid", "230361", "shape", wkt);
一、根据wkt创建几何对象：点、线、面
#region 创建几何对象：点、线、面
private SdoGeometry CreateGeometryByWkt(string wkt)
{
if (wkt.Contains("POLYGON"))
{
if (wkt.Contains("MULTIPOLYGON"))
{
//多边形：多个面
SdoGeometry MultiPolygon = new SdoGeometry();
MultiPolygon.Sdo_Gtype = 2007;
MultiPolygon.Sdo_Srid = null;
MultiPolygon.Point = null;
MultiPolygon.ElemArray = CreateMultiPolygonArray(wkt);
MultiPolygon.OrdinatesArray = ConvertPointToArray(wkt);
return MultiPolygon;
}
else if (wkt.Contains("),"))
{
//多边形：环(有岛多边形)
SdoGeometry CirculPolygon = new SdoGeometry();
CirculPolygon.Sdo_Gtype = 2003;
CirculPolygon.Sdo_Srid = null;
CirculPolygon.Point = null;
CirculPolygon.ElemArray = CreateCurArray(wkt);
CirculPolygon.OrdinatesArray = ConvertPointToArray(wkt);
return CirculPolygon;
}
else
{
//多边形：简单面
SdoGeometry SimplePolygon = new SdoGeometry();
SimplePolygon.Sdo_Gtype = 2003;
SimplePolygon.Sdo_Srid = null;
SimplePolygon.Point = null;
SimplePolygon.ElemArray = new decimal[] { 1, 1003, 1 };
SimplePolygon.OrdinatesArray = ConvertPointToArray(wkt);
return SimplePolygon;
}
}
else if (wkt.Contains("LINESTRING"))
{
//直线
SdoGeometry SimpleLine = new SdoGeometry();
SimpleLine.Sdo_Gtype = 2002;
SimpleLine.Sdo_Srid = null;
decimal[] dest = ConvertPointToArray(wkt);
SimpleLine.Point = null;
SimpleLine.ElemArray = new decimal[] { 1, 2, 1 };
SimpleLine.OrdinatesArray = dest;
return SimpleLine;
}
else if (wkt.Contains("CIRCULARSTRING"))
{
//曲线
SdoGeometry CirculLine = new SdoGeometry();
CirculLine.Sdo_Gtype = 2002;
CirculLine.Sdo_Srid = null;
decimal[] dest = ConvertPointToArray(wkt);
CirculLine.Point = null;
CirculLine.ElemArray = new decimal[] { 1, 2, 2 };
CirculLine.OrdinatesArray = dest;
return CirculLine;
}
else if (wkt.Contains("POINT"))
{
if (wkt.Contains("MULTIPOINT "))
{
//多点
SdoGeometry MultiPoint = new SdoGeometry();
MultiPoint.Sdo_Gtype = 2005;
MultiPoint.Sdo_Srid = null;
decimal[] dest = ConvertPointToArray(wkt);
MultiPoint.Point = null;
MultiPoint.ElemArray = new decimal[] { 1, 1, dest .Length/ 2 };
MultiPoint.OrdinatesArray = dest;
return MultiPoint;
}
else
{
//单点
SdoGeometry SimplePoint = new SdoGeometry();
SimplePoint.Sdo_Gtype = 2001;
SimplePoint.Sdo_Srid = null;
decimal[] dest = ConvertPointToArray(wkt);
SimplePoint.Point = new SdoPoint();
SimplePoint.Point.X = dest[0];
SimplePoint.Point.Y = dest[1];
SimplePoint.Point.Z = null;
SimplePoint.ElemArray = null;
SimplePoint.OrdinatesArray = null;
return SimplePoint;
}
}
return null;
}
二、读取wkt字符串并转换为数组
#region 读取wkt字符串并转换为数组
public decimal[] ConvertPointToArray(string wkt)
{
string RegExp = @"(\d+\.\d+|\d+)\s(\d+\.\d+|\d+)";
Regex regex = new Regex(RegExp, RegexOptions.Singleline | RegexOptions.IgnoreCase);
Match m = regex.Match(wkt);
int count = 0;
while (m.Success)
{
count++;
m = m.NextMatch();
}
count *= 2;
decimal[] dest = new decimal[count];
m = regex.Match(wkt);
int arrayIndex = 0;
while (m.Success)
{
string[] arr = m.Value.Trim().Split(new Char[] { ' ' });
decimal x0 = Convert.ToDecimal(arr[0]);
decimal y0 = Convert.ToDecimal(arr[1]);
dest[arrayIndex++] = x0;
dest[arrayIndex++] = y0;
m = m.NextMatch();
}
return dest;
}
#endregion
三、多边形：环 类型数组
#region 多边形：环 类型数组
public decimal[] CreateCurArray(string wkt)
{
string[] aa = wkt.Replace("),", "@").Split('@');
decimal[] bb = new decimal[aa.Length * 3];
bb[0] = 1;
bb[1] = 1003;
bb[2] = 1;
for (int i = 1; i < aa.Length; i++)
{
int length = aa[i - 1].Length - aa[i - 1].Replace(",", "").Length + 1;
bb[i * 3] = length * 2 + 1;
bb[i * 3 + 1] = 2003;
bb[i * 3 + 2] = 1;
}
return bb;
}
#endregion
四、多边形：多面 类型数组
#region 多边形：多面 类型数组
public decimal[] CreateMultiPolygonArray(string wkt)
{
string[] aa = wkt.Replace(")),", "@").Split('@');
decimal[] bb = new decimal[aa.Length * 3];
bb[0] = 1;
bb[1] = 1003;
bb[2] = 1;
int s = 0;
for (int i = 1; i < aa.Length; i++)
{
int length= aa[i-1].Length - aa[i-1].Replace("," , "").Length+1 ;
s = s + length;
bb[i* 3] = s * 2 + 1;
bb[i* 3 + 1] = 1003;
bb[i* 3 + 2] = 1;
}
return bb;
}
#endregion
五、更新几何对象：点、线、面
#region 更新几何对象：点、线、面
public string UpdateGeometryData(string conn_str, string tableName, string keyId, string KeyValuePar, string shapeId,string wkt)
{
try
{
OracleConnection conn = new OracleConnection(conn_str);
conn.Open();
string sql = string.Format("update {0} set {1}=:shape where {3}=:objectid", tableName, shapeId, wkt, keyId, KeyValuePar);
OracleCommand cmd = new OracleCommand(sql, conn);
//图形参数
OracleParameter pra = new OracleParameter();
pra.OracleDbType = OracleDbType.Object;
pra.UdtTypeName = "MDSYS.SDO_GEOMETRY";
//图形
pra.Value = CreateGeometryByWkt(wkt);
cmd.Parameters.Add(pra);
//关键字参数
OracleParameter prc = new OracleParameter(":objectid", KeyValuePar);
cmd.Parameters.Add(prc);
cmd.ExecuteNonQuery();
conn.Close();
cmd.Dispose();
}
catch (Exception ex)
{
throw ex;
}
return null;
}
#endregion