SQL SERVER – Spatial Database Queries – What About BLOB – T-SQL Tuesday #006
- by pinaldave
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)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Spatial Database
