SQL SERVER – Validating Spatial Object with IsValidDetailed Function

SQL SERVER - Validating Spatial Object with IsValidDetailed Function minority_report What do you prefer – error or warning indicating error may happen with the reason for the error. While writing the previous statement I remember the movie “Minory Report”. This blog post is not about minority report but I will still cover the concept in a single statement “Let us predict the future and prevent the crime which is about to happen in future”. (Please feel free to correct me if I am wrong about the movie concept, I really do not want to hurt your sentiment if you are dedicated fan).

Let us switch to the SQL Server world. Spatial data types are interesting concepts. I love writing about spatial data types because it allows me to be creative with shapes (just like toddlers). When working with Spatial Datatypes it is all good when the spatial object works fine. However, when the spatial object has issue or it is created with invalid coordinates it used to give a simple error that there is an issue with the object but did not provide much information. This made it very difficult to debug. If this spatial object was used in the big procedure and while this big procedural error out because of the invalid spatial object, it is indeed very difficult to debug it. I always wished that the more information provided regarding what is the problem with spatial datatype.

SQL Server 2012 has introduced the new function IsValidDetailed(). This function has made my life very easy. In simple words this function will check if the spatial object passed is valid or not. If it is valid it will give information that it is valid. If the spatial object is not valid it will return the answer that it is not valid and the reason for the same. This makes it very easy to debug the issue and make the necessary correction.

DECLARE @p GEOMETRY = 'Polygon((2 2, 6 6, 4 2, 2 2))'
SELECT @p.IsValidDetailed()
GO
DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.IsValidDetailed()
GO
DECLARE @p GEOMETRY = 'Polygon((2 2, 4 4, 4 2, 2 3, 2 2))'
SELECT @p.IsValidDetailed()
GO
DECLARE @p GEOMETRY = 'CIRCULARSTRING(2 2, 4 4, 0 0)'
SELECT @p.IsValidDetailed()
GO
DECLARE @p GEOMETRY = 'CIRCULARSTRING(2 2, 4 4, 0 0)'
SELECT @p.IsValidDetailed()
GO
DECLARE @p GEOMETRY = 'LINESTRING(2 2, 4 4, 0 0)'
SELECT @p.IsValidDetailed()
GO

Here is the resultset of the above query.

SQL SERVER - Validating Spatial Object with IsValidDetailed Function IsValidDetailed

You can see any valid query and some invalid query. If the query is invalid it also demonstrates the reason along with the error message.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Fix : Error 3623 – An invalid floating point operation occurred
Next Post
SQL SERVER – Validating Spatial Object as NULL using IsNULL

Related Posts

1 Comment. Leave new

  • Hi,

    We are creating a procedure in which we use bcp(xp_cmdshell) to archive out the data for particular month. When I execute procedure without begin tran/commit tran it works fine and bcp out the data from table to hard disk. But when i try to use transaction handling using begin tran the procedure keeps running, the bcp remains open and the procedure stucks and does not complete.

    Is there any special way to handle transaction when using bcp and xp_cmdshell in the procedure?

    This is the only procedure running on machine.

    Thanks

    Reply

Leave a Reply

Menu