C # Development of SQL Server geometry and Geography storage

Original: C # Geometry and Geography storage for SQL Server development

The biggest change since SQL Server2008 was introduced is the geometry and geography that support spatial data storage, which is a data storage mode that can be selected by the user if the ARCSDE and SQL Server databases are combined. Because the database versions before SQLServer2008 are blobs to store spatial data objects, there is currently no test to determine which performance is better, but individuals tend to geometry and geography.

One advantage of using these two types of storage is that SQL Server databases can be manipulated using SQL as well as with Oracle, PostgreSQL.

In the previous article we also covered the operation of SQL, but it was done in SQL Server Manger Studio.

Related reference to: SQL operations with ArcSDE for SQL Server

This article describes how to use C # in a programmatic way to manipulate the geometry and geography of SQL Server spatial data stores.

First we need to quote a Microsoft.SqlServer.Types.dll,

This file differs according to the different distributions of 32Bit and 64Bit

32bit:c:\program Files \microsoft SQL Server\100\sdk\assemblies

64bit:c:\program Files (x86) \microsoft SQL Server\100\sdk\assemblies

Note If your SQL Server is 2008 and 2012 there are also related differences.

Referencing the relevant DLLs, we can develop the

The following code environment: VS2010, referencing DLL version SQL Server2012

Using system;using system.collections.generic;using system.componentmodel;using system.data;using System.Drawing; Using system.linq;using system.text;using system.windows.forms;using microsoft.sqlserver.types;using System.data.sqlclient;using system.data.sqltypes;using system.configuration;namespace SQL{public partial class Form1                      : Form {public Form1 () {InitializeComponent ();        Object s= Getsrid ("sde101", "SDE", "chinacitytable"); }//connection string string connstring = "Data source=192.168.100.111;initial catalog=sde101; Persist Security info=true; User Id=sde;        Password=sde "; Get data Connection Public SqlConnection getsqlconnection () {try {return new SQL            Connection (connstring);            } catch (Exception) {return null; }}//Convert Geometry object to wkt string public void Querytowkt () {stringsql = "Select shape from Xzq where objectid=45";            SqlGeometry g = getgeometry (SQL);            if (g! = null) {//with wkt output Console.WriteLine (g); }}//Get Geometry object////<summary>//////Use SQL statement to get Sqlgeometry object///</summary&gt        ; <param name= "SQL" ></param>///<returns></returns> public SqlGeometry GETGEOMETR                Y (String sql) {try {SqlCommand cmd = getcommand (SQL); SqlDataReader reader = cmd.                ExecuteReader (); while (reader. Read ()) {sqlbytes SB = reader.                    GetSqlBytes (0);                    The deserialize method can only be used with DLLs that reference SQLServer2012, and 2008 does not support SqlGeometry g = sqlgeometry.deserialize (SB);                return g;            } return null;       } catch (Exception e)     {return null;} }///<summary>///Sqlgeometry objects///</summary>//<param name= "swkt" according to WKT string ></param>//<param name= "Ssrid" ></param>//<returns></returns> P Ublic SqlGeometry getgeometry (string swkt, int ssrid) {//var sql = "POLYGON (509827.650 3848402.022,50                9763.336 3847282.564,509268.926 3847640.308,509827.650 3848402.022)) ";            SqlChars polytext = new SqlChars (SWKT);        Return Sqlgeometry.stgeomfromtext (Polytext, Ssrid);            } Public SqlCommand GetCommand (String sql) {SqlConnection conn = getsqlconnection (); Conn.                  Open ();                  SqlCommand cmd = new SqlCommand (SQL, conn);        return cmd; }///delete and change directly write a different SQL statement can public void EditData () {try {string SWK T = "POLYGON ((513132.704 3847868.592,513860.309 3846638.277,512682.911 3846598.589,513132.704 3847868.592)) ";                String sql = "Insert Xzq (Objectid,shape) VALUES (11111, '" +swkt+ "')";                SqlCommand cmd = getcommand (SQL); Cmd.            ExecuteNonQuery (); } catch (Exception e) {}}//Get Intersect data///<summary>//Unable to L Oad DLL ' SqlServerSpatial110.dll ': The specified module could not be found.            (Exception from hresult:0x8007007e)///</summary> public void getintersections () {                try {string sql1 = "Select shape from Xzq where objectid=86";                String sql2 = "Select shape from Xzq where objectid=71";                SqlGeometry G1 = getgeometry (SQL1);                             SqlGeometry g2 = Getgeometry (SQL2); Get the intersecting graphics SqlGeometry g3 = G1.                Stintersection (G2); if (g3! = null) {//with wkt output Console.WriteLine (G3); }} catch (Exception e) {}}//Get two points away from public string getdistance (SQL Geometry P1, SqlGeometry p2) {return P1. Stdistance (p2).        ToString (); }///<summary>////////////////////////For the Gdb_items table to obtain the Srid value of the Table object//Gdb_items table Table after ARCSDE is installed///select shape from [sde101]. [SDE].  [chinacitytable]//</summary>//<param name= "sTableName" ></param>//<param Name= "Sdatabasename" ></param>//<param name= "suser" ></param>//<returns>< /returns> public Object Getsrid (string sdatabasename,string suser,string stablename) {//If not found            , returns 0 object SRID = null; If you are familiar with the table structure of the ARCSDE library, just parse the relevant XML file to get the Srid string sql = "Select Definition.value (' (/defeatureclassinfo/spatialref   Erence/latestwkid) [1] ', ' nvarchar (max) ') from "+             Sdatabasename+ "." +suser+ ". [Gdb_items] WHERE name= ' "+sdatabasename+". " +suser+ "."              +stablename+ "'";                SqlCommand cmd = getcommand (SQL); SqlDataReader reader = cmd.                ExecuteReader (); while (reader. Read ()) {srid= reader.                Getsqlvalue (0);        } return SRID; }    }}

The above code has several needs to explain

1: In the process of using Geometry object parsing, use deserialize, the method only SQL Server2012 has, if you use SQL Server2008 without the method

The difference between 2:geometry and geography is that the projections you use are planar coordinates and geographic coordinates.

3: In the use of spatial relationships, the system will prompt

Unable to load DLL ' SqlServerSpatial110.dll ': The specified module could not be found. (Exception from hresult:0x8007007e)

This is because the Microsoft.SqlServer.Types.dll file needs to use the operating system C:\Windows\System32\SqlServerSpatial110.dll (also installed with different versions of SQL Server , the version number under the path is not the same, 110 for the 2012 version), the DLL is not loaded, it needs to be stored in the system path, note that both versions are consistent.

4: Get the Srid, I get it directly from the XML of the ARCSDE schema.

Finally, because this is the space storage object provided by Mircosoft, Help is available to view Microsoft Help.

http://technet.microsoft.com/zh-cn/library/ee642046 (v=sql.105). aspx

Related DLLs

A step further, if you are interested in the content of this aspect, it is recommended to see

SQL Server Spatial ToolsThe following address has the relevant source code download-------------------------------------------------------------------------------------------------------
Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal responsibility!
------------------------------------------------------------------------------------------------------

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

C # Development of SQL Server geometry and Geography storage