Loading Open Street Map POIs with SQL Server / Bing Maps

Open Street Maps uses the same spherical Mercator projection as used by Bing Maps and Google Maps. This means that it’s possible to “mix-n-match” tile layers from each source, and there’s already a couple of posts on the internet demonstrating how to overlay Open Street Map tiles on Bing Maps (see for example, this article by Chris Pietschmann). In fact, last year Microsoft launched an official Open Street Map App that lets you overlay Open Street Map imagery in a layer on the http://www.bing.com/maps website.

However, the previous approaches demonstrate only how to display pre-rendered OSM tiles as an overlay on the map. Open Street Map has a massive (170Gb+) of raw spatial data from which those tiles are produced, all of which is publicly created, editable, and downloadable. In this post, I’ll look at how to download this base OSM vector data in XML format, shred it in SQL Server, and then display pushpins based on that data in Bing Maps.

Acquiring OSM Data

To start with, you need some OSM data. If you only want a small amount of data, then simply go to http://www.openstreetmap.org/ , pan and zoom the map to display the area of interest, and then select the “Export” tab.

From here, manually tweak the bounding box if required, ensure that the OpenStreetMap XML Data format option is selected, and then click Export to download the corresponding data file.

image_thumb1

The XML export for the city of Norwich, shown above, is around 5Mb in size.

This method will work well for small datasets that contain up to 50,000 nodes. For datasets greater than this, you can download complete or partial subsets of planet.osm – the master file that contains all nodes in the Open Street Map dataset. The original file can be downloaded from http://planet.osm.org/ , but be warned that, when expanded, this is currently over 170Gb in size (and constantly growing).

For more manageable subsets of  OSM data, such as splits by individual country or feature type, you can try sources such as http://download.geofabrik.de/osm/ or http://downloads.cloudmade.com/

Shred the OSM Data in SQL Server

The native format for exported OSM data is a single XML file, containing a number of elements – <node>s, <way>s, and <relation>s, each with a variety of attributes. These data primitives are documented on the following Open Street Map wiki page.

To load this data into SQL Server, I’m going to shred the hierarchical XML structure into a relational structure describing each feature and its attributes.

Load the XML File

First things first, I’ll load the entire OSM XML file into a variable of the xml datatype using OPENROWSET(BULK), as follows:

DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(
BULK 'C:\Downloads\norwich_map.osm',
SINGLE_BLOB) AS x);

Nodes

The basic unit in the OSM dataset is a “node”. Nodes can be standalone (e.g a phone box), or they can be part of a “way”, which is a connected series of nodes forming either a line (i.e. a road or river) or an area (e.g. an area, building etc.). The following code listing uses XQuery syntax to extract all the nodes from the XML variable and inserts them into a new table, together with a geography point instance representing the location of each node.

OSM coordinates are always measured in EPSG 4326, so I’ll use the geography datatype and supply the appropriate SRID:

CREATE TABLE nodes (
nodeid int,
latitude float,
longitude float,
geog4326 geography
);
INSERT INTO nodes
SELECT
OSMnode.value('@id', 'int') AS nodeid,
OSMnode.value('@lat', 'float') AS latitude,
OSMnode.value('@lon', 'float') AS longitude,
geography::Point(OSMnode.value('@lat', 'float'), OSMnode.value('@lon', 'float'), 4326) AS geog4326
FROM
@x.nodes('/osm/node') AS OSM(OSMnode)

(14138 row(s) affected)

Here’s the first 5,000 rows from the Nodes table just created from my Norwich import displayed in SQL Server Management Studio:

image_thumb3

NodeTags

Each Node may (optionally) have any number of tags. In the source xml file, these are represented in the child <tag> element(s) of each <node>, with an attribute k representing the property being described, and the attribute v containing the value of that property. I’ll shred these tags into a separate table, keyed by NodeId to link back to the corresponding node from the Nodes table. I use the CROSS APPLY operator to select all those tag elements that are children of each node row in the main SELECT statement:

CREATE TABLE nodetags (
nodeid int,
tagname varchar(32),
tagvalue varchar(32)
);
INSERT INTO nodetags
SELECT
OSMNode.e.value('(@id)[1]', 'int') AS 'NodeID',
OSMNodeTag.e.value('@k', 'nvarchar(32)') AS 'TagName',
OSMNodeTag.e.value('@v', 'nvarchar(32)') AS 'TagValue'
FROM
@x.nodes('/osm/node') AS OSMNode(e)
CROSS APPLY
OSMNode.e.nodes('tag') AS OSMNodeTag(e)

(5177 row(s) affected)

Selecting and Displaying Nodes from the data

Having shredded the data, we can now get on and start using it. To select a certain type of node, e.g. pubs, we can SELECT from the nodes table only those nodeids that have corresponding tags in the nodetags table. To display all nodes tagged with the “pub” amenity, for example:

SELECT
n.nodeid,
n.geog4326,
nt.TagValue
FROM nodes n
LEFT JOIN nodetags nt ON n.nodeid = nt.nodeid AND TagName='Name'
WHERE n.nodeid IN
(
SELECT nodeid FROM nodetags nt WHERE nt.TagName = 'amenity' AND nt.TagValue = 'pub'
)

Here’s the results showing pubs in my Norwich dataset in SSMS spatial results tab:

image

And, when viewed on Bing Maps:

image

Or, if you’d rather know the location of postboxes:

SELECT
n.nodeid,
n.geog4326,
nt.TagValue
FROM nodes n
LEFT JOIN nodetags nt ON n.nodeid = nt.nodeid AND TagName='Name'
WHERE n.nodeid IN
(
SELECT nodeid FROM nodetags nt WHERE nt.TagName = 'amenity' AND nt.TagValue = 'post_box'
)

image

About these ads
This entry was posted in Bing Maps, Spatial, SQL Server and tagged , , , , . Bookmark the permalink.

10 Responses to Loading Open Street Map POIs with SQL Server / Bing Maps

  1. Hey Alastair,

    I like the article. Looking at the tagvalue column I think this should be bigger as there appears to be URL’s in the data. I’m looking forward to see what fun things I can do with this.

    Cheers,
    Ricky

    • alastaira says:

      Thanks for the comment, Ricky! Yeah – you’re right, the tagvalue should probably be a nvarchar(255) at least… tags are free-form, so people can add a tag of “description” with a value describing a node with pretty much anything they want.

  2. Hann says:

    hey am bit new to this, am getting an error on the OSMnode.value thing ??why is that

  3. Echilon says:

    That’s an excellent blog post. Thanks!

  4. Ricci says:

    Hi there,
    I just want to thank you. Your docs are so easy to understand and you help me so much geocoding some addresses in ssms.
    cheers(?)
    Ricci

  5. Cristianssenn says:

    Hi,

    I tried to import a big OSM (147GB) for entire Europe but the limit of importing xml ( An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.) don’t allowed me to do that.

    Is there any solution to import such a large file?

    • DutchHarry says:

      @Cristianssenn
      size limit of xml data type is same as varchar(max), thus 1Gb-1byte.
      So you have to wrok with smaller chunks, or split the .OSM file manually.
      I tried a few splliters today on the UK file (about 8 Gb) to no avail.
      Most promising and working for smaller files:

      http://www.codeproject.com/Articles/31114/Split-large-XML-files-into-small-files

      but that broke down on splitting 8Gb in 250Mb chunks. That is it completed, but it did not get the root tags right. Probably needs some work on the source.

      Commercially there is of course there is Safe FME.
      Or just doing it manually in a text editor (I am using ultraedit for that).

      Cheers

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s