SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 geography type fails
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 09/28/2012 :  18:28:49  Show Profile  Visit jezemine's Homepage  Reply with Quote
Hi folks!

anyone know why this would fail:


select
geography::STGeomFromText('
LINESTRING(
-2.102648 52.689665,-2.1020967 52.6894158,-2.1016698 52.6893074,-2.099642 52.688624,-2.099124 52.688517,-2.09863919374729 52.6884465624763,
-2.09536600265477 52.6879710003857,-2.095366 52.687971,-2.093593 52.687737,-2.093071 52.687668,-2.0917919 52.6874964,-2.090201 52.687283,
-2.0893703219431 52.6871653547087,-2.08695300296693 52.6868230004202,-2.086953 52.686823,-2.0865103 52.6867641,-2.0844264 52.6864867,
-2.081544 52.686103,-2.08134192876777 52.6860700119097,-2.08022472426102 52.6858874901085,-2.07835027573898 52.6854675098915,
-2.078348 52.685467,-2.07835025919766 52.6854675362239,-2.07834574172405 52.6854664639949,-2.0754030008175 52.684768000194,
-2.075403 52.684768,-2.07179641513535 52.683901721624,-2.069616 52.683378,-2.06816691874845 52.6830729039474,-2.0681669 52.6830729,
-2.066553 52.682669,-2.0655323 52.6825139,-2.0635816 52.6822174,-2.0603805 52.6817308,-2.06025 52.681711,-2.05751359349862 52.6812792699719,
-2.05464701316506 52.6808270020771,-2.054647 52.680827,-2.053208 52.6806,-2.0522064 52.6803495,-2.052095 52.6803217,-2.051172 52.680091,
-2.0500225 52.6798038,-2.049687 52.67972,-2.0491893838454 52.679587434265)', 4326)


I get this:


Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)


but if I trim off a few significant digits of the last point, it works:



select
geography::STGeomFromText('
LINESTRING(
-2.102648 52.689665,-2.1020967 52.6894158,-2.1016698 52.6893074,-2.099642 52.688624,-2.099124 52.688517,-2.09863919374729 52.6884465624763,
-2.09536600265477 52.6879710003857,-2.095366 52.687971,-2.093593 52.687737,-2.093071 52.687668,-2.0917919 52.6874964,-2.090201 52.687283,
-2.0893703219431 52.6871653547087,-2.08695300296693 52.6868230004202,-2.086953 52.686823,-2.0865103 52.6867641,-2.0844264 52.6864867,
-2.081544 52.686103,-2.08134192876777 52.6860700119097,-2.08022472426102 52.6858874901085,-2.07835027573898 52.6854675098915,
-2.078348 52.685467,-2.07835025919766 52.6854675362239,-2.07834574172405 52.6854664639949,-2.0754030008175 52.684768000194,
-2.075403 52.684768,-2.07179641513535 52.683901721624,-2.069616 52.683378,-2.06816691874845 52.6830729039474,-2.0681669 52.6830729,
-2.066553 52.682669,-2.0655323 52.6825139,-2.0635816 52.6822174,-2.0603805 52.6817308,-2.06025 52.681711,-2.05751359349862 52.6812792699719,
-2.05464701316506 52.6808270020771,-2.054647 52.680827,-2.053208 52.6806,-2.0522064 52.6803495,-2.052095 52.6803217,-2.051172 52.680091,
-2.0500225 52.6798038,-2.049687 52.67972,-2.04918938 52.6795874)', 4326)



All I did was change the last point from -2.0491893838454 52.679587434265 to -2.04918938 52.6795874 to make it work!




elsasoft.org

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/29/2012 :  07:44:32  Show Profile  Reply with Quote
I am asking myself whether this is a bug. For two or three reasons:

1. The geometry when plotted in Excel looks perfectly fine.

2. If you change one or more points somewhere else (or for that matter, almost anywhere else), it works without error. Or remove a random point somewhere in the middle. Still it works right. This specific combination somehow throws the exception.

3. On my SQL 2012 (installed on the same Windows 7 box, both 64 bit), this works correctly.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 09/29/2012 :  15:14:31  Show Profile  Visit jezemine's Homepage  Reply with Quote
hey thanks for trying this out - do you mind me asking what build of 2008R2 you tried this on?

the server I am seeing this on is 10.50.1600 which is RTM not SP2. Need to upgrade this server.

My (vain?) hope is that once it's upgraded to SP2 this is fixed, if indeed it's a bug.


elsasoft.org
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:49:32  Show Profile  Reply with Quote
My 2008R2 is RTM too 10.5.1600.1. Unfortunately, I don't have access to an R2 SP2.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000