SQL Server 2008 geodetic data

The engine behind the tramper.co.nz website is designed with three key features:

1. an extensible system of object storage based on “classes” of objects that are predefined using XML.
2. Versioned objects, including images and files, so that previous versions of an object may be viewed, and any object may be reverted to a prior version.
3. Geographic points associated with objects, based on the WGS84 standard datum, but with automatic conversion between a variety of predefined coordinate systems. There has never been storage available for shapes other than points (lines, polygons and so forth).

The geographic part of this system was a very hard piece of the puzzle: it involves complex numbers, iterative, formulae, and long, complicated functions for calculating distances on great circles. I spent around a year developing it in my spare time.

SQL Server 2008 includes support for geometry and geography. I will be converting to these data types as they offer improved performance, greatly improved maintainability, and the possiblility of doing things I simply couldn’t do (for example, finding all the polygons crossed by a line, which would translate to finding all the parks and reserves for a particular tramping track).

So this morning I took my points table (which stores latitudes and longitudes as numeric data) and added a new “geodeticData” column with the geography data type. I ran this query:

update hivemind_points
set geodeticdata = geography::STGeomFromText(
  'POINT (' + CAST(latitude AS VARCHAR(30)) + ' '
    + CAST(longitude AS VARCHAR(30)) + ')', 4326);

And then (optimistically) this one…

SELECT geodeticData FROM hiveMind_points

And this is what I got. How exciting! A map of points right there inside SQL Server Management Studio. Thank you Microsoft.