C # Create geometric objects in Oracle: Point, line, polygon

Originally wrote this program is the boss's request to solve the "update the spatial data in Oracle because the WKT string is too long to error" This problem, the previous update is to insert an SQL statement in the program to update, due to some limitations of the SQL statement itself, When the WKT string contains more than tens of thousands of points, the "ORA-01074: string literal is too long" error is reported, and two workarounds are presented here:

The first one: to pass the simple SQL UPDATE statement to the stored procedure;

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

Second: Use the parameter method to pass the value

This method is mainly referred to http://www.cnblogs.com/Anders888/p/3542288.html this article, the final problem resolution, the code is as follows:

Here Sdogeometry, Sdopoint have been encapsulated, can refer to http://blog.csdn.net/rrrrssss00/article/details/22879719

The final parameters are used:

Single point
String wkt = "point (63918.6936862222 39311.6724619204)";
String conn_str = "Data source=orcl162; User ID = gttest; Password = Gttest ";//String connecting the Oracle Database
Geoneoraclegeometry geo = new Geoneoraclegeometry ();
Geo. Updategeometrydata (Conn_str, "Zd_test", "Objectid", "230361", "shape", wkt);

I. Create geometric objects from wkt: Point, line, polygon

#region creating geometric objects: Point, line, polygon

Private Sdogeometry creategeometrybywkt (string wkt)
{
if (wkt. Contains ("POLYGON"))
{
if (wkt. Contains ("Multipolygon"))
{
Polygon: Multiple Faces
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 ("),"))
{
Polygon: Ring (with island polygon)
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
{
Polygons: Simple Faces
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"))
{
Linear
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"))
{
Curve
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"))
{
Multi-point
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
{
Single point
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;
}

Second, read the WKT string and convert the array

#region Read wkt string and convert to array

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

Three, polygon: Ring type array

#region Polygon: Array of ring types

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

Iv. polygons: Arrays of multi-faceted types

#region Polygon: Array of faceted types
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

V. Updating geometric objects: Point, line, polygon

#region Updating geometric objects: Point, line, polygon

public string Updategeometrydata (string conn_str, String tableName, String keyId, String keyvaluepar, String Shapeid,stri Ng 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);
Graphics parameters
OracleParameter pra = new OracleParameter ();
Pra. Oracledbtype = Oracledbtype.object;
Pra. UdtTypeName = "Mdsys. Sdo_geometry ";
Graphics
Pra. Value = Creategeometrybywkt (wkt);
Cmd. Parameters.Add (PRA);
Keyword parameters
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 # Create geometric objects in Oracle: Point, line, polygon