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)
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).
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.
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!