Expand the space capabilities of Microsoft SQL Server

I often think that in support of spatial information, because it lacks the storage of geometry, MSSQL is always a bit slower than other databases. With the support of the new. NET CLR, you can really add your own based on. NET objects. Although I tried to implement a simple geometric type of storage in SQL Server, there were some limitations that I had to give up. First, the user data type cannot exceed 8000 bytes. That is, a geometry object cannot exceed 500 nodes, which is too few for objects like coastlines. Another problem is that SQL Server does not support inheritance, so you can't do a better object-oriented implementation of your data type.

... So yesterday I tried to find a completely different and simpler implementation. I decided to take the form of well-known Binary (translator note: OpenGIS's manual defines two standard ways of expressing space objects: one is WKT (the well-known Text) and the other is WKB (the Well-know N Binary) stores the geometry in an image column. The purpose of using an image column is that it can save up to 2G of data, which is sufficient for most geometric objects. Byte columns, like user-defined types, also have a limit of 8,000 bytes, so it's not good enough. In addition to the geometric columns, I also created four real-type columns to store the maximum minimum of the rectangle for the geometric outer rectangular box. This improves the efficiency of queries based on an external rectangular box. Other columns are used to store the properties of the geometry.

I realized this method in the Sharpmap. First, I set up a small database importer to import the Shapefile file. It creates a table in the database and then imports the geometry and its objects into it. Sharpmap provides it with the necessary data reader and WKB formatter. The second part is the creation of a data supply interface that SHARPMAP can draw data based on this interface. I do these when the number of reference to the Postgresql/postgis data to provide the interface, just use four bounding box coordinates column to do external rectangular box query. All these jobs are not issued for more than one hours, so it can be said that it is relatively simple to do so.

I must say that I am amazed at the efficiency of this approach. It is a bit faster than the Shapefile data interface, and the Shapefile data interface was once the fastest data interface in Sharpmap. And Postgresql/postgis is 4-6 times slower than that.

I've created a downloadable web demo here. It is divided into two pages: one is to import to the database, the other is to read data from the database and draw layers. All you have to do is add a SQL Server App_Data Express database in the folder and name it "Geodatabase.mdf."