David Cameron defaced

When the smug face of David Cameron started popping up on billboards all over the place, I had a very strong urge to deface it. It would appear I wasn’t the only one, although these are generally more amusing than what I had in mind.

http://www.mydavidcameron.com/

Metastorm release BPM 9 and don’t tell anyone

Here’s an odd thing. When most software companies release a new version of their software, they shout about it until they are hoarse. But Metastorm have released version 9 of their BPM software and as far as I can see they haven’t even produced a press release to announce it to the world. Searching on Google News brings back no results and I can’t see any mention of it on the Metastorm website.

Initially I’d assumed this was because version 9 was released in the quiet period before Christmas and the PR onslaught would start in the new year, but here we are well into 2010 and there is still silence.

Now generally if it’s a choice between cock-up and conspiracy, I’ll plump for cock-up every time but I really can’t believe that any software company can forget to produce a press release to announce their new baby to the world so I have to go for the conspiracy option but what is the conspiracy? Answers on a postcode or in the comments…

Buying steak and kidney puddings on the web

I was reminded of steak and kidney puddings some time ago when I read Stuart Maconie’s “Pies and Prejudice” and suddenly had a strong desire to once again experience the long forgotten taste of them. But my searches on the web to find somewhere to purchase them in this culinary wasteland called London led me nowhere. Then the other day I received an email from Holland's Pies and ventured back to their website. I then discovered they have recently opened their online shop with a selection of their pies available. So £18 and a couple of days later I am now in possession of ten pies (including several steak and kidney pudds)and am looking forward to sampling one for my lunch tomorrow…

Cost benefit analysis of energy efficient bulbs

We’ve had six GU10 bulbs in our kitchen for a while now and I’ve never really been very happy with them. They swallow a lot of power, don’t seem to last very long and are pretty expensive to replace. I got hold of an LED replacement some time ago and was less than impressed. It didn’t produce enough light, was really really expensive and then died after a couple of months. So when I popped into Maplin at the weekend, I thought I’d try out their low-energy GU10 replacement. Admittedly these are even more expensive than the full powered version, but they claim to last for 8000 hours and use a lot less power. I’m happy with the amount of light they throw out, although they suffer from the usual problem of taking a while to warm up. Anyway, I thought I’d do a quick calculation of how long it would take me to earn back the money I shelled out for them.

Cost per KWH – 0.15

Old 6 bulbs @ 50W = 300W

New 6 bulbs @ 11W = 66W

KWHs saved = 0.3-0.066 = 0.234 KWHs

Cost of bulbs = £7.49*6 = £45

Number of KWHs required to cover cost = 45/0.15 = 300KWHs

Number of hours required to cover cost = 300/0.234 = 1282 hours

Assuming 5 hours use a day = 1282/5 days = 256 days = 8.5 months

So based on this very rough calculation and assuming I’ve calculated it correctly, this would appear to be a good deal. Fingers crossed they actually last for as long as claimed, which I’m reasonably confident about based on my experiences with other energy efficient bulbs.

Getting all the points in a SqlGeometry

It’s quite simple to get hold of the points in a SQL Server geometry polygon using the STNumPoints and STPointN SQL functions, but this requires quite a few queries (or some kind of stored procedure). I tend not to like running lots of queries, even if that does smell like premature optimisation, and for my little project I don’t want to be adding stored procedures but then I realised the SqlGeometry type is implemented as a .NET type, so all its properties and methods are available from .NET code, so here’s a little extension method to get all the points for a polygon

public static class GeometryHelper
{
  public static SqlGeometry[] Points(this SqlGeometry geometry)
  {
    List<SqlGeometry> points = new List<SqlGeometry>();
    for (int i = 1; i <= geometry.STNumPoints(); i++)
    {
      points.Add(geometry.STPointN(i));
    }
    return points.ToArray();
  }
}

Converting a SqlDouble to a .NET double

Note to self - this is simple, just typecast like so

(double)points[i].STX

Improving performance with Gzip compression in PHP

I was perusing through some of the data provided by Google Webmaster Tools when I came upon the Site Performance section. this told me that doogal.co.uk took longer to load than 96% of sites on the web, which was a little embarrassing. I knew what was causing at least some of this slowdown, the incomplete list of UK postcodes which has been getting gradually slower as the number of postcodes has got longer and longer. I’d already started to address that by paging the data, but the CSV data couldn’t really be paged, without reducing the value of it. So I had a look at the suggestion provided by Google, which was to use Gzip compression on the page. I’ve not really thought much about GZip compression before, assuming if it was so useful it would be on by default but I thought I’d give it a go anyway. So I fired up Fiddler and tried downloading my big CSV page and it took approximately 9.5 seconds to fully download. I arrived at this figure as an average after several reloads.

Next I added this line to my PHP source file, ob_start("ob_gzhandler");, and measured the difference. It now took 3.7 seconds to download, wow! A lot of gain for very little effort.

At this point, I wondered if I could add Gzip compression to all my pages. It turns out this is straightforward, just add the following to the .htaccess file

php_flag zlib.output_compression on

After adding this, the CSV page now arrived in 2.7 seconds. I’m not sure why this is even faster, but I’m not complaining. Now the rest of the site feels much snappier as well. So what am I missing? Is there a reason GZip compression isn’t on by default? Am I going to get bitten by this at some point in the future?

Getting boundary data out of OS OpenSpace

I’ve been able to pull out a single county’s boundary data from OS OpenSpace using its JavaScript API, but I wanted to get the data for all counties. Now I could have used my little example webpage to do this manually for each county, but that would be extremely tedious. So based on the fact that all AJAX APIs can be considered as simple HTTP requests, I wrote a little piece of C# code that made the required HTTP request and dumped the data into my SQL Server table. This is very rough and ready but it met my needs. Some time soon I will probably put together a class to handle all the OS Open Space interaction a bit more nicely and with more functionality.

      HttpWebRequest req = (HttpWebRequest)WebRequest.Create(
        "http://openspace.ordnancesurvey.co.uk/osmapapi/boundary?geometry=true&"+
        "key=74B560DED619715AE0405F0AF060615A&f=xml&" +
        "url=http%3A%2F%2Flocalhost%2FOpenSpaceBoundary%2FDefault.aspx&area_code=CTY&" +
        "bbox=0%2C0%2C1400000%2C1400000&resolution=1000&dojo.preventCache=1261317566652&" +
        "callback=dojo.io.script.jsonp_dojoIoScript4._jsonpCallback");
      using (WebResponse resp = req.GetResponse())
      using (Stream respStream = resp.GetResponseStream())
      using (StreamReader reader = new StreamReader(respStream))
      {
        string response = reader.ReadToEnd();
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.LoadXml(response);

        using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OGC;Data Source=NEWDOOGAL"))
        {
          conn.Open();
          using (SqlCommand command = conn.CreateCommand())
          {
            command.CommandText = "DELETE FROM CountyBoundaries";
            command.ExecuteNonQuery();
          }

          JavaScriptSerializer serializer = new JavaScriptSerializer();
          XmlNodeList nodes = xmlDoc.SelectNodes("BoundaryResultVO/items/item/geojson");
          foreach (XmlElement element in nodes)
          {
            Dictionary<string, object> data = 
              (Dictionary<string, object>)serializer.DeserializeObject(element.InnerText);
            Dictionary<string, object> properties = (Dictionary<string, object>)data["properties"];
            string countyName = (string)properties["NAME"];

            // get polygon data
            Dictionary<string, object> geometry = (Dictionary<string, object>)data["geometry"];
            object[] coordinates = (object[])geometry["coordinates"];
            
            for (int i = 0; i < coordinates.Length; i++)
            {
              StringBuilder wktBuilder = new StringBuilder();
              object[] polygonParts = (object[])coordinates[i];
              wktBuilder.Append("POLYGON(");
              for (int j = 0; j < polygonParts.Length; j++)
              {
                if (j>0)
                  wktBuilder.Append(",");
                wktBuilder.Append("(");
                object[] points = (object[])polygonParts[j];
                for (int k = 0; k < points.Length; k++)
                {
                  object[] point = (object[])points[k];
                  if (k > 0)
                    wktBuilder.Append(",");
                  wktBuilder.Append(point[0]);
                  wktBuilder.Append(" ");
                  wktBuilder.Append(point[1]);
                }
                wktBuilder.Append(")");
              }
              wktBuilder.Append(")");

              // put into database
              using (SqlCommand command = conn.CreateCommand())
              {
                command.CommandText = string.Format(
                  "INSERT INTO CountyBoundaries (County, Boundary) VALUES ('{0}', " +
                  "geometry::STPolyFromText('{1}', 4277))", countyName, wktBuilder.ToString());
                command.ExecuteNonQuery();
              }
            }
          }
        }
      }

Getting all available geographic reference systems out of SQL Server 2008

As a reminder to myself, the query required to get a list of all available geographic reference systems out of SQL Server 2008 is

SELECT * FROM sys.spatial_reference_systems

uDig – free GIS

To be frank I don’t much like anything Java based but uDig is the exception that probably proves the rule. It’s a free GIS based on Eclipse and it is pretty user friendly. Unfortunately it doesn’t handle data stored in SQL Server 2008, which marks it down a bit in my eyes, but it does support most other data sources.

Using OGC functions in SQL Server 2008 part 5 – Google Maps and polygons

In this series of posts I’ve so far managed to pull some spatial data into SQL Server, query it in a number of ways and finally been able to display some of that data in Google Maps. The next thing I wanted to do was to display the county boundary for Lancashire, since it was the postcodes for that great county I was displaying. I’ve already got the data for the county boundary from OS OpenSpace but the problem is this data is in UK OS coordinates rather than latitude and longitude as used by Google Maps. There are two ways to deal with this. Convert the data to latitude/longitude before displaying it or implement the GProjection interface to handle the conversion in the web page. The latter is something I want to look at at some point but for this I decided to do the conversion server-side.

So the first thing I did was to write a simple generic handler to generate an XML document containing the points for the county boundary. This code uses the .NET Coordinates library I have converted from the Java original. This is what the handler looks like

  public void ProcessRequest (HttpContext context) 
  {
    context.Response.ContentType = "text/xml";
    StringWriter stringWriter = new StringWriter();
    XmlTextWriter writer = new XmlTextWriter(stringWriter);
    writer.WriteStartElement("points");
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["postcodesDatabase"].ConnectionString))
    {
      conn.Open();
      using (SqlCommand comm = conn.CreateCommand())
      {
        comm.CommandText = "SELECT Boundary.STNumPoints() FROM CountyBoundaries WHERE County='Lancashire'";
        int numPoints = (int)comm.ExecuteScalar();
        for (int i = 1; i <= numPoints; i++)
        {
          writer.WriteStartElement("point");
          // get X
          comm.CommandText = "SELECT Boundary.STPointN(" + i + ").STX FROM CountyBoundaries WHERE County='Lancashire'";
          double x = (double)comm.ExecuteScalar();

          // get Y
          comm.CommandText = "SELECT Boundary.STPointN(" + i + ").STY FROM CountyBoundaries WHERE County='Lancashire'";
          double y = (double)comm.ExecuteScalar();

          // convert to lat/long
          OSRef osRef = new OSRef(x, y);
          LatLng latLong = osRef.ToLatLng();

          writer.WriteAttributeString("x", latLong.Longitude.ToString());
          writer.WriteAttributeString("y", latLong.Latitude.ToString());
          
          writer.WriteEndElement();
        } 
      }
    }
    writer.WriteEndElement(); // points
    context.Response.Write(stringWriter.ToString());
  }

Next up was the JavaScript required in the web page to display the data. This is pretty simple

        // load boundary
        var boundReq = new XMLHttpRequest();
        boundReq.open("GET", "GetBoundary.ashx", true);
        boundReq.onreadystatechange = function() {
          if (boundReq.readyState == 4) {
            if (boundReq.status == 200) {
              var nodes = boundReq.responseXML.selectNodes("points/point");
              var latLongs = new Array();
              for (var i = 0; i < nodes.length; i++) {
                var lng = nodes[i].getAttribute("x");
                var lat = nodes[i].getAttribute("y");
                var latLong = new GLatLng(lat, lng);
                latLongs.push(latLong);
              }
              var polygon = new GPolygon(latLongs, "#FF0000", 3);
              map.addOverlay(polygon);
            }
          }
        };
        boundReq.send(); 

If you look at the image above you may think the boundary displayed is not correct since it doesn’t include some places that are quite obviously in Lancashire (Blackburn and Darwen for instance) and this confused me for a while. But the boundary displayed is that of Lancashire County council, rather than the full county, which doesn’t include some areas which are in unitary authorities but still within the county.

Using OGC functions in SQL Server 2008 part 4 – Google Maps

So I’d now got to the stage where I’d pulled in some postcode data into SQL Server, got the county boundaries for one county (the mighty Lancashire) and figured out how to do a spatial query to tell me which postcodes were in that county. Now I wanted to display those postcodes on a map. So time to turn to Google Maps and Visual Studio.

First I created a simple generic handler in C# that returned an XML document containing the geometries of the postcodes. This could return JSON if that floats your boat but here is what my version looks like.

    public void ProcessRequest (HttpContext context) 
    {
      context.Response.ContentType = "text/xml";
      StringWriter stringWriter = new StringWriter();
      XmlTextWriter writer = new XmlTextWriter(stringWriter);
      writer.WriteStartElement("points");
      using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["postcodesDatabase"].ConnectionString))
      {
        conn.Open();
        using (SqlCommand comm = conn.CreateCommand())
        {
          comm.CommandText = "SELECT Location.STX AS X, Location.STY AS Y, City, Region, Postcode FROM Postcodes WHERE " +
            "(SELECT Boundary FROM CountyBoundaries).STContains(OsLocation)=1";
          using (SqlDataReader reader = comm.ExecuteReader())
          {
            while (reader.Read())
            {
              writer.WriteStartElement("point");
              writer.WriteAttributeString("description", reader["Postcode"].ToString() + ", " + reader["City"].ToString() + ", " + reader["Region"].ToString());
              writer.WriteAttributeString("x", reader["X"].ToString());
              writer.WriteAttributeString("y", reader["Y"].ToString());
              writer.WriteEndElement();
            }
          }
        }
      }
      writer.WriteEndElement(); // points
      context.Response.Write(stringWriter.ToString());
    }

Next I created a HTML page with a bit of JavaScript to load up the postcode data, which looked like this.

      function loadMap() {
      var map;
      if (GBrowserIsCompatible()) {
        map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(53.756830663572174, -2.73834228515625), 9);
        map.addControl(new GSmallMapControl());
        map.addControl(new GMapTypeControl());
        map.enableDoubleClickZoom();
        map.enableScrollWheelZoom();

        // load data
        var req = new XMLHttpRequest();
        req.open("GET", "GetPoints.ashx", true);
        req.onreadystatechange = function() {
          if (req.readyState == 4) {
            if (req.status == 200) {
              var nodes = req.responseXML.selectNodes("points/point");
              for (var i = 0; i < nodes.length; i++) {
                var lng = nodes[i].getAttribute("x");
                var lat = nodes[i].getAttribute("y");
                var marker = new GMarker(new GLatLng(lat, lng), { title: nodes[i].getAttribute("description") });
                map.addOverlay(marker);
              }
            }
          }
        };
        req.send();
      }
    }

Again nothing groundbreaking but the next stage could me more challenging. That is pulling out the data for the county boundary and plotting that on the map. And what is this all for? All part of my plans for world domination.

Using OGC functions in SQL Server 2008 part 3

It’s been a while since I last tried to do anything with the spatial features in SQL Server 2008. My next plan was to query the database to see which postcodes were in a particular county. To do this I got hold of the boundary data for some counties using the OpenSpace API. I just added one county, the one and only Lancashire, with the following code.

INSERT INTO CountyBoundaries (County, Boundary) VALUES ('Lancashire', 
geometry::STPolyFromText('POLYGON((335390.6 402509.3,335318.8 405919.1,337285.8 405717.9,341638.1 403012.7,341198.7 401158.3,

345355.3 399036.6,348231.1 404146.9,349111.4 402150.3,351662.6 402905.5,353409.1 407181.8,352474 409073.9,354484.8 412190.4,

358109.6 412599.6,358670 410970.3,360670.3 412859.2,362419.8 411151.7,366280.6 414615.7,364921.9 418843.3,366064.7 421813,

363060.8 427981.2,363748.5 429031.4,369403 431708.6,372966.5 423266.5,375369.8 421263.6,375568.6 419018,379162.2 417655.1,

380342.3 418953.7,382117.1 413139.9,383850.1 418586.1,385712 419187.4,387839.3 415759.4,389429.8 416106,390432.6 420649.6,

388662.6 425190.8,391447.8 428359.4,392703.4 434383.5,396065.9 436596.5,397063 439322.3,394185.1 441332.3,392652.8 446613.3,

388130.5 448821.7,387988.3 450699.5,387239.2 452627.7,385286.7 451690.4,384991.7 453964.6,380792.9 453190.5,379173.9 455357.1,

376997.3 455460,377844.2 457098.1,375605.7 461521.8,372209.8 460317.7,369352.3 461239,369714.2 464470.9,363191.2 470277.6,

363491 473169.9,365227.3 473672.8,370052.2 481319.2,370144.8 482748.7,362512.5 477911.7,358350.8 478665.3,355703.4 474137.7,

347972.3 478292.1,345446.8 475698.7,342749 476087.5,342784.7 474615.5,340414.8 475469.9,342882.8 469480.8,341304.5 465488.7,

344238.6 465331.3,340055 463217.2,340345.5 460141.4,335601.4 455045.4,336057.5 453597.1,332947.7 450366,329952.5 450443.2,

330737.1 442705.2,333041.8 441857.2,333572.8 437130.7,335319.6 433501.9,333963.4 433247,334425.2 431366.8,329729.3 431451.9,

329199.8 427517.3,328505.3 422791.4,330070.5 422095.3,336522 423531.2,337967.3 418422.4,332016.4 412241.1,332374.5 408594.8,

330736.4 405654.3,335390.6 402509.3))', 4277))

This is where things got a little tricky. The county boundaries used the OS coordinate system, whereas my postcodes were stored in latitude/longitude format. I was hopeful that SQL Server would do some magic for me and handle the different coordinate systems, but it would appear this isn’t the case.

So it looked like the only option was to convert my postcode points into the OS coordinate system. I’ve used phpCoord to do this conversion in the past, but this time I was looking for a .NET solution. So I took the Java implementation and converted it C#. That took a while (and I’ll upload the code at some point when I’ve OKed it with the original author) but after modifying my code to insert postcodes into the database I was able to run the following query and successfully pull out postcodes that were in the Lancashire area.

SELECT * FROM Postcodes WHERE (SELECT Boundary FROM CountyBoundaries).STContains(OsLocation)=1

Legal action against free postcode lookup

I only discovered the Ernest Maples website today. Ernest Maples was the man who introduced the UK postcode system. The website was providing a useful service that took a UK postcode and returned the latitude and longitude of that postcode, which seems kind of familiar… But they’ve recently received a threatening legal letter from the Royal Mail’s lawyers telling them to remove this service from their site.

This raises so many questions. When will I get my threatening letter? When will Google get their threatening letter, since they provide this exact same feature but through a JavaScript API rather than via a URL? Why is the publically owned Royal Mail charging for this data anyway, since it was taxpayer money that paid for its creation in the first place?

Google Maps tools

This is an interesting tool to see what is possible with the Google Maps API. Something I feel missing is real-time update of the shapes being drawn as the user moves the mouse around, something our mapping tool will provide when we ship the next version very soon.

Micro Men

BBC3 and BBC4 often have some interesting programmes that are easily missed. I remembered to record Micro Men, a dramatisation of the work of Clive Sinclair and Chris Curry who produced the ZX Spectrum and BBC Micro respectively. It was good fun, although Sinclair didn’t come across as a particularly likeable character, which I found disappointing since he was always something of a hero for me as a child. Acorn lives on with its ARM processor powering most of the world’s mobile phones and Sinclair continues to invent things, although with somewhat less success than the ZX Spectrum. Anyway, Micro Men is available to watch on BBC iPlayer for a while.

Zopa – 18 months on

About 18 months ago, I started to siphon some of my savings into Zopa in an attempt to get a slightly better return on my money. So I thought now would be a good time to look back on how well it has gone. First up, it certainly provides better returns than having your money in a bank account, with interest rates as low as they are. On the downside, this increased return does mean increased risk, the old risk premium again. Those loans can sometimes turn bad and when they do, all the outstanding debt is written off. Well I guess some of it may be returned, but I’ve not had any bad debt returned yet.

But the main downside is that the money is tied up for a long time, and by my calculations the return from part paying off my mortgage is still better than the returns on Zopa. And although doing that means that the money is gone forever, in the future I’ll probably be more likely to do that than put more money into Zopa.

Checking a latitude/longitude is in the UK

I found quite a lot of duff data appearing in my incomplete database of UK postcodes, so thought I’d better filter out some of the rubbish. So the obvious thing to do was figure out which latitude and longitudes weren’t in the UK and stop them being put into in the database in the first place and also delete the ones already in there. There are definitely better ways of achieving this but my simple solution seems to have done the trick. First check the latitude is greater than 49 and less than 61, then check the longitude is greater than –12 and less than 3. It’s not perfect but I think it’s probably good enough for many applications.

OS OpenSpace API

I’m pretty new to the world of GIS but one thing seems blindingly obvious to me, maps are becoming commoditised. With Google Maps and Virtual Earth already on the block, who is ever going to pay for maps anymore? New to the fray is the OS OpenSpace API, from the Ordnance Survey. It looks pretty decent, it includes some things not available in the other map providers, such as administrative boundaries, but misses other things like aerial imagery. When I get chance I will play with the API to see how good it is and get some ideas for our own JavaScript API.

Using OGC functions in SQL Server 2008 part 2

In part 1 I created a table that contained some spatial UK postcode data. Now the next task is to work out how to query this data. A typical question we may want to answer is ‘what are the nearest postcodes to a particular postcode?’. OK, what we probably want to answer is ‘what is the nearest station/cash machine/post office etc to a particular postcode?’ but the only spatial data I’ve got is for postcodes, so we’ll use that. And the query to answer this question is pretty straightforward.

SELECT TOP 100 P.Postcode, P.Location.STDistance(PL.Location) AS Distance 
FROM Postcodes P, Postcodes PL
WHERE PL.Postcode='KT1 3EG'
ORDER BY Distance

The MSDN documentation for the STDistance function is a bit sparse but from what I can find from other sources, it will return the distance between two locations in metres.

Obviously this has all been possible in the past by writing your own code to do the calculation, but executing it directly against the database makes life a lot simpler. I have no idea if it will be quicker but simpler generally wins out for me.