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)

About these ads

13 thoughts on “SQL SERVER – Spatial Database Queries – What About BLOB – T-SQL Tuesday #006

  1. Really cool examples, currently working on a spatial project going through the hoops of learning to write meaning ful spatial queries. thanks pal.

  2. Hi i am trying to upload the .shp file on c ing ur demo on world file . I tried to dowl load it woks fine for that file and when i tried to load other file like US states files the tool says load complete in a database .

    BUT I DON’T SEE ANY TABLE THERE ….. I dont Know Y it happens . I tried a lot of file i have seen only 5 or 6 files i am able to see tables when tool says import done

    I Dont understand where the problem is ……….. the morton tool is error or i need to change any settings in database .

    I tried changing all settings in the tool

  3. Hi i am trying to upload the .shp file on c ing ur demo on world file . I tried to down load it woks fine for that file and when i tried to load other file like US states files the tool says load complete in a database .

    BUT I DON’T SEE ANY TABLE THERE ….. I dont Know Y it happens . I tried a lot of file i have seen only 5 or 6 files i am able to see tables when tool says import done

    I Dont understand where the problem is ……….. the morton tool is error or i need to change any settings in database .

    I tried changing all settings in the tool

  4. Mohan,

    Try Shape2SQL Utility or OGR2OGR.

    U might be doing something wrong with the spatial reference.

    — Shail ….

  5. Hi Shil thanks for reply…….

    IF (@distance=’Driving’ or @distance=’Nearby’)
    begin
    SELECT @g = (geography:: Point(@Lat1,@Lon1 , 4326))
    if @distance=’Driving’
    begin
    set @g1=@g.BufferWithTolerance(15000,5,1) IF (@distance=’Driving’ or @distance=’Nearby’ or @distance=’City’ or @distance=’World’) and LEN(@latlong)>2
    begin
    SELECT @g = (geography:: Point(@Lat1,@Lon1 , 4326))
    if @distance=’Driving’
    begin
    set @g1=@g.BufferWithTolerance(15000,5,1)

    can u check this query wat i am using for this
    Query= ‘ and Position.STDistance(”’+@h1+”’)<1'

    I need to return spots in 10 miles radius circle … so i am using this one

    The query is fine working for nearby 2 miles radius the query is super fast .. when it comes for 10 miles driving it is slow i have spatial index and column type is geography

    wat is the best approach for tat solution .. to make it fast ….. the cases like in new york city the spots we have like 500000 with in 6 miles radius . when searching on that table its very slow ……… we have like 2 mill rows in that tables the query is using in fulltext where clause condition…………………………………………

  6. Hi,

    I was trying to execute below query but I don’t know from where I will get the Countries & IndiaGeoNames tables and their data to execute them.

    Please send the link to download data for these two tables.

  7. nice one..

    I”m create the Bing Map portal..I’m search the location. it’s point the location and show the Longitude & Latitude Value. This location store to the my database table. how to store the location & Longitude ,Latitude value ?

  8. Hi,
    I want to display a Point but in the form of a Small Image. For example, I have location of House (lat and Long) but I want to display it on the map not like a Point instead of a Small Image of a house. how can I do that? can you please help

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s