Geospatial data support in SQL Server

I wanted to do some research on geospatial data support in SQL Server after attending Refresh Colombo July meeting.

There Janith did a small presentation on how to finding a bus route in Colombo. Here is his blog explaining how he has done it. 

He has implemented the solution based on MYSQL database and I’m not quite sure about the database design used for the application.

This tended me to look at how we can implement such a thing in MS SQL Server and did some googling around the geospatial data support.

Geospatial support allows to store spatial data in SQL Server tables in the forms of points, lines, and polygons. It has set of rich functionalities which you can manipulate geospatial data stored in the database.

I created a small demo to explain on geospatial data support in SQL Server. Let’s assume you want to draw a triangle in SQL Server. (You can store it as well)

As per the above diagram, it has three coordinates. (X, Y) 

You need to declare a variable of geometry data type.

DECLARE @gtriangle geometry

Then use the LINESTRING object to set the coordinates of the triangle.

SET @gtriangle='LINESTRING(1 2, 3 5, 5 2, 1 2)'

Finally display the variable.

SELECT @gtriangle

See below diagram for the output.

Usually we see only two tabs in the SSMS output but this time it becomes three tabs with the additional one, "Spatial Results". Let's move to the second tab and see below;

To get more understanding on geospatial data, visit below sites. 

http://en.wikibooks.org/wiki/Geospatial_Data_in_SQL_Server
http://msdn.microsoft.com/en-us/library/bb895372.aspx
Spatial Data White Paper