Land Registry data for Feb 2014

I have uploaded the Land Registry house price data to doogal.co.uk.

There’s been a lot of talk in the press recently about there being a two speed housing market, London and the rest of the UK. You can see this illustrated fairly clearly if you first look at house prices in Blackburn then compare them with prices in West London.

Prices in Blackburn can be broken into three distinct periods. Prior to 2003, they were gently rising, probably in line with wage increases. Then in 2003 things went ballistic (I’m not sure of the trigger for that, although I’d guess it was easier access to mortgages). 5 years later in 2008, things ground to a halt, sales fell of a cliff and prices have been flat-lining ever since.

But look at West London and the only similarity is that sales volumes dropped off rapidly in 2008, but you’d never know that there was a financial crisis at all. Houses have been a one way bet for nearly 20 years. You’ve got to wonder how long it can go on.

Bluffers Guide to responsive design

A while back I spent a bit of time making doogal.co.uk more mobile friendly. I’d put it off for a long time primarily because I thought it would be really hard. But actually it turned out to be not too tricky. So here is my not so comprehensive guide to making your site mobile friendly with responsive design

The first thing to do is decide at what screen size your design will change from the normal design to the mobile design. My decision was that tablets should see the standard design but mobile phones should see the mobile design. So the media query I use for all my mobile CSS is

@media handheld, only screen and (max-width: 600px), only screen and (max-device-width: 600px) 

Better menus - My standard menus were tricky to use on a small screen, but fortunately they were built using styled unordered lists i.e. <ul></ul>, so I was able to use the Mobilemenu JavaScript library. This converts the list into a dropdown <select> which is much more useable on mobile devices.

Hide stuff – display:none is your friend. Most websites have bits on the page that may be useful but aren’t entirely essential. On a big screen we can get away with that, but on mobile devices it’s necessary to concentrate on the essential information. So hide anything that isn’t needed. I have several tables with many columns, but each row links through to more information, so I hid several of the columns since the tables didn’t render very well. The best approach to this appears to be CSS like the following

.postcodeTable  td + td { display: none;} 

No more table layouts – We’ve been told for years not to use tables for layout of our websites. But I’ve certainly been guilty of it, since it’s always easier to build a multi-column layout using a table. But now is when the chickens come home to roost. You may want two columns on the desktop, but on a mobile device, you’ll probably want to have the two columns stacked on top of each other, so those tables will need to be converted to div’s which float on the desktop and don’t on mobile devices.

Server side control – The solutions so far have all been client-side. I think this is generally the easiest way to deal with the issue, but there is certainly a good argument for saying content shouldn’t be getting pushed to the client if the client will never actually display it. If you’re using PHP on the back-end, Mobile Detect Library can be used to tailor your HTML before it leaves the server. One place where you may need to do this is adverts. Google’s T&Cs say you can’t hide adverts, so using display:none for them is probably a bad idea.

The beginning of the end of Metastorm BPM

It looks like development of Metastorm BPM has, if not stopped completely, at least slowed down. So I thought I’d write something about my thoughts about what was a big part of my professional career. If you want the full history, this isn’t it, have a look at Jerome’s book.

For me, it all started sometime in 1997. I was writing software for a firm called Bacon and Woodrow that sold actuarial software. It wasn’t really my kind of thing, but it was my first proper job, something to add to the CV. I got a call from a former colleague, Richard Kluczynski, who’d gone off to write his own software, then got a gig with a software house called Sysgenics. Before the days of mobile phones (or at least before I had one), I remember having to wander the streets of Epsom to find a phone box to call him back to discuss properly, away from the office. It sounded interesting but it wasn’t the right time for me as we were trying to get the first Windows version of our software out the door.

A few weeks or months later I got a call from a recruiter asking if I was looking for a new job and telling me about a company called Sysgenics. We were still trying to get our Windows version out the door, but I guess getting called about the same company twice peeked my interest. I remember looking at their website and getting pretty excited about the screenshots of some kind of graphical tool for building workflows called e-work. Before I knew it, I was in Wimbledon for an interview with Steve Brown and Jerome Pearce. And an hour later I was in the pub. This was obviously a great place to work!

And it was. We had no customers but some VC money to keep us ticking over. We were using the latest technologies (Delphi and MS Access!). Having no customers meant we could build stuff and break stuff without having to worry too much about upsetting people using the software, so we were always making a lot of progress.

Before long we were bought out by Metastorm. Looking back, that was actually a bit weird. Initially they seemed to be some massive software company but looking closer, the one product they sold, InForms, was clearly coming towards the end of its life, since it was tied into the dying Novell Groupware. But they had what we needed, money, and I guess we had what they needed, some modern software to sell.

The years flew by, six in fact. By that point we had quite a few customers, the little startup was a proper software house. There was structure and rules, forms to fill in, basically not really my scene anymore. So I flew the nest to work for a financial software house in central London. But a couple of years after that, Jerome asked me to join his little band of Metastorm consultants. So I built a shed and got to work building stuff on top of Metastorm e-work. Metastorm e-work became Metastorm BPM, we carried on calling it e-work… Metastorm started rewriting it from scratch in .NET, releasing it as version 9 (missing out version 8, some kind of off by one error I think).

I’d probably still be working in my shed for Jerome had the financial crisis not hit, caused major stress to our main client who then couldn’t pay us. So I went to Croydon, regretted it almost immediately and started working for myself. Back to the shed…

Then I started doing some work for Steve’s new company, Business Optix. That eventually became a full time job and is where I am now. Meanwhile Metastorm got bought out by OpenText. Given the price they paid, you’d think anyone who’d taken up their share options would have done well out of the deal, but you’d be wrong. Somebody must have made a nice chunk of money out of it, but it wasn’t the people who’d originally developed the software (this isn’t bitterness on my part, I never exercised the option on my shares).

But not content with one BPM tool, OpenText also bought Cordys and Global 360. I guess the writing was on the wall for two of those products at that point, why would a company want three BPM tools? Anyway, it looks like Metastorm BPM is one of the victims. You have to wonder why OpenText bought them in the first place, presumably not for the customer base, already fed up with having to rewrite their processes for version 9, now fuming that they need to rewrite again in some other system.

Land Registry sales data uploaded to doogal.co.uk

I’ve uploaded the latest Land Registry sales data, covering sales for January 2014, to my website. The good or bad news, depending on your point of view, is that prices continue to creep up. 

ONS postcode data for February 2014 available

I’ve just uploaded the latest postcode data from the ONS to my website. My checks suggests everything is OK with the data but let me know if you spot anything awry

Land Registry December 2013 data on doogal.co.uk

I’ve imported the latest Land Registry to doogal.co.uk. As ever, little can be inferred from a single month of data but draw your own conclusions. And let me know if you’d like to see this data in some other way.

doogal.co.uk is more mobile friendly

I’ve been trying to ignore mobile devices for a long time. Although doogal.co.uk has always been available on mobile phones, so long as you were happy to squint and zoom in and out a lot, it’s never been particularly useable. I’d kind of hoped that as more people started browsing the web from their phones, the phones would get bigger screens and I’d get away without doing anything. Whilst some of the phones have got bigger, it seems a lot of people don’t want to talk into a phone the size of a paperback book. Understandable really, they don’t want to look like dicks.

On top of that, Google keeps telling me my site isn’t mobile friendly and it’s got to a point where a significant percentage of my visitors are using small devices to access the site, so I figured it was time to bite the bullet and fix it. So now if you visit from a smart phone, chances are you’ll see the more mobile friendly version of the site. In fact if you squidge your desktop browser to a small enough size, you’ll also see the mobile layout. Of course, just like the regular desktop site, it’s butt ugly but there is a reason why web design doesn’t appear on my CV!

But if you spot anything broken, please let me know in the comments or email.

Improving the performance of MySql bulk inserts

When I was trying to improve the performance of my old server, I came across this page. Unfortunately it didn’t help with the old server and just made me realise I needed to upgrade my server hardware.

But once I got my new hardware, I wanted some tweaks to improve the performance of importing postcode and property sales data. Both were taking days to complete. One suggestion from that page was to use SET autocommit=0;. I was a little sceptical, but was willing to try anything to speed up the import.

So all I did was add the statement on the start of every insert and add a counter to my code. After 1000 inserts, I committed the changes, using the following

              if (count % 1000 == 0)
              {
                command.CommandText = "COMMIT";
                command.ExecuteNonQuery();
              }

And this made a huge difference to my imports. Whereas I was importing for days before, now the time taken was down to hours. Maybe I could improve it further by committing the changes even less frequently, but I’m pretty happy with the current situation.

So in conclusion, if you need your data imports to run quicker, start using SET autocommit=0; now!

November 2013 Land Registry data

I’ve uploaded the latest Land Registry property sales data to my website, covering November 2013. As always, not a lot can be inferred from a single month’s data, but drilling down to individual postcode areas shows fairly different stories for prices and sales over the long term.

Google’s weird support for KML

KML is a standard XML format used to describe geographic data. It’s been owned by Google ever since they bought the company who originally developed Google Earth. So you’d think they’d have great support for KML in their products, right? I thought so as well until the other day when I was asked about a problem with some of the KML from my site. So here’s a summary of how different Google products cope with KML.

Google Earth – As you’d expect, Google Earth has full support for KML

Google Maps Javascript API – Using the KmlLayer class, it’s possible to load up KML files from anywhere on the web. There are some limitations on file sizes and not all of the features of the KML file format are supported, but for simple points and polygons it works perfectly well.

Of course, not everyone is a programmer, so using the Javascript API is not really an option for a lot of people. Unfortunately this is where things get messier…

Custom Maps (Maps Engine) – If you try to create a custom map in the new Google Maps, importing from KML is not even an option (the user interface says ‘Google Engine Lite’, so maybe if you pay some money to Google you’ll be able to import KML), which means you have to switch back to…

Custom Maps (Classic) – This claims to support importing KML. My experiments suggests it copes with KML files containing points (up to a fairly small maximum size), but I couldn’t get it working with files containing polygons.

Fusion Tables – Hidden in a corner of the web, is Google Fusion Tables. Basically it’s a spreadsheet with maps and has always seemed like a cool piece of technology but it hasn’t been pushed by Google much. But it seems Fusion’s support for KML is much better than either of the Custom Maps options. Points and polygons worked for me.

What I find weird is that all the web based technologies above are produced by a single company and all do pretty much the same thing, take a KML file and display it on a map, and yet they all clearly use completely different code. Maybe it’s time for some consolidation there?

Bing Maps – If none of the Google options work for you, maybe Bing Maps will do the trick. Points and polygons seem to work, with a couple of restrictions. There’s a fairly low limit on the maximum file size and the Import button only seems available in Internet Explorer.

No doubt there are other options, so let me know if you find any better alternatives.

Land Registry sales data back to 1995 and a new server

After much fighting with my old server trying to import the 18 million property sales from 1995 to today, I decided it was time to upgrade the server. I’ve now got 4 times as much memory, twice as many CPU cores and I’ve successfully imported all the Land Registry data. I’ve flicked the switch to point doogal.co.uk to the new server and so far everything is looking good. Let me know if you see any problems or have any performance problems. Also let me know if you’d like the property sales data sliced and diced in any other ways.

Handler "PHP55_via_FastCGI" has a bad module "FastCgiModule" in its module list

Trying to get a PHP app working on Windows Server 2012, I got the following error message

“HTTP Error 500.21 - Internal Server Error

Handler "PHP55_via_FastCGI" has a bad module "FastCgiModule" in its module list”

I’d installed PHP via the IIS Web Installer but had failed to do one more thing, enable CGI in ‘Add Roles and Features’ in Server Manager, under Web server (IIS)/Web Server/Application Development. After enabling that, everything sprang to life

The Land Registry destroyed my database

I started to import all the Land Registry data back to 1995 into doogal.co.uk, which was taking a looooong time. So I tried a little optimisation which involved clearing out some of the data I had already imported. Unfortunately that corrupted my database, which meant I had to drop my property sales table and start again from scratch.

So the property sales data on the site has less information than it did, but hopefully it should all be back online in the next few days. 

Kingston Council optimise their revenue capturing techniques

Not content with having one of the highest council taxes in the UK…*

Local authority	2013-14 Average Band D (excluding Parish Precepts)
Gateshead	1,443.20
Rutland UA	1,430.51
Hartlepool UA	1,418.70
Walsall	1,410.26
Nottingham UA	1,404.42
Stockport	1,397.05
Oldham	1,392.95
Kingston-upon-Thames	1,379.65

Not content with charging us £80 a year to park on a street somewhere near our house…

Kingston Council have made some more changes. Now not only can we not park on our own street most of the day, we can’t load there either. Practically I’m not sure how this is meant to work. How are we ever meant to get something delivered? How are older residents meant to get picked up from their house? But not happy with this change, the council have also introduced a parking enforcement smart car that roams the streets trying to catch any parking offenders. This was a big money spinner for Sutton council and I guess it will be the same for Kingston. Last Saturday I was caught unloading my car by this roaming enforcer. Unlike traffic wardens, it’s impossible to have any kind of rational discussion with them since they drive off as soon as they’ve got their photographic evidence.

So what to do? Well Kingston Council maybe behaving like the Goldman Sachs vampire squid, but fortunately there are local elections coming up soon. Frankly anyone who stands in that election who will stand up for residents’ interests will get my vote. Which pretty much excludes any of the three main parties…

And maybe another option is just to not play the game anymore. Get rid of the car, try to reduce my income to a level where I no longer have to pay the full council tax. Or just move somewhere with a more sensible level of council taxation.

Update – I appealed against the ticket since there were no markings on the pavement to show loading and unloading was restricted, and my appeal was accepted. I’m happier, but still not satisfied with the council’s tactics.

*Just look at the original document I pulled this data from to see the dramatic difference in council tax levels, can anyone explain these vast differences?     

Land Registry Oct 2013 data uploaded to doogal.co.uk

I’ve uploaded the latest Land Registry data to my website covering sales in October 2013.

In more interesting news, with a few days to spare the Land Registry have met their pledge to upload all sales data from 1995 to today. That will take a little longer to upload…

November 2013 ONS postcode data uploaded to doogal.co.uk

I’ve uploaded the latest ONS postcode data to my website. My data integrity check suggest it’s all fine but the size of the CSV download has shrunk a bit. I’m not entirely sure why that is, so let me know if you spot any anomalies.

Joining the dots

Today's BBC business pages contain these two headlines
Black Friday flurry for UK retailers
UK household debt hits record high
Black Friday seems to be another pointless tradition we've just picked up from the US, to give us all another chance to buy more shit we don't need and, it would appear, we can't even afford. I will not be participating.

Making cycling safer in London

Cycling is in the news due to several deaths in London. As someone who has cycled quite a lot in and around London (although very little in Central London) I have a few suggestions.

1. Don’t panic. Yes, cyclists have died and that is a tragedy for the families involved. But the number of deaths has remained fairly constant, even with an increasing number of cyclists on the road (see this document for details). And more pedestrians die than cyclists (where is the uproar about that?)

2. Get involved. Cycling may be no more unsafe than in the past but we still compare badly to other countries, even if our own government ministers try to bend this obvious truth (of course Norman Baker is an utter berk, just read his poorly researched conspiracy theory about the death of David Kelly). And pressure from the public may make a difference. Space for Cycling are targeting the upcoming local elections, which seems like a good idea.

Another obvious target for pressure is Boris Johnson. Boris seems to want to be a friend to motorists and to cyclists, which is a noble plan but is doomed to failure. Cars and bikes do not mix, so anything that is going to improve life for cyclists will almost certainly make life worse for cars. So Boris, which side are you on?

3. Help yourself. Here are some things I do when I’m riding my bike

• Find a route that you are comfortable with. One of the weird things about the cycling super highways is they encourage cyclists on to major arterial roads. Generally I try to avoid main roads as much as possible, since they are a pretty unpleasant experience. If you’re commuting to the same location day after day and you don’t feel safe on the route you’re using, then find an alternative. This is is where a smart phone comes in really useful. You can nip down some unfamiliar roads to see if the route is better and if you get lost, you can find your way using the phone’s maps. If the route is better, your phone should be recording where you went.
• Wear a helmet. I’m still surprised when I see a cyclist without a helmet, or even more bizarrely a child with a helmet sharing a bike with a parent without a helmet. It might not help if you get dragged under a lorry, but it will certainly help for less serious accidents.
• Don’t jump red lights. I have to admit I don’t always stick to this, but there are many junctions where you would have to be insane to ignore the lights.
• Assume every driver is an idiot. Most drivers are sensible but there are still plenty who will do daft things, like turning at a junction without indicating, or pulling out right in front of you.
• Don’t undertake lorries. Undertaking in general is not a great idea, but undertaking lorries is an even worse idea since they likely won’t be able to see you at all.

Getting the icon for the user’s default browser in C#

In my last post I showed how to get hold of the associated icon for a file. Now, say you want to get hold of the icon for the user’s default browser, you may think you can just grab the icon for HTML files and that’ll do the job. Whilst that will work for some users, it won’t work for all of them. The application used to open HTML files does not need to be the same application that is used to open websites. For example changing your default browser to Chrome won’t change the default application for HTML files to Chrome. So here’s an extension to that previous code to get the icon for the default browser. It won’t work for XP since the way default applications are handled has changed but it should work with all later OSes.

    private static string GetDefaultBrowserPath()
    {
      const string userChoice = @"Software\Microsoft\Windows\Shell\Associations\UrlAssociations\http\UserChoice";
      using (RegistryKey userChoiceKey = Registry.CurrentUser.OpenSubKey(userChoice))
      {
        if (userChoiceKey != null)
        {
          object progIdValue = userChoiceKey.GetValue("Progid");
          if (progIdValue != null)
          {
            string progId = progIdValue.ToString();
            const string exeSuffix = ".exe";
            string progIdPath = progId + @"\shell\open\command";
            using (RegistryKey pathKey = Registry.ClassesRoot.OpenSubKey(progIdPath))
            {
              if (pathKey != null)
              {
                string path = pathKey.GetValue(null).ToString().ToLower().Replace("\"", "");
                if (!path.EndsWith(exeSuffix))
                {
                  path = path.Substring(0, path.LastIndexOf(exeSuffix, StringComparison.Ordinal) + exeSuffix.Length);
                }
                return path;
              }
            }
          }
        }
      }

      return null;
    }

    public static Icon GetDefaultBrowserLargeIcon()
    {
      string browserPath = GetDefaultBrowserPath();

      if (!string.IsNullOrEmpty(browserPath))
        return GetLargeIcon(browserPath);

      // last chance (probably XP), just grab the icon for HTML files
      return GetLargeIcon("test.html");
    }

    public static Icon GetDefaultBrowserSmallIcon()
    {
      string browserPath = GetDefaultBrowserPath();

      if (!string.IsNullOrEmpty(browserPath))
        return GetSmallIcon(browserPath);

      // last chance (probably XP), just grab the icon for HTML files
      return GetSmallIcon("test.html");
    }

Getting the associated icon for a file in C#

Sometimes it’s useful to display an icon for a file in an application and probably the best icon to display is whatever the operating system uses. The Windows API provides the SHGetFileInfo function for this purpose, so here’s a little wrapper around it. All the methods take a file name parameter, but the file doesn’t need to actually exist.

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace TestIcons
{
  public static class ImageUtilities
  {
    [StructLayout(LayoutKind.Sequential)]
    struct SHFILEINFO
    {
      public IntPtr hIcon;
      public IntPtr iIcon;
      public uint dwAttributes;
      [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 260)]
      public string szDisplayName;
      [MarshalAs(UnmanagedType.ByValTStr, SizeConst = 80)]
      public string szTypeName;
    };

    static class Win32
    {
      internal const uint SHGFI_ICON = 0x100;
      internal const uint SHGFI_LARGEICON = 0x0; // 'Large icon
      internal const uint SHGFI_SMALLICON = 0x1; // 'Small icon
      internal const uint SHGFI_USEFILEATTRIBUTES = 0x10;
      internal const uint SHGFI_LINKOVERLAY = 0x8000;

      [DllImport("shell32.dll")]
      public static extern IntPtr SHGetFileInfo(string pszPath, uint dwFileAttributes, ref SHFILEINFO psfi, uint cbSizeFileInfo, uint uFlags);

      [DllImport("User32.dll")]
      public static extern int DestroyIcon(IntPtr hIcon);
    }

    public static Icon GetSmallIcon(string fileName)
    {
      return GetIcon(fileName, Win32.SHGFI_SMALLICON);
    }

    public static Icon GetSmallOverlayIcon(string fileName)
    {
      return GetIcon(fileName, Win32.SHGFI_SMALLICON | Win32.SHGFI_LINKOVERLAY);
    }

    public static Icon GetLargeIcon(string fileName)
    {
      return GetIcon(fileName, Win32.SHGFI_LARGEICON);
    }

    private static Icon GetIcon(string fileName, uint flags)
    {
      SHFILEINFO shinfo = new SHFILEINFO();
      IntPtr hImgSmall = Win32.SHGetFileInfo(fileName, 0, ref shinfo, (uint)Marshal.SizeOf(shinfo),
        Win32.SHGFI_ICON | Win32.SHGFI_USEFILEATTRIBUTES | flags);

      if (hImgSmall == IntPtr.Zero)
        return null;

      Icon icon = (Icon)Icon.FromHandle(shinfo.hIcon).Clone();
      Win32.DestroyIcon(shinfo.hIcon);
      return icon;
    }
  }
}