SQL Server 2008 Spatial Data Application series six: SQLCRL-based spatial data programmability

Original: SQL Server 2008 Spatial Data Application series six: SQLCRL-based spatial data programmability

tips, the prerequisites for reading this blog post are as follows:

1. This sample is based on Microsoft SQL Server R2 Commissioning.

2. Experience in Transact-SQL programming and use of Management Studio.

3. Have experience using Microsoft Visual Studio for Microsoft. NET Framework Development.

4. Be familiar with or understand the spatial data types in Microsoft SQL Server 2008.

5, with corresponding (such as OGC) of GIS professional theoretical knowledge.

Microsoft. NET applications are typically hosted on the. NET framework of the operating system platform, if you want to use it in SQL Server 2008. NET for managed code, SQL Server 2008 will run an own. NET Framewrok Platform Runtime Environment (Sqlos), and the Sqlos and. NET CLR will share the database engine process space, so SQL Server-based The 2008 CLR is also known as the SQLCLR. SQL Server 2008-based core components SQLCRL provides friendly programmability support,The SQL Server database engine uses features provided by the CLR seamlessly to provide a variety of features , This includes programmability support for data that uses spatial data types goegraphy and goemetry.

I. SQLCLR &. NET CLR Interoperability

You can find the spatial data type Shared library component (Microsoft.SqlServer.Types.dll) for SQLCLR and. NET CLR shared in the installation directory of SQL Server 2008, which supports SQL Server-based 2008 of Spatial data type database programming, but also supports the. NET framework-based object-oriented programming, including support for VB.net, C # MultiLanguage, which is located in the following installation directory :

C:\Program Files\Microsoft SQL Server\ - \sdk\assemblies\microsoft.sqlserver.types.dll

The following sample program demonstrates bidirectional mutual support based on Microsoft.SqlServer.Types.dll between the SQLCLR and. NET CLR.

SQLCLR: Defining a geographic coordinate point
declare @geom geometry;
Set@geom=geometry::P oint (107.04352,28.870554,4326);
Select @geom;
-----------------------------------------------------------------------------------
. NET CLR: Defining a geographic coordinate point
Static voidMain (string[] args)
{
var point=Sqlgeometry.point (107.04352, 28.870554, 4326);

Console.WriteLine (Point. STX);
Console.WriteLine (Point. STY);
Console.WriteLine (Point. ToString ());
}

In the. NET CLR, you can use the programming interfaces provided in all SQLCLR programming, which means that the. NET CLR becomes more than just the definition of a re-spatial object, but also includes the object's related properties, methods, and the following code block demonstrates SQLCLR and. Net CLR programming enables comparison of geographic ranging functions.

declare @geom geometry;
Set@geom=geometry::P oint (107.04352,28.870554,4326);

declare @end geometry;
Set@end=geometry::P oint (103.84041, 29.170240,4326);
Select @geom. Stdistance (@end);
------------------------------------------------------------------------
var Pointstart=Sqlgeometry.point (107.04352, 28.870554, 4326);
var pointend=Sqlgeometry.point (103.84041, 29.170240, 4326);
var result=pointstart.stdistance (pointend);
Console.WriteLine ("Geographic Distance:" +result+ "(m)");

About the properties of the spatial object, the specific use of the function is not introduced here, in detail, please refer to: The OGC method on the geometry instance.

Ii. creating a Spatial object to a database

Microsoft.SqlServer.Types.dll provides friendly. NET CLR programmability, as well as the ability to insert objects created in the. NET CLR into a spatial database for querying for spatial analysis and computation. The following demonstrates constructing a polygon space object through the. NET CLR and inserting it into a SQL Server 2008 database.

Static voidMain (string[] args)
{
//Define a polygon
var polygon=Sqlgeography.stgeomfromtext (
NewSqlChars (
NewSqlString ("POLYGON ( -114.01611328125 42.0003251483162, -114.0380859375 42.0003251483162,"
+ "-113.994140625 37.0200982013681, -109.05029296875 37.0200982013681, -109.09423828125 41.0130657870063,"
+ "-111.07177734375 41.0462168145206, -111.07177734375 42.0003251483162, -114.01611328125 42.0003251483162))",
111)),
4326);

var sql= "Insert Cities (cityname,citylocation) VALUES (' Test ', '" +Polygon. ToString ()+ "')";

INSERTTODB (SQL);
}

Private Static voidInserttodb (stringSQL)
{
using(Var conn= NewSqlConnection (configurationmanager.appsettings["SQL2008"]))
{
if(Conn. State==connectionstate.closed) Conn. Open ();
using(var cmd= NewSqlCommand (SQL, conn))
{
intRow=cmd. ExecuteNonQuery ();
}
}
}

Iii. Querying database spatial data

First take a look at the records that SQL Server Management Studio query has just put in, and you can preview the results of the query directly with spatial results.

Declare @citygeometry;
Select @city =citylocation fromCitieswhereID=5;
Select @city;
Select @city. Starea () as area;--Find Area----------------------------------------------------------------------------------------------------------- ----------------------------
0x000000000104080000000000000008815cc035b18aa70a0045400000000070825cc035b18aa70a00454000000000a07f5cc0cecaee9392824240000 0000038435bc0cecaee93928242400000000008465bc0bcf1c323ac8144400000000098c45bc01572bd6eea8544400000000098c45bc035b18aa70a00 45400000000008

(1 rows affected)

Area
----------------------
22.6802255629445

(1 rows affected)

　　　　　　　　

It is also possible to programmatically implement it using the. NET CLR, which is no different from querying the normal data, but simply converting the query result to a spatial data type. Query spatial data can be implemented in the application using the following code.

Static voidMain (string[] args)
{
var sql= "Select Citylocation from Cities where ID = 5";
var result=querydb (SQL);

var polygon=Sqlgeography.stgeomfromtext (
NewSqlChars (
NewSqlString (Result)),4326);

Console.WriteLine (Polygon. ToString ());
}

Private Static stringQuerydb (stringSQL)
{
using(Var conn= NewSqlConnection (configurationmanager.appsettings["SQL2008"]))
{
if(Conn. State==connectionstate.closed) Conn. Open ();
using(var cmd= NewSqlCommand (SQL, conn))
{
returncmd. ExecuteScalar (). ToString ();
}
}
}

This article is about here, the regular content, please follow the following series of posts, the next one will introduce Microsoft Bing Maps and spatial data close contact, please look forward to ~ ~ ~ ~

Iv. Related Information

[1], data table use spatial data type: http://www.cnblogs.com/beniao/archive/2011/02/21/1959347.html

[2], the OGC method on the geometry instance: http://msdn.microsoft.com/zh-cn/visualc/bb933960.aspx

[3], the extension method on the geometry instance: http://msdn.microsoft.com/zh-cn/library/bb933880.aspx

[4], OGC static geometry method: Http://msdn.microsoft.com/zh-cn/library/bb933894.aspx

Copyright notice

This article is an original article, welcome to reprint and note the source of the article, its copyright belongs to the author and the blog Garden altogether. In order to preserve the author's passion for creation, please mark the source of this article in the obvious place after reprint.

Author: Beniao

Article source: http://beniao.cnblogs.com/or http://www.cnblogs.com/

SQL Server 2008 Spatial Data Application series six: SQLCRL-based spatial data programmability