SQL SERVER – Validating Spatial Object with IsValidDetailed Function

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.

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 (http://blog.sqlauthority.com)

About these ads

3 thoughts on “SQL SERVER – Validating Spatial Object with IsValidDetailed Function

  1. 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

    Like

  2. Pingback: SQL SERVER – Validating Spatial Object as NULL using IsNULL « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s