# New Spatial Features in SQL Server 2012

SQL Server 2012 adds many significant improvements to the spatial support that was first introduced with SQL Server 2008. Among the more notable enhancements is support for curves (arcs), where SQL Server 2008 only supported straight lines, or polygons composed of straight lines. Microsoft also provides methods that test for non-2012-compatible (curved) shapes, and convert circular data to line data for backward compatibility with SQL Server 2008 (as well as other mapping platforms that don’t support curves).

### New Spatial Data Classes

The three new spatial data classes in SQL Server 2012 are:

- Circular strings
- Compound curves
- Curve polygons

All three of these shapes are supported in WKT, WKB, and GML by both the *geometry* and *geography* data types, and all of the existing methods work on all of the new circular shapes. My previous post, Geospatial Support for Circular Data in SQL Server 2012 covers these new spatial classes in detail, and shows you how to use them to create circular data. This post focuses on additional spatial features that are new in SQL Server 2012.

### New Spatial Methods

Let’s explore a few of the new spatial methods. Some of these new methods complement the new curved shapes, while others add new spatial features that work with all shapes.

### The *STNumCurves* and *STCurveN* Methods

These two methods can be invoked on any *geometry* or *geography* instance. They can be used together to discover information about the curves contained within the spatial instance. The *STNumCurves* method returns the total number of curves in the instance. You can then pass any number between 1 and what *STNumCurves* returns to extract each individual curve, and thus iterate all the curves in the instance.

For example, the WKT string *CIRCULARSTRING(0 4, 4 0, 8 4, 4 8, 0 4)* defines a perfect circle composed of two connected segments; 0 4, 4 0, 8, 4 and 8 4, 4 8, 0 4 (the third coordinate 8 4 is used both as the ending point of the first arc and the starting point of the second arc. The following code demonstrates how to obtain curve information from this circular string using the *STNumCurves* and *STCurveN* methods.

-- Create a full circle shape (two connected semi-circles) DECLARE @C geometry = 'CIRCULARSTRING(0 4, 4 0, 8 4, 4 8, 0 4)' -- Get the curve count (2) and the 1st curve (bottom semi-circle) SELECT CurveCount = @C.STNumCurves(), SecondCurve = @C.STCurveN(2), SecondCurveWKT = @C.STCurveN(2).ToString()

This query produces the following output:

CurveCount SecondCurve SecondCurveWKT ---------- -------------------------------- ------------------------------ 2 0x000000000204030000000000000... CIRCULARSTRING (8 4, 4 8, 0 4)

You can see that *STNumCurves* indicates there are two curves, and that *STCurveN(2)* returns the second curve. If you view the results in the spatial viewer, you’ll see just the top half of the circle. This is the semi-circle defined by the second curve, which is converted back to WKT as *CIRCULARSTRING (8 4, 4 8, 0 4)*. Notice that this represents the second segment of the full circle.

### The *BufferWithCurves *Method

SQL Server 2008 introduced the *STBuffer* method which “pads” a line, effectively converting it into a polygon. If you look closely at the resulting polygon shapes in the spatial viewer, it appears that the points of each line string (including the mid points) are transformed into rounded edges in the polygon. However, the rounded edge look is actually produced by plotting many short straight lines that are clustered very closely together, presenting the illusion of a curve. This approach is necessary since curves were not previously supported before SQL Server 2012 (but the *STBuffer* method was).

Clearly, using native curve definitions in a curve polygon is more efficient than clustering a multitude of straight lines in an ordinary polygon. For backward compatibility, *STBuffer* continues to return the (inefficient) polygon as before. So SQL Server 2012 introduces a new method, *BufferWithCurves*, for this purpose. The following code uses *BufferWithCurves* to pad lines using true curves, and compares the result with its straight-line cousin, *STBuffer*.

DECLARE @streets geometry = ' GEOMETRYCOLLECTION( LINESTRING (100 -100, 20 -180, 180 -180), LINESTRING (300 -300, 300 -150, 50 -50) )' SELECT @streets.BufferWithCurves(10) SELECT AsWKT = @streets.ToString(), Bytes = DATALENGTH(@streets), Points = @streets.STNumPoints() UNION ALL SELECT @streets.STBuffer(10).ToString(), DATALENGTH(@streets.STBuffer(10)), @streets.STBuffer(10).STNumPoints() UNION ALL SELECT @streets.BufferWithCurves(10).ToString(), DATALENGTH(@streets.BufferWithCurves(10)), @streets.BufferWithCurves(10).STNumPoints()

Here is the resulting shape returned by the first *SELECT* statement (the collection of padded line shapes generated by *BufferWithCurves*):

As with *STBuffer*, the new shapes have rounded edges around the points of the original line strings. However, *BufferWithCurves* generates actual curves, and thus, produces a significantly smaller and simpler polygon. The second *SELECT* statement demonstrates by comparing the three shapes—the original line string collection, the polygon returned by *STBuffer*, and the curve polygon returned by *BufferWithCurves*. Here are the results:

AsWKT Bytes Points ----------------------------------------------------------- ----- ------ GEOMETRYCOLLECTION (LINESTRING (100 -100, 20 -180, 180 -... 151 6 MULTIPOLYGON (((20.000000000000796 -189.99999999999858, ... 5207 322 GEOMETRYCOLLECTION (CURVEPOLYGON (COMPOUNDCURVE ((20.000... 693 38

The first shape is the original geometry collection of line strings used for input, which requires only 151 bytes of storage, and has only 6 points. For the second shape, *STBuffer* pads the line strings to produce a multi-polygon (a set of polygons) that consumes 5,207 bytes and has a total of 322 points—a whopping 3,448 percent increase from the original line strings. In the third shape, *BufferWithCurves* is used to produce the equivalent padding using a collection of curve polygons composed of compound curves, so it consumes only 693 bytes and has only 38 points—a (relatively) mere 458 percent increase from the original line strings.

### The *ShortestLineTo *Method

This new method examines any two shapes and figures out the shortest line between them. The following code demonstrates:

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -26, 14 -28, 20 -40, -20 -30))' DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))' SELECT @Shape1 UNION ALL SELECT @Shape2 UNION ALL SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)

This code defines two polygons and then uses *ShortestLineTo* to determine, generate, and return the shortest straight line that connects them. *STBuffer* is also used to pad the line string so that it is more clearly visible in the spatial viewer:

### The *MinDbCompatibilityLevel* Method

With the added support for curves in SQL Server 2012 comes support for backward compatibility with previous versions of SQL Server (2008 and 2008 R2) that don’t support curves. The new *MinDbCompatibilityLevel* method accepts any WKT string and returns the minimum version of SQL Server required to support the shape defined by that string. For example, consider the following code:

DECLARE @Shape1 geometry = 'CIRCULARSTRING(0 50, 90 50, 180 50)' DECLARE @Shape2 geometry = 'LINESTRING (0 50, 90 50, 180 50)' SELECT Shape1MinVersion = @Shape1.MinDbCompatibilityLevel(), Shape2MinVersion = @Shape2.MinDbCompatibilityLevel()

The *MinDbCompatibilityLevel* method returns 110 (referring to version 11.0) for the first WKT string and 100 (version 10.0) for the second one. This is because the first WKT string contains a circular string, which requires SQL Server 2012 (version 11.0), while the line string in the second WKT string is supported by SQL Server 2008 (version 10.0) and higher.

### The *STCurveToLine* and *CurveToLineWithTolerance* Methods

These are two methods you can use to convert curves to roughly equivalent straight line shapes. Again, this is to provide compatibility with previous versions of SQL Server and other mapping platforms that don’t support curves.

The *STCurveToLine* method converts a single curve to a line string with a multitude of segments and points that best approximate the original curve. The technique is similar to what we just discussed for *STBuffer*, where many short straight lines are connected in a cluster of points to simulate a curve. And, as explained in that discussion, the resulting line string requires significantly more storage than the original curve. To offer a compromise between fidelity and storage, the *CurveToLineWithTolerance* method accepts “tolerance” parameters to produce line strings that consume less storage space than those produced by *STCurveToLine*. The following code demonstrates by using both methods to convert the same circle shape from the previous *STNumCurves* and *STCurveN* example into line strings.

-- Create a full circle shape (two connected semi-circles) DECLARE @C geometry = 'CIRCULARSTRING(0 4, 4 0, 8 4, 4 8, 0 4)' -- Render as curved shape SELECT Shape = @C, ShapeWKT = @C.ToString(), ShapeLen = DATALENGTH(@C), Points = @C.STNumPoints() -- Convert to lines (much larger, many more points) SELECT Shape = @C.STCurveToLine(), ShapeWKT = @C.STCurveToLine().ToString(), ShapeLen = DATALENGTH(@C.STCurveToLine()), Points = @C.STCurveToLine().STNumPoints() -- Convert to lines with tolerance (much smaller, much fewer points) SELECT Shape = @C.CurveToLineWithTolerance(0.1, 0), ShapeWKT = @C.CurveToLineWithTolerance(0.1, 0).ToString(), ShapeLen = DATALENGTH(@C.CurveToLineWithTolerance(0.1, 0)), Points = @C.CurveToLineWithTolerance(0.1, 0).STNumPoints()

The query results show that the original circle consumes only 112 bytes and has 5 points. Invoking *STCurveToLine* on the circle converts it into a line string that consumes 1,072 bytes and has 65 points. That’s a big increase, but the resulting line string represents the original circle in high fidelity; you will not see a perceptible difference in the two when viewing them using the spatial viewer. However, the line string produced by *CurveToLineWithTolerance* consumes only 304 bytes and has only 17 points; a significantly smaller footprint, paid for with a noticeable loss in fidelity. As shown by the spatial viewer results below, using *CurveToLineWithTolerance* produces a circle made up of visibly straight line segments:

### The *STIsValid*, *IsValidDetailed* and *MakeValid* Methods

Spatial instance validation has improved greatly in SQL Server 2012. The *STIsValid* method evaluates a spatial instance and returns a 1 (for true) or 0 (for false) indicating if the instance represents a valid shape (or shapes). If the instance is invalid, the new *IsValidDetailed* method will return a string explaining the reason why. The following code demonstrates.

DECLARE @line geometry = 'LINESTRING(1 1, 2 2, 3 2, 2 2)' SELECT IsValid = @line.STIsValid(), Details = @line.IsValidDetailed()

This line string is invalid because the same point (2 2) is repeated, which results in “overlapping edges,” as revealed by the output from *IsValidDetailed*:

IsValid Details ------- ----------------------------------------------------------------- 0 24413: Not valid because of two overlapping edges in curve (1).

SQL Server 2012 is more tolerant of invalid spatial instances than previous versions. For example, you can now perform metric operations (such as *STLength*) on invalid instances, although you still won’t be able to perform other operations (such as *STBuffer*) on them.

The new *MakeValid* method can “fix” an invalid spatial instance and make it valid. Of course, the shape will shift slightly, and there are no guarantees on the accuracy or precision of the changes made. The code in Listing 10-27 uses *MakeValid* to remove overlapping parts (which can be caused by anomalies such as inaccurate GPS traces), effectively converting the invalid line string into a valid spatial instance.

DECLARE @line geometry = 'LINESTRING(1 1, 2 2, 3 2, 2 2)' SELECT @line.MakeValid().ToString() AS Fixed

The WKT string returned by the *SELECT* statement shows the “fixed” line string:

Fixed ---------------------------------------------------------------- LINESTRING (3 2, 2 2, 1.0000000000000071 1.0000000000000036)

## Other Enhancements

The remainder of this post gives brief mention to several other noteworthy spatial enhancements added in SQL Server 2012. These include better *geography* support, and precision and optimization improvements.

### Support for *geography* Instances Exceeding a Logical Hemisphere

Previous versions of SQL Server supported *geography* objects as large as (slightly less than) a logical hemisphere (half the globe). This limitation has been removed in SQL Server 2012, which now supports *geography* instances of any size (even the entire planet).

When you define a *geography* polygon, the order in which you specify the ring’s latitude and longitude coordinates (known as *vertex order*) is significant (unlike *geometry*, where vertex order is insignificant). The coordinate points are always defined according to the *left-foot inside* rule; when you “walk” the boundary of the polygon, your left foot is on the inside. Thus, vertex order determines whether you are defining a small piece of the globe, relative to the larger piece defined by the entire globe *except* for the small piece (that is, the rest of the globe).

Since previous versions of SQL Server were limited to half the globe, it was impossible to specify the points of a polygon in the “wrong order,” simply because doing so resulted in too large a shape (and thus, raised an error). That error potential no longer exists in SQL Server 2012, so it’s even more critical to make sure your vertex order is correct, or you’ll be unwittingly working with the exact “opposite” shape.

If you have a *geography* instance that is known have the wrong vertex order, you can repair it using the new *ReorientObject* method. This method operates only on polygons (it has no effect on points, line strings, or curves), and can be used to correct the ring orientation (vertex order) of the polygon. The following code demonstrates.

-- Small (less than a logical hemisphere) polygon SELECT geography::Parse( 'POLYGON((-10 -10, 10 -10, 10 10, -10 10, -10 -10))') -- Reorder in the opposite direction for "rest of the globe" SELECT geography::Parse( 'POLYGON((-10 -10, -10 10, 10 10, 10 -10, -10 -10))') -- Reorient back to the small polygon SELECT geography::Parse( 'POLYGON((-10 -10, -10 10, 10 10, 10 -10, -10 -10))').ReorientObject()

Three *geography* polygon instances are defined in this code. The first *geography* instance defines a very small polygon. The second instance uses the exact same coordinates, but because the vertex order reversed, it defines an enormous polygon whose area represents the entire globe *except* for the small polygon. As explained, such a definition would cause an error in previous versions of SQL Server, but is now accommodated without a problem by SQL Server 2012. The third instance reverses the vertex order on the same shape as the second instance, thereby producing the same small polygon as the first instance.

### Full Globe Support

Along with the aforementioned support for *geography* instances to exceed a single logical hemisphere comes a new spatial data class called *FULLGLOBE*. As you may have guessed, this is a shape that represents the entire planet. If you’ve ever wondered how many square meters there are in the entire world, the following query gives you the answer (which is 510,065,621,710,996 square meters, so you can stop wondering).

-- Construct a new FullGlobe object (a WGS84 ellipsoid) DECLARE @Earth geography = 'FULLGLOBE' -- Calculate the area of the earth SELECT PlanetArea = @Earth.STArea()

All of the common spatial methods work as expected on a full globe object. So you could, for example, “cut away” at the globe by invoking the *STDifference* and *STSymDifference* method against it using other polygons as cookie-cutter shapes.

### New “Unit Sphere” Spatial Reference ID

The default spatial reference ID (SRID) in SQL Server 2012 is 4326, which uses the metric system as its unit of measurement. This SRID also represents the true ellipsoidal sphere shape of the earth. While this representation is most accurate, it’s also more complex to calculate precise ellipsoidal mathematics. SQL Server 2012 offers a compromise in speed and accuracy, by adding a new spatial reference id (SRID), 104001, which uses a sphere of radius 1 to represent a *perfectly* round earth.

You can create *geography* instances with SRID 104001 when you don’t require the greatest accuracy. The *STDistance*, *STLength*, and *ShortestLineTo* methods are optimized to run faster on the unit sphere, since it takes a relatively simple formula to compute measures against a perfectly round sphere (compared to an ellipsoidal sphere).

### Better Precision

Internal spatial calculations in SQL Server 2012 are now performed with 48 bits of precision, compared to 27 bits used in SQL Server 2008 and SQL Server 2008 R2. This can reduce the error caused by rounding of floating point coordinates for original vertex points by the internal computation.

### Summary

This blog post introduced you to some of the powerful new spatial capabilities added to SQL Server 2012. You saw how to use *STNumCurves* and *STCurveN* to obtain curve information from circular data, the *BufferWithCurves* method to produce more efficient padded line shapes than *STBuffer*, and the *ShortestLineTo* method to figure out the shortest distance between two shapes. Then you saw how to use the new *MinDbCompatibilityLevel*, *STCurveToLine*, and *CurveToLineWithTolerance* methods for supporting backward compatibility with SQL Server 2008. You also learned how SQL Server 2012 is much better at handling invalid spatial data, using the *STIsValid*, *IsValidDetailed*, and *MakeValid* methods. Finally, you learned about the new full globe support, unit sphere SRID, and improved precision.

You can learn much more about spatial functionality in Tallan’s new book Programming Microsoft SQL Server 2012, which has an entire chapter dedicated to the topic. I hope you get to enjoy these powerful new spatial capabilities in SQL Server 2012!

## No comments