Visio API ‘Cell is guarded’ exception

If you’re using the Visio COM API to generate or modify Visio diagrams, you may have come across the exception ‘Cell is guarded’ when trying to set a cell’s FormulaU property (and probably when setting the Formula property). For me it only happened on a machine with Visio 2007 installed, Visio 2003 was fine. The solution was pretty simple, use the FormulaForceU property instead (although potentially this may lead to unexpected behaviour apparently, worked fine for me though).

Importing the Code-Point dataset into MySql

I recently realised that capturing the geocoding results from Google wasn’t such a good idea. It seems that postcodes that are only partially correct will still be geocoded, but will be returned with a default latitude and longitude which are kind of in the right area for that postcode area but obviously aren’t right, since the postcode doesn’t exist.
So I decided it was time to grasp the nettle and use the Code-Point dataset for my UK postcodes page, so the data should be much more accurate. The only issue with the Code-Point dataset is that it doesn’t contain postcodes in Northern Ireland, Guernsey, Jersey and the Isle of Man for some reason.
So the first thing to do was to download the .NET connector for MySql. Then I modified my SQL Server import code to use MySql. As mentioned in that post, the code has dependencies on this CSV parser and my .NET coordinates library. I made some modifications to add spaces to postcodes as necessary and also to not bother inserting postcodes with no geographical data (I’m not sure if this is missing data or if these are simply non-geographical postcodes).
The code is shown below. Although it works, this is a simple implementation and is not particularly fast. It will probably take a few hours running against a local MySql installation but running against a remote installation will probably take longer. I only plan to run it once, so I’m not particularly concerned with performance, but if performance is an issue, you’ll probably want to take a look at the MySqlBulkLoader class or peruse the MySql documentation on improving the performance of inserts.

using System;
using System.IO;
using DotNetCoords;
using LumenWorks.Framework.IO.Csv;
using MySql.Data.MySqlClient;

namespace ImportCodepoint
{
  class Program
  {
    static void Main(string[] args)
    {
      string[] files = Directory.GetFiles(@"C:\Users\Doogal\Downloads\codepo_gb\Code-Point Open\data\CSV");
      foreach (string file in files)
      {
        ReadFile(file);
      }

    }

    private static void ReadFile(string file)
    {
      using (StreamReader reader = new StreamReader(file))
      {
        CsvReader csvReader = new CsvReader(reader, false);
        using (MySqlConnection conn = new MySqlConnection(
          "server=127.0.0.1;uid=root;pwd=password;database=test;"))
        {
          conn.Open();
          foreach (string[] data in csvReader)
          {
            string postcode = data[0];
            // some postcodes have spaces, some don't
            if (postcode.IndexOf(' ') < 0)
              postcode = data[0].Substring(0, data[0].Length - 3) + " " + data[0].Substring(data[0].Length - 3);
            double easting = double.Parse(data[10]);
            double northing = double.Parse(data[11]);

            // there are some postcodes with no location
            if ((easting != 0) && (northing != 0))
            {
              // convert easting/northing to lat/long
              OSRef osRef = new OSRef(easting, northing);
              LatLng latLng = osRef.ToLatLng();
              latLng.ToWGS84();

              using (MySqlCommand command = conn.CreateCommand())
              {
                Console.WriteLine(postcode);
                command.CommandTimeout = 60;
                command.CommandText = string.Format("INSERT INTO Postcodes (Postcode, Latitude, Longitude) " +
                  "VALUES ('{0}', {1}, {2})",
                  postcode, latLng.Latitude, latLng.Longitude);
                command.ExecuteNonQuery();
              }
            }
          }
        }
      }
    }
  }
}

MySql Workbench

I’ve used phpMyAdmin for a long time to administer MySql databases on the web. I’ve been pretty happy with it, although it can occasionally behave a bit strangely if it times out. But I was at the MySql website the other day to download MySql for my laptop when I spotted MySql Workbench, so I thought I’d give it a go. And it’s a very nice piece of free software. Call me an old timer but I still prefer a desktop application and it delivers. It looks good and offers all the features I need. I’ve been spoiled by SQL Server Management Studio (and not spoiled by the Oracle admin tools…) but MySql Workbench seems comparable in terms of functionality and ease of use. The pre-release 5.2 seems quite a lot better than the official 5.1 release, so go for that if you feel brave.

FxCop custom rules

There are quite a few examples of custom rules for FxCop (this was the best I found) but I was unable to find an example Visual Studio project to download. So after I got my simple rules assembly up and running, I thought I’d upload it to the web, you can grab it from here. It just contains one rule, taken from the site mentioned before, which checks for the usage of the ArrayList class.

My only advice is if you are having trouble getting your rules loaded up into FxCop then debug your assembly through Visual Studio and make sure you have set up Visual Studio to break when CLR exceptions are thrown. If any exceptions are thrown when trying to load your assembly, FxCop will catch the exception and not display the rules.

Getting the height of a background image

Getting hold of the physical height of an image would initially appear to be a pretty straightforward thing to do. This may well be the case with an image held in an <img> tag (at least if the height of the <img> tag hasn’t been set) since it will resize to fit the image so you just need to get the height of the <img> element. But with a background image it’s not quite so simple. I wanted to get the height of an image used as a background image so I could resize the header of some HTML output when users supplied their own custom background images.

Even so, this should be pretty easy I thought. Load up the image using the Image JavaScript object and get the height from there. There were a couple of issues with this. I decided to pick up the image URL from the background-image CSS, rather than hard coding it. But this was complicated by the fact that different browsers store the URL in different ways, some with quotes, some without.

The other crazy thing I encountered was Internet Explorer seemingly loading up an old version of the image and returning the dimensions of that even though a new different sized image had replaced it. Emptying my cache and fiddling with the options dialog made no difference, so I was forced to add a query string to stop this insane caching.

Another thing which seems to have tripped up other people when I was trawling the web is the need to set up the onload handler before setting the src property.

Here’s the code, hope it’s useful.

      // get the background image
      var backgroundImg = new Image();
      backgroundImg.onload = function() {
        headerHeight = backgroundImg.height;
        // do other stuff
      };
      var imgSrc = $('.header').css('background-image');
      // IE and FireFox have quotes, Safari and Chrome don't
      imgSrc = imgSrc.replace(/["']/g, '');
      imgSrc = imgSrc.substring(4);
      imgSrc = imgSrc.substring(0, imgSrc.length - 1);
      // IE seems to be too aggressive in caching
      backgroundImg.src = imgSrc + '?' + Math.random();

Importing the Code-Point dataset into SQL Server

Many moons ago, I imported my own postcode dataset into SQL Server but ever since the Ordnance Survey have made their Code-Point dataset available for free, I’ve been meaning to import that, since it’s likely their data will be more correct than my own.
It comes in a series of CSV files and contains the postcode, easting, northing and various other bits of data. Unfortunately the documentation is somewhat lacking so it’s not entirely clear what all the other data is. But those three bits of data are all I was after.
So I wrote a little console application to import the data, that looks like this

using System;
using System.Data.SqlClient;
using System.IO;
using DotNetCoords;
using LumenWorks.Framework.IO.Csv;

namespace ImportCodepoint
{
  class Program
  {
    static void Main(string[] args)
    {
      string[] files = Directory.GetFiles(@"C:\Users\Doogal\Downloads\codepo_gb\Code-Point Open\data\CSV");
      foreach (string file in files)
      {
        Console.WriteLine("Importing file " + Path.GetFileName(file));
        ReadFile(file);
      }

    }

    private static void ReadFile(string file)
    {
      using (StreamReader reader = new StreamReader(file))
      {
        CsvReader csvReader = new CsvReader(reader, false);
        using (SqlConnection conn = new SqlConnection(
          "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Postcodes;Data Source=DOOGAL-LAPTOP\\SQLEXPRESS"))
        {
          conn.Open();
          foreach (string[] data in csvReader)
          {
            string postcode = data[0];
            double easting = double.Parse(data[10]);
            double northing = double.Parse(data[11]);

            // convert easting/northing to lat/long
            OSRef osRef = new OSRef(easting, northing);
            LatLng latLng = osRef.ToLatLng();
            latLng.ToWGS84();

            using (SqlCommand command = conn.CreateCommand())
            {
              command.CommandText = string.Format("INSERT INTO Postcodes (Postcode, Coordinates) " +
                "VALUES ('{0}', geography::STPointFromText('POINT({2} {1})', 4326))",
                data[0], latLng.Latitude, latLng.Longitude);
              command.ExecuteNonQuery();
            }
          }
        }
      }
    }
  }
}

A couple of points, I’ve used this CSV parser, rather than re-inventing the wheel. It would be pretty easy to do this parsing myself, since only a few fields are quoted and no fields contain any special characters, but I’ve been pretty happy with this CSV parser in the past so saw no point re-inventing the wheel.
I’ve also used my .Net coordinates library to convert GB OS references to latitude/longitude, but obviously you can store the postcodes using eastings and northings if you prefer.
The final thing to do is to check that the imported data looks sensible. The easiest way to do this is to query the table in SQL Server Management Studio and look at the spatial results. Unfortunately this is limited to 5000 results, so I used this little bit of SQL (based on this blog post) to select a subset of the data to make sure the general shape looked correct. Obviously this doesn’t check all the data but did build some confidence that what I’d done was correct.

SELECT 
   EXPR2.*
FROM 
   (
   SELECT 
      Postcode, Coordinates,
      RowNumber,
      (EXPR1.RowNumber % 400) AS ROW_MOD
   FROM
      (
      SELECT 
         Postcode, Coordinates, 
         ROW_NUMBER() OVER ( ORDER BY Postcode ) AS 'RowNumber'
      FROM 
         Postcodes 
      ) EXPR1
   ) EXPR2
WHERE 
   EXPR2.ROW_MOD = 0

Image file locking in the .NET Bitmap constructor

Consider the following code

      Dim bitmap As New Drawing.Bitmap(fileName)
      Return bitmap

It certainly looks fairly innocuous but it had a problem, or rather it caused a problem further down the track. We wanted to replace some of the image files that we had previously loaded into the application. But trying to do that threw an exception telling us “The process cannot access the file '…’ because it is being used by another process.”. Which wasn’t entirely accurate, actually we couldn’t access the file because it was in use by this process. But nitpicking aside, the reason for this was the Bitmap constructor above which loads the file but doesn’t unlock it, only a call to Bitmap.Dispose() unlocks it again.

So my first attempt to fix this looked something like this

      Dim fs As New FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
      Dim bitmap As New Drawing.Bitmap(fs)
      fs.Close()
      Return bitmap

Since this opened the image via a FileStream and closed it afterwards, this should solve the problem. And it did on my Windows 7 machine, but it caused even bigger problems on a Windows XP machine. We started getting an out of memory exception for no apparent reason.

I eventually came to the conclusion that this was down to the Bitmap constructor that took a FileStream instance. When it’s passed a file name, it can make a pretty good guess at what kind of file it’s loading based on the extension, but when it is just given a bunch of bytes, it has to make a guess as to what type of image it’s looking at and I presume the heuristics in XP weren’t as good as they are in Windows 7. I’m guessing here, since all this happens in the guts of the GDI+ API.

So my final attempt at fixing this looks like this

      Dim fileBitmap As New Drawing.Bitmap(fileName)
      Dim bitmap As New Drawing.Bitmap(fileBitmap)
      fileBitmap.Dispose()
      Return bitmap

Which so far hasn’t caused any more issues…

Debugging KML loading problems in Google Maps

I was attempting to load some KML into Google Maps via the KmlLayer class and not having much luck. Nothing was appearing. Unfortunately the KmlLayer class doesn’t provide an event to indicate an error has occurred. So what is available to debug these problems and what are some of the issues that can cause load errors?

• Fiddler may help, although it didn’t give any indication to me about what was going on. But I have seen some HTTP responses with 400 bad request status codes in the past. Unfortunately they didn’t say why the request was bad.
• Check that the KML is valid using Feed Validator. I’ve found this doesn’t work too well with large KML files in IE, so use some other browser instead.
• When Google Maps does server side rendering of KML it appears to do some caching of the data, so if you change your KML, changes may not appear immediately.
• There are limits to the size of KML file supported by Google Maps, have a look at this page to see if this is your problem. I think this is what caused my issues.
• Try loading the KML in Google Earth to see if it’s an issue with the KML or Google Maps’ handling of it.

Moving from Google Maps 2 to Google Maps 3

After playing with version 3 of the Google Maps API and trying to convert my ScratchPad application, I’m pretty impressed. It’s a much more consistent API than version 2 and provides quite a few new features. But one thing is for sure, converting an application from version 2 to version 3 will be a big chunk of work. Pretty much every line will need rewriting.

So I thought I’d provide a little crib sheet to help people who are trying to upgrade. I don’t claim that this list is in any way complete. I’ll update it as I find more things out, but even so I only intend it to be a pointer to the right class/method to use. In most cases the way methods or classes are used has also changed. If you have any other information to add, please add a comment.

Google Maps version 2	Google Maps version 3
GMap2 class	google.maps.Map class
GMap2.disableDoubleClickZoom()	MapOptions.disableDoubleClickZoom:true passed to Map constructor or Map.setOptions()
GMap2.getDragObject().setDraggableCursor()	MapOptions.draggableCursor passed to Map constructor or Map.setOptions()
GMap2.addOverlay()	Call setMap() on the layer to add
GMap2.clearOverlays()	???
GMap2.removeOverlay()	Call setMap(null) on the layer to remove
GLatLng class	google.maps.LatLng class
GMarker class	google.maps.Marker class
GMarker.bindInfoWindowHtml()	google.maps.InfoWindow class, but you need to manually open the info window using     google.maps.event.addListener(marker, 'click', function () {     infoWindow.open(map, marker);   });
GEvent.bind(), GEvent.addListener	google.maps.event.addListener(), although it doesn’t seem possible to bind the event to particular object instance (where the keyword this can be used in the event handler)
GEvent.removeListener()	google.maps.event.removeListener()
GNavLabelControl class	???
GAdsManager class	???
GGeoXml class	google.maps.KmlLayer class
GGeoXml.load event	???
GGeoXml.getDefaultCenter()	KmlLayer.getDefaultViewport().getCenter()
GStreetviewOverlay class	This is now integrated into the map, pass streetViewControl: true into MapOptions to make it appear
GClientGeocoder class	google.maps.Geocoder class
GClientGeocoder.getLatLng()	Geocoder.geocode()
GPolyline class	google.maps.Polyline class
GPolyline.getLength()	There is no direct replacement, but this forum post discusses how to implement something similar yourself.
GPolyline.getVertexCount()	Polyline.getPath().length()
GDirections class	google.maps.DirectionsService class to query for directions, google.maps.DirectionsRenderer class to render them on a map
GDirections.load()	DirectionService.route()
GDirections.loadFromWaypoints()	DirectionsRequest.waypoints property
GDirections.clear()	No equivalent
GTrafficOverlay class	google.maps.TrafficLayer class
GLayer class	??? – This is in the API issues database, star it if you want it implemented
GTileLayerOverlay class	???