Databases
StackOverflow2013
Postsdbo
POCross-machine inconsistency in WKT conversion for SQL spatial types
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCross-machine inconsistency in WKT conversion for SQL spatial types
    text
    copied!<p>I am experiencing inconsistent behavior across machines with conversion to Well-Known Text (WKT) of spatial types in SQL server 2008. It looks as if the data is being stored identically, but the conversion back to WKT acts differently depending on the machine!</p> <p>Here's something I put together to pinpoint the issue:</p> <pre><code>SET NOCOUNT ON; DECLARE @TestTable TABLE (TestPoint GEOGRAPHY); INSERT INTO @TestTable(TestPoint) VALUES (geography::STGeomFromText('POINT(-124.957140999999993 39.326679)',4326)); DECLARE @PointAsText NVARCHAR(max); SELECT @PointAsText = TestPoint.STAsText() from @TestTable; PRINT @PointAsText; DECLARE @PointAsBinary BINARY(22); SELECT @PointAsBinary = CAST(TestPoint AS BINARY(22)) from @TestTable; print @PointAsBinary; print @@version; </code></pre> <p>On various machines I have available, I see two different results:</p> <blockquote> <p>POINT (<strong>-124.95714099999999</strong> 39.326679)<br> 0xE6100000010C1EA5129ED0A94340492A53CC413D5FC0<br> Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 <strong>(X64)</strong><br> &nbsp;&nbsp;&nbsp;&nbsp;Jun 17 2011 00:54:03<br> &nbsp;&nbsp;&nbsp;&nbsp;Copyright (c) Microsoft Corporation<br> &nbsp;&nbsp;&nbsp;&nbsp;Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)<br></p> </blockquote> <p>or </p> <blockquote> <p>POINT (<strong>-124.957141</strong> 39.326679)<br> 0xE6100000010C1EA5129ED0A94340492A53CC413D5FC0<br> Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 <strong>(Intel X86)</strong><br> &nbsp;&nbsp;&nbsp;&nbsp;Apr 2 2010 15:53:02<br> &nbsp;&nbsp;&nbsp;&nbsp;Copyright (c) Microsoft Corporation<br> &nbsp;&nbsp;&nbsp;&nbsp;Developer Edition on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor)<br></p> </blockquote> <p>Another test case shows that an <strong>X64 machine with 2008 R2 (RTM)</strong> gives <strong>-124.95714099999999</strong>. So, definitely indicates x86 vs x64.</p> <p>I'm at least somewhat familiar with lack of floating point precision, but I was unaware it was architecture specific. It seems as if working with SQL spatial storage involves data going through through WKT conversions like these. Am I failing to see a more appropriate strategy to working with this data?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload