C#建立Oracle中的幾何對象：點、線、面

標籤：

最初寫這個程式是應老大的要求解決“更新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

C#建立Oracle中的幾何對象：點、線、面