Monthly Archives: March 2013

SQL CASE Statement

Maybe knew of it, but hadn’t had reason until now to use a CASE statement in SQL:

UPDATE Devices

SET InUse = (CASE UserId WHEN 3



WHERE DevGuid = '94F74150-26B0-4396-982C-675A14A66FAD';

In this situation, there are multiple rows with the same device GUID because more than one user may use the same app on the device (but not simultaneously…unless they’re extraordinarily close). What this query does is get all the rows that share the same device GUID, then sets the InUse bit to true (1) if the UserId matches (3). Any row within that set with a different user ID gets its InUse bit set to false (0). This avoids having to make more than one call to the server.


Recently we encountered a bug in SQL Server’s spatial aggregation algorithms. Sets of polygons that should have been combined strictly into WKT (well-known-text) POLYGON or MULTIPOLYGONs were being stored as GEOMETRYCOLLECTIONs instead. Turns out the algorithm was inserting one or two LINESTRINGs (of two points each) into the collection, forcing it to be stored this way. The bug was supposedly fixed a couple years ago, but I’m going to submit a new bug and sample dataset to Microsoft. Because I’ve double-checked we’re using the latest version of the DLL, etc.

So why do I think the GEOMETRYCOLLECTION is “evil” (or at least don’t think highly of it)? Primarily because it caused our customer to question both the integrity of our software and the use of SQL Server to store AND manipulate spatial data instead of relying on purely an ESRI ArcGIS solution. We were unable to open the SQL Server datasets via ArcCatalog or ArcMap (using the Database Connection in 10.1). On a broader note, I tend to side with ESRI in not supporting more than one data type in a layer, so I’m perplexed that the creators of the WKT standard even thought up such a datatype. I can see where it might make sense to group related geometric objects but believe there are better ways to do that.

I’m tempted (hey, in a post where the word evil is mentioned it seems appropriate word use) to say that this never would have happened in the first place if a GEOMETRYCOLLECTION wasn’t a possibility. But I know that even if the bug gets fixed, Microsoft and others will still adhere to the WKT specification for storing data and ESRI will continue to invalidate coverages that contain multiple datatypes. So we will improve on the hack below to ensure that what gets output in the end is valid by our customer’s definition.

// Well, semi-verified anyway. At least it won't contain any LINESTRINGs.
public SqlGeometry VerifiedPolygonGeometry(SqlGeometry inputGeometry)
 SqlGeometry outputGeometry = null;

 var goodReservoirGeometriesList = new List<SqlGeometry>();

int numGeometries = (int)inputGeometry.STNumGeometries();

for (int i = 1; i <= numGeometries; i++)

var geometry = inputGeometry.STGeometryN(i);

if (geometry.STGeometryType() != "LineString")

var agg = new GeometryUnionAggregate();


foreach (SqlGeometry geometry in goodReservoirGeometriesList)

outputGeometry = agg.Terminate();
 catch (Exception exception)

return outputGeometry;


“We are overloaded with childish mythologies like ‘the cloud’; a soft, fuzzy metaphor for enormous infrastructural projects of undersea cables and power-hungry data farms.” – Timo Arnall

Even in rebutting a metaphor, one can have difficulty avoiding metaphors, e.g. “data farms.”

I’ve largely ignored term “no ui” because it’s a crock and a waste of time. As for terms like “the cloud” anyone that develops against remote servers/storage knows better. Yet it’s not the worst shorthand in the world to describe some set of services and storage that can be so varied but is definitely not “here” or local to the user or their devices. From a developer standpoint, all I care about is making sure my users don’t get screwed. That involves handling a lot of edge case scenarios, both in terms of some users and occasional tasks or conditions confronted by any user. While aesthetics, input and appropriate functionality are all important, my main goal is to create software “that just works.”

To give an example of that, someone once figured out that you could use Adobe Photoshop’s lighting filters to created shaded relief maps and Adobe’s programmers were attentive enough to make something unanticipated like that work, but almost certainly they never thought about creating the filters for use by cartographers.


Incidentally (in, Jared Spool has come around more to a notion of transparency rather than invisibility. So while Arnall may be right in citing Spool’s 2009 article as seminal in the No UI movement, it would be good for him to update his information. Spool, IMO, has some good things to say and even if it’s indirect inspiring developers and designers to do better work.

Now That’s a Detour

Here’s a screenshot from Flightradar24. It’s typical of flights between Beirut and Amman. I can see why, given the situation in Syria, flights wouldn’t go that way. But I’d be curious to know if planes detour around Israeli airspace because they aren’t allowed there. Or is it because of some Arab political decision?


I find the app fascinating, especially when you see a flight heading right over Helena that’s going from somewhere like SFO to Dubai. Or even the Fedex flights from Anchorage and everywhere else that all still flock to Memphis. I wonder, despite the package processing infrastructure there, why someone hasn’t come up with a more efficient set of routing algorithms to use the least effort to get packages from A to B. Amazon Air anyone?

Nobody Notified Me About This!

Earlier on in the Windows 8 development cycle, apps had a checkbox in their app manifest indicating whether the app should be capable of receiving notifications. Later, that explicit requirement went away from the UI in the app manifest window. Thanks to a discussion with a Microsoft DPE (Development & Platform Evangelism) guy yesterday, I was steered towards using a version of the server-side notifications code that includes full diagnostics. My notifications were being sent, but had a “dropped” status, indicating they weren’t making it to the client app. The client app was running. So why, why, why wasn’t it working?

It was then that a little searching online turned up recommendations to check if the app had permissions enabled to receive notifications. Opening the XML for package.appxmanifest (by right-clicking on it and selecting “View Code” or select the file and hit F7), I was able to just add ToastCapable=”true” to the VisualElements tag. That took care of it.