Introduction
It was very easy to store location data and just to store the latitude and longitude of the location, by using simple floating point fields. Of course, it was not enough, which can just only achieve simple applications. However, if you want to create more advanced applications or do location of data points by using geometric algorithms, you must write the code by yourself. For example, how would you go on and find all stored locations within a 10 mile radius of a given spot? In such a situation, wouldn’t it be nice if the database itself could handle some of the work for you? This is what the latest release of SQL Server aims to do. With SQL Server 2008, you will have new built-in support for location based data types and supporting geospatial features. Next you will learn how these new data types work.
Two types, two models
SQL Server 2008 supports two similar but still distinct data types called geometry and geography. If simplifying things a bit, both are able to store x and y values and support a set of operations to do calculations based on these values. However, what separates these types from each other is that geometry is based on calculations on a straight surface (the “flat Earth” model), but geography does calculations based on the fact that Earth is round.
This distinction is important to remember, as for example taking the shortest route can be fundamentally different depending on whether you calculate Earth to be flat or round. This can easily be seen in shortest routes in inter- continental flights (Figure 1). In fact, SQL Server can base its calculations on dozens of different roundness and coordinate calculations. Such support is needed because traditionally different countries have had slightly different ways of calculating the shape of Earth. These differences are minute, but nonetheless can affect your results if you are not careful.
Figure 1. Which route is actually the shortest one?
SQL Server uses a notion of Spatial Reference Identifiers (abbreviated as SRID) which you must use when using either of the data types. However, for the simpler geometry data type, the SRID value is ignored, and can be set to zero. For the geography type, you have to explicitly set the SRID value. SQL Server prefers to use a coordinate system called WGS 84 (World Geodetic System 1984), which is assigned to the SRID magic value 4326, which itself is just an arbitrary number. A list of all the supported SRIDs can be found from the sys.spatial_reference_systems view in the Master database (Figure 2).
Figure 2. SQL Server supports many different SRIDs.
Nowadays, the importance of different coordinates has diminished, as the WGS 84 system is used globally in the GPS system (Global Positioning System). Thus most online mapping sites are based on the same reference system.
Back to school with Euclidean geometry
If you were at all awake during your math classes at school, you will surely recall the Euclidean geometry and the simple Cartesian coordinate system (Figure 3). In this system, the center, or origo, is at the coordinates (0,0) and in the classic positioning, the x axis runs from left to right and y bottom to top. In this coordinate system, distances are calculated the same no matter which unit is used: inches, meters and miles all give the same numeric results.
Figure 3. The classic Cartesian coordinate system
The SQL Server’s geometry data type sits well within this model. SQL Server implements the type internally as a .NET type, and thus you can directly use the same type in your own applications. The type is defined in the assembly Microsoft.SqlServer.Types.dll, which by default resides in the folder C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies.
As you want to put these new types into real use, take a look at the sample application shown in Figure 4. This application contains simple functionality: at the top, it can store points in a database, and at the bottom, it can plot those points on a map. It can also check whether a point is inside a polygon.
Figure 4. The sample application.
Let’s take the topmost button, which can store a geometry point in a database. To form storage for the point(s) in SQL Server, you would first need a suitable table to store the values in. This can be done with a very simple CREATE TABLE statement:
CREATE TABLE [dbo].[GeometryTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Points] [geometry] NOT NULL,
[Description] [nvarchar](50) NULL
)
With this table in place, you could use the following C# code to store a value into the field: using
Microsoft.SqlServer.Types;
...
private void geometryAddPointButton_Click(
object sender, EventArgs e)
{
int x = int.Parse(geometryXTextBox.Text);
int y = int.Parse(geometryYTextBox.Text);
SqlGeometry geom = SqlGeometry.Point(x, y, 0);
// add to database
string sql = "INSERT INTO [geometrytest] " +
"([points], [description]) VALUES " +
"(@points, @description)";
StorePointIntoDatabase(geom, sql, "geometry",
"My first geometry point");
}
First, the code reads the x and y coordinates from the user interface, and then constructs a SqlGeometry instance from the point specified by the x and y values. The SqlGeometry type lives in the Microsoft.SqlServer.Types namespace, which is in turn implemented in a similarly named assembly. Note how a point object can be constructed using the static Point method of the class. As the final step, a simple SQL insert statement is constructed. The actual database access is done in the method StorePointIntoDatabase, which in turn is implemented like this:
internal int StorePointIntoDatabase(
object geoPoint, string sql,
string udtTypeName, string description)
{
SqlConnection conn = GetConnection();
try
{
SqlCommand cmd = new SqlCommand(
sql, conn);
SqlParameter param = cmd.Parameters.
AddWithValue("@points", geoPoint);
param.UdtTypeName = udtTypeName;
cmd.Parameters.AddWithValue(
"@description", description);
conn.Open();
int rows = cmd.ExecuteNonQuery();
MessageBox.Show("Added " + rows +
" row(s) to the database.");
cmd.Dispose();
return rows;
}
finally
{
conn.Dispose();
}
}
If you have worked with SQL Server previously, the above code should look very familiar. First, the code opens a connection to the SQL Server database (its specifics are not important here), and then constructs a SqlCommand object with the given INSERT statement.
Note how the parameter value is specified with the Parameters.AddWithValue call. In the case of most other parameter types, the AddWithValue call alone is enough on its own. However, as the new geospatial types are implemented as UDF types (.NET types), the UdtTypeName property of the parameter object must be set. For the geometry type, it is simply “geometry”, and likewise for the geography type, it is “geography”.
Now that you are aware of how spatial data can be added to SQL Server 2008 database tables, you need to learn how to read those values back from the tables. For instance, assume you have the database table GeometryTest which was defined previously. This table has an id value, and you might need to retrieve the point given an id value of 3. In code, you could implement a method similar to the following:
internal SqlGeometry ReadPointFromDatabase(int id)
{
SqlConnection conn = GetConnection();
try
{
string sql = "SELECT [points] " +
"FROM [geometrytest] " +
"WHERE ([id] = @id)";
SqlCommand cmd = new SqlCommand(
sql, conn);
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
object geometryPoint = cmd.ExecuteScalar();
if ((geometryPoint != null) &&
(geometryPoint is SqlGeometry)) {
return (SqlGeometry)geometryPoint;
}
else return null;
}
finally
{
conn.Dispose();
}
}
Here, the implementation is very straightforward: you only need a SQL Server connection, a command object and a simple SELECT statement. Then, you simply set the parameter values and fetch the results.
Plotting points on a map
Now that you know how to read a point value pair back from the database, you can start doing something interesting with them. For instance, you might wish to point a geography latitude and longitude pair on a map. On the web, there are multiple mapping providers, for instance from Google, Microsoft and others.
If you take Microsoft’s solutions, then the Bing Maps offering is a possible choice. When Bing shows you maps, the images are created using URLs like the following: http://www.bing.com/maps/default.aspx?v=2&FORM=LMLTCC &cp=37.839179~-119.541904&style=r&lvl=8 Here, the query string parameter “cp” starts the latitude and longitude pair; in this case they would be 37.8 and -119.5, respectively. With this information in place, you could construct URLs with the following code:
int id = int.Parse(pointIdTextBox.Text);
SqlGeography geog = ReadPointFromDatabase(id);
string bingMapsFormat =
"http://www.bing.com/maps/default.aspx?" +
"v=2&FORM=LMLTCC&cp={0}~{1}&style=r&lvl=8";
string bingMapsUrl = string.Format(
bingMapsFormat, geog.Lat, geog.Long);
webBrowser1.Navigate(bingMapsUrl);
MessageBox.Show("Point " + geog.Lat +
", " + geog.Long + " plotted!");
Here, the ReadPointFromDatabase method is able to retrieve a SqlGeography object from the database given an id value (this could be any other query suitable for the situation). Next, the code constructs the necessary URL value, and then uses a simple WinForms WebBrowser component to display the web page showing the map (see again Figure 4).
Of course, a more sophisticated implementation would use an actual map control, and add for example pushpin or other similar objects on top of the map. However, for many simple applications, it is more than enough to be able to center the map on a certain point.
Calculations with polygons as an example
So far, we’ve discussed the geometry and geography types, but have only focused on single points. However, although points in a two-dimensional space can be very useful, wouldn’t it be great if you could also store lines or even polygons inside a single data field in your SQL Server tables? The good news is that you can!
That said, take for example a look at the SqlGeometry type. By using the static Point method, you can store a single point inside the object. But the same class also contains methods to create lines and polygons with three or more points (Figure 5). Each SqlGeometry object (or a SqlGeography object for that matter) can thus store one or more points, and these multiple points can form any objects you wish to present with those points.
Figure 5. Methods of the SqlGeometry class
For instance, assume you would need to store straight lines. This requires two points, the start and end. You could easily use two different database fields for the start and end points, but SQL Server 2008 allows you to store the two points inside a single geometry data type. You might start to think why it is useful to be able to store one or more points in a single table column. The answer is for one part in storage efficiency, but also in calculations. Yes, SQL Server and the corresponding .NET object types allow you to do calculations!
For example, you might have multiple points stored in your database which form a polygon, for instance a square. Then, you might need to determine whether another single point is inside or outside that square. Of course, there are simple algorithms to do this, and you could implement them directly on C#. But you can also let SQL Server do this for you.
SQL Server also supports more advanced features than just simple “points exists” tests. For instance, you can calculate areas, find the center location of an object, detect whether a polygon is closed or not, or find the number of points in a polygon. This gives you a lot of possibilities, especially since you can do these test either in code using the SqlGeometry and SqlGeography objects, or as functions inside a stored procedure, for instance.
But let’s return to a simple square and the need to detect whether a point is inside that square or not. Take for instance a square defined with the Cartesian coordinates ((0,0), (0,10), (10,10), (10,0)). Then, you would need to determine whether the point (5,5) is inside this square (Figure 6). How would you implement this kind of test using the SqlGeometry .NET class?
Figure 6. Is the point within the square?
First, you need to define your square. Just like with strings in .NET, you can use a StringBuilder component to build your strings. Similarly, you use a SqlGeometryBuilder object to build geometry objects. Here is an example of using the class:
SqlGeometryBuilder builder = new SqlGeometryBuilder();
builder.SetSrid(0);
builder.BeginGeometry(OpenGisGeometryType.Polygon);
builder.BeginFigure(0, 0);
builder.AddLine(0, 10);
builder.AddLine(10, 10);
builder.AddLine(10, 0);
builder.AddLine(0,0);
builder.EndFigure();
builder.EndGeometry();
Here, the SqlGeometryBuilder (part of the same Microsoft.SqlServer.Types namespace as the other objects) is used to construct the square with four points. However, since you can store almost an unlimited number of points even in multiple distinct objects, you must specify whenever a figure starts, and when it ends. Before this, you need to specify the spatial reference id (SRID) and also the type of object you wish to create using the BeginGeometry method call. The SRID must be specified, although its value is ignored. Thus, a value of zero will do nicely.
Now that you have the square defined, you need to detect whether the point is inside the square. This can be done using the STContains method of the SqlGeometry object. But first, you need to get an instance of the geometry object from the builder. This can be done using the ConstructedGeometry property. Here is an example:
SqlGeometry square = builder.ConstructedGeometry;
SqlGeometry point = SqlGeometry.Point(5, 5, 0);
SqlBoolean contains = square.STContains(point);
if (contains.Value)
{
MessageBox.Show(”Contains point!”);
}
As you can guess, the above code will display the message, as the point (5,5) is firmly inside the defined square. In fact, it is the center point.
Conclusion
The SQL Server release 2008 supports many interesting new features, and among them is support for spatial data. In this article, you learned about the geometry and geography types, and saw how these can be used in regular C# applications. For instance, you learned how data points can be read and written, and how you can utilize latitude and longitude values to show points on a map.
Although it has been previously possible to store for example latitudes and longitudes using simple float fields, SQL Server 2008 makes this much easier. Each geometry or geography data type in a table can store one of more points, and thus very complex polygons can be formed if need be. This support allows you to define only a single field in your database table, and store one or more points in it. This would be difficult using regular float fields.
Another benefit of the new special data types is the fact that you can do calculations based on these values. SQL Server 2008 contains nice support for different calculations, and guessing that the next SQL Server versions would further improve things, doesn’t seem far-fetched.
This article is from http://www.developer.com/db/article.php/3839056/Using-new-location-aware-data-types-in-SQL-Server-2008.htm
Share This
