Map your Data – Part 4

Article
06/29/2008

In my last post, I introduced the SQL 2008 spatial data types and scratched the surface of their features and power. While those types work great within SQL 2008 (either in T-SQL or in SQL CLR methods), I haven’t yet found a way to consume these types from outside SQL 2008 (in fact, I don’t believe that the SQL 2008 team intends for us to do so.)

Fortunately, SQL 2008 emits geography/geometry data in the well known text (WKT) format (it isn’t even necessary to ToString(..) it), and that form is at least parsable on the client. Using ADO.NET, you can query SQL as per usual to get the WKT – then it’s just a matter of doing a little parsing to turn it into the appropriate object from the System.Windows.Shapes namespace.

I’ll post a full demo of all this at the end of the series, but to give you a sense of how this flows, a simple factory method like this:

public static void LoadBrickGeographies(KPIBricks kpiBricks)
{
if (kpiBricks == null) return;

SqlConnection cnx = new SqlConnection(Settings.Default.DBCnx);
SqlCommand cmd = new SqlCommand("select BrickID, GEOM from BrickGeometry", cnx);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
Dictionary<string, string> brickGeoms = new Dictionary<string, string>();

try
{
da.Fill(ds);

foreach (DataRow dr in ds.Tables[0].Rows)
{
string brickID = (dr["BrickID"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["BrickID"]).Trim();
string geom = (dr["GEOM"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["GEOM"]).Trim();

brickGeoms[brickID] = geom;
}

foreach (KPIBrick kpiBrick in kpiBricks)
{
kpiBrick.GeoPolygons = GeoPolygonFactory.CreateGeoPolygons(brickGeoms[kpiBrick.Id]);
}
}
catch (Exception ex) { Debug.WriteLine(ex.Message); }

return;
}

(yes I know LINQ would make this nicer, but SQL 2008 Feb CTP didn’t support LINQ to SQL.) From there, you’ll need to sift your way through the WKT, as these methods show:

public static List<GeoPolygon> CreateGeoPolygons(string geometry)
{
if (geometry == null) return null;
if (geometry == string.Empty) return null;

List<GeoPolygon> geoPolygons = new List<GeoPolygon>();

if (geometry.StartsWith("POLYGON"))
geoPolygons.Add(CreateGeoPolygonFromPolygon(geometry));
else if (geometry.StartsWith("MULTIPOLYGON"))
geoPolygons = CreateGeoPolygonsFromMultipolygon(geometry);
else if (geometry.StartsWith("GEOMETRYCOLLECTION"))
geoPolygons = CreateGeoPolygonsFromGeometryCollection(geometry);

return geoPolygons;
}

public static GeoPolygon CreateGeoPolygonFromPolygon(string polygon)
{
if (polygon == null) return null;
if (polygon == string.Empty) return null;

GeoPolygon geoPolygon = new GeoPolygon();
try
{
polygon = polygon.Substring("POLYGON ((".Length);
polygon = polygon.Substring(0, polygon.Length - "))".Length);

geoPolygon = ParsePolygonPoints(polygon);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
geoPolygon = null;
}

return geoPolygon;
}

private static GeoPolygon ParsePolygonPoints(string polygonPoints)
{
if (polygonPoints == null) return null;
if (polygonPoints == string.Empty) return null;

GeoPolygon geoPolygon = new GeoPolygon();

try
{
polygonPoints = polygonPoints.Split(')')[0];
string[] points = polygonPoints.Split(new string[] { ", " }, StringSplitOptions.None);

if (points == null) return null;
if (points.Length < 3) return null;

foreach (string point in points)
{
string[] coord = point.Split(' ');
LatLong latLong = new LatLong() { Latitude = Convert.ToDouble(coord[1]), Longitude = Convert.ToDouble(coord[0]) };
geoPolygon.GeoPoints.Add(latLong);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
geoPolygon = null;
}

return geoPolygon;
}

Those methods will turn the WKT string into a set of lat+long pairs. In my next post, I’ll show how to transform those into a System.Windows.Shapes.Polygon.

Map your Data – Part 4

Additional resources