CODEWITHSUNDEEP
sql-serverazureazure-sql-database
user310988
user310988

Reputation:

Why is this Sql Geography "Valid" on SQL Server 2014 and "Invalid" on Azure SQL?

Here's the query. Sorry it's in a paste-bin link and not in this question, but the geography data is longer than the maximum characters for the question!

http://pastebin.com/i0t1sqQR

There's two versions of the query there.

The first is the normal query, it runs fine on 2014, but on Azure Sql I get:

Msg 6522, Level 16, State 1, Line 11
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. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a spatial instance to shift slightly.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromBinary(OpenGisType type, SqlBytes wkbGeography, Int32 srid)

The second version is with .MakeValid() added. Again it runs fine on 2014, but I get the exact same error again on Azure Sql.

Why is this behaving differently?

Why doesn't MakeValid() work?

As a side note ... where is the documentation for the differences between Sql Server and Azure Sql?

I've found a blog post from 2014: http://blogs.msdn.com/b/rbrundritt/archive/2014/08/18/working-with-invalid-geography-objects-in-sql-azure.aspx

That references patch notes form 2011: http://social.technet.microsoft.com/wiki/contents/articles/6197.updated-spatial-features-in-the-sql-azure-q4-2011-service-release.aspx

So MakeValid was shown in the table in the article as working, but had a note saying "actually this doesn't work at all" and that was the only indication of that issue for 3 years?

Upvotes: 3

Views: 1056

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

The short answer is that the first geography isn't valid in either case (i.e. on premises SQL or Azure). If I take the code from your pastebin link and transform it slightly to:

exec sp_executesql N'
DECLARE @Location geography = geography::STGeomFromWKB(@wkb, 4326);
SELECT  @Location.STIsValid()
',N'@wkb varbinary(max)',@wkb=0x0106...

I get back 0 (i.e. false). That said, I don't know why the on prem version of SQL is able to display the first geography instance; typically a precondition for that is that it's valid.

If this is a one-time operation, maybe using the seemingly less restrictive on prem version to generate a valid geography which you can then export to Azure will work for you.

Upvotes: 3

Related Questions