OpenStreetMap into SQL Server 2008 first steps

So as I said in my previous post "OpenStreetMap - show me some data" the next step in evaluating the use of the OSM data was to see how SQL Server 2008 Spatial features might help.

The first thing I wanted to look at was how to define the structure in SQL and work with it from C#. This was mainly to get a feeling for the data types involved. In SQL I created a database (UKGeo) and a table:

CREATE TABLE [dbo].[Nodes](
 [id] [int] NOT NULL,
 [location] [geography] NOT NULL,
 CONSTRAINT [PK_Nodes] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The key point is to note the datatype "geography" being used for the location field, this datatype can handle a number of GIS related structures. We also need a stored procedure to load the data:

CREATE PROCEDURE [dbo].[sp_NodeCreate]
 @id int, 
 @location geography
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 INSERT INTO Nodes(id, location) VALUES (@id, @location);
END

Once again it's a simple enough procedure, with the use of "geography" being the only complexity. From C# we just need to create a command pointing to this stored procedure:

   string sql = "sp_NodeCreate";
   SqlCommand command = new SqlCommand(sql, connection);
   command.CommandType = CommandType.StoredProcedure;

and when setting up the parameters we need to specify that the @location parameter is a Udt (user defined type), in this case geography is already registered in SQL, so we can just name it:

   SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int);
   SqlParameter geoParam = new SqlParameter("@location", SqlDbType.Udt);
   geoParam.UdtTypeName = "geography";
   command.Parameters.Add(idParam);
   command.Parameters.Add(geoParam);

and finally a function to call the procedure:

 private void CreateNodeInDatabase(string id, string longtitude, string latitude)
  {
   SqlGeographyBuilder builder = new SqlGeographyBuilder();
   builder.SetSrid(4326);

   builder.BeginGeography(OpenGisGeographyType.Point);

   builder.BeginFigure(double.Parse(longtitude), double.Parse(latitude));
   builder.EndFigure();

   builder.EndGeography();

   SqlGeography geo = builder.ConstructedGeography;

   command.Parameters["@id"].Value = id;
   command.Parameters["@location"].Value = geo;

   command.ExecuteNonQuery();
  }

This code uses the SqlGeographyBuilder to create an instance of type Point, which is what we want location to contain. I'm sure this code is not the best way to do this, but it serves as a starting point for understanding the relationship between SQL and CLR implementations of spatial information.