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

Question

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.

Can you provide WKT of the "bad" instance? Also, a code smell is that you're using the geometry data type (instead of geography) to store geographic data. — Ben Thul, Aug 07 '17 at 18:19
Sure I've posted the WKT data here https://gist.github.com/dannyshisler/66da21b345038d4238be92e925490aa5 thanks. The data is imported from a Shapefile (.shp) using a tool called shape2sql.exe by SharpGIS which is why the data is in the format it is. I haven't found another way to import .shp data into SQL Server. — Danny S, Aug 07 '17 at 19:06
I can see why it's not a valid geography instance (geography assumes the coordinates are latitudes/longitudes which these are not). But as for your actual problem, I had a weird behavior when I loaded it into my local instance. If I did `declare @g geometry = geometry::STGeomFromText(«your WKT», 0); select @g;` everything worked fine. But if I tried to call a method on `@g` (like `STNumGeometries()`) it complained. Calling `MakeValid()` on it made everything okay. My suspicion is that you have an internal ring orientation problem. That is, the first polygon has "holes" in it as defined… — Ben Thul, Aug 07 '17 at 20:02
… and I think one of those holes is defined in the wrong direction (clockwise if the "parent" polygon is defined counter-clockwise for instance). — Ben Thul, Aug 07 '17 at 20:03
That is strange. I also still don't understand why it works on one server and not the other though? Anyway, adding `MakeValid()` to my query does allow it to run: `SELECT * FROM PolygonData WHERE @Point.STWithin(GeoField.MakeValid()) = 1` Thanks for your help. — Danny S, Aug 08 '17 at 01:05

score 2 · Accepted Answer · answered Aug 08 '17 at 01:23

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.

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

1 Answers1