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

spatialobject2 SQL SERVER   Validating Spatial Object as NULL using IsNULL

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

spatialobject1 SQL SERVER   Validating Spatial Object as NULL using IsNULL

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)

SQL SERVER – Validating Spatial Object with IsValidDetailed Function

minority report SQL SERVER   Validating Spatial Object with IsValidDetailed FunctionWhat 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.

IsValidDetailed SQL SERVER   Validating Spatial Object with IsValidDetailed Function

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.

spatial 1 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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

spatial 2 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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

spatial 3 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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.

spatial 4 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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.

ramsetu SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

ramsetu1 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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.

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

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.

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

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.

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

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.

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

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

KL Malaysia (4) SQLAuthority News   2 Sessions at TechInsight 2010   June 29   July 1, 2010

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

KL Malaysia (5) SQLAuthority News   2 Sessions at TechInsight 2010   June 29   July 1, 2010

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.

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

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

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

I will be presenting two SQL Server advance level sessions at SQL Data Camp @ Chennai.

I am very excited for this event as I am going to meet my friends Sugesh, Deepak, Vidhya and Madhivanan at this event. All of them are SQL Server MVPs. I have come to know that there are two other SQL Server MVPs – Madhu andVenkatesh are also joining the event as speaker. This is going to be one mega fest as so many of SQL Server MVPs are going to be present at same place. The event is going to be world-class event and there is no doubt about the same.

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.

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

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.

I have presented above two sessions earlier as well but for this one specially I have created new demos and they are going to be very interesting. If you are in Chennai, I strongly suggest that you all attend this event, we are really going to do one great event.

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

SQL SERVER – Spatial Database Queries – What About BLOB – T-SQL Tuesday #006

Michael Coles is one of the most interesting book authors I have ever met. He has a flair of writing complex stuff in a simple language. There are a very few people like that. I really enjoyed reading his recent book, Expert SQL Server 2008 Encryption. I strongly suggest taking a look at it. This blog is written in response to T-SQL Tuesday #006: “What About BLOB? by Michael Coles.

Spatial Database is my favorite subject. Since I did my TechEd India 2010 presentation, I have enjoyed this subject a lot. Before I continue this blog post, there are a few other blog posts, so I suggest you read them. To help build the environment run the queries, I am going to present them in this single blog post.

SQL SERVER – What is Spatial Database? – Developing with SQL Server Spatial and Deep Dive into Spatial Indexing
This blog post explains the basics of Spatial Database and also provides a good introduction to Indexing concept.

SQL SERVER – World Shapefile Download and Upload to Database – Spatial Database
This blog post will enable you with how to load the shape file into database.

SQL SERVER – Spatial Database Definition and Research Documents
This blog post links to the white paper about Spatial Database written by Microsoft experts.

SQL SERVER – Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet
This blog post links to the white paper explaining coordinate system, as written by Microsoft experts.

After reading the above listed blog posts, I am very confident that you are ready to run the following script.

Once you create a database using the World Shapefile, as mentioned in the second link above,you can display the image of India just like the following. Please note that this is not an accurate political map. The boundary of this map has many errors and it is just a representation.

You can run the following query to generate the map of India from the database spatial which you have created after following the instructions here.

USE Spatial
GO
-- India Map
SELECT [CountryName]
,[BorderAsGeometry]
,[Border]
FROM [Spatial].[dbo].[Countries]
WHERE Countryname = 'India'
GO

mapIndia SQL SERVER   Spatial Database Queries   What About BLOB   T SQL Tuesday #006

Now, let us find the longitude and latitude of the two major IT cities of India, Hyderabad and Bangalore. I find their values as the following: the values of longitude-latitude for Bangalore is 77.5833300000 13.0000000000; for Hyderabad, longitude-latitude is 78.4675900000 17.4531200000. Now, let us try to put these values on the India Map and see their location.

-- Bangalore
DECLARE @GeoLocation GEOGRAPHY
SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(77.5833300000 13.0000000000)',4326).STBuffer(20000);
-- Hyderabad
DECLARE @GeoLocation1 GEOGRAPHY
SET @GeoLocation1 = GEOGRAPHY::STPointFromText('POINT(78.4675900000 17.4531200000)',4326).STBuffer(20000);
-- Bangalore and Hyderabad on Map of India
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I
WHERE I.[GeoLocation].STDistance(@GeoLocation) <= 0
UNION ALL
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I
WHERE I.[GeoLocation].STDistance(@GeoLocation1) <= 0
UNION ALL
SELECT '',[Border]
FROM [Spatial].[dbo].[Countries]
WHERE Countryname = 'India'
GO

MapIndia1 SQL SERVER   Spatial Database Queries   What About BLOB   T SQL Tuesday #006

Now let us quickly draw a straight line between them.

DECLARE @GeoLocation GEOGRAPHY
SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(78.4675900000 17.4531200000)',4326).STBuffer(10000);
DECLARE @GeoLocation1 GEOGRAPHY
SET @GeoLocation1 = GEOGRAPHY::STPointFromText('POINT(77.5833300000 13.0000000000)',4326).STBuffer(10000);
DECLARE @GeoLocation2 GEOGRAPHY
SET @GeoLocation2 = GEOGRAPHY::STGeomFromText('LINESTRING(78.4675900000 17.4531200000, 77.5833300000 13.0000000000)',4326)
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I
WHERE I.[GeoLocation].STDistance(@GeoLocation) <= 0
UNION ALL
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I1
WHERE I1.[GeoLocation].STDistance(@GeoLocation1) <= 0
UNION ALL
SELECT '' name, @GeoLocation2
UNION ALL
SELECT '',[Border]
FROM [Spatial].[dbo].[Countries]
WHERE Countryname = 'India'
GO

MapIndia2 SQL SERVER   Spatial Database Queries   What About BLOB   T SQL Tuesday #006

Let us use the distance function of the spatial database and find the straight line distance between this two cities.

-- Distance Between Hyderabad and Bangalore
DECLARE @GeoLocation GEOGRAPHY
SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(78.4675900000 17.4531200000)',4326)
DECLARE @GeoLocation1 GEOGRAPHY
SET @GeoLocation1 = GEOGRAPHY::STPointFromText('POINT(77.5833300000 13.0000000000)',4326)
SELECT @GeoLocation.STDistance(@GeoLocation1)/1000 'KM';
GO

The result of above query is as displayed in following image.

MapIndia4 SQL SERVER   Spatial Database Queries   What About BLOB   T SQL Tuesday #006

As per SQL Server, the distance between these two cities is 501 KM, but according to what I know, the distance between those two cities is around 562 KM by road. However, please note that roads are not straight and they have lots of turns, whereas this is a straight-line distance. What would be more accurate is the distance between these two cities by air travel. When we look at the air travel distance between Bangalore and Hyderabad, the total distance covered is 495 KM, which is very close to what SQL Server has estimated, which is 501 KM.

Bravo! SQL Server has accurately provided the distance between two of the cities.

SQL Server Spatial Database can be very useful simply because it is very easy to use, as demonstrated above.

I appreciate your comments, so let me know what your thoughts and opinions about this are.

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

SQL SERVER – What is Spatial Database? – Developing with SQL Server Spatial and Deep Dive into Spatial Indexing

What is Spatial Database?

A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types. (Source: Wikipedia)

Today I will be talking about the same subject at Microsoft TechEd India. If you want to learn about how to spatial aspect of data and how to integrate them with SQL Server this is the perfect session for you. Spatial is very special concept of SQL Server and I really like how it is implemented in SQL Server. In general Performance Tuning and Query Optimization is something I always have enjoyed in my professional life. Index are my best friends and many time, by implementing and many time by removing I have improved the performance of the system. In this session, I will be talking about Index along with Spatial Data. As Spatial Database is very interesting concept, I will cover super short but very interesting 10 quick slides about this subject. I will make sure in very first 20 mins, you will understand following topics

  • Introduction to Spatial Database
    • One line definition
  • Understanding Spatial Indexing
    • Index Internals
    • Query/Performance Tuning
    • Query Hinting/Cost Analysis
  • Spatial Index Catalog Views
  • Performance Troubleshooting
    • Finding Optimal Index using Spatial Index SP
    • Common Errors
  • Index Maintenance

This slides decks will be followed by around 30 mins demo which will have story of geometry, geography, index internals and performance tuning. If you are interested in learning how GIS works and how SQL Server out of the box supports this wonderful tools, you will really like how the story is told. I am sure all people who attend the event will know how the Bangalore is positioned on the map of India. I will take example of Bangalore and Hyderabad and demonstrate how index can improve the performance. Well there are lots of story to tell in the session, and I will be opening this session with the beautiful script of Botticelli’s Birth of Venus created by Michael J. Swart.

I will also demonstrate few real life scenario where I will be talking about Spatial Database and its usage.

Do not miss this session. At the end of session there will be book awarded to best participant.

My session details:

Session 3: Developing with SQL Server Spatial and Deep Dive into Spatial Indexing
Date: April 14, 2010 Time: 5:00pm-6:00pm

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.

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