In this, the eighth part in a series on the new Spatial Data types in SQL Server 2008, I'll step away from the database and do a little spatial coding using .NET.
Redistributable .NET Library
Up to this point in the series, I have demonstrated a lot of interesting (?) things that you can do with the new Spatial data types (Geometry and Geography) in SQL Server 2008. You might be thinking, "That's swell, and all, but I wish I could do some of that stuff without needing to be tethered to a database." Well, you know what? You can!
I mentioned in a previous post that the Spatial data types were implemented as SQLCLR User-Defined Types. I've since been corrected by Isaac Kunen, who stated that they are more accurately described as System-Defined Types, with the difference being that these are automatically installed and available for use as part of SQL Server 2008, regardless of whether the ENABLE CLR bit has been activated. Semantics aside, these types are merely classes within a .NET assembly, and Microsoft is making this freely available as part of a Feature Pack for SQL Server (which will be redistributable as part of your stand-alone application, according to Isaac):
SQL Server 2008 RC0 Feature Pack Download Page
(Look for "Microsoft SQL Server System CLR Types," which includes the two Spatial types plus the HierarchyID type. This link is for RC0, and may not be applicable to future versions as the product is finalized and released.)
Builder API
A new feature that was included with the first Release Candidate (RC0) is the Builder API. This is a collection of interfaces and classes that helps you to construct spatial types by specifying one point at a time until all points have been added.
The Builder API is not only useful for creating new instances of spatial data, but also for consuming existing instances one point at a time (maybe to convert an instance into another format). Documentation is light at the moment, so I'm still trying to grok exactly how to best utilize it.
For my first experiment with the API, I obtained some Zip Code Boundary data in ASCII format from the U.S. Census Bureau:
http://www.census.gov/geo/www/cob/z52000.html#ascii
My goal was to parse the data, and then create a new SqlGeography instance for each zip code. (Note: SqlGeography is the .NET class name that T-SQL refers to simply as Geography). The SqlGeographyBuilder class proved to be perfect for accomplishing this task.
At its core, the SqlGeographyBuilder implements the IGeographySink interface. If you wanted to consume an existing SqlGeography instance, you could implement IGeographySink in your own class, and then invoke the SqlGeography's Populate() instance method, passing in your object as the parameter. The Populate() method takes care of calling the appropriate IGeographySink methods within your class.
In this case, I'm not starting with an existing SqlGeography instance, so my code will need to call the methods of the SqlGeographyBuilder in the correct order:
After EndGeography() has been invoked, the new instance is available via the ConstructedGeography property of the SqlGeographyBuilder class.
Simple enough, right? Yeah, I'm still a little lost myself... But, here's some code to help demonstrate what's going on!
First, let's look at the ASCII data. A single zip code's boundary might be defined as:
1469 -0.824662148292608E+02 0.413848583827499E+02
-0.824602851767940E+02 0.413864290595145E+02
-0.824610630000000E+02 0.413860590000000E+02
-0.824685900000000E+02 0.413841470000000E+02
-0.824686034536111E+02 0.413843846804627E+02
-0.824605990000000E+02 0.413863160000000E+02
-0.824602851767940E+02 0.413864290595145E+02
END
The very first line happens to contain an identifier (maps to a second file that lists the actual USPS zip code). The coordinate listed in the first line is not actually part of the boundary, but rather appears to be the population center of that area. The actual boundary begins with the second line, and continues until you encounter the "END". Also, in case you couldn't tell, coordinates in this data are in Longitude-Latitude order.
Since a Zip Code is a polygon, and since we are working with SqlGeography, we must be aware of ring ordering. That is, the exterior ring of a polygon must be defined in a counter-clockwise order so that as you "walk the ring", the interior is always to your left. If you reverse the order, then SqlGeography assumes that you're trying to define a polygon containing the entire world except for the small area inside of the polygon.
Well, in this case, the order of the points of the Zip Code boundary is defined in clockwise order... so, we must be aware of this and call into the SqlGeographyBuilder in the opposite order (so the last point defined in the ASCII data is the first point used while building our new instance).
To accomplish this, I simply parse the Lat/Long coordinates as "double" types, and then push them onto a stack. Then, I pop the stack and call into the Builder API with each point. At the end, I obtain the new SqlGeography instance from the ConstructedGeography property.
(Note: This is demonstrative code - some things should probably be cleaned up/refactored/error handled... You have been warned)
public SqlGeography ParseAsGeography(string zipcode_points)
{
StringReader sr = new StringReader(zipcode_points);
string line = sr.ReadLine();
Stack<double[]> Points = new Stack<double[]>();
while (line != null && line != "END")
{
if (line != String.Empty)
{
Points.Push(ParseLatLngValues(line));
}
line = sr.ReadLine();
}
return CreateGeography(Points);
}
private double[] ParseLatLngValues(string line)
{
// -0.838170700000000E+02 0.409367390000000E+02
double[] ret = new double[2];
string lng = System.Text.RegularExpressions.Regex
.Matches(line, "\\S+")[0].Value;
string lat = System.Text.RegularExpressions.Regex
.Matches(line, "\\S+")[1].Value;
double.TryParse(lat, out ret[0]);
double.TryParse(lng, out ret[1]);
return ret;
}
private SqlGeography CreateGeography(Stack<double[]> points)
{
SqlGeographyBuilder builder = new SqlGeographyBuilder();
builder.SetSrid(4326);
builder.BeginGeography(OpenGisGeographyType.Polygon);
double[] point = points.Pop();
builder.BeginFigure(point[0], point[1]);
while (points.Count > 0)
{
point = points.Pop();
builder.AddLine(point[0], point[1]);
}
builder.EndFigure();
builder.EndGeography();
return builder.ConstructedGeography;
}
SQL Server 2008: Spatial Data, Part 1
SQL Server 2008: Spatial Data, Part 2
SQL Server 2008: Spatial Data, Part 3
SQL Server 2008: Spatial Data, Part 4
SQL Server 2008: Spatial Data, Part 5
SQL Server 2008: Spatial Data, Part 6
SQL Server 2008: Spatial Data, Part 7
