CODEWITHSUNDEEP
sqlsql-servert-sql
encikpiee
encikpiee

Reputation: 69

SQL Server Argument Exception

I tried to run this code on MSSQL 2014 and it returns a value. 

DECLARE @g geometry;   
SET @g = geometry::STPointFromText('POINT (60 60)', 4326);   

Select Country From [vstl].[dbo].[EEZ_1]  where  geom.STIntersects(@g)=1;

Results

Singapore

However, when I tried to run on MSSQL 2012 and 2008R2, with the same sql query and data, it give and exception error as below.

Msg 6522, Level 16, State 1, Line 4
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
   at Microsoft.SqlServer.Types.SqlGeometry.STIntersects(SqlGeometry other)
.

Does anybody have any idea what is going on?

Upvotes: 2

Views: 511

Answers (1)

encikpiee
encikpiee

Reputation: 69

Found the culprit! There is an invalid shapefile geometry in the table.

Select * from eez where geom.STIsValid()=0

Turns out give 5 invalid values. Removing the invalid values solved the problem.

Upvotes: 1

Related Questions