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.

擴展微軟 SQL Server 的空間功能

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

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

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

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

我在這里創(chuàng)建了一個可下載的 web 演示程序。它分為兩頁：一個是導(dǎo)入到數(shù)據(jù)庫，另一個是從數(shù)據(jù)庫讀取數(shù)據(jù)和繪制圖層。所有你要做的是在 App_Data 文件夾中增加一個 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 版中，包含一個導(dǎo)入所有 SharpMap 支持的數(shù)據(jù)格式到 MS SQL 的方法。

譯注：這是一篇比較長的文章，花了我大約一個半小時的時間，但是還是翻譯得比較粗糙，請大家改進。