This is the 4th article in our “Bring the clouds together: Azure + Bing Maps” series. You can find a preview of live demonstration on http://sqlazurebingmap.cloudapp.net/. For a list of articles in the series, please refer to http://blogs.msdn.com/b/windows-azure-support/archive/2010/08/11/bring-the-clouds-together-azure-bing-maps.aspx.
The Plan My Travel sample stores the location information as spatial data. If you are already familiar with SQL Server spatial data, you can skip this post. We assume most developers are not quite familiar with spatial data yet. So we’ll briefly introduce it in this post.
What is spatial data
If you haven’t heard of the term spatial data, do not worry. It is simple. Spatial data is the data that represents geometry or geography information.
We have all learned from our geography lessons in middle school that to describe a location on the earth, latitude and longitude are used. In many cases, it is easy enough to store the latitude and longitude separately, using two columns in a database table.
However, there’re also a lot of scenarios which require more than that. For example, how to store the boundary of a country (which can be considered as a polyline)? How to calculate the distance between two points on the earth (line)? In addition, even if you’re working against a single location, it is often easier to consider the data as a point.
Traditionally, different solutions have been created for different applications. Each has its own advantages and disadvantages. But none is perfect, and multiple solutions make it difficult for different applications to interoperate with each other.
To solve the problems, an open standard must be embraced. Today the most widely adopted standard is OpenGIS created by Open Geospatial Consortium (OGC). Using OpenGIS, geometry and geography data can be represented in several ways, such as Well-known binary (WKB), Well-known text (WKT), and Geography Markup Language (GML).
WKB provides an efficient format to store spatial data. Since the data is stored in binary, it usually takes only a little space, and it is very easy for computers to perform calculations over the data.
In SQL Azure (as well as SQL Server and several popular third party databases such as Oracle and DB2), WKB is the internal presentation of the stored spatial data. This makes it easy to interoperate among different database providers.
However, WKB is not easy to read. It is machine friendly but not human friendly. We will not discuss the binary format in this article. You can always refer to the standards if you’re interested in. To make the data more human friendly, other formats have been developed.
WKT is much more human friendly compared to WKB. For example:
- POINT(2 3)
- LINESTRING(2 3, 7 8)
- POLYGON((2 3, 7 8, 3 5, 2 3))
As you can see, the data is self-descriptive.
However, since the data is represented by text, it is less efficient for computers to perform calculations, and it usually takes more space to store the data (each character requires at least 8 bits). Fortunately, SQL Azure provides build-in features to convert between WKB and WKT. So in most cases, the data is stored using WKB, while WKT is used to represent the data to human.
GML is another string representation of spatial data. It is usually lager than WKT, but it adopts another open standard: xml. Thus it is easier for programs to parse the data using xml libraries (such as LINQ to XML). We will not discuss GML in detail in this article. Please refer to http://en.wikipedia.org/wiki/Geography_Markup_Language if you’re interested in.
Working with spatial data in SQL Azure
Create table and column
SQL Server 2008 introduced two new data types for spatial data: Geometry and Geography. They’re fully supported in SQL Azure. To get started, please walk through the spatial data related tutorials in the SQL Server training course.
Spatial data types are just data types. So when creating a table column, you can use the data type just like nvarchar(50). As you’ve already seen in our last post, below is the Travel table:
[GeoLocation] [geography] NOT NULL,
[Time] [datetime] NOT NULL,
CONSTRAINT [PK_Travel] PRIMARY KEY CLUSTERED
[PartitionKey] ASC, [RowKey] ASC
CONSTRAINT [IX_Travel] UNIQUE NONCLUSTERED
Please note the type of the GeoLocation column is geography.
Construct the data
The data is stored in binary. However, you don’t need to understand the binary representation. You can construct the data using types defined in the Microsoft.SqlServer.Types.dll assembly. This is a SQL CLR assembly, which means it can be used in both managed languages and T-SQL.
For example, to create a geography object with a single point in C#:
SqlGeography sqlGeography = SqlGeography.Point(latitude, longitude, 4326);
In the above code, 4326 is reserved for geography data.
To create an object from WKT in T-SQL:
You can choose any programming language as you like. However, generally speaking, if the constructed object is for temporary usage (such as a temporary object used for calculating the distance between two locations), the code is usually executed from the application itself using a managed language without going through the database, especially if the database is on a cloud server instead of a local server.
If, however, the data needs to be stored in a database, a stored procedure written in T-SQL can help to increase performance. In addition, keep in mind that currently Entity Framework doesn’t support spatial data directly. So if you choose Entity Framework as the technology for your data access layer, you must create a stored procedure and invoke it from the EF code.
Below is the InsertIntoTravel stored procedure used in the Plan My Travel application. Pay attention to the STGeomFromText method:
CREATE PROCEDURE [dbo].[InsertIntoTravel]
Insert Into Travel
Values(@PartitionKey, NEWID(), @Place, Geography::STGeomFromText(@GeoLocation, 4326), @Time)
As discussed in our last post, the PartitionKey represents the user who creates this record. So we pass it as a parameter. The RowKey, however, is just a GUID, so we simply invoke NWEID to generate it automatically.
Now we can give the stored prodecure a quick test:
Pay attention to the GeoLocation parameter. Its value is a WKT.
Now query the table, and we can see the data is inserted succesfully.
Figure 1: Query the inserted data
Similarly, the UpdateTravel stored procedure:
CREATE PROCEDURE [dbo].[UpdateTravel]
Set [Place] = @Place,
[GeoLocation] = Geography::STGeomFromText(@GeoLocation, 4326),
[Time] = @Time
Where PartitionKey = @PartitionKey AND RowKey = @RowKey
And the DeleteFromTravel stored procedure:
CREATE PROCEDURE [dbo].[DeleteFromTravel]
Delete From Travel Where PartitionKey = @PartitionKey AND RowKey = @RowKey
For more information about working with spatial data, please refer to http://msdn.microsoft.com/en-us/library/bb933876.aspx.
This post introduced how to work with spatial data in SQL Azure. The same approach works for a normal SQL Server database as well (requires 2008 or later). The next post will discuss how to access data from an application.
CREATE TABLE [dbo].[Travel](
[PartitionKey] [nvarchar](200) NOT NULL,
[RowKey] [uniqueidentifier] NOT NULL,
[Place] [nvarchar](200) NOT NULL,