Katmai Spatial - First thoughts
I've been doing some work with the Spatial types in Katmai and these are my
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
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 =
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.
set @g =
geometry::STGeomFromText(‘LINE(1 1,10 10,10 20,20
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.