Using new location-aware data types in SQL Server 2008, Page 2

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.

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.

Page 2 of 3