Discussions on SQL Server Manageability and Development.

My last MSDN blog post, for a little while...

Olivier Meyer — Thu, 23 Sep 2010 23:37:58 GMT

I will be transitioning to a role outside Microsoft starting in October but intend to keep blogging in the future about topics of interest around software development, SQL Server spatial and other technology and entrepreneurship topics.

My new blog site will be: http://oliviersmeyer.wordpress.com

In the meantime, for those interested in SQL Server Spatial topics, please check out Ed's blog at: http://blogs.msdn.com/b/edkatibah/

Please keep in touch.

Sincerely,

Olivier

Cloud Computing and GeoSpatial forces unite in DC...

Olivier Meyer — Wed, 09 Jun 2010 17:23:07 GMT

I will be attending the www.com-geo.org conference in Washington DC in a couple weeks and it promises to be a pretty interesting conference, bringing together many of the major players in GeoSpatial (Public and Private Sector, ISVs, Academia). I'm attending to learn more about the state of cloud computing adoption in this space and also to hear about some pretty interesting uses of computing and spatial towards public policy problems.

If you will be in DC during that time and would like to discuss your own experiences and thoughts on cloud computing, please reach out and stop by, your time and feedback will be sincerely appreciated. You can also share your thoughts on Cloud Computing here if you will not be able to attend the conference.

Thank you,

Olivier

Calling all web and application developers: We need your help to make SQL Server better for developers.

Olivier Meyer — Tue, 30 Mar 2010 19:55:00 GMT

Good afternoon,

The SQL Server team is currently undertaking a short survey to learn more about the information you need from a database development and administration tool about your databases once they are "up and running".

Please take a few minutes to fill out the survey here.

Thank you for your help, your time and feedback will truly help make SQL Server a better product for web and application developers.

Sincerely,

Olivier

The Excel Spatial Spreadsheet (Part Deux)

Olivier Meyer — Tue, 29 Dec 2009 21:45:00 GMT

I hope everyone had a great holiday and that Santa was good to you.

My kids made out like troopers this year with the grandparents buying them a Wii. Yes I know I work for Microsoft -- but we already have an XBOX 360 – actually we have 2 of them but that’s another story. I probably should feel more guilty about having a Wii but I didn’t buy it myself… and… ok and its pretty fun to play… I mean show the kids how to use it. I just hope my friend Richard in the XBOX team is not reading my blog.

As for me, got some sweaters, some books and some gift cards which I used to buy the complete Rocky DVD collection and the complete Indiana Jones DVD collection.

All I want for Christmas is Part Deux of Excel Spatial

With the year quickly coming to a close, I realized I had limited time to blog a couple more times before 2010 started and that I owed everyone a follow up to my previous blog article on building Excel Add-Ins to turn Excel into a Spatial Spreadsheet.

In this somewhat lengthy follow on blog article I will revise and extend the COM automation add-in that was introduced in the previous article.

In the 3rd article I will introduce a second Excel add-in which will provide functionality for doing visualization of spatial data within the Excel spreadsheet.

When we last left our spatial add-in…

In the last article I mentioned that there were some annoying side effects to using the automated COM automation support from .NET. The main side effect was the exposure to COM automation of some of the inherited public methods from the Object base class (Equals, GetType, ToString). While this is not a catastrophic problem, it does introduce confusion for end users, since these methods have nothing to do with spatial analysis.

Refactoring the MySpatialFunctions Class

In order to hide from Excel the inherited methods from the Object base class we want to have finer control over what .NET will expose as the COM/Automation interface for our automation add-in.

(1) Define a new Interface

The first step to achieving finer grained control over the exposure of our spatial functions is to refactor the current MySpatialFunctions class into a a class that is derived from an Interface, let us call it IExcelSpatialFunctions.

   1: namespace ExcelSpatial

   2: {

   3:     public interface IExcelSpatialFunctions

   4:     {

   5:         bool INTERSECTS(string Shape1, string Shape2);

6:

   7:         // All other methods to be exposed to Excel

   8:    }

   9: }

(2) Decorate it with COM Attributes

Now we can decorate the IExcelSpatialFunction interface with some attributes from the System.Runtime.InteropServices and System.ComponentModel namespaces. These attributes will control how the interface will be exposed as a COM Automation interface for Excel.

The [ComVisible(True)] attribute is used to expose the IExcelSpatialFunctions interface as a COM interface and the [Guid()] attribute is used to specify a unique ID for this interface.

Note: The [Description()] attribute is used to provide additional help about the exposed functions and appears in the resulting type library for the COM component. Unfortunately that information will not be displayed by Excel (in the formula picker dialog for example) but I include it for completeness and the description should be visible to developers integrating with the COM automation add-in in VB or other COM enabled hosting development tools.

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Text;

   5: using System.Runtime.InteropServices;

   6: using System.ComponentModel;

7:

   8: namespace ExcelSpatial

   9: {

  10:     [ComVisible(true)]

  11:     [Guid("916F9116-1A08-4c09-8492-8E34C6EB9214")]

  12:     public interface IExcelSpatialFunctions

  13:     {

  14:         /// <summary>

15: /// Returns True if a geography instance intersects another.

  16:         /// </summary>

  17:         /// <param name="wktShape1"></param>

  18:         /// <param name="wktShape2"></param>

  19:         /// <returns></returns>

  20:         [Description("Returns True if a geography instance …")]

  21:         bool INTERSECTS(string Shape1, string Shape2);

22:

  23:         // All other methods we wish to expose to Excel.

  24:    }

  25: }

(3) Update our Spatial Function Class

We then update our original MySpatialFunctions class to derive from the interface. I also took the liberty to rename the class to ExcelSpatialFunctions.

The primary change from the previous implementation is to change the [ClassInterface()] attribute to ClassInterfaceType.None from the original ClassInterfaceType.AutoDual. We are being “explicit” about our exposed client interface with IExcelSpatialInterface as the interface to our class, so we don’t want an auto-generated COM interface for the class.

We also specify the Guid for the class using the [Guid()] attribute and the ProgId for the class using the [ProgId()] attribute. The ProgId is the human-readable string that will be used in Excel when selecting a formula category as well as the the name that will appear in the add automation add-in dialog box in Excel.

   1:  // ...

   2:  using System.ComponentModel;

   3:  using System.Runtime.InteropServices;

   4:  using Microsoft.SqlServer.Types;

5:

6:

   7:  namespace ExcelSpatial

   8:  {

   9:      [ComVisible(true)]

  10:      [Guid("F743643F-510D-416d-868D-92F5A5771101")]

  11:      [ProgId("ExcelSpatial.SpatialFunctions")]

  12:      [ClassInterface(ClassInterfaceType.None)]

  13:      [Description("Spatial add-in for Excel based on ….")]

  14:      public class SpatialFunctions :

  15:          IExcelSpatialFunctions

  16:      {

17:

  18:          #region IExcelSpatialFunctions Members

19:

  20:          /// <summary>

  21:          /// Returns True if a geography instance intersects another.

  22:          /// </summary>

  23:          /// <param name="wktShape1"></param>

  24:          /// <param name="wktShape2"></param>

  25:          /// <returns></returns>

  26:          [Description("Returns True if a geography instance...")]

  27:          public bool INTERSECTS(string Shape1, string Shape2)

  28:          {

  29:              bool bReturnValue = true;

30:

  31:              SqlGeography geoShape1 = Shape1.ToGeography();

  32:              SqlGeography geoShape2 = Shape2.ToGeography();

33:

  34:              bReturnValue = geoShape1.STIntersects(geoShape2).IsTrue;

35:

  36:              return bReturnValue;

  37:          }

38:

  39:          // All the other methods to expose to Excel

40:

  41:          #endregion

42:

  43:          // Other supporting code for the add-in

  44:  }

Note: For those familiar with the String class and looking at the above code, you may be asking yourself “I don’t remember there being a ToGeography() method on the string class.” Don’t worry, you’re not forgetting things, read on to next section…

(4) Updated COM registration methods

My previous blog post had those methods chopped off, so I’ve updated them and reposted them below. The following methods are called by VS (or by regasm.exe) to register your class within the registry to make it visible to COM Automation clients like Excel.

public class SpatialFunctions :

        IExcelSpatialFunctions,

   // ...

   #region COM / Automation Registration Methods

   [ComRegisterFunctionAttribute]

   public static void RegisterFunction(Type type)

      Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));

      RegistryKey key = Registry.ClassesRoot.OpenSubKey(

                GetSubKeyName(type, "InprocServer32"),

                true);

      key.SetValue("",

           System.Environment.SystemDirectory + @"\mscoree.dll",

           RegistryValueKind.String);

   [ComUnregisterFunctionAttribute]

   public static void UnregisterFunction(Type type)

       Registry.ClassesRoot.DeleteSubKey(

            GetSubKeyName(type, "Programmable"),

            false);

   private static string GetSubKeyName(Type type, string subKeyName)

       System.Text.StringBuilder s = new System.Text.StringBuilder();

       s.Append(@"CLSID\{");

       s.Append(type.GUID.ToString().ToUpper());

       s.Append(@"}\");

       s.Append(subKeyName);

       return s.ToString();

    #endregion

Cleaning up the plumbing a little bit

Making the string class cooler than it already is.

A large part of the Excel add-in is about handling textual representations of spatial data, converting them to SQL spatial types, performing calculations and then returning back an appropriate textual representation of the result.

It turns out there are several well adopted textual representations for spatial data. SQL Server’s type library supports two of the most common: WKT (well known text) and GML (geographical markup language). A third popular standard is KML which was created by Google (through an acquisition I believe). There are also some newcomers including GeoJSON.

It would be great if we build some of this “text handling” directly into the spatial types and the string class in an extensible manner. Ideally I would like to be able to do things like:

   1: String strSomePoint = "POINT(-8.6666 27.666)";

2:

   3: SqlGeography geo = strSomePoint.ToGeography();

4:

   5: strWKT = geo.ToWKT();

   6: strGML = geo.ToGML();

   7: strKML = geo.ToKML();

8:

   9: SqlGeography geo2 = strGML.ToGeography();

The SQL Spatial Types are sealed classes and we don’t easily have access to the source code for string to extend it, but that doesn’t mean you can’t extend them. You can use .NET Extension Methods to add new methods to existing classes.

Creating an .NET Extension Method

For those not familiar with creating a .NET extension method, here is the generic pattern for creating one:

   1:  public static class SomeClass

   2:  {

   3:      public static ReturnType methodname(this Type anInstance, ....)

   4:      {

   5:          // anInstance is the instance of Type that we can act on

   6:      }

   7:  }

Extending the string and SqlGeography types

In this case we are interested in extending the string type and the SqlGeography type. I chose to create a separate project as part of my solution to generate an assembly that hosts a single SpatialTextHandling class which uses .NET Extension methods to extend the String and the SqlGeography classes. This was completely optional, the extension methods could have easily been part of the main project.

This packaging allows these extension methods to be reused in other projects that leverage the SQL spatial types. In order to “consume” the extensions, you simply include the SpatialExtensions assembly in your project and include its namespace into your code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SqlServer.Types;

using System.Data.SqlTypes;

using System.Text.RegularExpressions;

using System.Xml.Serialization;

using System.IO;

namespace SpatialExtensions

    public static class SpatialTextHandling

        private static string FINGERPRINT_GML =

            "<?xml version=\"1.0\" encoding=\"utf-8\"?>";

        private static string FINGERPRINT_KML =

            "<?xml version=\"1.0\" encoding=\"utf-8\"?><kml ";

        private static string FINGERPRINT_GEOJSON = "[";

        /// <summary>

        /// Returns a SqlGeography object based on the text representation

        /// contained in the string, attempts to

        /// autodetect the text representation if hint not provided.

        /// </summary>

        /// <param name="p_strGeographyAsText"></param>

        /// <returns></returns>

        public static SqlGeography ToGeography(this string p_strGeographyAsText)

            SqlGeography geoShape = null;

try

                // Assume WKT as the default and exhaustively try

                // everything else if it fails.

                geoShape = SqlGeography.Parse(p_strGeographyAsText);

            catch (Exception)

                // Must not have been WKT, fall through and try

                // to check for other text formats

            if (null == geoShape)

                if (p_strGeographyAsText.StartsWith(FINGERPRINT_GEOJSON))

                    // TODO: Implement JSON Parsing

                    throw new ArgumentException("JSON not yet implemented.");

                else if (p_strGeographyAsText.StartsWith(FINGERPRINT_KML))

                    // TODO: Implement KML Parsing

                    throw new ArgumentException("KML not yet implemented.");

                else if (p_strGeographyAsText.StartsWith(FINGERPRINT_GML))

try

                        // XML Format, assume it is GML for now, at some point we'll need to support

                        // mutliple XML representations.

                        System.Xml.XmlTextReader xmlTextReader =

                          new System.Xml.XmlTextReader(p_strGeographyAsText,

                                        System.Xml.XmlNodeType.Document,

                                        null);

                        System.Data.SqlTypes.SqlXml xml =

                            new System.Data.SqlTypes.SqlXml(xmlTextReader);

                        geoShape = SqlGeography.GeomFromGml(xml, 4326);

                    catch (Exception xmlException)

                        // Problem parsing the GML encoding

            if (null == geoShape)

                throw new ArgumentException("Unsupported text for a shape.");

            return geoShape;

        /// <summary>

        /// Returns the WKT representation of a geography object.

        /// </summary>

        /// <param name="p_geography"></param>

        /// <returns></returns>

        public static string ToWKT(this SqlGeography p_geography)

            return p_geography.ToString();

        /// <summary>

        /// Returns the GML representation of a Geography object.

        /// </summary>

        /// <param name="p_geography"></param>

        /// <returns></returns>

        public static string ToGML(this SqlGeography p_geography)

            return p_geography.AsGml().Value.ToString();

        // Additional methods

You’ll notice the main extension method is defined on the string class and is called ToGeography(). The ToGeography() method attempts to auto-detect the text encoding or representation of the string and turns it into a new instance of a SqlGeography class.

 public bool INTERSECTS(string Shape1, string Shape2)

     bool bReturnValue = true;

     SqlGeography geoShape1 = Shape1.ToGeography();

     SqlGeography geoShape2 = Shape2.ToGeography();

     bReturnValue = geoShape1.STIntersects(geoShape2).IsTrue;

     return bReturnValue;

So in the INTERSECTS function above we can now pass in a string that is encoded in any of the supported textual representations, in fact Shape1 could be in WKT format while Shape2 is in GML format.

This becomes pretty powerful when you think about how users work with Excel, often combining data from different sources and copying and pasting data from different web sites and other spreadsheets. The ToGeography() extension method attempts to make it transparent to the end user.

Please note that this SpatialExtensions class is a work in progress with the current implementation supporting only WKT and GML. My goal is to eventually detect and automatically support KML, GeoJSON and GeoRSS.

The other methods in this SpatialExtensions class extend SqlGeography with convenience methods to return different text representations and as you’ll see below methods to handle specifying some constraints for the text representation.

Sometimes a picture is worth more than 32,767 characters

A textual representation is a really convenient representation to achieve interoperability between applications and to provide human-readable formats for end users, but in the case of complex spatial objects (detailed country borders, etc.) it can get quite lengthy.

Excel 2007 and earlier have a limitation on the number of characters that can be stored in an Excel cell and that limit is 32K characters. While this only affects complex shapes with lots of vertices, it would be nice if our add-in could somehow automatically make sure that results it returns are always represent-able as a string less than 32,000 characters long.

In order to reduce the complexity (# of a vertices) for a spatial object we can turn to the Reduce method on the SqlGeography type. The “trick” is to ensure that all our functions that return a string representation of a SqlGeography call Reduce if they are going to be “longer” than 32,000 characters.

I added some special versions of the ToWKT() and ToGML() extension methods to the SqlGeography class in the SpatialTextHandling class that allow the specification of a maximum string size. These functions are the same as the original version, but they make a call to a helper method called ReduceForTextRepresentation() to ensure the resulting string will be less than the maximum specified.

Caveat: I make no guarantees about the validity of the approach I use to “guess” at an appropriate value for the epsilon parameter to Reduce(). It seems to work relatively well for those cases I tested, but I’m sure the algorithm could be made more correct and any thought you have on this are appreciated.

namespace SpatialExtensions

    public static class SpatialTextHandling

        // .....

  public static string ToWKT(this SqlGeography p_geography,

                    int p_nMaxLength)

            // Assume all will be well

            string strGeography = p_geography.ToWKT();

            // Check to make sure the string is not too big.

            if (strGeography.Length > p_nMaxLength)

                // Reduce the shape as best we can to make it

                // fit in the string length specified.

                strGeography = ReduceForTextRepresentation(p_geography,

                   strGeography.Length,

     p_nMaxLength).ToWKT();

            // Return the string

            return strGeography;

        private static SqlGeography

            ReduceForTextRepresentation(SqlGeography p_geography,

                    int p_nCurrentStringLength,

                    int p_nStringTargetLength)

            // Take a best guess as to the right value for reduce by

            // looking at the "string length" per point in the shape,

            // estimating what the maximum # of points that is acceptable

            // should be, and then using the average distance/length per

            // point * the ratio of current points to needed points

            // as a basis for epsilon in the reduce formula.

            int nNumPoints = p_geography.STNumPoints().Value;

            double fShapeLengthPerPoint =

            p_geography.STLength().Value / nNumPoints;

            int nStringLengthPerPoint =

            p_nCurrentStringLength / nNumPoints;

            int nMaxPoints =

            p_nStringTargetLength / nStringLengthPerPoint;

            // how many new points to each original points must we have?

            double fOverByFactor =

            (float)nNumPoints / (float)nMaxPoints;

            // Not sure if this is "scientific" but tell the shape to reduce

            // so that we have a tolerance of n * the average length of line

            // segments in the shape.   It probably over compensates.

            double fReduceTolerance =

            ((fShapeLengthPerPoint / 2) * fOverByFactor);

            // Let's try it

            return (p_geography.Reduce(fReduceTolerance));

        // Additional methods

Implementing some Spatial Aggregate Functions

Up until now the functions I have shown take relative simple parameters, for example the INTERSECTS function takes two strings and returns a string. Sometimes it is useful for a method to act on a list of values or in the case of Excel, on a range of cells. One class of functions that could benefit from knowing how to handle a range of cells as a parameter would be aggregate functions.

Making the Add-in aware of Excel

To this point our add-in and the spatial methods we have created have been pretty generic and have not had to have any knowledge of Excel or the Excel Object Model. In order to accept parameters that represent ranges of cells and possibly return results that represent a list of values it becomes necessary to include some Excel namespaces and assemblies into our project.

You will need to add a reference to the Microsoft.Office.Interop.Excel assembly to your project and include its namespace in the ExcelSpatialFunctions class definition files.

using Excel = Microsoft.Office.Interop.Excel;

We can now start defining some functions that accept Excel cell ranges as parameters.

The example below will accept a range of cells containing various spatial objects (LINES, POINTS, etc.) and will return a GEOMETRYCOLLECTION that represents the UNION of all those shapes.

UNIONMANY function

First we add a UNIONMANY function declaration in the IExcelSpatialFunctions interface which will take a generic object for the cell range (we will cast that object to an Excel.Range in the implementation).

namespace ExcelSpatial

    [ComVisible(true)]

    [Guid("916F9116-1A08-4c09-8492-8E34C6EB9214")]

    public interface IExcelSpatialFunctions

...

        [Description("Returns an object representing the union of…..")]

        string UNIONMANY(object Range);

The implementation the UNIONMANY function consists of iterating through all the cells specified in the Range and repeatedly calling the union method exposed by the resulting SqlGeography of the previous iteration with the contents of the current cell.

As you can imagine, creating the UNION of many complex shapes can result in a very complex shape (lots of vertices), one whose textual representation may far exceed Excel’s cell content limit mentioned earlier in the article. We leverage the SpatialExtensions work from above to ensure that that UNIONMANY result will result in a shape that will fit in the 32,767 character limit.

[Description("Returns an object representing the union of two or more geography instances.")]

public string UNIONMANY(object Range)

    string wktUnionManyShape = "";

    SqlGeography geoUnionManyShape = null;

try

        // Get reference to the excel range object

        Excel.Range cellRange = (Excel.Range) Range;

        // How many cells are we dealing with

        int nRows = cellRange.Rows.Count;

        int nColumns = cellRange.Columns.Count;

        // Now go through all the remaining cells and keep "unioning" them.

        for (int nColumn = 0; nColumn < nColumns; nColumn++)

            for (int nRow = 0; nRow < nRows; nRow++)

try

                    // Look up the cell in question in Excel

                    Excel.Range currentCell =

                          (Excel.Range) cellRange.Cells[nRow+1, nColumn+1];

                    String wktCurrentShape =

                          currentCell.Value2.ToString();

                    SqlGeography geoCurrentShape =

                          wktCurrentShape.ToGeography();

                    if (null == geoUnionManyShape)

                        // This is the first shape in the union,

                        // so we just use it as is

                        geoUnionManyShape = geoCurrentShape;

                    else

                        // We need to perform the union of the current shape

                        // with the ongoing union shape

                        SqlGeography geoUnion =

                            geoUnionManyShape.STUnion(geoCurrentShape);

                        // Update the ongoing union for the next iteration

                        geoUnionManyShape = geoUnion;

                catch (Exception cellEx)

                    // Ignore bad stuff in conversion (union those that

                    // are valid, ignore those that are not)

                    System.Diagnostics.Trace.WriteLine(cellEx.ToString());

    catch(Exception ex)

        throw new ArgumentException("Expected geographies",ex);

    if (geoUnionManyShape != null)

        wktUnionManyShape =

          geoUnionManyShape.ToWKT(MAXIMUM_WKT_LENGTH);

    return wktUnionManyShape;

Some thoughts on Aggregates

There are some additional useful aggregate functions that could be implemented using a same approach (for example a LINESTRING constructor which uses the builder API to build a line-string from a set of points).

One set of functions that I am particularly interested in investigating in the future would be aggregate functions to perform clustering analysis on spatial data.

For example doing cluster analysis from customer location data to find best potential locations for new business branch offices or doing analysis on web traffic to find potential locations for data center load balancing or caching strategies.

Making the Add-in “super aware” of Excel

Up to this point Excel has been in the driver’s seat sort of speak. That is Excel “calls into” our add-in and for the most part we read what Excel gives us to perform some calculations.

It is also possible for our add-in to also take the driver’s seat from time to time and call into Excel itself. There are limited reasons to do this, so my recommendation is to use this carefully.

While I much prefer to make components know very little about each other, there are some cool things you can do if you know you are being called from Excel. For example populating the spreadsheet, formatting cells or even calling Excel provided functions.

Gaining Access to the Excel Application Object

When Excel loads an add-in as part of its startup, it will investigate if that add-in implements a special interface called Extensibility.IDTExtensibility2 (from the Extensibility assembly). If our class implements the IDTExtensibility2 interface, then Excel will call its methods upon loading and unloading our add-in. That provides us a great place in time to gain a reference to the Excel application object.

So we update the ExcelSpatialFunctions class to also derive from the IDTExtensibility2 interface and minimally implement the 5 methods it declares. The only method we are truly interested in is the OnConnection method which will provide us a reference to the Excel application object.

    public class SpatialFunctions :

        IExcelSpatialFunctions,

        Extensibility.IDTExtensibility2

        private Excel.Application m_excelApplication = null;

       . . .

        #region IDTExtensibility2 Members

        void Extensibility.IDTExtensibility2.OnConnection(object Application,

                Extensibility.ext_ConnectMode ConnectMode,

                object AddInInst,

                ref Array custom)

            // Keep a reference to the Application object from Excel

            m_excelApplication = (Excel.Application)Application;

        . . .

        #endregion

Finding out Who’s Calling?

One potential idea for leveraging our knowledge of Excel would be to somehow indicate through formatting that a particular cell is hosting a spatial function result. The trick is knowing which cell caused our function to be called since Excel does not pass in a reference to the calling cell (there is no “this” reference to the current cell or cell range).

It turns out you can in fact get that reference through the Excel application object and its method get_Caller. Once you have a reference to the cell or cell range you are free to do what you please to it. In addition to formatting, in the case of array formulas you can use the reference to infer the size of the return array. I also believe but have not yet tried it that this could be a method for achieving asynchronous calculations.

The following code is for a helper method to let you get a reference to the cell or cell range that caused the call to a function.

 private Excel.Range GetCallingCellRange()

    Excel.Range rangeCallingCells =

           (Excel.Range) m_excelApplication.get_Caller(System.Type.Missing);

    return rangeCallingCells;

Once you have access to the Excel application object you can navigate to other add-ins and make calls into them, you call built-in Excel functions and also do higher level order functions like printing and other Excel operations. A lot of power… use it wisely since your add-in could affect the user experience the end user expects from Excel.

So is spatial data special?

Turns out you already have a lot of it.

Most business users don’t start out with some ready made WKT encoded geospatial data sets. They typically start from a list of customers, accounts, sites, web logs or more specifically postal addresses and IP Addresses.

In order to make these add-ins truly approachable to all information workers we should add some ability to turn standard postal and IP address information into spatial objects, specifically POINTs.

Adding Geocoding to our Add-In

Luckily for us there are a variety of geocoding web services out on the “cloud” and Microsoft offers such a service for postal addresses through Bing Maps (the MapPoint Web Services and SDK).

Note: Using MapPoint web service requires a Bing Maps developer account, you can sign up for a developer account here. Signing up will provide you with the credentials (username, password) you will need to make calls against the web service.

I added a PARSEANDLOCATE function to the IExcelSpatialFunctions interface and to the ExcelSpatialFunctions class that accepts a string and expects that string to contain a postal address or partial postal address and returns a POINT spatial object with the latitude and longitude of the specified address, zip code or region.

Thankfully the MapPoint Web Service does the heavy lifting here as it can handle different combinations or permutations of addresses relatively well.

In order to access the web service you will need to sign up for a developer account (see above) and you will need to add a Web Reference to your project to point to the MapPoint web service URL (for example: http://staging.mappoint.net/standard-30/mappoint.wsdl) and get a proxy class to the web service.

The MapPoint Web Service SDK provides a Find service which is the service that provides geocoding, it expects an Address object as its parameter. The same service provides a helper method to turn a string address into an Address object.

using ExcelSpatial.MapPoint;    // reference to mappoint web service proxy

namespace ExcelSpatial

    // ...

    public class SpatialFunctions :

        IExcelSpatialFunctions

        // ...


        public string PARSEANDLOCATE(string FullAddress)

            Address parsedAddress = MapPointParseAddress(FullAddress);

            SqlGeography geoLocation = MapPointFindLatLon(parsedAddress);

            return geoLocation.ToWKT(MAXIMUM_WKT_LENGTH);

    #region MapPoint Web Service Helper Methods

        /// <summary>

        /// This helper method will return a POINT SqlGeography that

        /// is the LatLon of the address.

        /// </summary>

        /// <param name="p_mappointAddress"></param>

        /// <returns></returns>

        private SqlGeography MapPointFindLatLon(Address p_mappointAddress)

            SqlGeography geoPoint = null;

try

                // Create instance of the mappoint web service proxy

                FindServiceSoap findService = MapPointGetFindService();

                // Create the FindAddressSpecification

                FindAddressSpecification findAddressSpec =

                    new FindAddressSpecification();

                findAddressSpec.InputAddress = p_mappointAddress;

                findAddressSpec.DataSourceName = "MapPoint.NA";

                // For any addresses we find -- we want the LatLong returned

                FindOptions findOptions = new FindOptions();

                findOptions.ThresholdScore = 0;

                findOptions.ResultMask = FindResultMask.LatLongFlag;

                // Just get the 1st / best match

                FindRange findRange = new FindRange();

                findRange.StartIndex = 0;

                findRange.Count = 1;

                findOptions.Range = findRange;

                findAddressSpec.Options = findOptions;

                // Make the call to get any matching addresses (with

                FindResults findResults =

                findService.FindAddress(findAddressSpec);

                // Were there any matches?

                if (findResults.NumberFound > 0)

                    if (findResults.Results.Length > 0)

                        double lat =

                findResults.Results[0].FoundLocation.LatLong.Latitude;

                        double lon =

                findResults.Results[0].FoundLocation.LatLong.Longitude;

                        geoPoint =

                SqlGeography.Point(lat, lon, DEFAULT_SRID);

            catch (Exception ex)

                throw new Exception("There was a problem.", ex);

            return geoPoint;

        /// <summary>

        /// Helper method that wraps retrieving instance of web service

        /// </summary>

        /// <returns></returns>

        private FindServiceSoap MapPointGetFindService()

            // Create instance of the mappoint web service proxy

            FindServiceSoap findService = new FindServiceSoap();

            findService.Credentials = new NetworkCredential(

               Properties.ExcelSpatialResources.strVEUserName,

               Properties.ExcelSpatialResources.strVEPassword);

            return findService;

        /// <summary>

        /// Helper method that wraps call to the MapPoint web service

        /// </summary>

        /// <param name="p_strFullAddress"></param>

        /// <returns></returns>

        private Address MapPointParseAddress(string p_strFullAddress)

            FindServiceSoap findService = MapPointGetFindService();

            Address address = findService.ParseAddress(p_strFullAddress, null);

            return address;

        #endregion

What about that map you teased us with last time?

Text is great and as developers we tend to spend most of our day dealing with text in the form of code, but spatial data really lights up when you can visualize and interact with it using a map.

What would be really awesome (ok I’m a little biased here) would be the ability to embed a mapping control directly into the spreadsheet and to link the map control’s rendering to ranges of cells and formulas in the spreadsheet.

That would indeed be cool! (again I’m a little biased here), maybe it could look something like this:

In the next set of blog articles we will develop a second Excel add-in that will show one approach for achieving that result.

I’ll then post a final article in this series about creating an installer to install both add-ins as this can be a particular challenge when combining different types of add-ins into a single installer.

I am hoping to post the complete code for both Excel add-ins on CodePlex early next year so folks can download and extend the project, but I first need to clean up the code a little bit.

I look forward to hearing about your own experiments with the SQL Server spatial types and the Excel Spatial Spreadsheet example.

Sincerely,

Olivier

PDC 2009 Session Recording – Building Location-Aware Application with SQL Server Spatial Library is now posted.

Olivier Meyer — Fri, 20 Nov 2009 19:03:02 GMT

You can find a recording of the session Ed, Torsten and I gave at PDC on building location-aware applications using the SQL Server Spatial Library here:

http://microsoftpdc.com/Sessions/SVR33

Have a great weekend,

Olivier

Introducing the Excel Spatial Spreadsheet – Building an Excel Add-In for doing Spatial Analysis and Spatial Operations in .NET.

Olivier Meyer — Tue, 17 Nov 2009 04:53:00 GMT

Good Evening,

I sit down to write this blog article on the plane on my way to LA in preparation for tomorrow’s presentation at PDC on the same subject. In my last article I described the ability to leverage the hard work that the SQL Server team put into building a spatial library for your own .NET applications. In this article I will continue to describe how the SQL Server Spatial Library can be leveraged in many different types of applications, and in this case for use with Microsoft Office, and more specifically Microsoft Excel.

It will likely take a couple blog articles to cover the entire scope of this project, but this first article will show all the necessary steps to get the base functionality built and we will expand on it over the coming two articles.

Let’s get started building the ES2 (Excel Spatial Spreadsheet) add-in…

Pre-Requisites

In order to follow along you will need Visual Studio 2008, Microsoft Excel 2007 and you will also need to download the SQL Server Option Pack which contains the SQL Server types assemblies (or Install SQL Server 2008 on your development machine as it also includes the same assemblies).

Office Add-In Technologies

Microsoft Office offers a wide variety of options for developers to extend the core office suite. There is of course the built in Visual Basic for Applications which allows developers to build applications hosted by Office. You can also build different kinds of add-ins that plug into Office. Some add-ins are more “GUI” oriented, allowing you to add task panes, new ribbons while others focus on providing more “backend” services like new formulas for Excel.

There are also a few options for building add-ins for office. Visual Studio includes the VSTO project types and you can also go with the more traditional and complex native extensions written in C / C++.

What the heck is a Spatial Function for Excel?

For this blog article, our focus is to build new spatial operators that can be used within Excel as if they were native Excel functions. We want to enable an Excel User (Information Worker) who wants to find out the area of a state or zip code to type in something like:

=AREA(ZipCodeShape)

or to find out if a customer is located within a kilometer of a store location:

=IF(DISTANCE(CustomerLocation, StoreLocation)<1000,”YES”,”NO”)

it would also be nice if an Excel user could easily turn a user’s existing data like addresses into spatial objects for analysis by calling out to web services offered by Bing maps for example:

=PARSEANDLOCATE(“One Microsoft Way, Redmond WA”)

In fact we’d like to enable someone who is not an expert in spatial analysis to just “try and learn” spatial operators quickly and painlessly, which is exactly what Microsoft Excel enables (you can always Undo and try out different scenarios by pasting a new formula in a different cell).

We want “The Power of SQL Spatial with the Simplicity of Excel”

To achieve that goal I’ll be leveraging a type of add-in called an Automation Add-In. For those who remember the days of COM / DCOM programming (yes I’m that old) and terms like late binding, IDispatch and automation interfaces then you’ll be right at home, for those who were able to skip that time period, don’t stress out, .NET makes it pretty easy to create COM Automation classes.

Implementing the base Spatial Automation Add-In

Start Visual Studio 2008 or 2010 as Administrator (more on this later) and create a new .NET 3.5 C# Class Library Project and name it something interesting like MySpatialFunctions.

Once the newly created project is loaded in Visual Studio, right click on the project to bring up the project properties dialog.

Click the [Assembly…] button on the Application Tab of the project properties and make sure the “Make Assembly COM-Visible” is checked.

We also need to ensure that the “Register for COM Interop” on the Build tab is checked. This step tells Visual Studio to register any COM components in the project every time the project is rebuilt. The alternative is to manually register the assembly using the regasm.exe .NET utility.

Now rename the default class name to something a little nicer than Class1, for example MySpatialFunctions. Please note that the name you pick for the class will show up in Excel’s drop down list of functions as the Category name for your functions (example shown below):

Following the instructions from the previous article we need to add a reference to the SQL Server Type library which includes the Spatial data types. Right click on the references folder in your newly created project and add a reference to the SQL Server Type assembly.

You will also need to add:

using Microsoft.SqlServer.Types;

to the beginning of your class file to bring in the .NET definitions for the spatial data types.

Now let’s make sure your class can act as a COM object by bringing in the .NET COM interop support and decorating your class with the necessary attributes [ClassInterface(ClassInterfaceType.AutoDual)]. Without getting too deep into COM terminology, this tells .NET to expose our class as supporting both COM late-binding (IDispatch / Automation) and early binding clients.

Your class file should now look something like the following:

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Text;

5:

   6:  using Microsoft.SqlServer.Types;

   7:  using System.Runtime.InteropServices;

   8:  using Microsoft.Win32;

9:

  10:  namespace MySpatialFunctions

  11:  {

  12:      [ClassInterface(ClassInterfaceType.AutoDual)]

  13:      public class MySpatialFunctions

  14:      {

15:

  16:          [ComRegisterFunctionAttribute]

  17:          public static void RegisterFunction(System.Type t)

  18:          {

  19:              Microsoft.Win32.Registry.ClassesRoot.CreateSubKey

  20:                  ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");

  21:          }

22:

  23:          [ComUnregisterFunctionAttribute]

  24:          public static void UnregisterFunction(System.Type t)

  25:          {

  26:              Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey

  27:                  ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");

  28:          }

  29:      }

  30:  }

The above code will expose your C# class as a COM automation class and will expose all public methods as automation callable methods. The RegisterFunction, decorated [ComRegisterFunctionAttribute] is called by Visual Studio as part of building the project. These functions are used register the MySpatialFunctions class with the Windows Registry. You can also use the .NET regasm.exe utility to achieve same result.

Now that the plumbing is in place, let’s implement the SPATIALAREA function which will take in the description of a Geography object in WKT (well known text) format from Excel and return the area of that geographic object. WKT is really just text or a string, so the input parameter to the function will be a String and the COM interop between Excel and our automation add-in will make sure that whatever Excel is passing in will appear to us as a .NET String. We will return the area as a double.

   1:  namespace MySpatialFunctions

   2:  {

   3:      [ClassInterface(ClassInterfaceType.AutoDual)]

   4:      public class MySpatialFunctions

   5:      {

   6:          public double SPATIALAREA(String Shape)

   7:          {

   8:              double fReturnValue = -1;

9:

  10:              // Convert the String representation of

  11:              // geographic object into a SqlGeography

  12:              SqlGeography geo = SqlGeography.Parse(Shape);

13:

  14:              // We now have a valid Geography, let's call

  15:              // it's area function and convert the results

  16:              // from SqlDouble to .NET Double.

  17:              fReturnValue = geo.STArea().Value;

18:

  19:              // Return the results

  20:              return fReturnValue;

  21:          }

22:

  23:          // ....

  24:      }

  25:  }

The above code receives a String from Excel and converts the string representation into a SqlGeography object using using the class method Parse() which expects a WKT encoded string. Once the string is successfully parsed, we call the OGC standard STArea() method to return the shape’s area in square meters. The SqlGeography and SqlGeometry objects return SQL types (SqlDouble, etc.), so we use the .Value method to return the underlying double value. The area is then returned to Excel as a double.

I’ll stop with this one function for now to test that we have all the necessary pieces in place, but you could easily add additional public functions that expose additional methods from the SqlGeography class. For example you could add a SPATIALDISTANCE and a SPATIALINTERSECTS method to return the distance between two spatial objects or to test if two spatial objects intersect. I’ll in fact do that in the next blog article to demonstrate some more advanced Excel methods that operate on ranges of cells.

Now Build the Project (F5). This will compile the class assembly and register it as a COM class library. You need to run Visual Studio as an administrator in order for Visual Studio to correctly register the library as a COM Class Library. When the add-in is packaged for distribution we can create an installer that will take care of registration, for local development and small distributions, it is very convenient to have VS do the registration. Trust me… COM registration setup programs are an acquired taste and can be painful to get right.

Once the project is built and the assembly registered with Windows we can switch gears and launch Excel to bring in the new automation add-in we just created.

Manually Enabling the Automation Add-In within Excel 2007

Lanch Excel 2007 and click on the Office button (upper left round button):

Select [Excel Options…] on the bottom right of the dialog box and from the Options dialog box, select the [Add-Ins] tab.

Choose Manage: Excel Add-ins and click [Go…], on the dialog box that follows click [Automation….] and find your class MySpatialFunctions.MySpatialFunctions in the list, select it and click OK. This will register your automation add-in with Excel and make its functions available to Excel user as Formulas.

Let’s now make sure the method is available within Excel. In Excel, select a cell and click the Formula button (Fx) and in the Category drop down list, find your MySpatialFunctions.

You have now completed the basic process for registering a custom automation add-in with Excel. In the next blog article I will show how to automate this process using a setup program for deployment to end-users.

This is really just a glimpse into what is feasible in terms of spatial analysis now that this plumbing is in place.

The next blog post will include example of functionality to operate over a range of cells. These aggregate functions can act on multiple geographic shapes, and include UNION, INTERSECTION and a function to calculate the CONVEXHULL of a shape, etc.

Testing the base Spatial Add-In

So we’ve built an Add-In that currently supports one spatial function, the SPATIALAREA function, which when given a WKT encoded string representing a shape will return its area. So how do you get a set of WKT data to try this out? WKT is a standard format for geospatial data and there are different tools and data sources on the internet that let you convert data to / from WKT. Another such text representation is called GML and it is relatively easy to modify our Add-In to support both formats (and auto-detect what format).

For now here is an example WKT object, which represents the country of Morocco, all the pairs of numbers are Latitude and Longitude values which form the vertices of the country’s border:

POLYGON ((-8.6666678252256713 27.666664129182578, -8.667222995748233 28.709442230680754, -7.6447228335782063 29.38499825838333, -7.4338894686020938 29.397220532368337, -7.1238890576883476 29.636943787478454, -6.5858335012886569 29.567218718351096, -6.400278105924599 29.804443265276372, -5.5383339200815715 29.902496352581256, -5.2758341886801494 30.055274928261763, -4.9205561051119027 30.508052873828575, -4.396945078096711 30.648330706130608, -4.0169448364146341 30.911109819241169, -3.6266669275411774 30.970554415405132, -3.6022224394530782 31.095554457582143, -3.8261113959581103 31.163887059367184, -3.8183335039199489 31.695552783902336, -3.5116668926113768 31.673332210341314, -2.9994444009703307 31.833331997926884, -2.8538890815261464 32.088333074020561, -2.3311115158732578 32.157493608115495, -1.1805555950618838 32.110549872177238, -1.2958334558623108 32.163055394625928, -1.2497221362066235 32.326942482097934, -1.0102780535866551 32.50833136699751, -1.3827780560041652 32.724441516585529, -1.5424999126467027 32.9394379321343, -1.4802780647792078 33.063049307516593, -1.6680558432224473 33.261108396767547, -1.6002779231388138 33.556938179949341, -1.730555759883178 33.70499415941044, -1.6544443650187513 34.083610597469317, -1.7933334168756687 34.378326347728262, -1.6867106306540376 34.485511790735742, -1.8575001956524209 34.6102751699483, -1.7472224616645127 34.7472152758781, -2.209444613630581 35.085830782093907, -2.6350001107359389 35.099716151314666, -2.8855556899671657 35.242775016942396, -2.7727781165503669 35.12344354594692, -2.8416665727057948 35.126937801752014, -2.9147223394202406 35.273605397275951, -2.946944586102064 35.329162557540613, -2.986111147028685 35.418052595660889, -3.0663889907443789 35.28944394203873, -3.33250047654772 35.191383412856624, -3.6591668811445603 35.2727737647203, -4.3538894581048853 35.146110476744205, -4.6958342727246034 35.208885297255144, -5.25305545748645 35.576942396678376, -5.345833746377215 35.841659555457895, -5.3955573480761734 35.916336164243809, -5.9187442140904558 35.790649477730661, -6.3152781038228216 34.834999113878069, -6.8430557148205375 34.018608010752295, -7.4966669134759023 33.648330689833216, -8.5383338847421175 33.250549332499993, -9.27639013723884 32.558326726694006, -9.2811126007121647 32.175552453361512, -9.6791668736386267 31.708610438323728, -9.8252793032591743 31.3849982174836, -9.8402786969497935 30.628051715409157, -9.611946167924998 30.412777042428619, -9.64166834548642 30.161941508800595, -10.242500183623481 29.302219297167209, -10.606668533428026 28.966110252659604, -11.081111961457841 28.738609209711591, -11.509445127963096 28.304996469296981, -12.065833980717276 28.083053697361194, -12.902500271008393 27.954166414933635, -13.174961157838167 27.666957939188453, -8.6666678252256713 27.666664129182578))

You can copy the above text into a Cell within a blank Excel Spreadsheet, for example copy the text into cell A1. You can “truncate” the text by putting a space / blank in cell B2.

Now let’s try out our fancy SPATIALAREA function by typing =SPATIALAREA(A1) in cell A2 and pressing Enter.

And we should get the answer in square meters, format the cell to show 2 decimal places (unless you really like scientific notation):

Achieving A More Perfect Spatial Add-In

One of the issues with using the simple approach (Auto Dual Class Interface) to create and automation add-in is that some of the non-spatial related base Object methods are then exposed to Excel as callable methods and visible in the Function Picker in Excel. You can see some of those unintended functions above as “Equals”, “GetType” and “ToString”

It would be preferable from a user experience and ease of use perspective if we could hide those methods and only expose spatial methods within Excel. In order to achieve finer control over the exposed methods it is necessary to get a tiny bit more involved with the COM interop support in .NET.

With some refactoring we can in fact hide those base Object public methods and have finer control over the methods that are exposed to Excel.

I will cover this in detail in the next blog article, but here is the approach:

1. Define an Inteface in C# that represents all the methods to be exposed to Excel by the automation class.

Decorate that interface with [ComVisible(true)] attribute and specify a unique Guid for that interface [Guid(“unique-guid-here”)].

2. Modify the MyFunctions class:

o Derive / implements interface from step #1.

o Add [ComVisible(true)] decoration to the class

o Add [Guid(“unique-guid-for-the-class”)] decoration to the class.

o Add [ProgId(“MySpatialFunctions.MySpatialFunctions”)]

o Modify [ClassInterface] Attribute to reflect [ClassInterface(“ClassInterfaceType.None”)]

Modify the RegisterFunction and UnRegisterFunction to reflect both the Interface and the Class.

In addition to “cleaning” up some of the unintended methods from above, It would also useful to get a reference to the Excel application to enable more communication back and forth between the add-in and Excel. You can get a reference to the Excel Application object by implementing the Extensibility.IDTExtensibility2 interface as part of your class. Once you have access to the Excel Application object, your add-in can actually start communicating and controlling Excel, automatically formatting cells for example that include a spatial formula.

What’s Next?

In the next article I will build on this base add-in and expand on the functions it exposes as well as describe the process of building a VSTO add-in to provide a Spatial Ribbon within Excel. I’ll also cover how to build some spatial UI componentry for an Excel spreadsheet to allow the embedding of a visualizing / mapping spatial control within a spreadsheet. Finally I also plan to cover the creation of a setup project to install your add-ins (VSTO and Automation) on end-user workstations.

So some cool stuff is coming. Here is a small teaser to show you what you can expect:

Hope to see you at PDC tomorrow and let me know if you find this Excel add-in helpful in learning the SQL Spatial operators and in performing simple spatial analysis without having to leave Excel.

Sincerely,

Olivier

PDC 2009 is next week - I'll be Building Location Aware Applications with the SQL Server Spatial Library at Session SVR33 - Tuesday

Olivier Meyer — Fri, 13 Nov 2009 16:47:00 GMT

I will be presenting at PDC next week with Ed Katibah and Torsten Grabs on uses of the SQL Server Spatial library (SQLTypes). The session will be on Tuesday at 12:30pm.

Title:
Building Location Aware Applications with the SQL Server Spatial Library

Abstract:
Did you know that the new SQL Server 2008 Spatial Types are available in a redistributable library? This library can be used to build interesting location aware functionality in applications that don’t even connect to a database. See us build an Excel Add-In for spatial analysis and an application that demonstrates how to spatially-enable the new StreamInsight product. Hear about what the Spatial Library delivers today and what is coming in future releases.

The session will largely be demos and code walkthroughs with minimal powerpoint slides.

Please stop by if you are attending PDC and share your experiences building location-aware applications using SQL Server 2008.

Olivier

Watch out for gotcha with missing VC++ runtime dlls when deploying .NET applications that leverage the new SQL Server Spatial Types libraries.

Olivier Meyer — Thu, 09 Jul 2009 00:56:00 GMT

In my previous blog post I discussed the ability to leverage the SQL Server type library in your own .NET applications. The article showed how to create a simple console application that calculates some areas for different shapes and how to create these shapes either from a text representation like WKT or “by hand” using the builder classes in the spatial library.

I did not spend much time on discussing the details of deploying your spatially-enabled .NET application to other machines (client or server) since I expected it to be like any other .NET application: Just copy the EXE along with any dependent assemblies to the target machine and run, of if motivated, create a simple setup project, or possibly use Publish to create a click-once installer.

The best laid plans of…

It turns out that deployment of a spatially-enabled .NET application onto a “clean” target machine has a potential *gotcha*.

Taking the example of the simple .NET console application from the previous blog, there are several expected pre-requisites necessary on the target machine in order for the application to run successfully:

.NET Framework: that one is pretty obvious given my console application was a .NET application.
SQL Server Types: Running this installer will install and register the SQL Types assembly in the GAC. Alternatively you could place the SQL types assembly as a side-by-side assembly to your application.

It turns out that you will also need to make sure the Visual C++ 2008 C runtime libraries are also installed on that target machine.

The spatial data types make use of both managed code (.NET) and native code to speed up spatial calculations and as a result the spatial types make calls to some C runtime library functions.

If these C runtime libraries are not installed on your target machine you will get the following exception when you run your application:

Unable to load DLL 'SqlServerSpatial.dll': This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. (Exception from HRESULT: 0x800736B1)

You can find the 32bit version of the VC++ redistributables here and the 64bit version here. Run the appropriate version for your target environment and that should resolve the problem.

Alternatively build an installer for your application that installs the C runtime libraries as part of the installation of your application (note that I did not try this, so let me know if you run into any problems).

You can read more about this scenario on the Microsoft support site here.

Happy coding.

Olivier

Why I should have paid more attention in geometry class: Embedding the new SQL Server Spatial CLR types and functionality directly into your own .NET applications.

Olivier Meyer — Mon, 22 Jun 2009 21:41:00 GMT

In SQL Server 2008 we introduced the ability to store spatial objects as columns within a database through the Geometry and Geography types as well as the ability to perform efficient query operations against those columns through the use of a new Spatial index.

These data types were implemented as CLR (.NET) types hosted by SQL Server and a great benefit of this approach is that enabled the SQL team to make these same .NET assemblies are available for download and distribution “outside of SQL Server” for use in any .NET application.

You can download the SQL CLR Types here.

Once you have downloaded and installed the SQL types on your development machine, you can make them available to your .NET applications by adding a reference to the assembly within Visual Studio your project.

Let’s try it with a simple “hello world” console application that will create a couple of simple geometric shapes and calculate some simple geometric operations like area and intersections.

From Visual Studio, select File->New Project and select the Console Application project type.

Once your new project is loaded, you will need to add a reference to the SQL Server types assembly. Right click on the References node in your Solution Explorer and Choose Add New Reference.

Find the Microsoft.SqlServer.Types .NET component and add it to your project.

Now that the project is set up to use the Spatial data types, let’s try out a simple set of spatial operations.

   1:  using Microsoft.SqlServer.Types;

2:

   3:  class Program

   4:  {

   5:      static void Main(string[] args)

   6:      {

   7:          // Build a geometry object from Well Known TextSqlGeometry

   8:           a = SqlGeometry.Parse("POLYGON ((0 0, 10 0, 10 10, 0 0))");

9:

  10:          // Calculate and display its area

  11:          System.Console.WriteLine("Area of A = " + a.STArea().Value.ToString());

12:

  13:          // Create a buffered version of the original

  14:          SqlGeometry b = a.STBuffer(1);

15:

  16:          // Calculate and display its area

  17:          System.Console.WriteLine("Area of B = " + b.STArea().Value.ToString());

18:

  19:          // Create the shape that represents the difference of the two shapes

  20:          SqlGeometry c = b.STDifference(a);

21:

  22:          // Show its area

  23:          System.Console.WriteLine("Area of B-A = " + c.STArea().Value.ToString());Console.ReadLine();

  24:      }

  25:  }

We start by creating a very simple shape, a triangle (line 8) by converting a text representation of the shape into a SqlGeometry object. The text representation used by the SQL Spatial type library is WKT (well known text).

Next we buffer the triangle (line 14) by one unit all around, which means “grow” the shape around its border by one unit. Buffering can be useful when used in conjunction with other operators to find out if shapes border each other or to create more complex shapes that represent shapes with holes.

Now we create a more complex shape, by geometrically “subtracting” the original triangle from the buffered triangle (line 20), resulting in a shape that represents the one unit border around the original shape.

We can now verify that these operations resulted in shapes that are in fact complements of each other by calculating the areas of the individual shapes and comparing the area of the complex border shape with the areas of the original triangle and its buffered version (lines 11, 17 and 23).

A question you may be asking is can one start from a non-textual representation of a shape? Can I build the shapes in code?

Yes you can in fact create shapes “by hand” using builder objects (SqlGeometryBuilder and SqlGeographyBuilder) exposed by both the SqlGeometry and the SqlGeography data types.

Here is a simple example that builds a triangle by hand:

   1:     class Program

   2:      {

   3:          static void Main(string[] args)

   4:          {

   5:              // Create instance of a geometry builder

   6:              SqlGeometryBuilder geoBuilder = new SqlGeometryBuilder();

7:

   8:              // Spatial objects have a reference ID / reference system

   9:              // which identifies the type of projection (or lack of)

  10:              // used, for example WGS84 - SRID = 4326

  11:              geoBuilder.SetSrid(4326);

12:

  13:              // Building a simple polygon

  14:              geoBuilder.BeginGeometry(OpenGisGeometryType.Polygon);

15:

  16:              // Start the shape at 0,0 and add each line segment for the

  17:              // triangle (0,0 - 0,10) - (0,10 - 10,10) - (10,10 - 0,0)

  18:              geoBuilder.BeginFigure(0, 0);

  19:              geoBuilder.AddLine(0, 10);

  20:              geoBuilder.AddLine(10, 10);

  21:              geoBuilder.AddLine(0, 0);

  22:              geoBuilder.EndFigure();

23:

  24:              // We're done, this polygon has one figure.

  25:              geoBuilder.EndGeometry();

26:

  27:              // retrieve the constructed geometry

  28:              SqlGeometry geo = geoBuilder.ConstructedGeometry;

29:

  30:              Console.WriteLine("The new shape's WKT:" + geo.ToString());

  31:              Console.WriteLine("and it's area: " + geo.STArea().Value.ToString());

32:

  33:              // Wait

  34:              Console.ReadLine();

  35:          }

You are probably thinking to yourself, that’s neat, it had been a long time since I had thought about geometry, but Olivier how do I use this in my day job?

Agreed, these were very simple examples intended to show you how to get started.

You now have at your disposal a set of industry standard operations as well as extension methods that allow for complex calculations and operations against complex geometric shapes and a similar set of operations for geographic shapes.

So while calculating the area of a triangle seems pretty simple, what if that shape was a state and you were asked to calculate the area of a state, excluding all water areas? Remember that Minnesota is the land of 10,000 lakes.

The spatial library can handle complex shapes made up of 100’s of 1000’s of vertices, including shapes that have “holes”.

What if you were collecting the locations for the current instances of the bird flu reported across different regions and wanted to quickly calculate an aggregate shape that encompasses those points for clustering analysis (ConvexHull)?

What if you wanted to efficiently compare the current location for trucks within your fleet against a known “good” path for a route to better track fuel utilization or compliance (STIntersects)?

These are some of the types of problems that the SQL Server Spatial types can assist with.

Can I extend the existing SqlGeometry and SqlGeography types?

The classes are sealed, but you can use the new .NET extension methods to make it look like you have extended the existing data types.

In my next blog posting I’ll show some examples of using this extension mechanism to provide integration with Bing Maps Web Services as well as provide ways to aggregate and disaggregate complex shapes.

So what can I do with this new knowledge?

With the basics out of the way there are a series of business problems that you could look to leverage the libraries and SQL Server for:

Location based services like asset tracking and employee tracking. For example having mobile devices or laptops report their location through posts against a .NET web service or Windows Azure Web Service application and using the library to quickly calculate the proximity or relationships between assets and employees.
Combine the SQL CLR types with other Microsoft services like Bing Maps and Mappoint web services to perform Geocoding and analysis within your .NETapplication.
Analysis of real estate opportunities by combining in-house data, commercial data and census or government data.
Building extensions and utilities to make working with spatial data easier and more efficient.

Many of the spatial goodies and tools that have been made available on CodePlex were written using these builder APIs and the .NET types. You can download and try these add-ons from CodePlex here.

Modeling and analysis of floor plans
<Your App Here> – Let me know what unique applications you find for using the new SQL Server Spatial Types in your own application.

Have a great week,

Olivier

Calling all web developers: The SQL Server Team needs your help.

Olivier Meyer — Thu, 11 Jun 2009 02:43:49 GMT

The Microsoft SQL Server product team is undertaking a short survey to help guide potential new features and capabilities for web developers, please take a few minutes to take the survey and help shape the future of SQL Server.

Click this link to take the survey.

Thank you for your time and feedback.

Olivier