October 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Using new location-aware data types in SQL Server 2008

  • September 21, 2009
  • By Jani Järvinen
  • Send Email »
  • More Articles »

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.





Page 3 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel