[SOLVED] Geometry error on one server but not the other with same data

Geometry error on one server but not the other with same data

I have a table with approx 400 rows of geographic data and I'm using the STWithin method to determine if a point exists within the boundary of one of those rows.

On my test server it works fine. However with the latest version of the dataset the query fails on the live server on one of those rows of data. If I exclude the row from the query then it succeeds.

The error is:

Msg 6522, Level 16, State 1, Line 3

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.

A simplified version of the query is:

DECLARE @Point GEOMETRY = GEOMETRY::Point(416420, 345058, 0)

SELECT  *
FROM    PolygonData
WHERE   @Point.STWithin(GeoField) = 1

The test server is SQL Server 2012 (11.0.2100.60), the live server is SQL Server 2012 (11.0.6544.0).

I can't see why the same data would be succeeding on one server and failing on the other? Any help much appreciated, thanks.

Solution

It turns out that there is invalid data in the table. Adding this as an answer, but also adding a way to fix the data in the table.

update t
set g = g.MakeValid()
from dbo.yourTable as t
where t.g.STIsValid() = 0;

(replacing yourTable and g with the name of the actual table and column, respectively) By updating the bad data as a one time operation, you won't incur the overhead of calling MakeValid() at select time (as presumably reads are more frequent than writes). You could also implement something like the above for any subsequent data loads you do.