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.