Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 geography type fails

Author  Topic 

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-09-28 : 18:28:49
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-29 : 07:44:32
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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-09-29 : 15:14:31
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-30 : 18:49:32
My 2008R2 is RTM too 10.5.1600.1. Unfortunately, I don't have access to an R2 SP2.
Go to Top of Page
   

- Advertisement -