"Finding nearest" with Umbraco and MS SQL Server 2008

MS SQL Server 2008 includes a .Net assembly for working with geographic coordinates.  It means that you don't have to write custom functions to find the distance between points (have a look at how complicated the maths can be).

So, needing to be able to work with geo-coded items in my DB, I did a little looking around and found a really helpful article on SQL Server Helper.  What I needed, then, was a table with a column of type "geography" that I could query against.

However, this is an Umbraco site, which means that if I used the (really helpful) GMaps data type, I'm going to have my coordinates stored as a string with a comma in-between, rather than numbers I can actually convert into a geography data type (the format of the output is "[latitude],[longitude],[zoom]").  I hate doing string manipulation in SQL Server, so I didn't want to write a trigger to update my geography values.

So - in the end I decided to not bother with SQL Server triggers to update the record.  Instead I created a new class that inherits from Umbraco's ApplicationBase class.  Here I added a method to run on page publish that checks for if I need to add the geography data type.  If I do, it runs a little stored procedure that I added to the DB.  .Net can parse the GMap coordinates into decimal values, then pass those to the stored procedure.

To store the geography values, I created a *really* simple table:


CREATE TABLE [dbo].[PageLocation](
   [pageID] [int] NOT NULL,
   [location] [geography] NOT NULL
) 

All I want to do is store the ID of the Umbraco page and the coordinates.  I'll need to do some other filtering but I plan on using Lucene to do that for me since it's nice and fast and doesn't rely on proper relationships being created in the database (since the relationships are all stored as Umbraco fields, this is tricky).  Then it's just a matter of using the lovely uQuery to fetch the details of the pages that are returned.

I've yet to actually try this all out, but I think the theory is sound  :)