Make Microsoft SQL Server geospatial

【原文】：http://www.sharpgis.net/2006/05/14/MakeMicrosoftSQLServerGeospatial.aspx

【翻譯】：小粟 日期：2007年2月11日

I’ve always thought that on the spatial support, MSSQL was way behind many of the other database servers, in its lack of supporting storage of geometry. With the new .NET CLR you can actually add your own .NET-based object types and I’ve also tried implementing the Simple Features Specification in SQL Server. There are some limitations that made me give this up though. First of all, a user data type cannot be more than 8000 bytes. That is at most no more than 500 vertices in a geometry object, which is far too little for an accurate coastline for instance. Another problem is that SQL Server doesn’t support inheritance chains, so you can’t make a good object-oriented implementation of your datatype either.

… so yesterday I went for a completely different and much simpler approach. I decided to just store the geometry as Well-Known Binary in an image column. The reason for using an image column is that it can hold up to 2Gb of data, which should be sufficient for most geometry objects . A binary field has the same 8000 byte limitation as UDT so this is no good. In addition to the geometry field, I create four real-type fields, holding the min/max values of the geometry envelope. This makes it efficient to do boundingbox based queries on the data. Any additional fields would be attributes for the geometry object.

I implemented the whole thing using SharpMap. First I created a small upload application that takes a shapefile, creates a table in the database and uploads the geometry and attributes to it. SharpMap has the necessary data readers and WKB formatters for this. The second part was to create a data provider from which SharpMap could draw from. I more or less based this on the PostGreSQL/PostGIS data provider for SharpMap, by changing the boundingbox query to use the four envelope fields. All this was not much more than an hour’s work, so it is very simple to accomplish.

I must say I was very surprised by the performance of the approach. It is just slightly faster than the shapefile data provider, which used to be the fastest data provider for SharpMap. In comparison the PostGreSQL/PostGIS is generally 4-6 times slower.

I have created a small demo web-application you can download from here. It contains two pages: one for uploading to the database, and one for rendering a layer from the database. All you need to do is to add an empty SQL Server 2005 Express database to the App_Data folder and name id 'GeoDatabase.mdf'.

Download SharpMapSqlServer.zip (181,74 KB) (updated May 20, 2006)

Update: The MsSqlProvider is now also included in v0.9RC1, including a method for uploading from any of the SharpMap datasources to MS SQL.

擴(kuò)展微軟 SQL Server 的空間功能

我經(jīng)常想，在對(duì)空間信息的支持上，由于它缺乏對(duì)幾何體的存儲(chǔ)， MSSQL 總是比別的數(shù)據(jù)庫慢了一拍。在新的 .NET CLR 的支持下，你可以真正地添加你自己的基于 .NET 的對(duì)象。盡管我也試了下在 SQL Server 中實(shí)現(xiàn)簡(jiǎn)單幾何類型的存儲(chǔ)，但有一些限制使我不得不放棄了嘗試。首先，用戶數(shù)據(jù)類型不能超過 8000 字節(jié)。也就是說，幾何體對(duì)象不能超過 500 個(gè)節(jié)點(diǎn)，這對(duì)像海岸線這樣的對(duì)象就顯得太少了。另一個(gè)問題是 SQL Server 不支持繼承，所以你也不能對(duì)你的數(shù)據(jù)類型做比較好的面向?qū)ο髮?shí)現(xiàn)。

… 所以昨天我試著找到了一個(gè)完全不同的更簡(jiǎn)單的實(shí)現(xiàn)。我決定以 Well-Known Binary 的形式（譯者注： OpenGIS 的說明書中定義了兩個(gè)表述空間對(duì)象的標(biāo)準(zhǔn)方式：一個(gè)是 WKT （ the Well-Known Text ）形式，另一個(gè)是 WKB （ the Well-Known Binary ）形式）存儲(chǔ)幾何體在一個(gè)圖像列中。使用圖像列的目的是它能夠保存大到 2G 的數(shù)據(jù)，這對(duì)大多數(shù)的幾何對(duì)象都足夠了。而字節(jié)列和用戶自定義類型一樣，也有 8000 個(gè)字節(jié)的限制，所以也不夠好。除了幾何列之外，我還創(chuàng)建了四個(gè)實(shí)數(shù)類型的列，用來存儲(chǔ)幾何外接矩形框的最大最小坐標(biāo)值。這能提高基于外接矩形框的查詢的效率。其它的列用來存儲(chǔ)幾何體的屬性。

我在 SharpMap 中實(shí)現(xiàn)了這個(gè)方法。首先，我建立了一個(gè)小的數(shù)據(jù)庫導(dǎo)入程序用來導(dǎo)入 shapefile 文件。它在數(shù)據(jù)庫中建立一個(gè)表，然后把幾何體及其對(duì)象導(dǎo)入其中。 SharpMap 為其提供了必要的數(shù)據(jù)讀取器和 WKB 格式化程序。第二個(gè)部分是建立了一個(gè)數(shù)據(jù)提供接口， SharpMap 能夠基于這個(gè)接口繪制數(shù)據(jù)。我做這些時(shí)多少參照了 PostGreSQL/PostGIS 的數(shù)據(jù)提供接口，只是用四個(gè)外框坐標(biāo)列來做外接矩形框查詢。所有這些工作所發(fā)費(fèi)的時(shí)間不超過一個(gè)小時(shí)，因此，可以說做起來是比較簡(jiǎn)單的。

我必須說，對(duì)于這種方法的效率我是很驚訝的。它比 shapefile 的數(shù)據(jù)接口還快一點(diǎn)點(diǎn)，而 shapefile 數(shù)據(jù)接口曾經(jīng)是 SharpMap 中最快的數(shù)據(jù)接口。而 PostGreSQL/PostGIS 相比而言要慢 4 － 6 倍。

我在這里創(chuàng)建了一個(gè)可下載的 web 演示程序。它分為兩頁：一個(gè)是導(dǎo)入到數(shù)據(jù)庫，另一個(gè)是從數(shù)據(jù)庫讀取數(shù)據(jù)和繪制圖層。所有你要做的是在 App_Data 文件夾中增加一個(gè) SQL Server 2005 Express 數(shù)據(jù)庫并把它命名為 'GeoDatabase.mdf' 。

下載 http://www.sharpgis.net/ct.ashx?id=d3710d85-a735-4efc-aad7-f9c8f4844c1e&url=http%3a%2f%2fwww.sharpgis.net%2fcontent%2fbinary%2fSharpMapSqlServer.zip (181,74 KB) (2006 年 5 月 20 日更新 )

更新：現(xiàn)在， MSSQL 數(shù)據(jù)接口也包含在 0.9RC1 版中，包含一個(gè)導(dǎo)入所有 SharpMap 支持的數(shù)據(jù)格式到 MS SQL 的方法。

譯注：這是一篇比較長的文章，花了我大約一個(gè)半小時(shí)的時(shí)間，但是還是翻譯得比較粗糙，請(qǐng)大家改進(jìn)。