SQL SERVER – Validating Spatial Object as NULL using IsNULL

Follow up questions are the most fun part of writing a blog post. Earlier I wrote about SQL SERVER – Validating Spatial Object with IsValidDetailed Function and today I received a follow up question on the same subject. The question was mainly about how NULL is handled by spatial functions. Well, NULL is NULL. It is very easy to work with NULL. There are two different ways to validate if the passed in the value is NULL or not.

1) Using IsNULL Function

IsNULL function validates if the object is null or not, if object is not null it will return you value 0 and if object is NULL it will return you the value NULL.

DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.ISNULL ObjIsNull
GO
DECLARE @p GEOMETRY = NULL
SELECT @p.ISNULL ObjIsNull
GO

2) Using IsValidDetailed Function

IsValidateDetails function validates if the object is valid or not. If the object is valid it will return 24400: Valid but if the object is not valid it will give message with the error number. In case object is NULL it will return the value as NULL.

DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.IsValidDetailed() IsValid
GO
DECLARE @p GEOMETRY = NULL
SELECT @p.IsValidDetailed() IsValid
GO

When to use what?

Now you can see that there are two different ways to validate the NULL values. I personally have no preference about using one over another. However, there is one clear difference between them. In case of the IsValidDetailed Function the return value is nvarchar(max) and it is not always possible to compare the value with nvarchar(max). Whereas the ISNULL function returns the bit value of 0 when the object is null and it is easy to determine if the object is null or not in the case of ISNULL function. Additionally, ISNULL function does not check if the object is valid or not and will return the value 0 if the object is not NULL.

Now you know even though either of the function can be used in place of each other both have very specific use case. Use the one which fits your business case.

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

About these ads

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)

SQL SERVER – Finding Shortest Distance between Two Shapes using Spatial Data Classes – Ramsetu or Adam’s Bridge

Recently I was reading excellent blog post by Lenni Lobel on Spatial Database. He has written very interesting function ShortestLineTo in Spatial Data Classes. I really loved this new feature of the finding shortest distance between two shapes in SQL Server. Following is the example which is same as Lenni talk on his blog article .

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -26, 14 -28, 20 -40, -20 -30))'
DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
GO

When you run this script SQL Server finds out the shortest distance between two shapes and draws the line. We are using STBuffer so we can see the connecting line clearly.

Now let us modify one of the object and then we see how the connecting shortest line works.

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))'
DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
GO

Now once again let us modify one of the script and see how the shortest line to works.

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))'
DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -18, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
SELECT @Shape1.STDistance(@Shape2)
GO

You can see as the objects are changing the shortest lines are moving at appropriate place. I think even though this is very small feature this is really cool know.

While I was working on this example, I suddenly thought about distance between Sri Lanka and India. The distance is very short infect it is less than 30 km by sea. I decided to map India and Sri Lanka using spatial data classes. To my surprise the plotted shortest line is the same as Adam’s Bridge or Ramsetu. Adam’s Bridge starts as chain of shoals from the Dhanushkodi tip of India’s Pamban Island and ends at Sri Lanka’s Mannar Island. Geological evidence suggests that this bridge is a former land connection between India and Sri Lanka.

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

SQLAuthority News – Speaking Online at Virtual Techdays – Aug 18, 2010 – Spatial Datatypes

I am honored that I have been invited to speak at Virtual TechDays on Aug 18, 2010 by Microsoft. I will be speaking on my favorite subject of Spatial Datatypes.

This exclusive Online event will have 30 deep technical sessions per day – and, attendance is completely FREE. There are dedicated tracks for Architects, Software  Developers / Project Managers, Infrastructure Managers / Professionals and Enterprise Developers.

Register for the event over here.

Date and Time : August 18, 2010, 4:15pm – 5:15pm

Developing with SQL Server Spatial and Deep Dive into Spatial Indexing

Microsoft SQL Server 2008 delivers new spatial data types that enable you to consume, use, and extend location-based data through spatial-enabled applications. Attend this session to learn how to use spatial functionality in next version of SQL Server to build and optimize spatial queries. This session outlines the new geography data type to store geodetic spatial data and perform operations on it, use the new geometry data type to store planar spatial data and perform operations on it, take advantage of new spatial indexes for high performance queries, use the new spatial results tab to quickly and easily view spatial query results directly from within Management Studio, extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications and much more.

I will be sharing few of the real life tricks and tips which I have recently picked up from my consultation on spatial database implementation.

If you have never attended my session on this subject I strongly suggest that you attend the event as this is going to be very interesting conversation between us. If you have attended this session earlier, this will contain few new information which will for sure interesting to share with all.

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

SQLAuthority News – SQL Data Camp, Chennai, July 17, 2010 – A Huge Success

I had great pleasure to attend very first SQL Data Camp at Chennai on July 17, 2010. This event was very unique as this was very first one-day SQL Event in whole Indian Subcontinent. The event was blast as there were so many back–to-back SQL Sessions with SQL Server MVPs. I was fortunate to present two different sessions at the SQL Data Camp in Chennai.

I must express my special thanks to event organizers Sugesh, Deepak and Vidyasagar for organizing such a wonderful event. Every participant who was attending the event had a great time and expressed their passion for SQL Server as well as the overall technology. I also enjoyed meeting SQL Server MVPs Madhivanan, Madhu and Venketasan.

Vidya, Madhu, Madhivanan, Pinal, Deepak, Venketasan

Vidya, Madhu, Madhivanan, Pinal, Deepak, Venketasan

The event was very well organized and the enthusiasm of the crowd was unmatched. After the event, I talked with nearly 30 different attendees on a one-to-one basis and was glad to know their desires to involve with the community activities and to learn advanced topics in SQL Server.

Pinal Presenting Spatial Database

Spatial Database & Spatial Indexing

Microsoft SQL Server 2008 delivers new spatial data types that enable you to consume, use, and extend location-based data through spatial-enabled applications. This session helps us to learn how to use spatial functionality in the next version of SQL Server to build and optimize spatial queries. This session also outlines the new geography data type to store geodetic spatial data and perform operations on it, and use the new geometry data type to store planar spatial data and perform operations on it.

Pinal Presenting Indexing Session

Good, Bad & Ugly – The other side of Index

Index is often considered as the sure shot tool of improving the performance of any query. You can learn the secrets of Indexing with examples and discover the good, bad and ugly sides of Index. This session will help you write queries efficiently in future and also make you go back and defector your earlier code.

Pinal giving away books

Chennai Data Camp

We distributed T-Shirts to all the participants. The most active participant received Redgate books written by renowned industry experts and MVPs. Once again, many thanks to all the organizers and SQL Server MVPs for arranging such a wonderful event for the entire SQL Server Community!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – 2 Sessions at TechInsight 2010 – June 29 – July 1, 2010

Earlier this month, I got the opportunity to visit Malaysia for community sessions on June 29 – July 1, 2010 at Kuala Lumpur, Malaysia, which I would consider as valuable experience. I presented two different sessions at the event. The event was extremely popular in local community, and I had great time meeting people in Malaysia. I must say that the best thing about Kuala Lumpur is the people and their response.

Malaysia Twin Towers
Malaysia Twin Towers

Techinsights is a major technology conference to network with like-minded peers and also up-skill your knowledge on latest technologies. An event that offers opportunity to dabble in hardcore technologies with in-depth and hands-on demonstration by Microsoft MVPs and industry experts local and abroad. This three-day event will challenge what you think you already know. You’ll return to the office with cutting-edge insights and expertise that will make life easier for you (and everyone else) at work. This round, we have a special highlight on new technologies such as SharePoint 2010, Visual Studio 2010, SQL Server 2008 R2, Silverlight 4, Windows 7, Windows Server 2008 R2 and many more. TechInsight is an event created by techies for techies. There is no marketing involved. It is indeed an experience to rediscover the uber-geek within you. Sign up today to secure your seat.

Techinsight - 2 Sessions
Techinsight – 2 Sessions

I presented two sessions there. Both of my sessions were in the TOP 5 sessions of Development track. Additionally, my session on Join got the highest ranking ever in Dev Track.

1) My Join, Your Join and Our Joins – The Story of Joins

Joins are very mysterious; there are many myths and confusions. This session will address all of them and also tell the story of how they act when it is about performance. Does the order of table in Join matter? Does the right or left join any different to each other? Does the Join increase IO? When is an outer join not an outer join and inner join? All these questions are answered and many more stories of Joins are included. Learn the simple tricks to get the maximum out of this tool.

Session Evaluations

Overall session rating 7.5
How valuable was the content presented 7.467741935
How effectively did the presenter communicate the content 7.596774194

2) Spatial Database – The Indexing Story

The world was believed to be flat but no more. Now SQL Server supports the spatial datatypes and many more functions. This session addresses the most vital part of Spatial datatypes and talks about how to improve the performance for the application, which is already blazing fast. We will look at how indexes are behaving with different spatial datatypes and how they can help to improve the performance and also learn the pitfalls to avoid them affecting performance.

Session Evaluations

Overall session rating 7.237288136
How valuable was the content presented 7.322033898
How effectively did the presenter communicate the content 7.457627119

I must express my special thanks to all the organizers of the event – Ervin, Walter, Raymond, and Patrick (in no particular order). They did an excellent job, and all the attendees of the event had great time as well. The food was awesome, and the response was excellent. After one month, when I am writing this review, I am still thinking of the wonderful experience I had from this event. This makes me want to not miss this event any year.

Techinsight - Event Organizers
Techinsight – Event Organizers

This one event is truly TechEd quality event in Malaysia. Kudos to the organizers and Microsoft.

Techinsight - Kuala Lumpur, Malaysia
Techinsight – Kuala Lumpur, Malaysia

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

SQLAuthority News – The story of the world – Spatial Data types – July 24, 2010

Today I will be speaking on the subject of Spatial Database at Community Tech Days at Ahmedabad. The event is absolutely FREE. We have so far received 500+ RSVP but there are only limited 250 seats are available. We are doing our best to inform everybody about their registration status. If you have received confirmation email, I suggest that you come in early enough to reserve the place.

We have excellent line of the speakers. I will be giving opening note and will be revealing the “The Real Agenda” of Community Tech Days. The opening will also follow up with my session.

I am going to talk about flat world and non-flat world. I have plenty of giveaways during the session.

SQL Server – The story of the world – Spatial Data types

Speaker: Pinal Dave
Event Date: 24th July 2010
Session Time: 10:30am – 11:30am
Location: Ahmedabad Management Association, AMA Complex, ATIRA, Dr. Vikram Sarabhai Marg.

See you all at the event!

Please come in early and secure your seat. The complete agenda is listed here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)