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.

XML encoding in JavaScript

As far as I know there is no cross browser way to generate valid XML documents, so it seems like string concatenation is the way to go. So here’s a little function to do the required encoding of characters that need to be specially encoded for XML. As ever, I’ve not done any kind of thorough testing on this…

    function XmlEncode(text) {
      text = text.replace(/&/g, '&');
      text = text.replace(/\"/g, '"');
      text = text.replace(/\'/g, ''');
      text = text.replace(/</g, '&lt;');
      text = text.replace(/>/g, '&gt;');
      return text;
    }

Using OGC functions in SQL Server 2008 part 1

One great thing about having a new job is being faced with new technologies. So I’ve just come across the new geography and geometry data types in SQL Server 2008. These allow you to store geographical data in a database. In some ways this is nothing new, you could always store latitude and longitude or eastings and northings if you only cared about the UK area. But what is new is that the database actually understands what these values are, meaning it’s possible to write queries that are based on geographical location, which to my mind is absolutely frigging awesome.

So I thought I’d play around with these new features in SQL Server. Oracle also now has spatial features, but frankly I try and avoid using Oracle wherever possible. First up I created a new table with a geography column, as shown above. Next I had to populate it. Fortunately I knew where I could find some data. With a little help from C# and Visual Studio, it was pretty easy to get this into the database.

  class Program
  {
    static void Main(string[] args)
    {
      string[] lines = File.ReadAllLines(@"C:\Users\Doogal\Desktop\Postcodes.txt");
      using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OGC;Data Source=NEWDOOGAL"))
      {
        conn.Open();
        foreach (string line in lines)
        {
          string[] data = line.Split(',');
          using (SqlCommand command = conn.CreateCommand())
          {
            command.CommandText = string.Format("INSERT INTO Postcodes (Postcode, Location, City, Region) " +
              "VALUES ('{0}', geography::STPointFromText('POINT({2} {1})', 4326), '{3}', '{4}')",
              SqlEscape(data[0]), data[1], data[2], SqlEscape(data[3]), SqlEscape(data[4]));
            command.ExecuteNonQuery();
          }
        }
      }
    }

    private static string SqlEscape(string sql)
    {
      if (sql == null)
        return null;
      return sql.Replace("'", "''");
    }
  }

So after getting the data in the database, the next step was to query it and see what we got. And after running a query against a spatial table in Management Studio, the first thing you notice is the new ‘Spatial results’ tab. Clicking on that showed the diagram shown on the left. And that looked remarkably like a map of the UK.

I guess that in itself is not hugely impressive, but it certainly pleased me. Next I have to move onto real queries, such as ‘give me all the postcodes within 1km of this location’, which is where spatial databases potentially come into there own.

When will IE6 die?

Just like everybody else who has to do any kind of web development, I’m quite keen to see the back of IE6 so I thought I’d make an estimate of when IE6 would die. I took the Google Analytics data from the Random Pub Finder and pulled out the monthly percentage of IE users who were using IE6, which produced the graph to the left. It looked pretty linear, except for the initial drop off after IE7 was released, so I added a linear trend line. And voila, IE6 will die at the beginning of 2011. Well, one can live in hope…

RBS. Make it happen

I’m constantly amazed at the number of adverts I see around for RBS. I guess all the sponsorship deals they signed up for before they blew up have to be honoured so they still appear all over the place. I’ve passed through Glasgow airport a couple of times recently and it is plastered in ads for RBS saying ‘RBS. Make it happen’. I’m not sure if this a command to readers of the ad or they are just telling the reader that RBS make ‘it’ happen. I’m assuming it’s the latter. So what is it they make happen? Global financial meltdown? A massive public deficit due to bailing them out? Either way, I think perhaps they should update their ads to say something like ‘Look we are really really fecking sorry’, it might be more effective.