Map your Data – Part 3

Sorry I've been away from this, but time to get back at it.  There have been a number of highly positive developments on this topic in the interim - can't wait to share them with you.

In my first post in this series, I motivated why you might want to use WPF/SL to plot data on map.  In my second post, I showed how you could find the correct x+y pixel coordinate to plot a geocoded lat+long point on a canvas in WPF/SL.

I’ll get to the options available to render a map soon, but before that, I'd like to talk about the SQL Server 2008 geography and geometry types, and then show how they can be consumed by WPF and/or Silveright to produce descriptors of points, lines, polylines, polygons, and multipolygons.

SQL Server 2008 provides a rich hierarchy of CLR types for managing geometric and geographic data.  A great article to get up to speed on this is posted at, and I highly encourage you to read if you’d like to dig into the details.

To digest what you’ll find there, the SQL Server geometry and geography data types store POINT, LINE, POLYLINE, POLYGON, and MULTIPOLYGON data, and provide a powerful library of CLR methods to operate on them.  These types behave probably as you’d expect them to, and they’re compatible with both the well-known text (WKT) and well-known binary (WKB) formats prevalent in the geographic information system (GIS) world.

So a query like this:

select BrickID, GEOM.ToString() from BrickGeometry

would yield results like this:

BrickID    (No column name)

BRICK1      POLYGON ((2.6940779908110484 42.666860496270452, 2.793203946078807 42.654124350427637, 2.85332634315329 42.633159059335512, 2.9008365235573712 42.581074118386638, 2.9716402459158595 42.583045824823834, 3.039842535786792 42.592666827635938, 3.0597324537044734 42.534440550200827, 3.1366958440131323 42.51469895264924, 3.1531440016937893 42.457698080777575, 3.0934471081677009 42.423993806382946, 3.0379399142947454 42.465023034855911, 2.9620273068307781 42.464311134388815, 2.8989353331986742 42.455954877155612, 2.8271370987776692 42.450923287518776, 2.7681304264288853 42.411655280383563, 2.6915678888964303 42.409208618786508, 2.6561498112496791 42.368291247834385, 2.5823957030734119 42.353683250586059, 2.5081109899081762 42.335532161065913, 2.4410152019093529 42.368189899221477, 2.3815952334833472 42.399250179244547, 2.3135335336032017 42.423919667914681, 2.3810973824841688 42.446054890103312, 2.4267269997262222 42.477433712544922, 2.4841727096691146 42.502223398766027, 2.5478221659450648 42.5232805860582, 2.56617021653745 42.580614282835143, 2.5856662777039086 42.6274647832002, 2.6940779908110484 42.666860496270452))

where the stringified POLYGON contains a series of points that (in this case) correspond to longitude+latitude pairs.)

What makes the SQL 2008 spatial types special though is the set the methods the SQL team has provided for querying them.  If you look in the SQL 2008 books online, you’ll see a whole bunch of methods that make doing sophisticated spatial queries easy.  For example, to find out if a LINE intersects a POLYGON, you’d simply call the STIntersects(…) method on the LINE (which would be represented as a geometry/geography variable in T-SQL), passing in the POLYGON as the argument.  There are also methods for computing proximity (even around highly complex MULTIPOLYGONs), overlaps, and even unions of geometric/geographic objects.

The STUnion(…) method is highly useful if your geographic entities have a hierarchy.  To union bricks into territories, and then territories into districts, you’d simply need to build up a T-SQL script that looks something like this:

declare @TerritoryA geometry

declare @Brick1 geometry
select @Brick1 = GEOM from BrickGeometry where BrickId = ‘BRICK1’

select @TerritoryA = @Brick1

declare @Brick2 geometry
select @Brick2 = GEOM from BrickGeometry where BrickId = 'BRICK2'

select @TerritoryA = @TerritoryA.STUnion(@Brick2)

To fully appreciate the complexity of what just happened there, try unioning 2 arbitrary polygons in, say, WPF.  It’s a pretty heavy-duty piece of computation that SQL 2008 is providing, and it can make for some really heavy-duty analysis apps.

In my next post, I’ll discuss how to handle these from the WPF/SL client.


Comments (0)

Skip to main content