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;
    }
  }
}

None of our politicians have a clue

So we have a Labour party who are talking about a cost of living crisis. Good on them. Until you hear their main policy, which seems to be fixing the cost of energy. I’m pretty sure the talk of the lights going out are completely alarmist, but I doubt trying to fix prices is going to work, the fact is fossil fuel costs go up and down and no politician can stop that happening.

But Ed is missing the point a little anyway. For most people, energy is a big cost but it’s generally outweighed by something else, housing. We hear a lot about energy price rises, but not so much about rises in rent. And if house prices go up, that’s a good thing apparently? As a home owner, I don’t get it. If I want to move up the ladder, higher prices mean I need a bigger mortgage to move anywhere. First time buyers presumably don’t want higher prices. As a parent, I don’t want housing to be completely unaffordable for my child when she gets to an age when she wants her own place. Yes, there are people who gain from house price rises, but I’m pretty sure they are outnumbered by people who would actually gain from falling prices.

But enough of the Labour party, what have the Conservatives got to offer? Oh yes, Help To Buy, the scheme that seems most people are a little concerned about. Apparently it may cause a new housing bubble. What the ….? We haven’t escaped the last bubble yet. We are still number one in the housing bubble stakes. What George seems to have missed is that the problem with our housing market is not a demand problem, there’s plenty of demand. The problem is not enough houses are being built and the ones that are being built cost too much!

So what’s the solution? First, the British need to get over their obsession with house prices and realise houses are a place to live, not pay for their retirement. Second, we need to radically change our planning laws, so we can build a lot more houses. Third, the government needs to start building some council houses.

The sad thing is none of this looks likely to happen. No political party is going to say “We will build houses in your back yard and let other people do the same and the price of your property will then fall, but in the long run it will be good for the country”

Our dog really hates cats

Eyes and nose removed, most internal organs gone. Fortunately it wasn’t a real cat, just a toy.

Postcode populations

doogal.co.uk has some exciting new postcode data (depending on your definition of exciting…). I’m in the process* of importing population data for each active postcode in England and Wales. This includes the estimated population and number of households and comes from the ONS website based on the Census of 2011, which I’ve just realised only covers England and Wales. Scottish data is a separate download and will be my next task. Northern Ireland doesn’t seem to provide population data per postcode.

The data appears on the information page for each postcode and will become part of the CSV downloads once the import is complete. I could aggregate this data up to postcode district and sector level, so let me know if that would be useful to you.

* A database of 2.5 million postcodes can take some time to update!

More UK parliamentary constituency postcode data on doogal.co.uk

I’ve added a UK parliamentary constituency page to doogal.co.uk for England, Wales and Scotland (Northern Ireland continues to not provide its data under an open license). You can view maps and grab KML and CSV downloads of the postcodes for each constituency.

If you have requests for any different slicing and dicing of this data, let me know.

National Park postcodes

Postcode data can be diced and sliced in lots of different ways. So I’ve sliced the UK postcodes so you can grab data for each National Park. I’ve no idea if this will be useful to anyone, but sometimes the weirdest things turn out to be hugely popular, so here it is

http://www.doogal.co.uk/NationalParks.php

Data is available in CSV and KML formats.

“unknown CMAP subtable format” error when generating PDFs

Whenever I get an unusual exception being thrown by my application that I don’t know how to fix, I Google the error message which generally leads to some kind of explanation of what’s happening. But Google seems to have a hole in its knowledge regarding this “unknown CMAP subtable format” error I was getting. So I’ll try and fill that hole.

Our software uses XSLT to generate XSL:FO files that are then converted to PDF files using AltSoft’s XML2PDF engine. The error was occurring deep inside their assembly and the only clue I could get from looking at the call stack was that the problem was font related. My immediate suspicion was that the error was due to the user using a strange font in their input, but the fonts in use were fairly standard (Arial, Calibri).

I eventually figured out the problem was due to some of the particular characters being used in the input text. It seems the user had pasted some text from Word that contained some characters that weren’t available in the fonts used in the output. Cleaning up the user’s input text stopped the exception being thrown.

Postcode information sites

The release of free postcode data from the Ordnance Survey with very relaxed licensing has led to a raft of sites showing off this data in different ways. I’ve compiled a list of the ones I’ve spotted and will add to this list if I find anymore.

http://www.doogal.co.uk/UKPostcodes.php – Obviously first up is my own site. I’m unable to write an objective review, so won’t.

http://www.free-postcode-maps.co.uk/ – This site doesn’t provide all the postcode data (understandably since it looks like it’s been set up by a company who sell this data in various flavours) but does provide some nice map overlays showing postcode districts and sectors. So nice in fact, I may borrow some of their ideas for my own site.

http://www.britishpostcodes.info/ – I was a bit upset when a search for my own postcode brought up this site above my own site, but it does provide a lot of useful information for each individual postcode. I haven’t figured out where all the data has come from, but once I do, I’ll probably get some of it on my site.

http://postcodeof.co.uk/ – Again a search for a postcode brought up this site above my own, very disappointing. But the site was intriguing, if only for the broken English in the content. A quick domain check showed it was registered by someone in Serbia. I never realised Serbians had a passion for UK postcodes! The other interesting thing is the use of sub-domains for each individual postcode, presumably for SEO reasons. There’s not much data for each postcode, but things may improve.

http://mapit.mysociety.org/ – If you have a postcode and you want to find out what administrative areas it is part of, this site if useful. You can find the electoral constituency, local authority wards, census areas etc and also grab the boundaries of those areas. It also has an API, so you can automate your data grab!

Land Registry to release all data from 1995 onwards

The Land Registry has been releasing its data under the Open Government Licence since the start of 2012. This was great but only covering the last year and a bit meant it wasn’t that useful. But when I popped over to the Land Registry site the other day I noticed they’d got some historical data and are planning to release all of their data from 1995 onwards.

I’ve sucked this data into my site, but I haven’t done anything particularly exciting with it yet. It’s interesting (to me at least) that the average price that I’ve calculated using a straightforward mean (about £230,000) is so different to the average price that the Land Registry comes up with (about £160,000). They do some fancy maths on their numbers but it still seems odd that they are so different.

But me and the Land Registry agree on one thing, the trend. House prices have basically gone nowhere for the last 3 or 4 years.

Postcode electoral constituency data on doogal.co.uk

I’ve just recently imported the latest ONS UK postcode data and added the Westminster constituency data at the same time (except for the Northern Irish postcodes, which still aren’t covered by the OpenData initiative). I’ve only surfaced this on the map pages for individual postcodes, but I’ll show it in other places as time allows. Let me know if you need this chopped up in any particular way.