Katmai Spatial - First thoughts

I've been doing some work with the Spatial types in Katmai and these are my first thoughts.

The first thing is that the data type is the same used by SQL as used in your .Net code. So what does that mean,

·         You use the same methods, which is great from a familiarity perspective

·         When calling the methods in TSQL you are calling the CLR methods and so they are case sensitive.

·         Because TSQL doesn't have things like collections and arrays the types don't have them. So if you are using the spatial types in .Net this is why there is no .Points property that returns the points of the geometry. Instead you have to call a function if passing an index to get the relevant Point. There is a function that returns number of points to enable you to loop through the points. You will therefore have code like this,

for (int i = 0; i<geom.STNumPoints();i++)


     SqlGeometry Point = geom.STPointN(i+1);


·         You can return your value to the client and perform actions upon it orsan the client or do it in TSQL. This is great because you can make a collection of geometries on your client and do spatial stuff with it without even touching a database. Why might you want to do this? Spatial calculations are complex, its easy to calculate the area of a square, but what about an irregular polygon, you can do it but with how many lines of code. With the spatial types just use STArea().

·         There aren’t any nice methods that work with other CLR types. It would be great if the STPointN returned a CLR Point type, but it doesn’t, so you do end up with more code than you may think you should.

So what other pointers are there,

·         All methods are prefixed by ST. This I don’t understand and seems pointless to me. (Ok so most are and are due to conforming to standards/

·         The Point arrays are not 0 based but 1 based indexes. So you have to start at 1.

·         Everything is related, a Multi Polygon is a geometry that contains Polygon geometries, A Polygon consists of Points which themselves are geometries. You may ask why? Well the reason is that all the spatial methods can be applied to all the geometry types, finding distances, areas, unions etc.

·         Creating geometries isn’t the nicest mechanism. You generally create a string representation and then pass that to be parsed.

declare @g geometry;

set @g = geometry::STGeomFromText(‘LINE(1 1,10 10,10 20,20 10)’)

All in all its really easy. Once you get round the types of geometries and what all the methods do its amazing what you can do with so few lines of code, both in TSQL or in your favourite .Net code.


Published Sunday, December 9, 2007 9:43 PM by simonsabin


Monday, December 10, 2007 12:17 AM by Colin Angus Mackay

# re: Katmai Spatial - First thoughts

T-SQL may not have collection types, but I suspect that doesn't stop the SqlGeometry class from supporting a collection through a property. STNumPoints and STPointN are defined in the OGC Specification (which I remember reading many years ago and balking at it at the time because it didn't make much sense even in the COM days)

Also, all methods are not prefixed with ST. Just the ones that conform to the OGC Specification. There are a number of extended methods also: http://msdn2.microsoft.com/en-us/library/bb933880(SQL.100).aspx

I have to admit that I am a little disappointed with the spatial aspect of SQL Server 2008 as Microsoft seem to be bending over backwards to adhere to the specification rather than produce something that is more useful or fitting with the standards of their own products. I hope that in future version the number of "Extended" methods increases that fit the .NET way of doing things a lot better.

Thursday, December 13, 2007 12:05 AM by Jake Heidt

# re: Katmai Spatial - First thoughts

The ST prefix, is from what I understand to be a 'Spatial Temporal' function, which was specified in the OpenGIS spec.

There is pretty much zero temporal functionality in the current implementations of the spec these days, however. Querying spatial data in 4 dimensions sounds pretty awesome to me though.


Monday, December 31, 2007 8:54 AM by SimonS Blog on SQL Server Stuff

# SQL Server 2008 Spatial - Using it in .Net code

So you want to use the SqlGeometry and SqlGeogrpahy collections in .Net. I found the lack of friendly

Monday, December 31, 2007 5:49 PM by Morten

# re: Katmai Spatial - First thoughts

"Microsoft seem to be bending over backwards to adhere to the specification rather than produce something that is more useful or fitting with the standards of their own products"

WHAT ???!? This is exactly the kinda thing that gets Microsoft into trouble, and they finally learned from it and do what everyone else does. This is a BIG plus.

Because they adhered to the standard, I had no trouble adding MSSQL to the list of supported databased (I just wished they would have used the latest standard instead which would be much more fitting for the functionality MS put in there).

"The Point arrays are not 0 based but 1 based indexes. So you have to start at 1."

Again the OGC standard specifies this.

Tuesday, January 1, 2008 9:21 PM by simonsabin

# re: Katmai Spatial - First thoughts

They can include the standard methods andextend the types to have more CLR standard functionality.

The fact you can't get a PointF from a Point Geometry is just annoying. Similarly if I want to extend a line there is no means except to convert the points to format and then add the new point in the relevant format. It would have been nice to be able to create a geometry from a point collection.