A View Inside My Head

Jason's Random Thoughts of Interest


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

SQL Server 2008: Spatial Data, Part 2

In the first part of this series, I provided a somewhat verbose description of the two different types of Spatial data supported by SQL Server 2008: Geometry (flat Earth model) and Geography (ellipsoidal model).  Now, let's dive in a bit deeper, and take a look at some of the specifics.


From their website, the Open Geospatial Consortium, Inc. (OGC) is described as "a non-profit, international, voluntary consensus standards organization that is leading the development of standards for geospatial and location based services."  Unlike me, these folks are the real experts in the field who understand both the problems and the solutions of working with spatially-aware systems.  By defining standards, they help to ensure that geospatial data can be shared between different vendors and platforms.

SQL Server 2008's Spatial data types are based on the OGC's "Simple Feature Access" standards, which are described in more detail than you would likely ever care to know by two documents in particular:

Note that like any standard, there is no guarantee that everything declared will be implemented - it just tries to guarantee that if something is implemented, that it is done in a way that is compatible with other implementations.  Likewise, there's nothing to stop a vendor from adding their own extensions to an implementation that provide functionality above and beyond what is defined in the standard.

Specific Types of Spatial Data

The Geometry and Geography data types in SQL Server 2008 are actually abstract by themselves.  When you instantiate an object of this type, it becomes one of a number of possible concrete types (with each having its own set of characteristics).  The following diagram (from the SQL Server BOL) displays the inheritance tree of the various concrete types (in blue) and their abstract ancestors (in yellow):

 Spatial Types - Click to Enlarge 

As far as SQL Server is concerned, though, we must define a variable or a column in a table as a Geometry (or Geography) type.  There is no means to be more specific at the time of declaration, and it is the job of the Geometry (or Geography) type itself to be aware of the deriving types like Point and LineString. 

This also means that a column in a table can have a mixture of different geospatial information: one row could contain a single point indicating where you are right now, another row could contain the surface of the state of Ohio, and a third row could contain a line that represents the route between your home and workplace.  We recognize these three things as unique types of geospatial information, but SQL Server thinks of them as the same things. 

Note: If you are not familiar with the concepts of inheritance and polymorphism from Object Oriented Programming, then consider this to be the same as how humans are able to generalize a dog and a cat both as being an animal.

POINT: A Point is an exact location, and is defined in terms of an X and Y pair of coordinates, as well as optionally by a Z (elevation) and M (measure) coordinate.  It does not have a length or any area associated with it.  Points are used as the fundamental building blocks of more complex spatial types.  Note: Z and M are recognized and maintained by SQL Server 2008 if supplied, but are not used in any calculations).

LINESTRING: A line segment is the shortest path between two points.  A LineString, then, is defined as the path between a sequence of points (i.e., a series of connected line segments).  It is considered simple if it does not cross over itself, and is considered a ring if the starting point is the same as the ending point.  A LineString is always considered to be a one dimensional object; it has length, but does not have area (even if it is a ring).

POLYGON: A Polygon is a closed two-dimensional shape defined by a ring.  It has both length and area.  A Polygon may also have holes in its interior (a hole is defined by another Polygon).  Area within a hole is considered to be exterior to the Polygon itself.

COLLECTIONS: In addition to the single instance types (Point, LineString, and Polygon), there is also a type that can hold a collection of instances.  This is similar to a list or an array in most programming languages.  The most generic type of collection is the GeomCollection, whose members can be of any type.  Deriving from GeomCollection are MultiPolygon, MultiLineString, and MultiPoint.  As their names suggest, the members of these collection types must be of the single instance type (i.e., MultiPoint can only contain Points, etc).

Representing Spatial Data

The OGC defines different ways to represent geospatial information as bytes of data that can then be interpreted by the Geometry or Geography types as being Points, Linestrings, etc.  SQL Server 2008 supports three such formats: Well-Known Text (WKT), Well-Known Binary (WKB), and Geography Markup Language (GML). 

For this series of articles, I will primarily be using WKT in my examples since is both concise and [relatively] human-readable.

The syntax of WKT is not difficult to understand, and can usually be inferred simply by looking at some examples:

POINT(10 10)
POINT(10 10 10 1) // X Y Z M shown here LINESTRING(0 0, 10 10) POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))

People with backgrounds in mathematics may be compelled to put commas between each coordinate, but in WKT, a space is used to separate the coordinates of a point, and a comma is used to separate points within a more complex type.  Parentheses are used to group points into a single shape. 

As a more complex example, here is how you would represent the above Polygon with a triangle-shaped hole in it:

POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 4 5, 4 1, 1 1))

The first group of points defines the Polygon's ring, while the second set defines the hole within the Polygon's interior.

Though, as the old saying goes, a picture is worth a thousand words.  Therefore, it is often easier to plot out geospatial information for human consumption, so our Polygon would look something like this:

POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 4 5, 4 1, 1 1))

(Note: In this particular visualization, the point (0 0) is considered to be the upper-left corner, and the units increase down and to the right)

SQL Server 2008: Spatial Data, Part 1

(next part) 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

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 1

Beyond Relational

The introduction of SQLCLR in SQL Server 2005 allowed for very rich User Defined Types to be utilized.  This meant that a developer could create a single object that contained multiple data points (properties) and could also perform calculations internally (methods), yet store that instance in a single field of a single row in a database table.  Suddenly, any type of data could be stored and queried in the database, instead of just strings and numbers.

Fast forward to the present time, as Microsoft is celebrating the launch of SQL Server 2008 (notice that in the brilliance that is known as Marketing Speak, the term "Launch" does not mean "Release", so the product is still under development).  We're seeing the realization of the SQLCLR UDT concept being branded as being "Beyond Relational".  In this upcoming release, developers will have access to two new spatial data types that are included with the product: Geometry and Geography.


fixpic1Think back to High School.  No, I'm not talking about those painful memories of being on the receiving end of wedgies, or the awkwardness and loss of words that came over you when you were around members of the opposite gender.  I'm referring to Mr. Bollenbacher's Geometry class, where you learned about all sorts of useless things that you would never again need to use...

High School Geometry class, for the most part, involved working in a single plane.  A good part of the curriculum taught us mathematical proofs, and constructions using a compass and straight-edge.  But, we also pulled out the graph paper at times, and plotted points using a Cartesian Coordinate System, then connected two points points to form a line, and then connected  lines to create closed shapes, or polygons. 

The Geometry spatial data type in SQL Server 2008 is used to represent information in a uniform 2-dimensional plane, much like the graph paper analogy from High School.  The units are completely user-defined, and could be inches, miles, pixels, or even picas.  The point is that SQL Server doesn't care; it can perform calculations just the same. 

You would use the Geometry data type in conjunction with some existing planar projection of the real world that becomes the underlying reference.  That is, you would usually start with an existing diagram or a map, and define your spatial data in terms of that base map.

For example, perhaps your user-defined coordinate space is being used to represent a warehouse facility.  Within that coordinate space, you can use the Geometry data type to define polygons that represent storage bays within the warehouse.  Data in your tables can track where inventory is placed using these polygons.  Then, it becomes possible to do things with the data, like determine where the closest item of a certain type is to a forklift driver's present location.

The most important thing to keep in mind about the Geometry data type is that it works with uniform units of data.  The number of units across the top of the plane is the same as the number of units across the bottom, and the same applies to the left and right sides.


Perhaps you have not yet heard, but the Earth is actually not flat.  It's more like a ball.  Take a minute to absorb that information... I'll wait.

Since the Earth is not flat, it is challenging to represent real-world locations using 2-dimensional mathematics.  Sure, we're used to looking at flat maps of the world, whether it's that road map folded up in your car, or the rolled-up atlas that hung over the chalk board in school, or even a Google map displayed on your computer screen.  But, those are merely projections of the real world onto a plane. 

There are multiple ways to create such projections using mathematical transformations, with each type having benefits and disadvantages over other types of projections.  For instance, one type of projection might attempt to minimize the distortion for a particular area of the globe, while other areas towards the outer edges of the projection become very distorted.  Or, a projection might make it easier to represent points on the globe in a uniform fashion, but areas near the North and South poles (like Greenland and Antarctica) appear much larger in area than they actually are.

B_S_A_M ApianusII EquidistantConic

For this reason, representing geospatial information using the Geometry data type is not practical, especially if you wish to share information with other people who might be using a different projection.  Fortunately, we are able to use a Geographic Coordinate System to accurately represent locations on Earth, or any ellipsoidal body for that matter.

The SQL Server Geography data type uses Latitude and Longitude angles to identify points on the Earth.  Latitude measures how far North (or South) of the Equator a point is, while Longitude measures how far East (or West) of a Prime Meridian a point is.  Note that this coordinate system can be used to identify points on any sized "ball", be it a golf ball, the Earth, or even the Sun.

Spatial Reference System

Latitude and Longitude are thought of as being absolute values that are universally accepted, but this is not the case.  For instance, the Prime Meridians, or zero degrees Longitude, is quite arbitrary in nature.  It is commonly referred to as being a North-South line that passes through Greenwich, London, England, but in truth, there are four different Prime Meridian that have been used historically that actually pass through Greenwich.  Even the current Prime Meridian that is used by the GPS system (and most Internet mapping sites) is actually 102.5 meters to the East of the "official" line that marks the otherwise universally accepted Prime Meridian. 

Once you know the exact starting points of the coordinate system that a set of data uses, we're left with another dilemma when we try to calculate the distance between two points.  Because of the action of the Earth spinning on its axis, it is not actually spherical in shape, but rather ellipsoidal (it's fatter in the middle than at the poles).  That complicates the ability to accurately calculate distance and area in a uniform manner, but doesn't make it impossible.

To measure distance between two points on the globe, we need to know the radius of the Earth (i.e., how far from the center of the Earth that each point is).  The problem is that because of the land masses, the Earth doesn't have just one uniform radius that can be applied globally.  If you measure the distance between two points that are one degree apart at sea level, you will get a different result than two points that are one degree apart in the mountains, which might be 5000 feet above sea level.

So, when a location is surveyed and points are identified in terms of global coordinates, these are based on a model of the earth that is quite accurate for that localized area, but maybe not as accurate elsewhere.  The parameters that define the particular model of the Earth is known as the Spatial Reference System.  It is important to know which Spatial Reference System is used for a set of data, because you cannot always mix data from one set with another and get accurate results.

With the pervasiveness of GPS data, I would argue that the most commonly used Spatial Reference System today is WGS84

From Here

This first article was to establish a baseline of knowledge about some of the spatial concepts.  Going forward from here, I will describe the standards that have been implemented in the creation of the Geometry and Geography data types, as well as how to actually use the data types programmatically.

(next part) 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

SQL Server 2008: Spatial Data, Part 7

SQL Server 2008: Spatial Data, Part 8

kick it on DotNetKicks.com


This past Friday, I ended my 20+ year dependency on wearing glasses to see.  And while there's a lot of healing that remains as I write this, my vision is already better than with my old glasses.

Find a Facility and Get Evaluated

I started my adventure by selecting a LASIK center near me.  I was not necessarily interested in the lowest cost provider, but since I was using my Flexible Spending Account to pay for the procedure, I had a hard limit as to what I could afford.  The doctor that owns this particular business is from the Cleveland area, and comes to Toledo once a week to perform the procedure.  He comes well recommended, and has an impressive resume of experience.

The next step was to attend a free consultation.  Not everyone is a candidate for LASIK surgery, so pre-screening is required.  Aside from needing corneas that are thick enough to be reshaped, you also need to have a stable eye prescription.  There's no sense in performing surgery if your eyes are just going to change in a year or two! 

As you could have guessed, I "passed" this pre-screening, and was invited back for an official pre-operative examination (and to make a lump sum payment that covers the procedure itself, as well as all pre- and post-operative exams).  But first, I also had to decide what technologies I wanted to use.


You see, LASIK is sometimes referred to in endearing terms as the "Flap and Zap".  The surgeon cuts a thin flap of your outer cornea that resembles a contact lens, only it remains attached at the top.  The laser then reshapes the newly exposed layer of the cornea to correct your vision, and the flap is replaced.  By preserving your original outer cornea, the healing is extremely rapid, and the resulting vision is supposedly better than other types of refractive procedures.

 lasik1lasik2 lasik3

There are two ways that this flap can be created: by means of a specialized blade device, known as a microkeratome, or by a special type of laser.  As you can imagine, there's a price difference between the two.

Despite being used successfully millions of times since the inception of LASIK, the thought of the microkeratome scared the hell out of me.  With this, a contraption is placed precisely over your eye, and then an oscillating blade passes over the cornea, making a cut that results in a flap that is about the thickness of two human hairs. 


The more attractive (and more expensive, by about $500 per eye) method uses a femtosecond laser that scans across the flap area.  The beam is focused to just under the cornea, and is only active for one billionth of one millionth of a second per pulse.  Each pulse vaporizes a tiny portion of the cornea at the point of focus, causing a tiny bubble to form.  These bubbles start joining with adjacent bubbles, much like soap bubbles in the sink, until there is just one big bubble remaining.  In the end, the flap is freed from the cornea by lasering the outer ring.  Because IntraLase is the company that manufactures the FDA approved "laser microkeratome", this procedure is referred to as IntraLase.


The other pricing decision involved selecting either standard LASIK or custom wavefront LASIK.  Eyeglasses and contact lenses are able to correct certain vision problems, known as higher order aberrations, and they do this in a standard way.  People with the same prescription can actually share each other's spectacles and are then able to see just as well as when they wear their own.  With standard LASIK, the "generic" correction is made to your eyes, and the results would be the same as if you were wearing your glasses or contacts.

In my case, I had myopia (near-sighted) with astigmatism.  "Normal" people would see something like the flowers on the left, but I would see the blurry/doubled flowers on the right (only more blurry than these pictures):

2020 astig

However, there are other subtle deformities known as lower order aberrations that can make two people with the same prescription have different sight problems that are generally left uncorrected by glasses.  Custom wavefront LASIK takes a personalized mapping of your cornea, and as the name suggests, creates a custom reshaping just for you.  Again, it adds about $500 per eye over the standard (because the equipment manufacturer charges the facility more to use the custom mode, and this cost is simply passed onto the consumer).

In my case, I was able to afford the IntraLase and the custom wavefront, so that it what I ultimately decided on.


My pre-op exam was on Monday, March 3rd.  I knew that I would have to have my eyes dilated, so I took the day off of work.

When I first arrived, I was taken back to an examination room where my current prescription was determined ("better 1, 2"  "3, 4", etc).  It turned out that my prescription had changed in the past 7 years, and I was only seeing at about 20/40 with my glasses.  Who knew?!?!

Next, I was taken across the hallway and all of the same cornea measurements that were take during the consultation were repeated.  The computers produced identical results as before, so they had good numbers to program into the laser.

After that, it was time to watch a video to inform me of risks, sign paperwork, and wait for the eyedrops to work their magic and cause my irises to disappear.

When my eyes were fully dilated, I went into a different examination room and given a full optical exam by an ophthalmologist.  This involved repeating the "Better 1,2" test to ensure that her numbers matched the previous person's results, as well as an examination of my retina and whatever else they do as part of a full workup.

Then, it was done.  I was given a prescription for Valium and Zymar eye drops, as well as some eyelid cleansing pads (I had to disinfect my eyelids every evening leading up to the surgery), and sent home to sleep!  Awesome!

Operation Day!

I arrived on Friday, March 7 wearing a Red Gate T-Shirt (the alternative would have been a Developer Express shirt, but are you really interested in my choice of apparel?).  Right away, I was instructed to take the two Valium pills that I brought with me.  Then, I was taken back to a dimly-lighted waiting room with Lay-z-boy chairs and Men In Black II playing on a large television. 

My eyelids were sterilized with betadine, and surgical booties were placed over my shoes.  I also got an awesome hair net, and was not permitted to put my glasses back on.  So, there I sat, listening to the movie - because there's no way that I could see it. 

The operating room was separated from this waiting room by means of sliding glass doors.  They had a television placed facing the waiting room, presumably so that loved ones could watch the procedure taking place from the laser tracker point of view.  Despite my blindness, I could tell what was happening on this TV, and watched as the person ahead of me had the procedure done.  I had never taken Valium before, but my butterflies never went away.  I wonder how bad they would have been without the sedative!jQuery15206081989678969336_1341772668724!

Before I knew it, it was my turn!  I was led into the room, numbing drops were placed in my eyes, and then I was positioned on a bed (table?).  The IntraLase device was on my left, and the excimer laser was on my right, and by swinging the bed, my eyes were able to be placed under the appropriate devices.  I was handed a teddy bear with surgical scrubs (and a face mask) to hold onto.  How cute!

The surgeon covered my left eye with a pad of some sort, and then placed a ring over my right eye.  This device used suction to adhere to the whites of my eye, and served to hold open my eyelids and keep my eye from moving.  The pressure caused by the suction made my vision fade out, so I couldn't see anything.  Before I knew it, the laser tech was counting down how many seconds were left before the flap was complete.  Then they covered the right eye and repeated the procedure on the left.

With the flaps being cut, the suction devices were removed, and I was positioned under the excimer laser.  The surgeon carefully retracted the flaps, and I was staring up at a blurry orange light with a ring of LEDs surrounding it.  He taped my eyelids open, but essentially, I could look around if I wanted to (the computer scans your eye hundreds of times a second, and can track the laser precisely - still, I focused my gaze on the centermost blurry light).  There was zapping sounds as the laser started to vaporize my cornea in order to reshape it.  There was a smell, as you can imagine, and it reminded me of ozone and burning hair combined.  Again the laser technician was counting down the seconds until the laser was complete, and in the last 3 seconds, that blurry light that I had been staring at came into focus!

After both eyes were complete, the doctor placed what seemed to be 20 drops from a handful of different bottles into my eyes.  Then, I sat up.  The clock on the wall, which I could see, revealed that the entire procedure on both eyes took a little over 10 minutes.

My last stop was in the ophthalmologist office.  The eyechart revealed that I was about 20/80 or so with my brand new eyes.  The surgeon used the equipment to examine the positioning of the flaps, and said that everything turned out great.  The ophthalmologist gave me an assortment of drops and eye shields and sent me on my way to go home and sleep.


bugeyes At home, I took Tylenol every 4 hours for the discomfort (which would probably have been quite tolerable without taking anything, but I didn't want to chance it).  I also had to put medicated eyedrops in my eyes every 2 hours, and wear the eyeshields (and sunglasses, by my choosing) for a few days to keep me from accidentally rubbing my eyes.  I remained amazed at how quickly my vision sharpened up, and by Friday night, I was watching TV in my darkened bedroom.

On Saturday morning, while everyone else in Ohio was getting blasted by a blizzard, I drove myself in to the center so that the ophthalmologist could do a follow-up exam.  Everything was healing nicely, and my vision was 20/25 (!!!!). 

I continued to gaze at my surroundings, amazed by just how clear everything is (and getting crisper every day, it seems).  There are periods of time when my vision will blur up for a little bit, but this is chiefly attributed to the flap not being a perfect fit yet for the resurfaced cornea.  I have about another 1-3 months until everything is completely healed, and can expect my vision to get worse at times.  The LASIK center offers a lifetime guarantee, and will perform another touch-up surgery if my vision degrades beyond 20/40.


I can see clearly now, the rain is gone,
I can see all obstacles in my way
Gone are the dark clouds that had me blind
It's gonna be a bright (bright), bright (bright)
Sun-Shiny day

-Bob Marley   (correction) -Johnny Nash