Editing: Potential Side-Effects of Improved Spatial Precision in SQL Server 2012 and the SQL Azure September (Q2) 2011 Service Release

Title
SQL Server Technical Note Writer: Ed Katibah (Microsoft) Technical Reviewers: Michael Kallay (Microsoft), Milan Stojic (Microsoft) and Michael Rys (Microsoft) Published: November 2011 Applies to: SQL Server Code-Named “Denali”, SQL Server 2012, SQL Azure Introduction In Microsoft SQL Server 2012 (previously known as SQL Server Code-Name “Denali”), all constructions and relations are now done with 48 bits of precision, compared to 27 bits used in SQL Server 2008 and SQL Server 2008 R2. This can result in differences that range from how individual coordinates (vertices) in spatial objects appear (rounding) to how computational results are produced in different versions of the database server for certain spatial operations. Recently, SQL Azure has been upgraded in all data centers to incorporate the new SQL Server 2012 spatial library. This upgrade, known as the September 2011 Service Release, thus enables spatial operations in SQL Azure to be the computationally identical with SQL Server 2012.  Because of this new feature, results from spatial operations in SQL Azure September Refresh and SQL Server 2012 can differ from results produced in their respective prior versions. For most if not all cases, these changes will not be noticed and will not materially affect output results. There are, however, potential cases where this might matter.  <h1 style="margin: 24pt 0in 0pt;"><a name="Coordinate_Rounding"></a>Coordinate Rounding</h1> One side-effect of the precision change is that rounding of floating point coordinates for original vertices (points) not used in internal computation is minimized, in many cases not occurring at all. For example, consider the following vertex coordinates, which were processed using the STUnion() method in SQL Server 2008 but which were not involved in computation of the resulting geometry. Vertex Coordinates                         Vertex Coordinates After Computation 82.339026 29.661245  <img alt="" width="34" height="11" style="border-width: 0px; border-style: solid;" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/20x0/__key/communityserver-wikis-components-files/00-00-00-00-05/8032.Right-Arrow.png" />      82.339025999885052 29.662144999951124 In SQL Server 2012, the greater numerical precision assists in the preservation of original coordinates of input vertices, in most cases. Here is the result of the same STUnion() method in SQL Server 2012. Vertex Coordinates                         Vertex Coordinates After Computation 82.339026 29.661245  <img alt="" width="34" height="11" style="border-width: 0px; border-style: solid;" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/20x0/__key/communityserver-wikis-components-files/00-00-00-00-05/8032.Right-Arrow.png" />      82.339026 29.662145 <h1 style="margin: 24pt 0in 0pt;"><a name="Measurement_Values"></a>Measurement Values </h1> Length and area measurement value can vary between server versions. In the first case, length measurements for a series of highway routes in the United States, ranging from short to long, are illustrated. The results are shown in Table 1. <table width="583" style="border: currentcolor; width: 583px; height: 125px; border-collapse: collapse;" border="1" cellspacing="0" cellpadding="0"> <tbody> <tr> <td valign="top" style="background: gray; padding: 0in 5.4pt; border: 1pt solid windowtext; width: 72.9pt;"> Route                              </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in;"> SQL Server 2008 R2 </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in;"> SQL Server 2012 </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 67.5pt;"> Difference </td> </tr> <tr> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 72.9pt;">   </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in;"> Length in  Meters </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in;"> Length in Meters </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 67.5pt;"> Meters </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 72.9pt; background-color: transparent;"> “short segment” </td> <td valign="bottom" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in; background-color: transparent;"> 19.1505724768993 </td> <td valign="bottom" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in; background-color: transparent;"> 19.1505724768993 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 67.5pt; background-color: transparent;"> 0 </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 72.9pt; background-color: transparent;"> I-5 </td> <td valign="bottom" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in; background-color: transparent;"> 2234410.6503514800000 </td> <td valign="bottom" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in; background-color: transparent;"> 2234410.6503514700000 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 67.5pt; background-color: transparent;"> 0.00000001 </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 72.9pt; background-color: transparent;"> I-80 </td> <td valign="bottom" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in; background-color: transparent;"> 4688453.0050976100000 </td> <td valign="bottom" style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.75in; background-color: transparent;"> 4688453.0050976100000 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 67.5pt; background-color: transparent;"> 0 </td> </tr> </tbody> </table> Table 1. Length measurements for several routes (lines) illustrating differences between server versions, using the STLength() method. In the next case, area measurements for U.S. states, ranging from small to large, are illustrated. The results are shown in Table 2. <table width="585" style="border: currentcolor; width: 585px; height: 167px; border-collapse: collapse;" border="1" cellspacing="0" cellpadding="0"> <tbody> <tr> <td valign="top" style="background: gray; padding: 0in 5.4pt; border: 1pt solid windowtext; width: 68.95pt;"> State                             </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt;"> SQL Server 2008 R2 </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt;"> SQL Server 2012 </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt;"> Difference </td> </tr> <tr> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 68.95pt;">   </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt;"> Area in Sq. Meters </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt;"> Area in Sq. Meters </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt;"> Sq. Meters </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 68.95pt; background-color: transparent;"> Rhode Island </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt; background-color: transparent;"> 2838236251.32462 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt; background-color: transparent;"> 2838236251.32471 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt; background-color: transparent;"> -0.000009 </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 68.95pt; background-color: transparent;"> Hawaii </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt; background-color: transparent;"> 16721561359.33610 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt; background-color: transparent;"> 16721561359.33500 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt; background-color: transparent;"> 0.0011 </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 68.95pt; background-color: transparent;"> Oregon </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt; background-color: transparent;"> 251007880441.41700 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt; background-color: transparent;"> 251007880441.41700 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt; background-color: transparent;"> 0 </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 68.95pt; background-color: transparent;"> California </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt; background-color: transparent;"> 409601712151.16800 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt; background-color: transparent;"> 409601712151.17100 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt; background-color: transparent;"> -0.003 </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 68.95pt; background-color: transparent;"> Alaska </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 116.45pt; background-color: transparent;"> 1509048854853.74000 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 117pt; background-color: transparent;"> 1509048854853.82000 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 63pt; background-color: transparent;"> -0.08 </td> </tr> </tbody> </table> Table 2. Area measurements for U.S. states (polygons) illustrating differences between server versions, using the STArea() method. <h2 style="margin: 10pt 0in 0pt;"><a name="Spatial_Productions"></a>Spatial Productions</h2> Certain spatial productions, such as unions, can produce new spatial objects that may differ between the server versions in discussion here. If you compare such objects with a discrete measure, such as the number of points (vertices) that the object contains, you can see that their measurements can vary depending on the server version. For instance, consider the following Transact-SQL query. DECLARE @g1 GEOGRAPHY = (SELECT GEOG FROM States WHERE NAME = 'California'); DECLARE @g2 GEOGRAPHY = (SELECT GEOG FROM States WHERE NAME = 'Oregon'); SELECT @g1.STUnion(@g2).STNumPoints(); This produces a new spatial object, which is the geometric union of the States of California and Oregon spatial objects.  <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/0216.CA-OR-Union.png"><img alt="" style="border-width: 0px; border-style: solid;" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/0216.CA-OR-Union.png" /></a> The number of vertices is then calculated for the new object (STNumPoints). This produces the following results (Table 3). <table width="578" style="border: currentcolor; width: 578px; height: 64px; border-collapse: collapse;" border="1" cellspacing="0" cellpadding="0"> <tbody> <tr> <td valign="top" style="background: gray; padding: 0in 5.4pt; border: 1pt solid windowtext; width: 153.9pt;">   </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.5in;"> SQL Server 2008 R2 </td> <td valign="top" style="background: gray; border-width: 1pt 1pt 1pt 0px; border-style: solid solid solid none; border-color: windowtext windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.5in;"> SQL Server 2012 </td> </tr> <tr> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 153.9pt;"> Spatial Object </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.5in;"> Number of Points </td> <td valign="top" style="background: gray; border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.5in;"> Number of Points </td> </tr> <tr> <td style="border-width: 0px 1pt 1pt; border-style: none solid solid; border-color: #000000 windowtext windowtext; padding: 0in 5.4pt; width: 153.9pt; background-color: transparent;"> Union of California and Oregon </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.5in; background-color: transparent;"> 14854 </td> <td style="border-width: 0px 1pt 1pt 0px; border-style: none solid solid none; border-color: #000000 windowtext windowtext #000000; padding: 0in 5.4pt; width: 1.5in; background-color: transparent;"> 15050 </td> </tr> </tbody> </table> Table3. Number of vertices defining a unioned object. The difference between the numbers of vertices used to define the two combined spatial objects is the result of additional artifacts (see next section). Nevertheless, the output of the spatial methods, @g1.STUnion(@g2).STNumPoints(), is different between the two versions of SQL Server. <h2 style="margin: 10pt 0in 0pt;"><a name="Artifacts"></a>Artifacts</h2> The increased precision has also increased the possibility that certain spatial operations will produce spatial artifacts. In the example shown in Table 3 the primary difference between the resultant spatial objects is the number of artifacts produced. The artifacts objects are very thin polygons along the shared California/Oregon border (see Figure 2).   <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/4174.Union-Artifacts.png"><img alt="" style="border-width: 0px; border-style: solid;" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/4174.Union-Artifacts.png" /></a> Figure 2. Spatial artifacts resulting from a union operation between two adjoining polygons. The spatial objects inside the black box, along the Oregon-California border, illustrate the artifacts. In SQL Server 2008, the lower computational precision did not produce as many of these artifacts on the same data. To give you an idea of the nature of these artifacts, 1 artifact polygon was measured at 30 meters long and .186 meters wide. In many cases artifacts can be attributed to differences in coordinates of the shared edge. This, in combination with the increased computational precision of the new server, can exacerbate the artifact issue. <h2 style="margin: 10pt 0in 0pt;"><a name="Persisted_Computed_Columns"></a>Persisted Computed Columns</h2> Persisted computed columns, created using spatial methods, can also introduce side effects caused by the change in precision models. Consider the following scenario where a persisted computed column is created in SQL Server 2008 (including SQL Server 2008 R2) or in a version of SQL Azure prior to September 2011. Using the spatial production example, in a previous section, the following CREATE TABLE DDL is used. CREATE TABLE LOCATION (   ID INT PRIMARY KEY,   GEOM1 GEOMETRY,   GEOM2 GEOMETRY,   NUMPOINTS AS GEOM1.STUnion(GEOM2).STNumPoints() PERSISTED ); This table may now introduce NUMPOINTS column results that are different from the results shown in the Spatial Productions section, earlier. Upgrading to SQL Server 2012 and the latest SQL Azure Instances In SQL Server 2012, when a table with a potential backward compatibility issue is detected during the upgrade process, such as a computed persisted column using one or more spatial methods, that table is disabled. All disabled tables are clearly identified, allowing the database administrator to rebuild the tables and bring them back online. In SQL Azure, because the upgrade process is a continual, ongoing process, the strategy of disabling tables was not considered a viable solution. Hence, it is important to understand when and where such issues can occur. To assist you with this effort, see <a href="http://msdn.microsoft.com/en-us/library/hh295798.aspx">Backward Compatibility</a> on MSDN, which provides some strategies, including the new system view, <a href="http://msdn.microsoft.com/en-us/library/gg715275.aspx">sys.dm_db_objects_impacted_on_version_change</a>. Another document, <a href="http://msdn.microsoft.com/en-us/library/hh352174.aspx">Known Issues in SQL Azure Database</a>, may also be useful to review. Conclusion Spatial support in SQL Server 2012 and SQL Azure Q2 2011 Service Release now use the same version of the spatial library. Application migration from prior versions of SQL Server and SQL Azure should not be effected by the new library, in most cases. Special attention, however, should be paid to persisted computed columns that are defined using spatial methods.
Comment
Tags
Please add 8 and 5 and type the answer here:

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?