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.

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)

About these ads

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

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

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

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.

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)

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)

SQL SERVER – Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet

Introduction to Spatial Coordinate Systems: Flat Maps for a Round Planet
SQL Server Technical Article
Writers: Isaac Kunen
Project Editor: Diana Steinmetz
Published: July 2008

I recently read this very interesting white paper. I really found it very interesting as this one was one very easy to read and humourous white paper related to SQL Server. The white paper is starts with very interesting note regarding Columbus.

Contrary to popular opinion, Columbus did not prove that the Earth is round. Pythagoras, Plato, and Aristotle claimed a round Earth based on philosophic and observational grounds. More impressively, Eratosthenes measured the Earth’s circumference around 240 B.C., a measurement which comes amazingly close to modern values—within 17 percent when taking pessimistic conversions into account and perhaps 2 percent if we’re more generous.

After the introductory humorous start, this white paper right away go to the point asking question.

If we all agree that the world is round, why would anyone willfully flatten it?

This white paper continues to discuss other important feature of SQL Server with regards to flat map and continuously keep on enhancing knowledge as easy read.

Read white paper here.

Abstract courtesy : Microsoft

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

SQL SERVER – Spatial Database Definition and Research Documents

Recently I was asked in meeting of SQL SERVER user group, what my opinion about spatial database. I answered from my basic knowledge. Spatial database is like database of space (not the star wars or star trek kind space). SQL Server database can understand the numeric and string values. If we ask to SQL Server what is multiplication of 6 and 3 it will provide answer as 18. If we ask to SQL Server what is distance between two points in polygon, it will be not able to answer using native functions. Custom SQL code written by user can do similar coding. In short spatial database is about lines, points, polygons and their interesting features. SQL Server is not spatial database system.

After I came back from home I searched more about it and found some interesting research and documentation. I am posting here assuming it will be interesting to all of my readers.

Another example of spatial and non spatial query is here:
Non-spatial: List the names of all company with more than 100 employees.
Spatial: List the names of all company within 10 miles of Las Vegas.

Here is the definition from Wikipedia about 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.

Very interesting paper written by An Introduction to Spatial Database Systems by Ralf Hartmut Güting.
Author defines Spatial Database as
(1) A spatial database system is a database system.
(2) It offers spatial data types (SDTs) in its data model and query language.
(3) It supports spatial data types in its implementation, providing at least spatial indexing and efficient algorithms for spatial join.
Tutorial in form of slide show can be found here.

Reference for books and slide shows can be found here.
Reference : Pinal Dave (http://blog.SQLAuthority.com)