Importing OpenStreetMap Data into MS SQL Server

After analyzing OSM extracts files, we can now import the data from XML into SQL Server. Depending on your requirements, the table to hold the .osm data looks something like this:

CREATE TABLE [dbo].[OSM](
    [Lat] [float] NOT NULL,
    [Lon] [float] NOT NULL,
    [Country] [nvarchar](50) NULL,
    [PostCode] [nvarchar](50) NOT NULL,
    [City] [nvarchar](200) NOT NULL,
    [Street] [nvarchar](200) NULL,
    [HouseNumber] [nvarchar](50) NULL
)

Again, we start out reading from the .osm using an XmlReader

using (var reader = new StreamReader(@"path\to\data.osm", Encoding.UTF8))
{
  foreach (var el in GetNextNode(reader))
  {

We collect all tag data (attributes k and v) in a dictionary

    var attrs = new Dictionary<string, string>();

    foreach (var tag in el.Elements("tag"))
    {
      var k = tag.Attribute("k");
      var v = tag.Attribute("v");
      if (k != null && v != null)
        attrs.Add(k.Value, v.Value);
    }

If all required fields (in my case, addr:postcode and addr:city) are present, we generate the INSERT statement:

    if (attrs.ContainsKey("addr:postcode") && attrs.ContainsKey("addr:city"))
    {
      Console.WriteLine("INSERT INTO OSM (Lat, Lon, Country, PostCode, " + 
        "City, Street, HouseNumber)\n" + 
        "VALUES({5}, {6}, {0}, {1}, {2}, {3}, {4})\nGO",
        attrs.ToSql("addr:country"), attrs.ToSql("addr:postcode"), 
        attrs.ToSql("addr:city"), 
        attrs.ToSql("addr:street"), attrs.ToSql("addr:housenumber"),
        el.Attribute("lat").Value, el.Attribute("lon").Value);
    }
  }
}

Note that this code requires an extension method ToSql() defined on a dictionary to calculate the SQL notation for a nullable string literal:

public static class DictExt
{
  public static string ToSql(this Dictionary<string, string> dict, 
    string key)
  {
    if (!dict.ContainsKey(key))
      return "NULL";
    var s = dict[key];
    if (string.IsNullOrEmpty(s))
      return "NULL";
    return "N'" + s.Replace("'", "''") + "'";
  }
}