扩展微软 sql server 的空间功能_数据库技巧

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 的空间功能

我经常想，在对空间信息的支持上，由于它缺乏对几何体的存储， MSSQL 总是比别的数据库慢了一拍。在新的 .NET CLR 的支持下，你可以真正地添加你自己的基于 .NET 的对象。尽管我也试了下在 SQL Server 中实现简单几何类型的存储，但有一些限制使我不得不放弃了尝试。首先，用户数据类型不能超过 8000 字节。也就是说，几何体对象不能超过 500 个节点，这对像海岸线这样的对象就显得太少了。另一个问题是 SQL Server 不支持继承，所以你也不能对你的数据类型做比较好的面向对象实现。

… 所以昨天我试着找到了一个完全不同的更简单的实现。我决定以 Well-Known Binary 的形式（译者注： OpenGIS 的说明书中定义了两个表述空间对象的标准方式：一个是 WKT （ the Well-Known Text ）形式，另一个是 WKB （ the Well-Known Binary ）形式）存储几何体在一个图像列中。使用图像列的目的是它能够保存大到 2G 的数据，这对大多数的几何对象都足够了。而字节列和用户自定义类型一样，也有 8000 个字节的限制，所以也不够好。除了几何列之外，我还创建了四个实数类型的列，用来存储几何外接矩形框的最大最小坐标值。这能提高基于外接矩形框的查询的效率。其它的列用来存储几何体的属性。

我在 SharpMap 中实现了这个方法。首先，我建立了一个小的数据库导入程序用来导入 shapefile 文件。它在数据库中建立一个表，然后把几何体及其对象导入其中。 SharpMap 为其提供了必要的数据读取器和 WKB 格式化程序。第二个部分是建立了一个数据提供接口， SharpMap 能够基于这个接口绘制数据。我做这些时多少参照了 PostGreSQL/PostGIS 的数据提供接口，只是用四个外框坐标列来做外接矩形框查询。所有这些工作所发费的时间不超过一个小时，因此，可以说做起来是比较简单的。

我必须说，对于这种方法的效率我是很惊讶的。它比 shapefile 的数据接口还快一点点，而 shapefile 数据接口曾经是 SharpMap 中最快的数据接口。而 PostGreSQL/PostGIS 相比而言要慢 4 － 6 倍。

我在这里创建了一个可下载的 web 演示程序。它分为两页：一个是导入到数据库，另一个是从数据库读取数据和绘制图层。所有你要做的是在 \App_Data\ 文件夹中增加一个 SQL Server 2005 Express 数据库并把它命名为 “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 日更新 )

更新：现在， MSSQL 数据接口也包含在 0.9RC1 版中，包含一个导入所有 SharpMap 支持的数据格式到 MS SQL 的方法。

译注：这是一篇比较长的文章，花了我大约一个半小时的时间，但是还是翻译得比较粗糙，请大家改进。