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.

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:

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:

And, when viewed on Bing Maps:

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'
)

Advertisement