Microsoft & .NET .NET Using new location-aware data types in SQL Server 2008

Using new location-aware data types in SQL Server 2008

Introduction


As affordable, GPS based devices start becoming commonplace in the consumer space, providing solutions that understand the importance of location becomes more and more important. Similarly, in a business setting, location can play a key role in logistics, manufacturing and support, to name a few.


Storing location data is, in its simplest form, a matter of storing the latitude and longitude of the location. Using simple floating point fields allows you to store this information, and for simple applications this can be enough. However, if you want to create more advanced applications, or use geometric algorithms to locate data points, then you must code the necessary routines yourself. For instance, 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.

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).

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 FilesMicrosoft SQL
Server100SDKAssemblies
.


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.





Click here for larger image

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.





Click here for larger image

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.


Happy spatial programming!

Jani Järvinen


About the Author


Jani Järvinen is a software development trainer and
consultant in Finland. He is a Microsoft C# MVP and a
frequent author and has published three books about software
development. He is the group leader of a Finnish software
development expert group at ITpro.fi and a board member of
the Finnish Visual Studio Team System User Group. His blog
can be found at http://www
.saunalahti.fi/janij/
. You can send him mail by clicking
on his name at the top of the article.

Latest Posts

Related Stories