I have a bunch of work to do using git on OSX over the next few months and I figured it was about time I changed my prompt to be git aware. I’m really used to having this on Windows thanks to the excellent posh git. If you haven’t used it in the prompt you get the branch you’re on, the number of files added, modified and deleted as well as a color hint about the state of your branch as compared with the upstream (ahead, in sync, behind).

It is wonderful. I wanted it on OSX. There are actually quite a few tutorials that will get you 90% of the way there. I read one by Mike O’Breinbut I had some issues with it. For some reason the brew installation on my machine didn’t include git-prompt. It is possible that nobody’s does”¦ clearly a conspiracy. Anyway I found a copy over at the git repository on github. I put it into my home directory and sourced it in my .profile.

if [ -f $(brew –prefix)/etc/bash_completion ]; then . $(brew –prefix)/etc/bash_completion fi source ~/.git-prompt PS1=”33[32m]@ 33[33m]w$(__git_ps1 “ (33[36m]%s33[33m])”) n$33[0m] “

This got me some of the way there. I had the branch I was on and it was coloured for the relationship to upstream but it was lacking any information on added, removed and modified files.

So I cracked open the .git-profile and got to work. I’ll say that it has been a good 5 years since I’ve done any serious bash scripting and it is way worse than I remember. I would actually have go this done in powershell in half the time and with half the confusion as bash. It doesn’t help that, for some reason, people who write scripts feel the need to use single letter variables. Come on, people, it isn’t a competition about brevity.

I started by creating 3 new variables

local modified=”$(git status | grep ‘modified:’ | wc -l | cut -f 8 -d ‘ ‘)” local deleted=”$(git status | grep ‘deleted:’ | wc -l | cut -f 8 -d ‘ ‘)” local added=”$(git ls-files –others –exclude-standard | wc -l | cut -f 8 -d ‘ ‘)”

The first two make use of git status. I had a quick twitter chat with Adam Dymitruk who suggested not using git status as it was slow. I did some bench marking and tried a few other commands and indeed found that it was about twice as expensive to use git status as to use git diff-files. I ended up replacing these variables with the less readable

local modified=”$(git diff-files|cut -d ‘ ‘ -f 5|cut -f 1|grep M|wc -l| cut -f 8 -d ‘ ‘)” local deleted=”$(git diff-files|cut -d ‘ ‘ -f 5|cut -f 1|grep D|wc -l | cut -f 8 -d ‘ ‘)” local added=”$(git ls-files –others –exclude-standard | wc -l | cut -f 8 -d ‘ ‘)”

Chaining commands is fun!

Once I had those variables in place I changed the gitstring in .git-prompt to read

local gitstring=”$c$b${f:+$z$f}$r$p [+$added ~$modified -$deleted]”

See how pleasant and out of place those 3 new variables are?

I also took the liberty of changing the prompt in the .profile to eliminate the new line

PS1=”33[32m]@ 33[33m]w$(__git_ps1 “ (33[36m]%s33[33m])”) $33[0m] “

My prompt ended up looking like

Beautiful. Wish I’d done this far sooner.

Turns out DNS is kind of important and for some reason mine decided to leave. I went ahead and moved over to using DNSimple instead of my somewhat questionable registrar (only 1186 days until that comes up for renewal). So sorry the blog has been offline; I actually didn’t even notice it.

A couple of weeks ago Microsoft released Azure Event Hub. These are another variation on service bus that go on to join queues and topics. Event Hubs are Microsoft’s solution to ingesting a large number of messages from Internet of Things or from mobile devices or really from anything where you have a lot of devices that produce a lot of messages. They are prefect for sources like sensors that report data every couple of seconds.

There is always a scalabilitystory with Azure services. For instance with table storage there is a partition key; there is a limit to how much data you can read at once from a single partition but you can add many partitions. Thus when you’re designing a solution using table storage you want to avoid having one partition which is particularly hot and instead spread the data out over many partitions. With Event Hubs the scalability mechanism is again partitions.

When sending messages to table storage you can pick one of n partitions to handle the message. The number of partitions is set at creation time and values seem to be in the 8-32 range but it is possible to go up to 1024. I’m not sure what real world metric the partition count maps to. At first I was thinking that you might map a partition to a device but with a maximum around 1024 this is clearly not the approach Microsoft had in mind. I could very easily have more than 1024 devices. I understand that you can have more than 1024 partitions but that is a contact support sort of operation. The messages within a partition are delivered to your consumers in order or receipt.

In order delivery sounds mildly nifty but it is actually a huge technical accomplishment. In a distributed system doing anything in order is super difficult. Their cheat is that there is only a single consumer for each partition. I should, perhaps, say that there is at most one consumer per partition. Each consumer can handle several partitions. However you can have multiple consumer groups. Each consumer group gets its own copy of the message. So say you were processing alerts from a door open sensor and you want to send text messages when a door is opened and you want to log all open events in a log then you could have two consumers in two groups. Realistically you could probably handle both of these things in a single consumer but let’s play along with keeping our microservices very micro.

Messages sent to the event hub are actually kept around for at least 24 hours and can be configured up to 7 days. The consumers can request messages from any place in the stream history. This means that if you need to replay an event stream because of some failure you’re set. This is very handy should you have a failure that wipes out some in memory cache (not that you should take that as a hint that the architecture I’m using leverages in memory storage).

Until now everything in this article has been discoverable from the rather sparse Event Hub documentation. I had a bunch more questions about the provided EventProcessorHost that needed answering. EventProcessorHost is the provided tool for consuming message. You can consume messages using your own connectors or via EventHubReceiver but EventProcessorHost provides some help for dealing with which node is responsible for which partitions. So I did some experiments

What’s the deal with needing blob storage?

It looks like theEventProcessorHost writes out timestamps and partition information to the blob storage account. Using this information it can tell if a processing node has disappeared requiring it to spread the lost responsibility over more nodes. I’m not sure what happens in event of a network partition. It is a bit involvedto test. The blob storage is checked every 10 seconds so you could have messages going unprocessed for as long as 20 seconds.

Opening up the blog storage there is a blob for each consumer group * each partition. So for my example with only the $Default group and 16 partitions there were 16 blobs. Each one contained some variation of

{"PartitionId":"10","Owner":"host1","Token":"87f0fe0a-28df-4424-b135-073c3d007912","Epoch":3,"Offset":"400"}

Is processing on a single partition single-threaded?

Yes, it appears to be. This is great, I was worried I’d have to lock each partition so that I didn’t have more than one message being consumed at a time. If that were the case it would sort of invalidate all the work done to ensure in order delivery.

Is processing multiple messages on different partitions on a single consumer multi-threaded?

Yes, you can make use of threads and multiple processors by having one consumer handle several partitions.

If you register a new consumer group does it have access to messages published before it existed?

I have no idea. In theory it should but I haven’t been able to figure out how to create a non-default consumer group. Or, more accurately, I haven’t been able to figure out how to get any messages for the non-default consumer group. I’ve asked around but nothing so far. I’ll update this if I hear back.

I’m working on a system that consumes a bunch of readings from a sensor and I thought how nice it would be if I could get a rolling average into Redis. As I’m going to be consuming quite a few of these pieces of data I’d rather not fetch the current value from redis, add to it and send it back. I was thinking about just storing the aggregate value and a counter in a hash set in Redis and then dividing one by the other when I needed the true value. You can set multiple hash values at the same time with HMSET and you can increment a value using a float inside a hash using HINCRBYFLOAT but there is no way to combine the two. I was complaining that there is noHMINCRBYFLOAT command in Redis on twitter when Itamar Haber suggested writing my own in Lua.

I did not know it but apparently you can write your own functions that plug into Redis and can become commands. Nifty! I managed to dig up a quick Lua tutorial that listed the syntax and I got to work. Instead of aHMINCRBYFLOAT function I thought I could just shift the entire rolling average into Redis.

This script gets the current value of the field as well as a counter of the number of records that have been entered into this field. By convention I’m calling this counter key.count. I increment the counter and use it to weight the old and new values.

I’m using the excellent StackExchange.Redis client so to test out my function I created a test that looked like

The test passed perfectly even against the Redis hosted on Azure. This script saves me a database trip for every value I take in from the sensors. On an average day this could reduce the number of requests to Redis by a couple of million.

The script is a bit large to transmit to the server each time. However if you take the SHA1digest of the script and pass that in instead then Redis will use the cached version of the script that matches the given SHA1. You can calculate the SHA1 like so

The full test looks like

Thanks to a certain country which, for the purposes of this blog let’s call it Backwardlandia, which uses a different unit system there is frequently a need to use two wildly different units for some value. Temperature is a classic one, it could be represented in Centigrade, Fahrenheit or Kelvin Rankine (that’s the absolute temperature scale, same as Kelvin, but using Fahrenheit). Centigrade is a great, well devised unit that is based on the freezing and boiling points of water at one standard atmosphere. Fahrenheit is a temperature system based on the number of pigs heads you can fit in a copper kettle sold by some bloke on Fleet Street in 1832. Basically it is a disaster. None the less Backwardlandia needs it and they have so many people and so much money that we can’t ignore them.

I cannot count the number of terrible approaches there are to doing unit conversions. Even the real pros get it wrong from time to time. I spent a pretty good amount of time working with a system that put unit conversions in between the database and the data layer in the stored procedures. The issue with that was that it wasn’t easily testable and it meant that directly querying the table could yield you units in either metric or imperial. You needed to explore the stored procedures to have any idea what units were being used. It also meant that any other system that wanted to use this database had to be aware of the, possibly irregular, units used within.

Moving the logic a layer away from the database puts it in the data retrieval logic. There could be a worse place for it but it does mean that all of your functions need to have the unit system in which they are currently operating passed into them. Your nice clean database retrievals become polluted with knowing about the units.

It would likely end up looking something like this:

public IEnumerable<Pipes> GetPipesForWell(int wellId, UnitSystem unitSystem)
{
    using(var connection = GetConnection()){
        var result = connection.Query<Pipes>("select id, boreDiameter from pipes where wellId=@wellId", new { wellId});
        return NormalizeForUnits(result, unitSystem);
    }
}

I’ve abstracted away some of the complexity with a magic function that accounts for the units and it is still a complex mess.

##A View Level Concern
I believe that unit conversion should be treated as a view level concern. This means that we delay doing unit conversions until the very last second. By doing this we don’t have to pass down the current unit information to some layer deep in our application. All the data is persisted in a known unit system(I recommend metric) and we never have any confusion about what the units are. This is the exact same approach I suggest for dealing with times and time zones. Everything that touches my database or any persistent store is in a common time zone, specifically UTC.

If you want to feel extra confident then stop treating your numbers as primitives and treat them as a value and a unit. Just by having the name of the type contain the unit system you’ll make future developers, including yourself, think twice about what unit system they’re using.

public class TemperatureInCentigrade{
    private readonly double _value;
    public TemperatureInCentigrade(double value){
        _value = value;
    }

    public TemperatureInCentigrade Add(TemperatureInCentigrade toAdd) 
    {
        return new TemperatureInCentigrade(_value + toAdd.AsNumeric());
    }
}

You’ll also notice in this class that I’ve made the value immutable. By doing so we save ourselves from a whole bunch of potential bugs. This is the same approach that functional programming languages take.

Having a complex type keep track of your units also protects you from taking illogical actions. For instance consider a unit that holds a distance in meters. The DistanceInMeters class would likely not contains a Multiply function or, if it did, the function would return AreaInSquareMeters. The compiler would protect you from making a lot of mistakes and this sort of thing would likely eliminate a bunch of manual testing.

The actual act of converting units is pretty simple and there are numerous libraries out there which can do a very effective job for us. I am personally a big fan of the js-quantities library. This lets you push your unit conversions all the way down to the browser. Of course math in JavaScript can, from time to time, be flaky. For the vast majority of non-scientific applications the level of resolution that JavaScripts native math supports is wholly sufficient. You generally don’t even need to worry about it.

If you’re not doing a lot of your rendering in JavaScript then there are libraries for .net which can handle unit conversions (disclaimer, I stole this list from the github page for QuantityType and haven’t tried them all).

• Quantity Types
• CSUnits
• NGenericDimensions
• quantities.net
• unitcon
• units
• Measurement Unit Conversion Library
• Units of Measure Library
• Units of Measure Validator for C#
• Working with Units and Amounts
• Units.NET
• Gu.Units
• Unit Class Library

Otherwise this might be a fine time to try out F# which supports units of measure natively

The long and short of it is that we’re trying to remove unit system confusion from our application and to do that we want to expose as little of the application to divergent units as possible. Catch the units as they are entered, normalize them and then pass them on to the rest of your code. You’ll save yourself a lot of headaches by taking this approach, trust a person who has done it wrong many times.

I’m giving a talk this week at the local .net user group about ASP.net vNext. I thought I would try to get it running on my Mac because that is a pretty nifty demo. 1. If you don’t have mono installed at all then go grab the latest binaries. You need to have a functioning mono installation to build mono. Weird, huh? 2. Install mono from git. I set my prefix in the autogen step to be in the same directory as my current version of mono

git clone https://github.com/mono/mono.git cd mono ./autogen.sh –prefix=/Library/Frameworks/Mono.framework/Versions/3.6.1 make sudo make install

Now when I first did this I had all sorts of weird compilation problems. I messed around with it for a while but without much success. Google was no help so in a last ditch effort I pulled the latest and everything started to work again. So I guess the moral is that the cutting edge sometimes fails to build. On the other hand it would be good if the mono team had a CI server which would spot this stuff before it hit dumb end users like me.

Update: Mono 3.6 has been released which should fix most of the issues people were having with vNext on OSX. You don’t need to build from source anymore. The updated packages should be in brew in the next little while.

3. Install homebrew if you don’t already have it. You can find instructions athttp://brew.sh/

4. Use brew to install k. Whyk? I don’t know but it is a prefix which is used all over the vNext stuff.

brew tap aspnet/k brew install kvm source kvm.sh

This will set up kvm which is the version manager.

5. Use kvm to install a runtime. The wiki for vNext suggests this runtime but it is really old.

kvm install 0.1-alpha-build-0446

I’ve been using the default latest and it seems to be more or less okay.

6. Pull down the home depot fromhttps://github.com/aspnet/Home/. This repois the meeting point for the various aspnet projects and the wiki there is quite helpful.

7. Jump into the ConsoleApp directory and run

k run

This will compile the code and execute it. It will be compiled with Roslyn which is cool enough to make me happy. There is very little printed by default but you can change that by setting an environmental variable

export KRE_TRACE=1

I did run into an issue running the sample web application and sample MVC applications from the home repo.

System.TypeInitializationException: An exception was thrown by the type initializer for HttpApi —> System.DllNotFoundException: httpapi.dll

I chatted with some folks in the Jabbr chatroom for aspnet vNext and it turns out that the current self hosted ASP.net doesn’t work full yet on OSX. However there is an alternative inKestrela libuv based http server. I pulled that repo and tried the sample project which worked great.

If you’re around Calgary on Thursday then why not come to my talk and watch me stumble around trying to explain all of this stuff?http://www.meetup.com/Calgary-net-User-Group/

I’m a big fan of the d3 data visualization library to the point where I wrote a book about it. Today I came across and interesting problem with a visualization I’d created. I had a bunch of rows which I’d colored using a 10 color scale.

The users wanted to be able to click on a row and have it highlight. Typically I would have done this by changing the color of the row but I had kind of already used up my color space just building the rows. I needed some other way to highlight a row. I tried setting the border on the row but that looked ugly and became a tangled mess when adjacent rows were highlighted.

What I really wanted was to put some sort of a pattern on the row. As it turns out this is quite easy to do. SVG already provides a mechanism for applying patterns as fills. The one issue is that you can’t apply a pattern as an overlay to an existing fill you have to replace it completely.

First I created the pattern in d3

Here I create a new pattern element and put a rectangle in it. I rotate the whole pattern on a 45 degree angle to get a more interesting pattern. You may notice that the code references the variable d. I’m actually creating and applyingthis pattern inside of a click handler for the row. This allows me to create a new pattern for each row and color it correctly. The full code looks like

The finished product looks like

You can change the pattern to come up with more interesting effects

If you have the need to parse command line arguments in C# then might I recommend the excellent Command Line Parser. It can be installed from nuget by simply running

Install-Package CommandLineParser

Once you have it installed you start by setting up an options file which contains properties for all the options you would like your application to understand. Mine looks like

For each parameter you would like parsed you can decorate it with an Option attribute. Within this I defined the shortoptions name(u for export users and h for help) followed by the long option name. I also set the help text, required status and default value for each option.

Within the Main method of my application I called out to the option parser like so

The help text is particularly useful as you can now easily print print out help which is always a bit of a pain to maintain otherwise.

Another nifty feature of the library is the ability to define subcommands as part of your options. This allows building a command line interface similar to git in which the behaviour of the tool changes drastically based on the first undashed option:

git remoteadd http://some.url/project.git

This is quite well documented on the github wiki. I haven’t tried it yet as my needs are not that complex.

There is a version 2.0 of the library under development on githubwhich changes the interface pretty drastically. There doesn’t seem to be a whole lot of development on it so I’ve opted for the more stable version for my projects.

Overall I would recommend this library over Mono.Options which I’ve used with a high degree of success in the past.

A short while ago I wrote about how bad it is running background tasks in ASP.net. It basically comes down to “you don’t know when the application will recycle and your task will be killed”. My solution was to farm out background tasks to another machine through the use of a messaging system the likes of Azure Service Bus or MSMQ. I still believe that this is a great solution for running in the cloud. The issue with the cloud is that not only may the app pool recycle but the whole machine might disappear and pop up on another physical machine somewhere else in the data center. There may, however be scenarios in which you might like to perform an asynchronous actions on the server itself without having to worry about app pool recycles.

Until now there has been no way to do this. With .net framework 4.5.2 that all changes.

First a warning: at the time of writing 4.5.2 is very fresh. It is not yet supported on Azure or probably most other hosts you might use. To develop against it you’ll need the Microsoft .NET Framework 4.5.2 Developer Pack which can be downloaded here. 4.5.2 will eventually be installed on Azure and should also be included in updates to Windows and Visual Studio. I’ve never been able to get .net framework version adoption numbers out of Microsoft so I have no idea how long it will be before you can reasonably expect 4.5.2 to show up on the majority of machines. It really doesn’t matter for this feature as you only need updates to your servers.

The first thing you’ll need is to acquire the new developer pack and install it. Next you’ll need to update the target for your current application to target .net 4.5.2. Getting to be quite a few platforms in there now, huh? Let’s not look at the portable class library options.

A real good example background task is sending e-mail. This is an operation which can take quite a long time(relatively speaking) and is not, typically, something which needs status information fed back to the user. This, as it turns out, is a bit more complicated than we would like. There is a huge post over on StackOverflow about how to correctly send an asynchronous e-mail. Much of the confusion comes in around sending mail asynchronously. If you look at the SmtpClient class there are now two different flavours of sending async: SendAsync and SendMailAsync. The first is the old method for sending asynchronously complete with a cancellation token nobody has ever used. The second is the newer method which uses the async/await format. The old method caused a lot of issues with there was a problem sending mail. The errors would frequently be swallowed or bubble up so high as to blow up the worker role. This was because it executed outside of the normal page context so people forgot that their normal error catching code wouldn’t intercept issues. Whenever .net 5 is release I hope Microsoft make removing the old method a breaking change. To do so they really should have marked the method as deprecated in this release - perhaps for .net 6 then.

In this example we’ll actually use the newer SendMailAsync method and we’ll do it in a background thread. Using async allows fewer threads to be used in situations where there is some load on the system we should get better performance.

You will almost certainly wish to add some additional error checking in there. You should hook into UnobservedTaskException as the method returns a void task.

A Final Warning

A background task started in this fashion still only delays the recycling of an app pool for 30 seconds. Thus if your task takes longer than 30 seconds to execute it will still be killed. For long running tasks you are still far better saving them to a persistent queue or some other storage.

References:

http://www.davidwhitney.co.uk/Blog/2014/05/09/exploring-the-queuebackgroundworkitem-in-asp-net-framework-4-5-2/

http://blogs.msdn.com/b/dotnet/archive/2014/05/05/announcing-the-net-framework-4-5-2-release.aspx

If you spend any time working in oil and gas in this province then you’re going to run into a situation where you need to put some stuff on a map. If you’re like me that involves complaining about it on twitter

I don’t know how I get myself into working with GIS data. I seriously have no idea.

“” Simon Timms (@stimms) April 28, 2014

The problem is that GIS stuff is way harder than it looks on the surface. Maybe not timezone hard but still really hard. Most of it comes from the fact that we live on some sort of roughly spherical thing. If we live in flatland mapping would be trivial. As it stands we need to use crazy projections to map a 3D world onto a 2D piece of paper

https://www.youtube.com/watch?v=n8zBC2dvERM

There are literally hundreds of projections out there which stress different things. Add to that a variety of coordinate systems which can be layered on top of it. There is the latitude/longitude system with which we’re all familiar but there are also a bunch of others. In Western Canadathe important one is the Dominion Land Survey(well most of Western Canada, we’ll get to that). The Dominion Land Survey was actually a series of surveys starting as far back as the 1870s. Bands of bearded men (there may have been bearded women too, everybody back then had beards) traveled around Canada plunking down lines to divide the land into 1sq mile sections called, well, sections. Why miles? Because of some guys calledJ. S. Dennis and William McDougall figured that a lot of people would be coming up from the states and would better understand miles. Thanks for screwing us over, again, with your stupid outdated measurement system, United States.

Anyway you can read a ton more about the system over atwikipedia. The important thing to know is that Western Canada is divided into 6 mile by 6 mile blocks know as townships and that these are divided into 36 sections and each section is divided into 4 quarter sections. Sections can also be divided into 16 legal subdivisions commonly known as LSDs. The LSDsare numbered in the stupidest way possible starting in the bottom right corner and counting up by going left and then up then pretending that we’re a snake and just flipping back and forward

13	14	15	16
12	11	10	9
5	6	7	8
4	3	2	1

You might has well have numbered this things completely randomly as far as I’m concerned

1	7	27	cranberry
12	6	also 6	9
5	6	7
4	33	null	1

I’m told that this all makes sense if you have some background in cartography.

The point of all of this is that LSDs are super important in the oil and gas industry because that is how you lease land. The result is that people want to see LSDs on their maps and, as seems to frequently happen, I got the task of building a map. This post is about how to get LSDs onto a map and show it to your clients who aren’t asses. Mostly.

The company for which I’m working has most of its interests in Saskatchewan, Alberta and BC. I started with Saskatchewanas I figured that I might as well get into thinking like a cartographer and working right to left straight off the bat(I hear that cartographers from Arabic countries work from left to right to maximize the confusion). The first step was to find some LSD data. Saskatchewan have an open data portal at http://opendatask.ca/data/which contains a link for LSD data. What you want in particular is theSaskGrid2012. This file contains a lot of stuff but the four things you want are the various high level map structures: township, section, quarter section and legal sub division. We probably don’t need quarter section as once we get to that level most people are interested in LSDs. Inside one of these zip files are a number of files which, as it turns out, are Esri shape files. Esri is a piece of GIS software. It is expensive. However there is a free alternative which has all the functionality we need along with 9000 pieces of functionality we don’t:QGIS. If you download and install this software it will let you take a look at the shape files. You can add it by clicking on “Add Vector Layer” then pointing it at the .shp file.If you load the township file it will get you something which looks like very much likeSaskatchewan. What’s more is that if you click on the little identify featuretool then on the map it will tell you the name of that township. Awesome!

To give you an idea of how many of these features there are here is what the townships look like:

For each township there are 36 section (6×6) and for each section there are 16 (4×4) LSDs. So for Saskatchewan there are something like 7000 townships, 250 000 section and a mind blowing 4 million LSDs. Quite a bit of data.

So now we have a map. But it only works inside of QGIS and I’m sure not going to go around supporting that. It would be really nice if this layer was available on a Google maps like thing.

Leaflet

Leaflet.js is a nifty library for manipulating maps. It can use any number of map backends but I usedOpenStreeMap because it is awesome. Like really awesome. Start a new ASP.net project and then go and grab the latest leaflet from theirsite. Leaflet is in nuget but it is an older version which hasn’t been updated for 6 months or so. Add the files to the site bundles in BundleConfig.cs. I also included my site files in this bundle for convenience

I created a typescript file for the home page based on the example on the lealflet home page

Once I’d added a div in my Index.cshtml under the home controller and constructed the map I ended up with a map centered on, roughly, the border between Alberta and Saskatchewan.

Now to get our grid lines onto the map. To start I added a simple polygon

#file-index-ts-L14-L18 This builds a map which includes a nifty redbox.[](http://stimms.files.wordpress.com/2014/05/map2.png)This basically proves that we can draw out LSDs as needed. So the next thing is going to be combining the shape file data we had above and the map we have from OpenStreetMap. # Exporting KML Files I went down a few blind alley with this one before coming up with, what I think, is the best option. I decided to exploit the power of the geometry types in SQL Server to find any LSDs inside a bounding box. To display all the LSDs or even the townships at a low zoom level is messy. It covers up the map and is too detailed for that level. As such only showing a few at a time is a good idea. To figure out which ones to show requires putting in place a filter to show only LSDs within a bounding box, the bounding box described by the map at a high zoom level. Getting the data into SQL server is a two step process: this first is to create KML files and the second is to load them into SQL server. For each of township, section and LSD I loaded them into QGIS. Then right clicked on the layer and hit Save as. In that dialog I selected KML as the format and a file into which to save the layer.[](http://stimms.files.wordpress.com/2014/05/save.png)This generates a pretty sizable export file, well over 4GB for the LSDs. Don't worry though because these crummy things are XML so most of that will disappear when loaded into SQL server. If you want you can attempt to simplify the geometry in QGIS which will reduce the export size at the cost of fidelity. The file size does, however, pose a bit of a problem for our import tools as they use a DOM Parser for reading KML instead of a SAX parser. If I were going to make a living at manipulating maps this would be a place I expended some effort to correct. # Importing KML Files into SQL Server I hunted around and found a tool called[KML2SQL](https://github.com/Pharylon/KML2SQL)to import KML files into SQL server. It had a few issues with it so I[forked it](https://github.com/Pacesetter/KML2SQL)and made some updates. If you're going to make use of the tool then you're going to be better off using my fork at the current time. However if my pull requests are merged then the master repo may be more healthy. As I mentioned the KML files are too large to be consumed by the import tool. To solve this I wrote a quick KML splitter application, which splits the KML files into 500 feature blocks. I've included the source on[github](https://github.com/stimms/LSDMap/tree/master/KMLSplitter). It isn't pretty but it gets the job done. [](http://stimms.files.wordpress.com/2014/05/kml2sql.png) The KML2SQL tool will dynamically build tables with the correct columns in them so that's great. All I did was plop in my SQL server credentials and point the tool at the directory containing the output files from the splitter. I left this to churn for a few minutes. A few hours for the LSD divisions. I did see some import errors related to open polygons which I generally ignored. It is something I'll have to come back to in a while but they were perhaps 1% of the imports. SQL server wants the polygons to have the same star coordinate as they have end coordinate, which is only reasonable. The data from the government doesn't quite have that but for free data you can't complain too much. # Querying the Data Now that we have the data in the database the next step is to get it out onto the map. To start we need to have the map ask for some data when it is resized or panned. I hooked into the zoomend and dragend events in Leaflet. I found that it made sense to display the townships starting at zoom level 10 and at higher zoom levels show more detailed data such as sections or LSDs. I threw together a Web API controller to do the work of querying the database. Doing geospatial queries in SQL server is a bit more complicated that I would like but complex types in relational databases always are. I don't know, relational databases, man. I'm a big fan of the light weight ORM Dapper so I installed that and stole a bit of code for doing spatial queries from a posting by Sam Saffron on StackOverflow. I had to modify it a bit and ended up with:

This can be used to select the intersecting polygons by doing

STIntersects will check the number of intersection points between the given polygon and the ones in the database. If there are any intersections then we have a match and we add that to the result set. Actually building the search area can be a pain as you actually build a geometry like so

This provides a set of data to return to the client.

Plotting the Data

We’re almost there, folks, thanks for staying with it. The last step is to get the returned data plotted on the map. This is simply done by adding the polygons to a multipolygon layer

That’s it folks! You now have a map which looks like

The labels are a bit jaggedy right now as I’m just using the envelope center to calculate the position of the label. It doesn’t take a whole lot to screw that up. Putting them in the top left corner helps with that a lot

P.S. I promised I would get back to BC’s system. They don’t use the DLS they use theNational Topographic System