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:

    [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: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("'", "''") + "'";

3 thoughts on “Importing OpenStreetMap Data into MS SQL Server

  1. Pingback: OpenStreetMap for MS SQL Server | devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.