A View Inside My Head

Jason's Random Thoughts of Interest


SQL Server 2008: Spatial Data, Part 7

The Open Geospatial Consortium's Simple Features specification, which SQL Server 2008's Geometry data type is based upon, defines standards for working with spatial data using a flat-earth (projected planar) model.  Ironically, these standards don't exactly cover the intricacies of using an ellipsoidal model, which is needed to "accurately" represent the world that we live in.  In other words, the OGC standards define how to work with paper maps of the world, but not globes.

Fortunately, the SQL Server team recognized that that the Geometry type is inadequate for a lot of scenarios, and implemented a second data type just for representing geospatial data using a true ellipsoidal model: Geography.

In this, the seventh part of a series about the SQL Server Spatial Data Type, I'll examine some of the key differences between the Geometry and the Geography type that developers should be aware of.


Latitude and Longitude

Locations on a flat model are defined in terms of X and Y.  There exists some point known as the Origin where X and Y are both zero.  From there, it is defined that values of X will increase (or decrease, in the case of negatve numbers) if you move horizontally away from the Origin.  Likewise, the values of Y will increase if you move vertically away from the Origin. 

By convention, both X and Y will grow to infinity, so flat models do not "wrap around" and start approaching the Origin again if you go too far in one direction.  Usually, a coordinate system will be based on some underlying representation of the real-world, so coordinates that are beyond the defined boundaries of that map are logically undefined.

By contrast, though, an ellipsoidal model does wrap around.  If you started at a point in the middle and kept traveling in a straight line to the right, you will eventually return to that starting point. 

So, it turns out not to be very practical to define points on a ball using X and Y.  Instead, points are defined using angles.  Longitude is the horizontal angle (how far East or West from a Prime Meridian) and ranges from -180 degrees to 180 degrees (with -180 and 180 being the same).  Latitude is the vertical angle (how far North or South from the Equator) and ranges from -90 degrees to 90 degrees (with -90 representing the South Pole and 90 representing the North Pole).


In terms of the Geography data type, just be aware that there is no X and Y.  Instead, you work with Long and Lat.

Note: All of the SQL Server 2008 CTPs to date that include Spatial support, including the most recent February 2008 version, use Lat-Long ordering within WKT.  This was a design decision based on the fact that the OGC standard did not already define parameter ordering for angular coordinates.  Starting with the first Release Candidate, however, these parameters will be swapped to use Long-Lat ordering.  Doing so will align SQL Server's spatial support with other platforms that have already implemented Long-Lat ordering.  Note also that that this is aligned with the concepts of X and Y, which by convention lists the X value first.


Straight Lines

The shortest distance between two points is a straight line.  But, a straight line on a flat-earth model is far different than a straight line on an ellipsoidal model.  To demonstrate, consider the shortest path from Redmond, WA, USA to Cambridge, England, UK:

On this planar projection, it certainly looks like the shortest path.  Even when examined on a 3D model, it looks correct:

But, if the camera is moved towards the North Pole, then the error becomes apparent:


In the ellipsoidal model, the shortest path between the points is not the red line, which roughly parallels the lines of Latitude, but rather the black arrow!  Converted back to a planar projection, this actual shortest path appears curved:


(in this view, the black curve was [hastily] plotted by hand)

Instance Methods

The following slide shows in all of the instance methods that have been implemented for the Geography type as of the February 2008 CTP.  For comparison, instance methods from the Geometry type that do not exist in the Geography type are shown in gray.


It may be impossible to define some of these equivalent methods for Geography, simply because the rules are different.  For instance, if you define a set of points that make up a Polygon, what is considered to be the interior and exterior of that shape?  Since the world coordinates wrap around in an ellipsoidal model, you might be intending to represent a shape whose interior is the entire world except for the small portion.  There is simply no way to convey your intent using the methods as described by the OGC standard.

In an attempt to prevent this particular scenario, the SQL Server team has imposed a limit on the size of a Geography in the February 2008 CTP: you cannot define a Geography that is larger than a hemisphere.  

There may very well be logical solutions for working around some of the issues that prevented the SQL team from implementing all of Geometry's methods in the Geography type.  However, in this case, Microsoft appears to be waiting for the OGC to define certain rules as part of a standard rather than coming up with their own assumptions, which could be invalidated later by the standards group going in a different direction.

More on the Geography type later!

SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

SQL Server 2008: Spatial Data, Part 3

SQL Server 2008: Spatial Data, Part 4

SQL Server 2008: Spatial Data, Part 5

SQL Server 2008: Spatial Data, Part 6

(next part) SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com

SQL Server 2008: Spatial Data, Part 6

In the Part 4 and Part 5 of the series, I demonstrated some instance methods of the Geometry type that returned a new Geometry based on existing instances.  In this part, I will concentrate on instance methods and properties of the Geometry type that return scalar values and Points.

STArea, STLength

Typically, your spatial data will represent something from the real world.  A LineString may be the collection of points gathered from a GPS device, and together they may represent the path that you took from your home to the office.  A Polygon may be the collection of points around the boundary of governmental territory, like a county or a parish within your state.

In both of these cases, the time will come when you will want to know the length of the LineString (or length of the perimeter of the Polygon) and the area within the Polygon.  OGC standard method STArea() returns a float indicating the area of the instance in square units (or 0, if the instance is not a Polygon and does not have area).  STLength() returns a float indicating the length of the instance in units (or 0, if the instance is a Point and does not have length). 


DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))'
@g.STArea(), @g.STLength() Results: Area Length
450 102.426406871193




Thinking back to Mr. Bollenbacher's 10th Grade Geometry class, we had to use a compass and straight edge to construct lines bisecting the angles of polygons (primarily triangles).  The point where the angle bisectors met was the exact center, or centroid, of the shape.  Centroids are important because any line that passes through a centroid will divide the Polygon into two parts of equal area.  It should be noted that a Centroid may not actually be on the surface of a Polygon.

The OGC standard method STCentroid() returns a Point indicating the centroid of the shape.  If the instance is not a Polygon (or MultiPolygon), then NULL will be returned.

DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))'
@g.STCentroid().ToString() Results: POINT (20 30)

Note: SpatialViewer displays an individual point as an X.


STWithin, STContains

Two OGC standard methods returns a 1 or 0 indicating whether all of the points of one instance exist entirely inside of another instance.  STWithin() tests whether the base instance is inside of the parameter instance, while STContains() tests whether the parameter instance is inside of the base instance.

DECLARE @g geometry = 'POLYGON ((10 10, 13 30, 30 30, 30 15, 10 10))'
DECLARE @h geometry = 'LINESTRING (16 16, 16 24, 25 18)'
SELECT @g.STContains(@h), @g.STWithin(@h)
SELECT @h.STContains(@g), @h.STWithin(@g)
Results: 1 0

0 1



ST is {something}?

There are a number of OGC standard methods to check whether a given instance meets certain specifications: STIsClosed, STIsEmpty, STIsRing, STIsSimple, STIsValid

CLOSED: An instance is considered to be closed if the start point is the same as the end point.  By definition, a Polygon has to be closed, and a Point is not closed.  That really only leaves LineString.  For a collection of objects to be considered closed, all of its members must be closed.

EMPTY: A Geometry can be initialized in a special way as to not contain any points.  In SQL terms, this is sort of like having a NULL value, except it really is an instantiated object.  For example, LINESTRING EMPTY is a valid LineString, but it has no points.  Another humorous example is POINT EMPTY, which initializes to a Point without a Point....  so it's kind of Pointless, right?  (thank you, I'm here all week, tip your waitress).

RING: An instance is considered to be a ring if it is both Closed and Simple.

SIMPLE: An instance is considered to be simple if it does not cross over itself or otherwise touch itself.  For example, a LineString forming the letter 'S' is simple because it never comes in contact with itself.  But, a LineString that forms a Figure-Eight (8) is not simple because it would have to cross over itself.  Likewise, two circles (MultiPolygon) stacked on top of each other to form a Figure-Eight would not be simple because they touch each other.

VALID: A Geometry can cross over itself, but it cannot legally trace over itself.  That is, picture a LineString that backtracks over itself at some point, kind of like how I write my letter "P".  This is not considered to be Valid.


Tip: SQL Server will allow an invalid Geometry to be instantiated, and Microsoft has provided an extension method called MakeValid() that will convert the invalid instance into a valid instance.  In the letter "P" example, instead of the vertical line going down and then back up (as I draw it by hand), the valid form will eliminate the duplication of points simply by start at the bottom and going up (so that the LineString never traces over itself).  If it's not possible to simplify a shape in this way so that there is only one continuous path, then it will be broken up into multiple valid shapes (i.e., a MultiLineString, etc).



Individual coordinates of a Point can be accessed via the OGC Standard properties STX and STY.  Three-dimensional Points also have a Z coordinate, which can be accessed via Microsoft's extended Z property.  Likewise, four-dimensional Points have a M (for Measure) coordinate, which can be accessed via Microsoft's extended M property.  If Z or M is not defined for a given point, then NULL will be returned.

DECLARE @g geometry = 'POINT(1 2)'
@h geometry = 'POINT(1 2 3 4)'
@g.STX, @g.STY, @g.Z, @g.M SELECT @h.STX, @h.STY, @h.Z, @h.M Results: 1 2 NULL NULL 1 2 3 4



When working with spatial data, especially without using a viewer, it can be kind of difficult to pick an arbitrary point that is inside of a Polygon (or on a LineString).  Thankfully, the OGC standard method STPointOnSurface() does just that.  Given a Geometry instance, it will return a somewhat random point that is guaranteed to be located within the interior of that instance.

DECLARE @g geometry = 'POLYGON((10 10, 14 15, 50 12, 45 30, 10 30, 10 10))'
SELECT @g.STPointOnSurface().ToString() Results: POINT (23 25)




All of my examples to this point have used the default Spatial Reference ID of 0 (for the Geometry type) simply because I have not been specifying one.  The SRID is the mechanism that defines one geometry as being based on a different set of parameters than a geometry with a different SRID. 

For example, you may have a set of shapes defined where each unit represents one meter, while another set of shapes is based on a reference system where each unit represents 1.5 inches.  It's totally legal to mix these shapes together the same column of a table in your database, provided that you assign a different SRID to each.  SQL Server does not need to know what units represent, because it will never permit the interaction of a shape from one SRID with a shape from another SRID. 

The OGC standard property STSrid will get (or set) the SRID of the Geometry instance.

-- @g will have the default SRID = 0
DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))'

-- @h is defined with SRID = 123
DECLARE @h GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((10 10, 40 10, 40 40, 10 10))', 123) select @g.STUnion(@h).ToString() -- Returns NULL because of different SRIDs. But, let's change
-- @g to use SRID = 123

@g.STSrid = 123 SELECT @g.STUnion(@h).ToString() -- Returns POLYGON ((10 10, 40 10, 40 40, 10 40, 10 10))


Jason, What's Next?

Enough of this flat Earth stuff!  In the next part, I'll explore the Geography data type.  This is where things really start to get interesting.  Stay tuned!

SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

SQL Server 2008: Spatial Data, Part 3

SQL Server 2008: Spatial Data, Part 4

SQL Server 2008: Spatial Data, Part 5

(next part) SQL Server 2008: Spatial Data, Part 7

SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com

SQL Server 2008: Spatial Data, Part 5

In the previous part of this series, I demonstrated instance methods that transformed a single Geometry type into another useful Geometry.  In this post, we'll go a step further and show methods that allow two or more instances to interact with one another in order to produce a new Geometry.

For my baseline, I'll use two Polygons that overlap each other:

  DECLARE @g geometry 
        = 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'DECLARE @h geometry 
        = 'POLYGON((30 30, 50 30, 50 50, 30 50, 30 30))'



STDifference() returns a new instance consisting of all points from the base instance that do not contain points from the parameter instance.


  SELECT @g.STDifference(@h).ToString();


POLYGON ((10 10, 40 10, 40 30, 30 30, 30 40, 10 40, 10 10))




STIntersection() returns a new instance containing only the points that are in common between the base instance and the parameter instance.


  SELECT @g.STIntersection(@h).ToString();


POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))




STSymDifference() returns a new instance containing only the points that are unique to both the base instance and the parameter instance (i.e., it excludes the points that STIntersection() would return).

In this case, the set of points is actually two different Polygons.  Because STSymDifference() needs to return a single instance of something, it will wrap those two Polygons into a collection (MultiPolygon).


  SELECT @g.STSymDifference(@h).ToString();


MULTIPOLYGON (((40 30, 50 30, 50 50, 30 50, 30 40, 40 40, 40 30)), 
              ((10 10, 40 10, 40 30, 30 30, 30 40, 10 40, 10 10)))




STUnion() returns a new instance containing all of the points of the base instance and the parameter instance merged together.


  SELECT @g.STUnion(@h).ToString();


POLYGON ((10 10, 40 10, 40 30, 50 30, 50 50, 30 50, 
30 40, 10 40, 10 10))



Blended Types

The instance methods described above do not work just for Polygons.  You can actually use them on different types, or even collections of different types. 

For instance, if we look at the results of using a LineString as the base instance and a Polygon as the parameter instance, STDifference() will return a MultiLineString constisting of the points from the original LineString that do not lie within the Polygon:

  DECLARE @g geometry = 'LINESTRING(9 9, 40 40)'DECLARE @h geometry = 'POLYGON((15 15, 15 30, 30 30, 30 15, 15 15))'SELECT @g.STDifference(@h).ToString();


MULTILINESTRING ((40 40, 30 30), (15 15, 9 9))



STIntersection() will return the points from the original LineString that do lie within the Polygon:

  SELECT @g.STIntersection(@h).ToString();


LINESTRING (30 30, 15 15)



STUnion() cannot determine a single common Geometry type, so it will return a mixed collection of types:

  SELECT @g.STUnion(@h).ToString();


     LINESTRING (40 40, 30 30), 
     POLYGON ((15 15, 30 15, 30 30, 15 30, 15 15)), 
     LINESTRING (15 15, 9 9)



SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

SQL Server 2008: Spatial Data, Part 3

SQL Server 2008: Spatial Data, Part 4

(next part) SQL Server 2008: Spatial Data, Part 6

SQL Server 2008: Spatial Data, Part 7

SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com

SQL Server 2008: Spatial Data, Part 4

In this, the 4th post in a series (Part 1, Part 2, Part 3) on the new spatial data types in SQL Server 2008, I'll explain some of the methods that are used to transform a single Geometry instance into another useful Geometry instance.  Note that I'm using Geometry for simplicity, but these techniques also work with GeographyEdit: Ok, after starting to take a hard look at Geography, I realized that A LOT of the methods that Geometry offers are not implemented in Geography.  :-/  Sorry to mislead you.


Useful TipTo help me to visualize geometries as I explore the capabilities of the spatial data type, I've been using SpatialViewer, a tool written by fellow SQL Server MVP Simon Sabin.


As a baseline, I'll be using my highest quality, hand-drawn letter "S".  This LineString is a simple geometry (it does not cross over itself), but is not closed (the starting and ending points are not the same).


SET @g = 'LINESTRING (  69 26, 69 23, 69 21, 67 20, 65 20, 
          63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20, 
          44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38, 
          50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45, 
          69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63, 
          66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71, 
          51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56, 
          41 54, 42 53 )'




A bounding box is a rectangle that is defined by the combination of minimum and maximum X and Y values found in a given Geometry instance.  The OGC standard method STEnvelope() returns the bounding box (a Polygon) for the instance on which it is invoked.  All points of the original instance lie within the new Polygon.

Note: In the following T-SQL examples, I will trail the method calls with a ToString() so that we can examine the resulting WKT.  Normally, you would just work with the geometry (i.e., you wouldn't need call ToString())..


  SELECT @g.STEnvelope().ToString()


POLYGON ((41 17, 71 17, 71 71, 41 71, 41 17))


Note: In these pictures, the original geometry is drawn in black, and the new geometry is superimposed in red.  If the new geometry has area, then that area will appear as light brown or tan.



biten_apple_xl If something is convex, then it is thought of as having a surface that is curved or rounded outward from the center.  For example, the side of an apple is convex.  If you happen to take a bite out of the apple, however, then that the portion that has been removed is considered to be concave.  To "correct" the concave portion of the half-eaten apple, we could fill in the hole with something like clay, but we would only need to restore enough material so that there was a straight line from the top of the hole to the bottom (and the same for side to side).

The OGC standard method STConvexHull() returns the minimal bounding convex Polygon for a geometry instance.  That is, any convex parts of the original instance will be preserved, and any concave parts will be "filled in", so to speak, by defining a straight line to bypass them.  Like STEnvelope(), all points of the original instance lie within the new Polygon.


  SELECT @g.STConvexHull().ToString()


POLYGON ((71 51, 70 56, 68 63, 66 65, 65 66, 63 68, 60 71, 
          59 71, 57 71, 55 71, 51 69, 45 65, 42 62, 41 59, 
          41 57, 41 56, 41 54, 42 26, 44 20, 45 18, 49 17, 
          51 17, 52 17, 58 17, 63 18, 67 20, 69 21, 71 51))




What if you have an existing shape, and you want to make it bigger, but preserve the general... uh, shape of it?  Then you would use the OGC standard method STBuffer(distance)!  This method returns a Polygon that inflates the area around the original geometry instance by a number of units that you provide.  Note that if the original instance is a Point, then the result will be a circle with a radius of the number of units that you provided.  It is also possible to deflate an existing Polygon by supplying a negative buffer value.


  SELECT @g.STBuffer(5).ToString()


POLYGON ((49 12, 51 12, 52 12, 58 12, 
          58.246719360351562 12.00609016418457, 
          58.492688179016113 12.024333953857422, 
          58.737458229064941 12.054683685302734, 
          58.98058032989502 12.097097396850586, 
          (... snipped for clarity ...)
          48.693824768066406 12.009382247924805, 49 12))


What happens if the amount of buffering forces the inflated area to overlap with itself?  The resulting Polygon may develop holes (the area within a hole is still considered part of the exterior of the Polygon).  Here the buffer increases to 8, creating a hole:


  SELECT @g.STBuffer(8).ToString()


POLYGON (( exterior ring points ), ( interior ring points ))



STExteriorRing, STInteriorRingN

The result of the very last example was a Polygon with one interior hole.  OGC standards provide a way to access the various components of a polygon (exterior ring and interior rings) individually. 

STExteriorRing() returns just the closed LineString of the Polygon itself.


  SELECT @g.STBuffer(8).STExteriorRing().ToString()


LINESTRING (49 9, 51 9, 52 9, 58 9, 
            58.394750595092773 9.0097446441650391, 
            (... snipped for clarity ...)
            48.022533416748047 9.0599403381347656, 
            48.5101203918457 9.0150127410888672, 49 9)


Similarly, STInteriorRingN(n) is used to return the closed LineString of interior rings. 

Note: This method is accessing a member of a GeomCollection by index, which I plan to cover in a later post.  What's important to know now is that indexing starts at 1, and you will get an error if you specify an index that does not actually exist in the collection.

  SELECT @g.STBuffer(8).STInteriorRingN(1).ToString()


LINESTRING (48.893725268961383 48.937175344014442, 
            49.084709167480469 49.279642105102539, 
            (... snipped for clarity ...)
            48.893725268961383 48.937175344014442)

Extended Methods

Microsoft has implemented some additional methods on Geometry instances to perform tasks that are beyond the scope of the OGC standards.

Reduce(tolerance) is a method that will simplify a given instance using the Douglas-Peucker algorithm.  The result is a an approximation of the original instance containing a fewer number of points.  The accuracy of the new shape improves as the provided tolerance value approaches zero, but more points are necessary to provide that accuracy. 


  SELECT @g.Reduce(5).ToString()


LINESTRING (69 26, 49 17, 42 26, 42 35, 70 48, 
            60 71, 42 62, 42 53)



BufferWithTolerance(distance, tolerance, relative) is very similar in function to STBuffer(), only it gives you more control over the accuracy of the result.  The tolerance parameter, like in the Reduce() method, controls the amount of acceptable error a resulting line segment can be from what is ideal. 

To understand how tolerance factors into the result, then picture a Point that is buffered into a circle.  To truly represent a circle, we would need a Polygon consisting of a infinite number of points.  This is not practical, but by allowing for error, we can generate a regular Polygon with any number of sides that behaves like a circle.  Smaller tolerances result in Polygons with a very large number of very small sides.  Likewise, larger tolerances result in Polygons with fewer sides, and your circle will start to resemble things like octagons, hexagons, and even squares.

Compare the image below generated with a less accurate tolerance (left image), to the STBuffer(8) example from above (right image).  The difference is subtle, primarily because the scale that we're working with is pretty small.  But, one difference to note is that the ends of the inflated "S" on the left are straight while the ones on the right are rounded.  Overall, the the image on the right has more points, and thus is a more accurate the original "S" shape than the image on the left.


  SELECT @g.BufferWithTolerance(8, 10, 0).ToString()


POLYGON (( ring points ), ( hole points ))

SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

SQL Server 2008: Spatial Data, Part 3

(next part) SQL Server 2008: Spatial Data, Part 5

SQL Server 2008: Spatial Data, Part 6

SQL Server 2008: Spatial Data, Part 7

SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com

SQL Server 2008: Spatial Data, Part 3

In the previous parts of this series (Part 1, Part 2), I introduced the Geometry and Geography data types, the various subclasses (Point, LineString, Polygon, etc), and demonstrated a little bit of the Well-Known Text (WKT) syntax.  These two posts were primarily informational in nature, and didn't touch SQL Server at all.  Let's change that!

Instantiating the UDT

The Geometry and Geography data types are implemented as User Defined Types (UDT) written in .NET.  They are automatically installed with the server, and are available for use by any SQL Server 2008 database.  For this post, I will use the Geometry type to demonstrate capability, but the Geography type has the very same capabilities (the primary difference between the two types is how distance and area is calculated). Edit: After taking a hard look at Geography, I realized that a lot of methods that Geometry offers are not implemented.  So, I was incorrect in my assertion that everything available in Geometry is also available in Geography.

Any variable, parameter, or table column can be declared as type Geometry.  Notice that the type name is not case sensitive.

  DECLARE @g Geometry

As mentioned before, Geometry by itself is abstract.  In order to do something interesting, you need to instantiate the type as one of the concrete subclasses of the Geometry type.  Adhering to the OGC standards, you can use the STGeomFromText() static method to parse data provided in valid WKT syntax, and then your variable will take on the characteristics of the defined subclass (i.e., it will be instantiated as one of the concrete types).  Note that in SQL syntax, CLR method names are case sensitive, so stgeomfromtext() will not work.

Now, in C# and VB.NET, it is common to invoke static methods of a type using a dot notation, as in geometry.STGeomFromText().  However, this is not the case in T-SQL, because that particular syntax implies that you're calling a User Defined Function belonging to the "geometry" schema.  Instead, when calling a static method belonging to a type, you separate the type name and the method name using two colons (::).

For example, to use STGeomFromText() to create a LineString, you would do the following:

  DECLARE @g Geometry
SET @g = Geometry::STGeomFromText('LINESTRING(0 0, 10 10, 21 2)',


You might be wondering, "What's that weird zero at the end all about?"  In Part 1, I touched on the concept of a Spatial Reference System that defines things such as the unit of measure and the dimensions of the world being represented, etc.  The zero in this method call is the Spatial Reference ID (SRID) parameter, and it is required that the SRID being used is declared alongside any piece of spatial information.  SQL Server 2008 will not perform calculations on pieces of spatial information that belong to separate Spatial Reference Systems (because one system may use centimeters, and another may use miles, and SQL Server simply does not have the means to automatically convert units).  For the Geometry type, it is common to just use zero for the SRID when all of your data is from the same Spatial Reference System (Geography uses 4326 as the default, to be explained later).

Note: A linebreak was inserted between the parameters in order to make it obvious to the eye.  There is no requirement in T-SQL that requires the linebreak.

Since we were declaring a LineString specifically, we could have also used a static method that only accepts valid LineStrings as input:

  DECLARE @g Geometry
SET @g = Geometry::STLineFromText('LINESTRING(0 0, 10 10, 21 2)',


If we tried to supply something that was not valid WKT for a LineString, like POINT(0 0),then a .NET FormatException would have been thrown:

Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24142: Expected LINESTRING at position 0. The input has POINT(0 0).
   at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeToken(String token)
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringTaggedText()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ReadLineString()
   at Microsoft.SqlServer.Types.SqlGeometry.STLineFromText(SqlChars lineStringTaggedText, Int32 srid)

The entire list of OGC standard static methods on the Geometry type include: STGeomFromText, STPointFromText, STLineFromText, STPolyFromText, STMPointFromText, STMLineFromText, STMPolyFromText, STGeomCollFromText, STGeomFromWKB, STPointFromWKB, STLineFromWKB, STPolyFromWKB, STMPointFromWKB, STMLineFromWKB, STMPolyFromWKB, and STGeomCollFromWKB.  Note: The "FromWKB" methods are the "Well-Known Binary" equivalents to the "FromText" methods, and accept a specially crafted array of bytes as the input.  Using binary representations for initialization improves performance, but is much harder for humans to work with and comprehend.

There is one other trick to be aware of when initializing a spatial data type.  By contract, a UDT in SQLCLR must support serialization to and from a string.  That is, a UDT must implement a ToString() method and a Parse(string) method that are called implicitly when a conversion is required, but these two methods can also be explicitly invoked.  It just so happens that the string format used by the Geometry type is WKT (actually, the Parse() method is identical to STGeomFromText() with an implicit SRID of zero).

All of the following are functionally equivalent:

  DECLARE @g Geometry

SET @g = Geometry::STGeomFromText('LINESTRING(0 0, 10 10, 21 2)',
0) SET @g = Geometry::Parse('LINESTRING(0 0, 10 10, 21 2)') SET @g = 'LINESTRING(0 0, 10 10, 21 2)'


Note: The third example implicitly invokes the Parse(string) method.


Working with a UDT Instance

Once you have created an instance, then you can use a dot notation to access instance properties and methods:

  DECLARE @g Geometry
SET @g = Geometry::STLineFromText('LINESTRING(0 0, 10 10, 21 2)',
0) PRINT @g.STLength() -- Result: 27.7436


Up to this point, my example code has been declaring and instantiating spatial data as variables within a batch.  But, columns in a table can also be declared as spatial, and queries can access instance properties and methods:

  TABLE #demo
    G     GEOMETRY

VALUES    ('LINESTRING(0 0, 10 10, 21 2)'),
          ('LINESTRING(1 1, 11 11, 22 3)'),
          ('POINT(5 5)')

FROM #demo



1 LINESTRING (0 0, 10 10, 21 2) 27.7436061324664
2 LINESTRING (1 1, 11 11, 22 3) 27.7436061324664
3 POINT (5 5) 0

Instance methods are the exciting part of using the spatial data types in SQL Server 2008.  In the next part of this series, I will cover individual methods in detail.

SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

(next part) SQL Server 2008: Spatial Data, Part 4

SQL Server 2008: Spatial Data, Part 5

SQL Server 2008: Spatial Data, Part 6

SQL Server 2008: Spatial Data, Part 7

SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com