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)

SQL SERVER – SSRS 2008 R2 – MapGallery and Codeplex – World Map

SQL Server 2008 R2 has negatively integrated ability to work with maps. There are few ways how one can select map and use them in their projects. The one I recently came across was MapGallery. By default SQL Server 2008 R2 is enabled for USA maps. This is quite common request from developers around the globe that they want the same feature available in their own country.

Solid Quality Mentor Diego Nogare has recently worked on project and have put the same project online on CodePlex. This project has maps from all around the world and one can use the existing maps as well create new maps as well. The new maps also can be used by others. I am sure every developer who has previously requested the same feature will be happy as this project is now live.

I love working with Spatial Database and I have written before many articles on the same subject. I have earlier wrote about the article on the same subject over SQL SERVER – World Shapefile Download and Upload to Database – Spatial Database.

Have any of you worked with this tool and if yes, what is your experience.

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

SQLAuthority News – Community TechDays, Ahmedabad – July 24, 2010

Dive deep into the world of Microsoft technologies at the Community TechDays and get trained on the latest from Microsoft. Build real connections with Microsoft experts and community members, and gain the inspiration and skills needed to maximize your impact on your organization while enhancing your career. What more… you can watch some of these sessions LIVE online from the comfort of your workstation as well.

The event registration site is here.

I will be speaking on the subject.

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.

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.

The event is oversubscribed at the moment. I suggest to arrive at location early enough to get your space. It is quite possible even though you have registered you may not get the chance to enter the venue. Please come in early and secure your seat. The complete agenda is listed here.

I will also have special gift for few of attendees. Hope to see you soon there.

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

SQLAuthority News – Delivering Two SQL Sessions at SQL Data Camp at Chennai – July 17, 2010

SQL Server Community is very strong community world-wide. In India SQL is considered as one of the most popular technology. Chennai is the only city in India where there are more than 3 SQL Server MVPs are from. My MVP friends has arranged one of the very first whole day SQL event in India at Chennai. At this event all the speakers are MVPs as well there will be more than 6 SQL Server MVP present at this single event.

You can register for this event by going to the site http://sql-articles.com/events/ and clicking on link Register. I am very much looking forward to this event on July 17, 2010. The event venue is  Hotel Palmgrove, Vaishali Banquet Hall, 5, Kodambakkam High Road, Chennai – 600034 Tamil Nadu, INDIA.

I will be speaking on following two sessions at the event. I am very excited as Chennai is one of my favorite city and always love to visit the same. I am very glad that both the sessions which I will be doing are on Indexing and Performance Tuning. I love to talk about Index and Performance Tuning.

Spatial Database & Spatial Indexing

Speaker : Pinal Dave
Event Date : 17th July 2010
Session Time : 09:45 to 10:45

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.

Good, Bad & Ugly – The other side of Index

Speaker : Pinal Dave
Event Date : 17th July 2010
Session Time : 15:00 to 16:00

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

If you are in Chennai, I strongly suggest that you come to this event and take advantage of this wonderful session organized by SQL MVPs Sugesh, Deepak, and Vidya.

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

SQL SERVER – World Shapefile Download and Upload to Database – Spatial Database

During my recent, training I was asked by a student if I know a place where he can download spatial files for all the countries around the world, as well as if there is a way to upload shape files to a database. Here is a quick tutorial for it.

VDS Technologies has all the spatial files for every location for free. You can download the spatial file from here. If you cannot find the spatial file you are looking for, please leave a comment here, and I will send you the necessary details. Unzip the file to a folder and it will have the following content.

Then, download Shape2SQL tool from SharpGIS. This is one of the best tools available to convert shapefiles to SQL tables. Afterwards, run the .exe file.

When the file is run for the first time, it will ask for the database properties. Provide your database details.

Select the appropriate shape files and the tool will fill up the essential details automatically. If you do not want to create the index on the column, uncheck the box beside it. The screenshot below is simply explains the procedure. You also have to be careful regarding your data, whether that is GEOMETRY or GEOGRAPHY. In this example,  it is GEOMETRY data.

Click “Upload to Database”. It will show you the uploading process. Once the shape file is uploaded, close the application and open SQL Server Management Studio (SSMS).

Run the following code in SSMS Query Editor.

USE Spatial
GO
SELECT *
FROM dbo.world
GO

This will show the complete map of world after you click on Spatial Results in Spatial Tab.

In Spatial Results Set, the Zoom feature is available. From the Select label column, choose the country name in order to show the country name overlaying the country borders.

Let me know if this tutorial is helpful enough. I am planning to write a few more posts about this later.

Note: Please note that the images displayed here do not reflect the original political boundaries. These data are pretty old and can probably draw incorrect maps as well. I have personally spotted several parts of the map where some countries are located a little bit inaccurately.

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