SQL Server 2008 "Katmai" (was: MsSqlSpatial made in Microsoft?)

Topics: Project Management Forum
May 10, 2007 at 7:34 AM
http://www.allpointsblog.com/archives/2831-Next-Version-of-SQL-Server-to-Include-Support-for-Geospatial-Data.html

what do you think about that?
May 10, 2007 at 4:52 PM
It's actually not that big news. It has been down the pipeline for quite some time now, and its about time. By now MSSQL is more or less the only good DB around that doesn't have this support without plugins.

I think it is awesome! Some of the things I could see the GIS community could benifit from:
- A real spatial index instead of the current 4-double-value-min-max mess
- Very likely .NET geometries to match the SQL datatypes

So what would the role of MSSQLSpatial be? Very likely the same as PostGIS: Extending existing spatial datatypes with OGC complaince, advanced GIS geoprocessing, spatial reference support etc.
Coordinator
May 10, 2007 at 11:57 PM
More info at VE Blog:

  • Spatial will be supported in the next release of SQL Server (code named Katmai) as system data types
  • Katmai is scheduled to ship in 2008 and will most likely be called SQL Server 2008
  • Katmai spatial will support two models: a “Flat Earth” planar data type and a “Round Earth” geodetic data type
  • The Flat Earth data type (GEOMETRY) will support the Open Geospatial Consortium (OGC) Simple Features for SQL Specification with support for approximately 70 spatial methods/functions
  • There will be spatial indexes for both planar and geodetic data types


It sounds great. Some questions I can think right now:
  • What are those 70 functions? (ie. What are the gaps MsSqlSpatial can fill in?)
  • Will it support 3D geometries?
  • Will SQL Server 2008 Express have full spatial support?

Possible areas for MsSqlSpatial development after Katmai release:
  • Indexing (yes, it could exist a chance for indexing performace improvement... if they open an indexing API)
  • More spatial analysis functions
  • Network data model
  • Topology data model
  • Raster support
  • Routing
  • Geocoding

Best regards,
Ricardo Stuven.
Jun 20, 2007 at 1:45 PM
Hello,

I'm evaluating this project, and wondered if there would be a way to easily migrate relevant portions of data stored under MsSqlSpatial to the new 2008 server, or if there are plans to provide a migration tool.

The project I'm on uses shapefiles, in which the data of both the .dbf and .shp files are needed. The .dbf files are being parsed and stored in a SQL Server 2005. All of the information is available locally. The geometry is needed to render a map (I'm also looking into SharpMap). Which of the following two options do you think would be best:

  • Use MsSqlSpatial now and store the geometry in the database. When 2008 is released, migrate data, no changes to app.
  • Have the application load the .shp file locally. When 2008 is released, upload data, change app (with SharpMap I'm guessing it is the provider).

I understand that 2008 might be missing features that MsSqlSpatial can provide, but the assumption right now is that the geometry is used only for rendering purposes.

Thank you,
Jon
Jun 22, 2007 at 5:26 PM
Sql2008 supports reading WKB which is the format MsSqlSpatial uses to store data, so I don't see why you shouldn't be able to copy all your tables to Sql2008 using a simple SQL query. The SQL queries for retrieving data will change slightly, but they should only require minor changes.
I would go with option one and switch the provider in the code whenever it becomes available.
Jun 25, 2007 at 2:35 PM
Fantastic, thank you.
Coordinator
Jun 27, 2007 at 6:52 AM
Sure, a migration path will provided and, if possible, a way to use MsSqlSpatial on Katmai in a friendly and complimentary way. The details will be clear when a Katmai CTP with spatial support is released.

For rendering purposes, you still could need a simplification function (such as ST.Simplify) to preprocess geometries for improving performance.

Best regards,
Ricardo Stuven.
Aug 1, 2007 at 1:48 PM


SharpGIS wrote:
Sql2008 supports reading WKB which is the format MsSqlSpatial uses to store data, so I don't see why you shouldn't be able to copy all your tables to Sql2008 using a simple SQL query. The SQL queries for retrieving data will change slightly, but they should only require minor changes.
I would go with option one and switch the provider in the code whenever it becomes available.


SharpGIS:

I have tried to directly copy WKB data stored in a SQL SERVER 2005 table (created by MsSqlSpatial through loading a shape file) into a SQL server 2008 table (I used insert into and select sql statement). It didn't work. Here was what I got:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'geometry':
System.FormatException: One of the identified items was in an invalid format.
System.FormatException:
at Microsoft.SqlServer.Types.GeometryData.Read(BinaryReader r)
at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r)
at SqlGeometry::.DeserializeValidate(IntPtr , Int32 )

I have also tried to deploy MsSqlspatial to SQL server 2008 database. It also failed. I feel that a migration path would be nice to have.

The last thing left for me is to try using SSIS.

Thanks,
Adrian
Aug 2, 2007 at 3:28 AM
As far as I know, there are no public beta of MSSQL2008 yet. If you have access to some internal builds, you probably also have access to some Microsoft people who you can let know that there are problems with loading and parsing WKB.
Aug 2, 2007 at 2:32 PM


SharpGIS wrote:
As far as I know, there are no public beta of MSSQL2008 yet. If you have access to some internal builds, you probably also have access to some Microsoft people who you can let know that there are problems with loading and parsing WKB.


You are right that we did get the pre-beta from and internal source. We can ask Microsoft. I am more interested to know that MsSqlSpatial migration path and support for MSSQL2008. MSSQL2008 is coming on Feb. 27th, 2008 as you all know.

The public July CTP version of MSSQL2008 is available now and you can get it from here:

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

Thanks,
Adrian
Coordinator
Aug 2, 2007 at 2:57 PM
So there's a chance we can put our hands on Katmai Spatial right now... :-)

Regarding your question, it seems you're adding apples to oranges. Strictly speaking, MsSqlSpatial geometry binary format is not WKB (though is based on it), so you should use ST.AsWKB function prior to pass it to a WKB-aware Katmai function.

Best regards,
Ricardo Stuven.
Aug 2, 2007 at 4:05 PM


rstuven wrote:
So there's a chance we can put our hands on Katmai Spatial right now... :-)

Regarding your question, it seems you're adding apples to oranges. Strictly speaking, MsSqlSpatial geometry binary format is not WKB (though is based on it), so you should use ST.AsWKB function prior to pass it to a WKB-aware Katmai function.

Best regards,
Ricardo Stuven.


Thanks Ricardo for your reply.

Just to keep you guys updated. I can directly insert WKT output from MsSqlSpatial into SQL2008 using STGeomFromText() function in SQL2008. For WKB, I still have problem. I checked functions in MsSqlSpatial there is no function called St.AsWKB() in the version I have deployed on my server (version 0.1.0). I only have ST.AsBinary(). So I am guessing that it is a new one in Version 1.1.1, right?

Thanks,
Adrian
Coordinator
Aug 2, 2007 at 4:59 PM
Edited Aug 2, 2007 at 5:02 PM

I checked functions in MsSqlSpatial there is no function called St.AsWKB() in the version I have deployed on my server (version 0.1.0). I only have ST.AsBinary(). So I am guessing that it is a new one in Version 1.1.1, right?


Oops, my mistake! I always meant ST.AsBinary, which returns standard WKB. ST.AsWKB doesn't exist in any version.

And thanks for your progress reports. Please, keep us updated!

Best regards,
Ricardo Stuven.