STIsValid returning incorrect result for parameterized query

[mark-oppedahl]

STIsValid returning incorrect result for parameterized query

[mark-oppedahl]

That's what the second parameter in the sample is doing -- passing in the text and creating the geography server-side. It returns the correct result for STIsValid, unlike passing in the geography, which returns an incorrect result. I assume there's a performance hit to convert the geography to text client-side and back to geography server-side.

[dotMorten]

If you send in the text as a parameter (instead of as geography type) and convert to geography server-side do you get the same result?

[mark-oppedahl]

Some more information: It look like SQL Server's call to STIsValid returns whatever the geography object's internal _isValid is set to. If you create the geography in the sample client-side, the internal _isValid value is true. When you send the object up as a parameter and call STIsValid(), it returns true. But if you use reflection to set _isValid to false before sending the parameter, SQL returns false. SQL isn't recalculating isValid, but essentially trusting that the client set it correctly.

[mark-oppedahl]

MakeValid has a similar problem--SQL doesn't modifiy the geometry to make it valid if the internal _isValid is already true.

[dotMorten]

Whaaaaat? That’s just ridiculous 🤦‍♂️🤦‍♂️🤦‍♂️
In that case I honestly don’t think there’s much I can do beyond actually implementing valid calculations which is not something I want to get into

[mark-oppedahl]

Here's a way to pass the geometry as a parameter and force the server to reevaluate isValid:

SELECT geography::STGeomFromWKB(@1.STAsBinary(), 4236).STIsValid()

Similarly, if you want MakeValid to work, it's

SELECT geography::STGeomFromWKB(@1.STAsBinary(), 4236).MakeValid()

Yuck, but it appears to work.