A View Inside My Head

Product Evaluation: Aspose.Words

Fri, 29 Mar 2013 07:38:00 PDT

Introduction

In 2004, I was approached by a client to help write a web application. A multi-page wizard collected information from the user, and at the end, generated a Word document containing pages of best practices and engineering specifications for installing the company’s products. Our solution worked, and hundreds (if not thousands) of documents have been generated for their customers. But, we used Office Automation in ASP.NET in a way that was not supported by Microsoft, at least at that time (since it was executing on the server).

Fast forward to the present day, and I was invited to evaluate Aspose.Words for .NET. While I personally don’t get many requests for Office Automation-type projects these days, as a consultant, it is good to have a go-to library to use should the need ever arise. So, I agreed to take a look at the product.

The Unboxing Experience

These days, software typically does not come in a box, so there is no unboxing experience involved to establish that first impression. But, there is a lot to be said about how easy the installation process is for software, as well as how discoverable the documentation is. Aspose.Words can be installed by means of a traditional MSI install wizard, or as a NuGet package.

The MSI option provides a full experience, including:

Installs the Aspose.Words assemblies (multiple assemblies to support different versions of the .NET Framework)
Installs the demo projects with source code
Installs the documentation locally on the developer’s machine
Adds the assembly to the Add Reference dialog box in Visual Studio

After the installation, the developer must manually add a reference to the assembly. Locally-installed documentation is available by means of Windows HTML Help, which is completely available and searchable while disconnected from the Internet.

The NuGet option is intended to be a bit more task-oriented. Instead of modifying the developer’s machine to support general-purpose development with the Aspose.Words library, it merely copies the .NET 2.0 and .NET 3.5 Client Profile assembly versions to the project’s directory, and then adds the necessary reference to the project. The package will need to be added to each project that uses the library.

There is no help file installed locally with the NuGet option, so the developer is left with the online documentation located at: http://www.aspose.com/docs/display/wordsnet/Home.

Hello World

One challenge that we had with writing the document generator all those years ago was finding an efficient way to insert formatted text into the document. Specifically, the resulting document contained sections of text, each with paragraphs and special formatting. Because of time constraints, the decision was made to not build a document on-the-fly by inserting text into it, but instead start with a document that had every possible section already in it (nicely formatted by a human), and then just delete sections based on the user’s input.

So, in evaluating Aspose.Words, I wanted to see how easy it would be to just insert whole sections of pre-formatted text, written as HTML, into a document. As it turns out, besides the rich Document object (which is very similar to Microsoft Word’s object model), there’s also a DocumentBuilder object that abstracts away the layers of nodes that makes up a document, and lets you focus on the task at hand.

Conveniently, DocumentBuilder has an InsertHTML method that looks like it will do exactly what I want. But, will it handle all aspects of HTML, like images and hyperlinks? Let’s find out!

var doc = new Aspose.Words.Document(); 
var builder = new Aspose.Words.DocumentBuilder(doc); 
var html = @"<div> 
             <img src='http://thetabletshow.com/dnr_photos/JasonFollas.png'> 
             <a href='http://jasonfollas.com/'>Testing</a> 
             </div>"; 
			 
builder.InsertHtml(html); 
doc.Save(filename_or_Stream, Aspose.Words.SaveFormat.Docx);

The result? I was expecting the image not to be included (since that’s another fetch that Aspose.Words would have to perform), but it worked flawlessly!

Note: I received a license, but had not yet applied it when this demo executed. This screenshot shows the default behavior of the evaluation mode where text is inserted into the document.

Licensing

When not associated with an active license, Aspose.Words will run in an Evaluation Mode that injects red text into the documents that are produced (see the screenshot of my “Hello World” experiment). Developers evaluating the product can obtain a 30-day license in order to work with the fully unlocked behavior.

Licenses are distributed as XML files, and applications must set the license before working with the API in order to disable the Evaluation Mode. Though this may sound like an inconvenience, it’s actually not that bad. For example, to unlock the product for use by an ASP.NET application, simply place the .lic file into the /bin directory, and add the following to global.asax:

protected void Application_Start(object sender, EventArgs e) { 
   Aspose.Words.License license = new Aspose.Words.License(); 
   license.SetLicense("Aspose.Words.Product.Family.lic"); 
}

File Types

One very impressive aspect of Aspose.Words is the vast number of document formats that are supported for loading and saving documents. With two lines of code, the library could be used as a format converter to open a Word Document and save it as a PDF:

var doc = new Aspose.Words.Document("Document.doc"); 
doc.Save("Document.pdf", Aspose.Words.SaveFormat.Pdf);

Building upon this concept, you could start with a Word Document that was authored by a business person, open it on the web server, insert/modify/delete content within the document, and then send a PDF version to the user.

Load Formats

Microsoft Word 97-2003 document
Microsoft Word 97-2003 template
Office Open XML WordprocessingML Macro-Free Document
Office Open XML WordprocessingML Macro-Enabled Document
Office Open XML WordprocessingML Macro-Free Template
Office Open XML WordprocessingML Macro-Enabled Template
Flat OPC document
RTF format
Microsoft Word 2003 WordprocessingML format
HTML format
MHTML (Web archive) format
OpenDocument Text
OpenDocument Text Template
MS Word 6 or Word 95 format

Save Formats

Doc: Microsoft Word 97 - 2007 Document
Dot: Microsoft Word 97 - 2007 Template
Docx: Office Open XML WordprocessingML Document (macro-free))
Docm: Office Open XML WordprocessingML Macro-Enabled Document
Dotx: Office Open XML WordprocessingML Template (macro-free)
Dotm: Office Open XML WordprocessingML Macro-Enabled Template
FlatOpc: Office Open XML WordprocessingML stored in a flat XML file instead of a ZIP package
FlatOpcMacroEnabled: Office Open XML WordprocessingML Macro-Enabled Document stored in a flat XML file instead of a ZIP package
FlatOpcTemplate: Office Open XML WordprocessingML Template (macro-free) stored in a flat XML file instead of a ZIP package
FlatOpcTemplateMacroEnabled: Office Open XML WordprocessingML Macro-Enabled Template stored in a flat XML file instead of a ZIP package
RTF: Rich Text Format
WordML: Microsoft Word 2003 WordprocessingML format)
Pdf: Adobe Portable Document
Xps: XML Paper Specification
XamlFixed: Extensible Application Markup Language (XAML) format as a fixed document
Swf: Adobe Flash Player
Svg: Scalable Vector Graphics
Html
Mhtml: Web archive
Epub: IDPF EPUB format
Odt: ODF Text Document
Ott: ODF Text Document Template
Text: Plain text format
XamlFlow: Beta. Saves the document in the Extensible Application Markup Language (XAML) format as a flow document
XamlFlowPack: Beta. Saves the document in the Extensible Application Markup Language (XAML) package format as a flow document
Tiff: Renders a page or pages of the document and saves them into a single or multipage TIFF file
Png: Renders a page of the document and saves it as a PNG file
Bmp: Renders a page of the document and saves it as a BMP file
Emf: Renders a page of the document and saves it as a vector EMF (Enhanced Meta File) file
Jpeg: Renders a page of the document and saves it as a JPEG file

A More Complex Example

Since I was being asked to provide an honest evaluation of the product, I wanted to find a way to stress the document generation ability a little bit more. So, after a bit of brainstorming, I came up with the idea of creating a PDF containing a Reddit post (http://reddit.com) along with its nested comments. Note: This exercise is mostly academic in nature.

Reddit provides a RESTful API to permit third-party software access to its content. For .NET languages, there is an open source library called RedditSharp (https://github.com/SirCmpwn/RedditSharp) that abstracts away the details of the networking and transport, and allows the developer to focus on the data.

var reddit = new RedditSharp.Reddit();
var iama = reddit.GetSubreddit("/r/IAmA");
var firstPost = iama.GetPosts()[0];
var comments = firstPost.GetComments();

Comments on Reddit can nest deeply at times, so in order to make the document readable, I made two design decisions: Set the page orientation to Landscape, and use the Left Indent instead of the bullet list so that I could have better control over how much space each indent uses.

var doc = new Aspose.Words.Document();
var builder = new Aspose.Words.DocumentBuilder(doc);
builder.PageSetup.Orientation = Aspose.Words.Orientation.Landscape;

The post’s title and author are written at the top of the document. The Heading 1 style is applied to the title, while a small italic font is used to display the author:

builder.ParagraphFormat.StyleIdentifier = 
	Aspose.Words.StyleIdentifier.Heading1;

builder.Writeln(firstPost.Title);

builder.ParagraphFormat.StyleIdentifier = 
	Aspose.Words.StyleIdentifier.BodyText;

builder.Font.Name = "Arial";
builder.Font.Size = 8;
builder.Font.Italic = true;
builder.Writeln(" - " + firstPost.Author.Name);

Next, the comments collection must be crawled. I chose to use a recursive function to output each level of comments, and call itself if a given comment has comments of its own (i.e., nested comments). Each level of comments is indented using the ParagraphFormat.LeftIndent property, setting a value in points.

iterate(comments, builder);

...

private void iterate(List<RedditSharp.Comment> comments, 
			   Aspose.Words.DocumentBuilder builder)
{
    indent++;
    builder.ParagraphFormat.LeftIndent = indent * 12;

    foreach (var c in comments)
    {
        if (c.ContentHtml != null)
        {
            builder.ParagraphFormat.Borders.Top.LineStyle = 
			Aspose.Words.LineStyle.Dot;

            builder.ParagraphFormat.Borders.Top.DistanceFromText = 6;

            var html = Server.HtmlDecode(c.ContentHtml)
                             .Replace("<div class=\"md\">", "")
                             .Replace("</div>", "")
                             .Replace("<p>", "")
                             .Replace("</p>", "<br/><br/>");

            builder.Font.Name = "Times New Roman";
            builder.Font.Size = 10;
            builder.Font.Italic = false;

            builder.InsertHtml(html);

            builder.Font.Name = "Arial";
            builder.Font.Size = 8;
            builder.Font.Italic = true;

            builder.Writeln(" - " + c.Author);

            if (c.Comments.Count > 0)
                iterate(c.Comments, builder);
        }
    }
    indent--;
}

When I first wrote this function, I tried using RedditSharp’s Comment.Content property (instead of .ContentHtml). I had to replace hard returns (\n) in the text with vertical tabs (\v) to keep new paragraphs from being started in the document (otherwise, the indent was lost). Even so, the result looked strange to me, since comments in Reddit are entered in plain text using Markdown (http://en.wikipedia.org/wiki/Markdown), but they are intended to be rendered as rich text.

The rendered HTML version of the comments is wrapped in a <div> tag, and uses <p> elements for each paragraph. However, similar to the hard return issue with Markdown, the indenting was lost when the DocumentBuilder.InsertHtml() function encountered the <div> and <p> elements, because these were handled as new paragraphs by Aspose.Words. So, the easy solution seemed to be to clean the HTML before inserting it into the document (by removing the <div> and <p> tags, and inserting two <br> elements in place of the closing </p> tag, etc.).

Finally, after all of the comments are rendered, it is time to save the results. I wrote my program as part of an ASP.NET web application, so saving the document is really just streaming it to the user’s browser. The Document object’s save() method has an overload that accepts a HttpResponse object as the first parameter, and makes the task of saving/streaming to the user very straightforward:

doc.Save(Response, "iama.pdf", Aspose.Words.ContentDisposition.Inline, null);

Note: This method overload is not available in the 3.5 Client Profile assembly, but if you need to use the Client Profile, then chances are that you will just be saving the results to an actual file anyways.

Closing Thoughts

Overall, I was impressed by the power and ease provided by Aspose.Words. While it didn’t always do everything in the way that I thought it should, it is probably due more to my lack of understanding of how the Word document model works rather than a flaw in this library.

The DocumentBuilder object is the real powerhouse of the library, and the InsertHtml function makes it a breeze for web developers to add entire chunks of content to Word Documents. Though, while this technique may get someone like me to the 80% mark with very little effort, it still remains to be seen how much more effort would be required in the form of code tweaking in order to produce a 100% pixel-perfect document of content.

Disclosure of Material Connection: I received one or more of the products or services mentioned above for free in the hope that I would mention it on my blog. Regardless, I only recommend products or services I use personally and believe my readers will enjoy. I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: “Guides Concerning the Use of Endorsements and Testimonials in Advertising.”

Knave 21 (Blackjack): The Fairness of Random Cards

Mon, 26 Nov 2012 15:46:00 PST

Blackjack is one of my favorite games in the casino. I'm a casual player, and certainly not an advantage player by any means. To help me drill basic strategy, especially for things like soft hands and splits, I decided to write a version of Blackjack for Windows 8 and make it a game that *I* would like to play. Since I'm not a high-roller, I also wanted to simulate the bankroll that casual players might use in an actual casino: $500 buy in at a $10 minimum table.

Thus, Knave 21 (soon to be renamed Knave Blackjack) was born after a couple of hundred hours of work in the evenings and weekends (the version in the Windows Store represents the third rewrite, and a version currently in development has undergone major refactoring recently as I prepare to add more features in the near future). It's been a labor of love, and I hope that the attention to detail reveals that. I've received a lot of positive feedback, and, recently, some negative feedback as well (which prompted this blog post).

One thing that every card game author likely struggles with is how to randomize the card selection so that it's fair to the player. Knave 21 has the concept of a shoe, which is 2-6 decks of cards that are shuffled together. It penetrates much deeper than a real casino (the reshuffle automatically occurs when 12 cards remain in the shoe), but the randomness of the deck is very dependent on whatever pseudo-random number generator can be coded in JavaScript.

Instead of relying on JavaScript's Math.random() function, Knave 21 utilizes the Alea library by Johannes Baagøe to provide a better distribution of random numbers. And, since the shoe is serialized and saved to roaming storage (so you can resume a game on another computer), I didn't want to pre-shuffle the shoe. Instead, a random card is selected and removed from the shoe upon demand. If someone were to view the roaming state data, they could see what cards were left in the shoe, but not what the next card would be.

More importantly (and in response to some recent negative feedback), the game doesn't care what cards have been played or how much you are betting when determining the next card - it simply picks a random number and that's the card that is drawn.

I'm very concerned about Knave 21's reputation, especially since I have paying customers now that the app is listed in the Windows Store. In the interest of openness, the code for the shoe implementation follows. Note the "nextCard" function for the RNG implementation.

Knave.Shoe = (function (Alea, undefined) {

    var shoe = function (decks) {
        this.cards = [];

        for (var d = 0; d < decks; d++) {
            for (var s = 0; s < 4; s++) {
                for (var r = 0; r < 13; r++) {
                    this.cards.push(new Knave.Card("A23456789TJQK".charAt(r), "HDCS".charAt(s), true));
                }
            }
        }
    };

    shoe.prototype = {
        nextCard: function (facedown) {
            var random = new Alea();
            var index = random.uint32() % this.cards.length;
            
            var c = this.cards.splice(index, 1);

            if (c.length === 0)
                return undefined;

            var ret = c[0];

            if (facedown !== undefined)
                ret.facedown(facedown);

            return ret;
        },
        remaining: function () {
            return this.cards.length;
        }
    }

    return shoe;
})(Alea);

Metro: Introducing the Local and Web Contexts

Mon, 09 Jul 2012 10:02:00 PDT

I recently recorded an episode of The Tablet Show with Carl Franklin and Richard Campbell (it will be published at the end of July 2012) where I rambled on about discussed different concepts that are needed to construct a Geospatial Metro-style application. Since I seem to do more web development these days than anything else, I naturally approached this from a HTML/JavaScript developer's point of view.

The geospatial part is really irrelevant. I included it because I could speak to the subject matter, and it permitted me to establish some constraints around the otherwise huge topic of writing a Metro-style application. From the web developer's point of view, though, there are existing JavaScript-based APIs that the developer will be interested in using while writing the Metro application (i.e., jQuery and OpenLayers comes to mind).

Now, when you write a Metro application using .NET or C++, there's a compilation step that takes place to compile the source code into an executable (EXE and/or DLL). The executable and any dependencies are packaged into an application package (an .appx file), which is really just a renamed .zip file that contains the files, a manifest, and cryptographic information to ensure that the contents were not tampered with. Aside from WinRT libraries, everything that is needed to run the application must be included in the package.

HTML/JavaScript Metro applications, by contrast, actually run as a web page inside of an Internet Explorer 10 process. The source files are stored within the application package, much as they would be on a web server. But, instead of downloading HTML, JavaScript, CSS, and images from a web server across a network, the resources are retrieved from the .appx file on the user's hard drive (much faster load times, and the application is still accessible when there is no network connectivity).

Metro-style applications built using HTML/JavaScript still have access to the Windows Runtime and can do just about anything that their compiled-counterparts can. In order to allow such privileged access, however, certain common practices in the web world had to be explicitly prohibited. Specifically, anything that attempts to inject HTML or script into the DOM of a Metro-style application will result in an exception (to prevent third-party script injection attacks). More details can be found here: http://msdn.microsoft.com/en-us/library/windows/apps/hh465380.aspx

This safe HTML filtering causes a problem with a lot of existing JavaScript libraries. So, how is a developer supposed to use such a library inside of a Metro application? It turns out that in the case of Metro-style applications, Internet Explorer 10 will assume two different personalities: Local context and Web context.

The application bootstraps into the Local context. This mode is what you'll find most people talking about when they discuss building Metro-style applications using HTML/JavaScript because it has full access to WinJS (and WinRT). Local context URIs start with the "ms-appx" protocol, and the resources must exist in the .appx file.

The Web context is totally isolated from the Local context, and cannot access WinRT and a lot of other things that the Local context can. As a result, code running in the Web context behaves pretty much like a normal web application today. This means that developers can continue using those existing JavaScript libraries that don't work in the Local context!

Web context URIs start with the "ms-appx-web" protocol (if the resource exists in the .appx file), or the "http" protocol (if the resource is to be loaded from a web server). Specific differences between Local and Web contexts is documented here: http://msdn.microsoft.com/en-us/library/windows/apps/hh465373.aspx

In practice, an IFRAME is used to introduce Web context content into a Metro application. In this way, the outermost web page runs in the Local context, while the IFRAME's content runs in the Web context. It's possible for the IFRAME to be styled to fill the entire screen, if needed (and the outermost web page becomes just a thin hosting shell).

A common scenario is that data in the Local context will need to be sent to the web context (and vice versa). For instance, code running in the Local context may listen for events fired by the device's Compass sensor. When a new directional heading is established, the Local context code will need to send the new data to the Web context code for further processing.

HTML5 defines a means to do this cross-document communication using the window.postMessage() function. In the case of the new Compass reading, the Local context code would post a message to the IFRAME. The Web context code would need to register an event listener for the window's "message" event.

The messages themselves are strings, and the developer is left with the task of implementing a data protocol. JavaScript Object Notation (JSON) will often be used to serialize JavaScript object to a string representation for the purpose of transferring across the contexts (the sending side will use JSON.stringify() to serialize the object, while the receiving side will use JSON.parse() to deserialize it).

The hybrid Local-Web context architecture allows an existing HTML5/JavaScript web application to be converted into a Metro-style application without the need to modify third-party JavaScript libraries. Though, even after such a conversion, there will still likely be some clean-up needed to make the existing application behave more like a Metro-style application, such as implementing the App Bar for commands, and taking advantage of the Search and Sharing contracts. But, the reward for going through this effort is an application that can then be distributed or offered for sale on the Windows Store to an audience of millions of potential users.

Want to try these things out today before Windows 8 launches this Fall? Release candidates are available for free:

Windows 8 Download: http://aka.ms/JenWin8

Visual Studio 2012 Download: http://aka.ms/JenVS2012

Migrating to BlogEngine.NET

Sat, 07 Jul 2012 16:00:00 PDT

I've been wanting to migrate this blog to a different blogging engine for a while now. So, the other day, while recording an episode of The Tablet Show, Richard Campbell asked me "Did you know that your blog is down?"

Well, it seems that a recent security patch broke something about SubText as I had it set up on my hosting provider. This became my opportunity to finally spend some time on this tired site!

Entity Framework Spatial: A Real World Example

Wed, 27 Jul 2011 11:26:00 PDT

Background

From the Wikipedia article, Leadership in Energy and Environmental Design (LEED) is described as “an internationally recognized green building certification system, providing third-party verification that a building or community was designed and built using strategies intended to improve performance in metrics such as energy savings, water efficiency, CO₂ emissions reduction, improved indoor environmental quality, and stewardship of resources and sensitivity to their impacts.”

In my own words, LEED is a certification system that awards points for following certain environmentally-friendly practices when constructing a building. In the end, a building can be qualify for one of four different levels of certifications, based on the number of points: Certified, Silver, Gold, Platinum. There are often tax benefits associated with having a LEED certification, and many new government buildings (especially Federal) are required to be LEED certified.

Two points in particular (out of of 100, or so) from the LEED checklist are related to geospatial data. One point is awarded if at least 20% of the building materials (by cost) used in construction were manufactured within 500 miles of the job site. A second point is awarded if 10% of the raw materials of those building materials were extracted, harvested, or recovered within 500 miles of the job site.

As a window glass manufacturer, Tempuri Glass is often asked to provide data about its products that are being considered for use in construction. Tempuri Glass may have a certain advantage over its competitors if it can quickly show that its products would count towards these two points for any arbitrary job site.

Data

Tempuri is a simple organization, making only a single type of product (Soda Lime glass) that is then cut into different sizes per order. Therefore, regardless of how many different sized glass panes are produced by a given facility, the ingredients for that glass is the same. The formulas used will be different between facilities, though, since the raw ingredients will be sourced from different locations, and adjustments may need to be made to the ratios due to environmental factors (things like: elevation, temperature, humidity, etc).

So, for our data model, we just need to know where each facility is, and then the formula used to make the glass at that facility (including the ingredients of that formula and the location where they were harvested from).

Within the data store, the [Geocode] columns of the Facility and FormulaComponent tables use the SQL Server geography type. This is useful for the large-scale/real-world distance calculations that Tempuri Glass needs to perform, since the way that you calculate distance on an sphere or ellipsoid (like the Earth) is vastly different than on a flat map.

In the Entity Framework model (using the June 2011 CTP), the SQL Server geography types are mapped as the new System.Data.Spatial.DbGeography type. This makes the geospatial data a first class citizen of our data model, and not just a castable opaque BLOB, as was the case in the past.

Geospatial data can take on many forms, including Points, Line Strings, Polygons, and collections of these shapes. Even though it’s not apparent from the data model, our [Geocode] data will contain only Points (i.e., a single Latitude/Longitude pair). Likewise, a job site will be specified as a single Point, though there is no hard requirement for this because distance can still be calculated between a Polygon and a Point with no coding change required.

Facility Sample Data

FacilityID	FacilityName	City	State	Geocode
1	Greenfield, IA	Greenfield	IA	POINT (-94.4547843933106 41.3151755156904)
2	Spring Green, WI	Spring Green	WI	POINT (-90.053981 43.17431)
3	Tomah, WI	Tomah	WI	POINT (-90.477058 43.989319)
4	Fremont, IN	Fremont	IN	POINT (-84.9314403533936 41.7186070559443)
5	Fargo, ND	Fargo	ND	POINT (-96.8667125701904 46.8985894795683)
6	Waxahachie, TX	Waxahachie	TX	POINT (-96.8427014350891 32.4424403136322)
7	Hood River, OR	Hood River	OR	POINT (-121.51526927948 45.630620334868)
8	Vinton, VA	Vinton	VA	POINT (-79.863876 37.263329)
9	Casa Grande, AZ	Casa Grande	AZ	POINT (-111.78155422210693 32.882073958767954)
10	Mountain Top, PA	Mountain Top	PA	POINT (-75.896477 41.141327)
11	Winlock, WA	Winlock	WA	POINT (-122.926218509674 46.5449155194259)
12	Durant, OK	Durant	OK	POINT (-96.4133548736572 34.0001619910696)
13	Mooresville, NC	Mooresville	NC	POINT (-80.7865476608277 35.6316281732984)

FormulaComponent Sample Data

FormulaComponentID	Name	Percentage	SourceLocation	Geocode
14	Limestone	13	Genola, UT	POINT (-111.808204650879 40.0098667779887)
1	Silica Sand	75	Houck, AZ	POINT (-109.241695404053 35.2062151838369)
27	Soda Ash	12	Trona, CA	POINT (-117.311668395996 35.6955040738332)
15	Limestone	13	Genola, UT	POINT (-111.808204650879 40.0098667779887)
2	Silica Sand	75	Houck, AZ	POINT (-109.241695404053 35.2062151838369)
28	Soda Ash	12	Trona, CA	POINT (-117.311668395996 35.6955040738332)
16	Limestone	13	Chicago, IL	POINT (-87.6176834106445 41.5738476278005)
3	Silica Sand	75	Overton, NV	POINT (-114.4313621521 36.5146030619859)
29	Soda Ash	12	Green River, WY	POINT (-109.448783397675 41.5090754257687)

Spatial Querying Algorithm

Input: Job Site Latitude/Longitude

Steps:

A. Query for closest facility to Job Site within 500 miles:

Calculate the distance between the job site and each facility.
Filter the list of facilities to just those where distance < 500 miles.
Order the list of facilities by distance in ascending order.
The first element (if any) will be the closest facility, and also signifies that the product qualifies as being manufactured within 500 miles

B. If there is a facility within 500 miles, then sum the percentage of formula components that were sourced from within 500 miles of the Job Site:

Calculate the distance between the job site and each of the facility’s formula components
Filter the list of formula components to just those where distance < 500 miles
Sum the Percentages

Output: Boolean of whether the product qualifies; Percentage of the product’s ingredients that qualifies.

Implementation

Before we can calculate distance using an instance method of the DbGeography type, we need to actually create an instance to represent the Job Site. DbGeography is immutable and does not have a constructor, so instead, a static method must be called to create a new object. There are a number of these factory methods available to create specific kinds of shapes (Point, Line String, Polygon, etc) given different kinds of input (text, byte arrays).

For simplicity, let’s use the .Parse() method, which accepts Well-Known Text (WKT) as input, and assumes a Spatial Reference ID of 4326 (the same coordinate system that GPS and internet mapping sites use).

Note: WKT uses a (Longitude, Latitude) ordering for points, which adheres to the same concept as (X, Y) ordering for Cartesian coordinates.

private static DbGeography CreatePoint(double latitude, double longitude)
{
   return DbGeography.Parse(String.Format("POINT({1} {0})", latitude, longitude));
}

The first spatial query, written as a LINQ expression, finds the closest qualifying facility. Since SRID 4326 uses meters as the unit of measure, we need to convert 500 miles into meters within the predicate:

private Facility GetNearestFacilityToJobsite(DbGeography jobsite)
{
    var q1 = from f in context.Facilities
             let distance = f.Geocode.Distance(jobsite)
             where distance < 500 * 1609.344
             orderby distance
             select f;

    return q1.FirstOrDefault();
}

Assuming that a facility was returned, a second LINQ expression can be used to find the sum of Percentage from qualifying Formula Components:

private decimal SumQualifyingPercentages(Facility nearestFacility, DbGeography jobsite)
{
   var q2 = from fc in nearestFacility.Formula.FormulaComponents
            where fc.Geocode.Distance(jobsite) < 500 * 1609.344
            select fc;

   return q2.Sum(c => c.Percentage.GetValueOrDefault(0));
}

Finally, putting all of the parts together (using a Tuple<> for the output):

private Tuple<bool, decimal> GetResults(double latitude, double longitude)
{
   DbGeography jobsite = CreatePoint(latitude, longitude);
   Facility nearestFacility = GetNearestFacilityToJobsite(jobsite);

   if (nearestFacility != null)
   {
      return new Tuple<bool,decimal>(true, SumQualifyingPercentages(nearestFacility, jobsite));
   }

   return new Tuple<bool, decimal>(false, 0);
}

private void PerformQuery()
{
   double latitude = 47.63752;
   double longitude = -122.13343;

   var results = GetResults(latitude, longitude);
}

Entity Framework Spatial: DbGeography and DbGeometry Members

Thu, 21 Jul 2011 11:21:00 PDT

DbGeography Static Property	Return Type	DbGeometry Static Property	Return Type
DbGeography.DefaultSrid	int	DbGeometry.DefaultSrid	int

DbGeography Static Method	Return Type	DbGeometry Static Method	Return Type
DbGeography.FromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.FromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.FromGml(string geographyMarkup, int srid)	DbGeography	DbGeometry.FromGml(string geometryMarkup, int srid)	DbGeometry
DbGeography.FromText(string geographyText, int srid)	DbGeography	DbGeometry.FromText(string geometryText, int srid)	DbGeometry
DbGeography.GeographyCollectionFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.GeometryCollectionFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.GeographyCollectionFromText(string geographyText, int srid)	DbGeography	DbGeometry.GeometryCollectionFromText(string geometryText, int srid)	DbGeometry
DbGeography.LineFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.LineFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.LineFromText(string geographyText, int srid)	DbGeography	DbGeometry.LineFromText(string geometryText, int srid)	DbGeometry
DbGeography.MultilineFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.MultilineFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.MultilineFromText(string geographyText, int srid)	DbGeography	DbGeometry.MultilineFromText(string geometryText, int srid)	DbGeometry
DbGeography.MultipointFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.MultipointFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.MultipointFromText(string geographyText, int srid)	DbGeography	DbGeometry.MultipointFromText(string geometryText, int srid)	DbGeometry
DbGeography.MultipolygonFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.MultipolygonFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.MultipolygonFromText(string geographyText, int srid)	DbGeography	DbGeometry.MultipolygonFromText(string geometryText, int srid)	DbGeometry
DbGeography.Parse(string geographyText)	DbGeography	DbGeometry.Parse(string geometryText)	DbGeometry
DbGeography.PointFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.PointFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.PointFromText(string geographyText, int srid)	DbGeography	DbGeometry.PointFromText(string geometryText, int srid)	DbGeometry
DbGeography.PolygonFromBinary(byte[] geographyBinary, int srid)	DbGeography	DbGeometry.PolygonFromBinary(byte[] geometryBinary, int srid)	DbGeometry
DbGeography.PolygonFromText(string geographyText, int srid)	DbGeography	DbGeometry.PolygonFromText(string geometryText, int srid)	DbGeometry

DbGeography Instance Property	Return Type	DbGeometry Instance Property	Return Type
g.Area	double?	g.Area	double?
		g.Boundary	DbGeometry
		g.Centroid	DbGeometry
		g.ConvexHull	DbGeometry
g.Dimension	int	g.Dimension	int
g.EndPoint	DbGeography	g.EndPoint	DbGeometry
		g.Envelope	DbGeometry
		g.ExteriorRing	DbGeometry
g.GeometryType	string	g.GeometryType
g.IsClosed	bool?	g.IsClosed	bool?
g.IsEmpty	bool	g.IsEmpty	bool
		g.IsRing	bool?
		g.IsSimple	bool
		g.IsValid	bool
g.Latitude	double?
g.Length	double?	g.Length	double?
g.Longitude	double?
g.M	double?	g.M	double?
g.NumGeometries	double?	g.NumGeometries	int?
		g.NumInteriorRing	int?
g.NumPoints	int?	g.NumPoints	int?
		g.PointOnSurface	DbGeometry
g.ProviderValue	object	g.ProviderValue	object
g.Srid	int	g.Srid	int
g.StartPoint	DbGeography	g.StartPoint	DbGeometry
g.WellKnownValue WellKnownValue	DbGeographyWellKnownValue	g.WellKnownValue	DbGeometryWellKnownValue
g.WellKnownValue.Srid	int	g.WellKnownValue.Srid	int
g.WellKnownValue.WellKnownBinary	byte[]	g.WellKnownValue.WellKnownBinary	byte[]
g.WellKnownValue.WellKnownText	string	g.WellKnownValue.WellKnownText	string
		g.X	double?
		g.Y	double?
g.Z	double?	g.Z	double?

DbGeography Instance Method	Return Type	DbGeometry Instance Method	Return Type
g.AsBinary()	byte[]	g.AsBinary()	byte[]
g.AsGml()	string	g.AsGml()	string
g.AsText()	string	g.AsText()	string
g.Buffer(double distance)	DbGeography	g.Buffer(double distance)	DbGeometry
		g.Contains(DbGeometry other)	bool
		g.Crosses(DbGeometry other)	bool
g.Difference(DbGeography other)	DbGeography	g.Difference(DbGeometry other)	DbGeometry
g.Disjoint(DbGeography other)	bool	g.Disjoint(DbGeometry other)	bool
g.Distance(DbGeography other)	double	g.DistanceDbGeometry other)	double
g.GeometryN(int index) DbGeography	DbGeography	g.GeometryN(int index)	DbGeometry
		g.InteriorRingN(int index)	DbGeometry
g.Intersection(DbGeography other)	DbGeography	g.Intersection(DbGeometry other)	DbGeometry
g.Intersects(DbGeography other)	bool	g.Intersects(DbGeometry other)	bool
		g.Overlaps(DbGeometry other)	bool
g.PointN(int index)	DbGeography	g.PointN(int index)	DbGeometry
		g.Relate(DbGeometry other, string matrix)	bool
g.SpatialEquals(DbGeography other)	bool	g.SpatialEquals(DbGeometry other)	bool
g.SymmetricDifference(DbGeography other)	DbGeography	g.SymmetricDifference(DbGeometry other)	DbGeometry
		g.Touches(DbGeometry other)	bool
g.Union(DbGeography other)	DbGeography	g.Union(DbGeometry other)	DbGeometry
		g.Within(DbGeometry other)	bool

Entity Framework Spatial: First Look

Wed, 20 Jul 2011 11:19:00 PDT

Today, I began to look into the Entity Framework June 2011 CTP which includes first class support for Spatial data types. The ADO.NET team created an abstraction layer, based on the OGC simple features specification, with a goal being to support spatial implementations from multiple vendors. As you would expect, SQL Server 2008 Spatial is supported out of the box.

For some reason, I was expecting a lot of work to be done on the client-side within their abstraction data type. I was pleasantly surprised to see EF pass the heavy lifting to the underlying data store as part of the query.

For instance, I have a table in my database called Facility with a Geography column named [Geocode]. This field contains a point (single latitude/longitude pair) identifying the location of the Facility. Even though this would normally be represented in client-side code as a SqlGeography type, EF wraps it in the new DbGeography type (i.e., the abstraction data type for ellipsoidal data).

My first query was a LINQ expression to return a list of all facilities that are within 500 miles of a given location:

var q = from f in context.Facilities 
        let p = DbGeography.Parse("POINT(-83 45)") 
        where f.Geocode.Distance(p) < 500 * 1609.344 
        select new { f.FacilityID, wkt=f.Geocode.AsText() };

A couple things about this query:

The default SRID of 4326 is used. This spatial reference system uses meters for distance, so my predicate needs to convert the 500 miles into meters. Like the SqlGeography.Parse() method, DbGeography.Parse() will default to 4326.
The return type is an anonymous type. I wanted to see how the DbGeography type's .AsText() method was executed (i.e., would it be expressed in the resulting query, or would it be handled client side, etc).

When executed, the LINQ expression above generates the following TSQL:

SELECT  [Extent1].[FacilityID] AS [FacilityID],
        [Extent1].[Geocode].STAsText() AS [C1] 
FROM    [dbo].[Facility] AS [Extent1] 
WHERE   ([Extent1].[Geocode].STDistance(geography::Parse(N'POINT(-83 45)'))) < cast(804672 as float(53))

As you can see, the DbGeography.AsText() was translated into the STAsText() method in the query. And, as you might expect, the predicate's DbGeography.Distance() was properly translated into STDistance() in the TSQL WHERE clause.

The other thing that I was worried about was not having access to the actual SqlGeography type returned from the database. I was surprised to see that EF's DbGeography has a property called ProviderValue that returns the native type that the provider supports!

CodeRush: Anonymous Method Refactoring Fun

Wed, 14 Jul 2010 11:34:00 PDT

I recently had to work with some code that I wrote a few years ago, and stumbled upon this:

(Not that it's relevant to the rest of this blog post, but this code was responsible for deleting a Role in a custom membership provider, but due to foreign key relationships without cascading deletes, all of the User/Role assignments had to be deleted first)

Now, in all fairness to myself, there's really nothing wrong with this code. Array.ForEach<T> requires an Action<T> delegate, and this anonymous method certainly qualifies as one.

However, since the time that I wrote this particular piece of code, I started using Lambda expressions more often. To be consistent with the rest of the project's code, I should change this anonymous method into a Lambda expression.

Fortunately, CodeRush makes this a breeze! Simply put your cursor within with anonymous method itself, and then hit your Refactor Key and choose the "Compress to Lambda Expression" refactoring:

As simple as that, the wordy "delegate" syntax of the anonymous method is compressed into the terse "=>" syntax of a Lambda expression:

But, suppose that you are just learning Lambda expressions, and come across one in code that you don't fully understand. CodeRush allows you to go the other way, too, and expand a Lambda expression into an anonymous method, which may be easier to understand. Similar to above, put your cursor on the Lambda expression, hit the Refactor Key, and then choose "Expand Lambda Expression":

Anonymous methods were introduced as a way to inline functionality that otherwise had to exist in its own named method. This is really only good if that functionality is used one time. But, what if you discover that the functionality is needed in multiple places?

CodeRush provides a refactoring for anonymous methods called "Name Anonymous Method" that will extract the logic into a named method that can then be called from other code:

Activating this refactoring will first display the Target Picker (red arrow and line) so that you can select where to add the new method. Then, the new method's name can be set by editing the text in the green box that will appear.

Finally, suppose that the opposite is true: you have a named method that is only used in one place, and would prefer to just inline that code. Or, perhaps you would like to create a one-off modification to an existing method, and don't need the new functionality to be in its own named method.

CodeRush has a refactoring called "Inline Delegate" that replaces a delegate referring to a named method with an anonymous method. If there is only a single reference to that named method, then this refactoring will also delete the named method altogether.

Since I was already familiar with the anonymous method syntax, CodeRush played an instrumental part in my learning Lambda expressions because I could write an anonymous method and then have it transformed into an equivalent Lambda for me. But, once I knew Lambdas, I found that I wanted to update my older code in order to replace anonymous methods with the newer syntax, and CodeRush made it a trivial task to do this.

Happy Coding!

CodeRush: Markers

Tue, 13 Jul 2010 11:33:00 PDT

Have you ever become distracted while scrolling through code, or switched to another tab and then forgot what you were working on? It happens all the time when we try to multitask while coding. If only there was a call stack for our brains that we could just unwind to find our way back to where we started....

Turns out, there is!

CodeRush includes a feature called "Markers". These are little placeholders, or breadcrumbs, for your cursor location that can be created just before your focus changes to another part of code. When you're done with whatever task distracted you, simply hit ESC (or ALT+End) to collect the most recent marker and your cursor will be returned to exactly where it was when the marker was created.

Sometimes, CodeRush will automatically create markers for you during refactoring and template expansions. These appear as small blue triangles within the newly created code:

When you collect a marker, your cursor will be returned to that location, and an animated Locator Beacon will display on top of the cursor to draw your attention to that spot:

But, you can manually drop your own markers, too, by using ALT+Home. The manually created markers will be red in color:

Some tricks that you can do with markers:

SHIFT+ALT+Home: Drops a marker at the current cursor location and collects the most recent marker (so you can swap between the locations) Thanks Jay Wren for the clarification.
SHIFT+ALT+PgUp: Select text between the current cursor location and the most recently created marker
SHIFT+ESC: Collect the most recent marker and also paste whatever is in the clipboard at that location

Happy Coding!

CodeRush: CodeRush Tool Window

Fri, 09 Jul 2010 11:31:00 PDT

CodeRush is full of templates. Thousands of them, when you combine the basic templates with the various types that are supported by each. How does someone start to learn all of the required mnemonics in order to use CodeRush effectively?

The answer to that is a brilliant feature known as the CodeRush Tool Window:

(DevExpress menu in Visual Studio, Tool Windows -> CodeRush)

The CodeRush Tool Window provides a context-sensitive list of "next characters" in CodeRush templates that are valid at the cursor's current location. As you start to work with CodeRush, leave this window open (perhaps on a second monitor) so that you can train yourself.

In the screenshot above, I have the CodeRush Tool Window docked on the left side of my IDE. Depending on where my cursor is located, the contents of the window will change.

For example, I can create a new Auto-Implemented Property at the cursor's current location (line 49 in the screenshot). In the CodeRush Tool Window, I see that mnemonics for Auto-Implemented Properties start with "a", so I can type that character. But, after typing "a", the contents of the CodeRush window will change:

The letter "a" by itself is a valid mnemonic for a CodeRush Template, as indicated by the the green "expansion" frame at the top of the CodeRush Tool Window. If I were to hit the spacebar here, I'd get code for a new abstract class (a preview of this code appears in the expansion frame). This is not what I want, so I can look in the list to see if typing more characters results in an appropriate mnemonic. Note: If you do accidentally expand a wrong template, just perform an Undo (Ctrl-Z) to return to the pre-expansion state of your code.

Suppose that I was trying to create an Auto-Implemented Property of type Boolean. From the contents of the window, I see that "ab" is listed as "bool", and so I only need to type the second character ("b") and then hit the space bar.

Play around by moving your cursor to various places in your code, and observing how the contents of the CodeRush Tool Window changes. Try out some of the template mnemonics that are listed to see how they work. Remember that Undo (Ctrl-Z) will get you back to where you started.

Happy Coding!