Apple Maps and OSM – Further Explanation

So, this time yesterday I posted a blog post describing how Apple appeared to be using OSM data in their new map tiles without attribution.
In the last 24 hours, that post has received more page views and comments than I would normally expect to receive in an entire month on this site. I was even accused of “link-whoring” – making a sensational claim to intentionally drive traffic to that page, which I find a rather laughable accusation – I personally think that I’ve written posts of far greater merit than that!

Anyway, it’s fair to say that, had I known in advance the degree of scrutiny that my post would come under, I’d probably have taken more time to compose it and add more detail to my arguments. It’s clear from some of the comments that many people have entirely missed the point I was making. The curious nature of human behaviour on the internet means that those same people may well also miss the point of the additional information provided in this post, but here goes anyway:

Firstly, I am not making a generalised attack on Apple; I’m commenting on a very specific part of one product – the data from which the map tiles in the new iPhoto product are based. I have not made any comment, and frankly couldn’t care less who they copied the iPad 3 from.

Secondly, I am not making a comment as a random observer. Spatial data (or “making maps on computers”) is what I specialise in. I’ve presented first-hand research that justify my statement that Apple appears to be using OSM data in its products, and many other observers have found the same. Since my last post, this fact has also been confirmed by the OSM foundation: http://blog.osmfoundation.org/2012/03/08/welcome-apple/  (although, as far as I’m aware, Apple have still yet to confirm it themselves)

Thirdly, although there is an element of drawing attention to general business ethics and legality here (that it is both “right” for Apple to state that they are using OSM data, and also they need to do so to comply with the CC BY-SA licence under which it is made available), I also have a personal grievance. I am myself an OSM contributor, and I have not been credited for the data I have contributed that Apple is now using.

I should also perhaps mention that I have tried to contact Apple for comment on the issue, but have received no response. I also went into my local Apple store, where the sales representative informed me that the new iPhoto app was “sick” (does that mean it’s good? Or is it unwell?) but couldn’t show or tell me anything about where the maps were sourced from, because the iLife products are “not third party – they’re all Apple’s own”.

Finally, some commenters have accused me of being a “drama queen” or getting things out of proportion. They are perhaps right, but I still believe that we should not simply sit back and let these things pass. Whether Apple intentionally used OSM data without attribution, or whether that’s an error we shall have to find out in due course. They still appear to be using data that is not theirs, in breach of the licence, and lack of knowledge is not a defence.

However, if I have jumped to conclusions then it is worth reflecting on the fact that I am very fortunate to live in a country where I can point out the wrongdoings of large companies and organisations, however trivial they may seem (and, yes, that includes calling them “thieving bastards”) without fear of persecution. Many others in the world right now are not so fortunate.

So, come on Apple – do the right thing: add an appropriate attribution notice  – or, even better, add a link to encourage iOS users to contribute to OSM -  and let’s move on.

Categorising Open Street Map Road Types For Display and Routefinding

At the end of one my previous posts, I displayed a set of ways imported from Open Street Map in SQL Server Management Studio. It looked like this:

Now, as I stated previously, OSM ways don’t equate to roads. Ways can be any arbitrary series of nodes, so although at first glance it may appear to be so, the map above does not represent a roadmap. A single way may represent several roads, or only a single segment of one road. Many ways are nothing to do with roads at all – they may be rivers, or railway lines. Ways may also denote the boundaries of an area, such as a county, a park, or a building.

To create a dataset of OSM roads (or footpaths, tracks etc.) suitable for routefinding or display in a road map, it is necessary to retrieve only those ways that contain a tag element with a k attribute of “highway”. The corresponding v attribute describes the type of highway. Examples of possible values include:

• cycleway
• footway
• motorway
• path
• pedestrian
• primary
• residential
• road
• secondary
• service
• steps
• tertiary
• track
• unclassified

Note that this list isn’t exhaustive – the design of the OSM schema means that editors can tag ways or nodes with any values, but this is a list of some of the commonly-used tags.

There are many reasons why you might want to categorise each of these highway types separately.

• Consider access for different modes of transport, for example; clearly, a car can’t go down a cycleway. Nor can a tractor go on a motorway, or a cycle go down steps (unless you’re planning some sort of stunt bike ride).
• If you’re designing a route-finding application, you might want to consider and compare the relative costs of travelling down different routes. Motorway segments generally have a higher speed limit than primary roads, which in turn have higher average speed than secondary roads, etc. Therefore, a route that maximises the percentage travelled on higher-status roads may well be shorter in time, even if it covers a longer distance.
• When drawing features onto a map, it’s usual to display different categories of highways with different styles (e.g. motorways coloured blue, primary roads thicker than secondary, tracks as dotted/dashed lines).

As a simple example, to style the Spatial Results tab view of my OSM map to show different categories of roads with different thicknesses I created a table to attach a weight to each highway type, as follows:

CREATE TABLE RoadWeights (
  highwaytype varchar(32),
  roadweight int
);
INSERT INTO RoadWeights VALUES
(‘motorway’, 15),
(‘motorway_Link’, 15),
(‘trunk’, 10),
(‘trunk_Link’, 10),
(‘primary’, 8),
(‘primary_Link’, 8),
(‘secondary’, 5),
(‘tertiary’, 3),
(‘residential’, 1),
(‘unclassified’, 0);

Then, I edited my SELECT query to only display rows from my Ways table that were tagged with one of my chosen highway types, and buffered the geography LineString representing each road by the corresponding weight from the RoadWeights table:

SELECT
  w.wayid,
  wt.TagValue AS highwaytype,
  w.geog4326.STBuffer(ISNULL(roadweight,0))
FROM
  ways w
  INNER JOIN waytags wt ON w.wayid = wt.wayid AND wt.TagName = ‘Highway’
  LEFT JOIN RoadWeights rw ON wt.TagValue = rw.highwaytype
WHERE
  wt.TagValue IN (‘motorway’, ‘motorway_Link’, ‘trunk’, ‘trunk_Link’, ‘primary’, ‘primary_Link’, ‘secondary’, ‘tertiary’, ‘residential’, ‘unclassified’)

Even when viewed in the SSMS Spatial Results tab, the map already becomes much cleaner than the result shown at the top of this post – with the Norwich inner ring road and its primary arterial roads now clearly visible (and also, the train tracks coming into the railway station on the south east of the city are no longer shown)

Clearly you wouldn’t normally optimise your dataset just for the purposes of display in the SSMS spatial results tab, but you can apply this same technique to attach any other properties that correspond to the type of road – the average road speed limit, accessibility, or styling options that should be passed to a front-end display, for example.

Loading Open Street Map Data in SQL Server Part II: Ways.

In a previous post, I described how to load POI data from Open Street Maps into SQL Server. POIs are, by their very nature, pretty easy to define and store, since they are individual points of interest. In this post I’ll look at the slightly more complex issue of loading OSM ways – series of nodes that form lines (e.g. roads, rivers, county boundaries) or areas (buildings, lakes, administrative regions etc.).

Again, I’ll prove that this can be done using pure T-SQL, but I wouldn’t necessarily recommend this approach in a production environment when there are better tools for the job

Acquire OSM Data

Acquiring the OSM data follows exactly the same process as I described in my previous post:

• Go to http://www.openstreetmap.org/, pan and zoom the map to display the area of interest, and then select the “Export” tab.
• Or, 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/, or you can download subsets split by individual country or feature type from http://download.geofabrik.de/osm/ and http://downloads.cloudmade.com/

Shred the OSM Data in SQL Server

Shredding the data relating to OSM ways is a little bit more involved that for POIs, since each way contains references to a number of different nodes.

Load the XML File

As before, start by loading the XML file into a single SQL Server variable of the XML datatype using OPENROWSET(BULK) as a SINGLE_BLOB

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

 Nodes

Before loading the Ways, it’s first necessary to extract the nodes from which each way is formed:

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)

Ways

Ways do not necessarily represent routes, or even roads. Ways contain collections of nodes – they might mark out areas, footpaths, boundaries of fields, counties, or buildings, for example. To start with, I’ll just populate a table containing the IDs of all the ways included in the dataset:

CREATE TABLE ways (
wayid int
);
INSERT INTO ways
SELECT
OSMWay.e.value('(@id)[1]', 'int') AS 'WayID'
FROM
@x.nodes('/osm/way') AS OSMWay(e)

(2908 row(s) affected)

WayTags

Like Nodes, Ways may contain a number of key/value pair tag elements describing properties of ways. We’ll extract these into a separate waytags table to enable us to filter ways by category.

CREATE TABLE waytags (
wayid int,
tagname varchar(32),
tagvalue varchar(32)
);
INSERT INTO waytags
SELECT
OSMWay.e.value('(@id)[1]', 'int') AS 'WayID',
OSMWayTag.e.value('@k', 'nvarchar(32)') AS 'TagName',
OSMWayTag.e.value('@v', 'nvarchar(32)') AS 'TagValue'
FROM
@x.nodes('/osm/way') AS OSMWay(e)
CROSS APPLY
OSMWay.e.nodes('tag') AS OSMWayTag(e)

(9701 row(s) affected)

The tags assigned to a way describe properties such as what sort of way is it (a building, highway, administrative boundary, river…) and then a number of tags specific to that type of way. For highways, for example, tags might be used to describe whether it is publicly accessible, the maximum speed limit, or the type of road surface. The following query selects the ids and names of only those ways that have been tagged with one of the specified types of “highway” tags:

SELECT
w.wayid,
wtn.TagValue AS wayname,
wt.TagValue AS highwaytype
FROM
ways w
INNER JOIN waytags wt ON w.wayid = wt.wayid AND wt.TagName = 'Highway'
LEFT JOIN waytags wtn ON w.wayid = wtn.wayid AND wtn.TagName = 'Name'
WHERE
wt.TagValue IN ('motorway', 'motorway_Link', 'trunk', 'trunk_Link', 'primary', 'primary_Link', 'secondary', 'tertiary', 'residential')

(note the LEFT join to the tags table to retrieve the name of the road where known – not every way has a name)

WayNodes

The waynodes table will contain data on all of those nodeIDs that belong to a given wayID. I’ve added an identity column, orderid, in the table definition here – that’s because I need to retain the order of the nodes that make up each way as they are listed in the original OSM XML file. (Normally, you could do this with the xquery position(), but this is not supported by sql server – see https://connect.microsoft.com/SQLServer/feedback/details/383888/fully-support-position-in-xquery )

CREATE TABLE waynodes (
orderid int identity(1,1),
wayid int,
nodeid int
);
INSERT INTO waynodes (wayid, nodeid)
SELECT
OSMWay.e.value('(@id)[1]', 'int') AS 'WayID',
OSMWayNode.e.value('(@ref)[1]', 'int') AS 'NodeID'
FROM
@x.nodes('/osm/way') AS OSMWay(e)
CROSS APPLY
OSMWay.e.nodes('nd') AS OSMWayNode(e)

(17606 row(s) affected)

Construct geography Linestrings from the WayNodes

Since we can now identify:

• all those ways that are roads (by joining to the waytags table on wayid and filtering for selecting name/value tags)
• ….and we can also identify the nodes from which each way is formed (by joining from the ways table to the waynodes table on wayid and selecting nodes ordered by ascending orderid)
• …and we can retrieve the coordinates of those nodes (by joining to the nodes table on nodeid and retrieving that latitude and longitude coordinates)

we now can create geography LineStrings representing each road in our dataset.

Since OSM coordinates are measured using the WGS84 spatial reference system, first add a column of the geography datatype to the ways table:

ALTER TABLE ways
ADD geog4326 geography;

Next populate the geog4326 column with LineStrings. To do so, we’ll use the FOR XML PATH(‘’) technique of concatenating the coordinate string values of each waynode together, removing the extra comma, then manually adding the LINESTRING() around the outside to create a WKT string. It’s important to order the coordinates by the orderid column in the waynodes table to make sure that the nodes of each way are added in the correct order. Finally, parsing this value as WKT to update the geog4326 column. (Yes, this is ugly, but it works):

UPDATE ways
SET geog4326 = 'LINESTRING(' + STUFF((
SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
FROM
ways w JOIN waynodes wn ON w.wayid = wn.wayid
JOIN nodes n ON wn.nodeid = n.nodeid
WHERE wn.wayid = ways.wayid
ORDER BY w.wayid, orderid
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)'),1,1,'') +')'

(2908 row(s) affected)

Select the Data

If all goes well, you should now be able to run the following query to show all the geography LineStrings created by connecting their respective nodes

SELECT
wayid,
geog4326
FROM ways

And here, for reference, is the OSM Mapnik view of the same area – hopefully you can see the similarity!:

Displaying Open Street Map and ESRI tiles on Bing Maps AJAX v7

In a previous post, I explained how to replace the base tile layer in the Bing Maps Silverlight control with an ESRI tile layer. In this post, I’ll show how to do the same but using the Bing Maps AJAX v7 control. You can use this technique to use the Bing Maps AJAX control, but replace the Bing imagery with OSM tiles or the ESRI tile layers used in the previous Silverlight post, as well as many other tile sources.

The first step is to specify not to load the default Bing Maps tile layer. Do this by specifying the mercator MapTypeId in the options passed to the constructor when you first initialise the map:

mapTypeId: Microsoft.Maps.MapTypeId.mercator

The next step is to create a new TileSource. The uriConstructor of the TileSource must return the correct URI for a requested tile. If your tile provider names its tiles according to the default Bing Maps quadkey numbering system, then the uriConstructor can be a simple string using the {quadkey} placeholder. This will be replaced with the appropriate quadkey when the tile is requested:

var tileSource = new Microsoft.Maps.TileSource(
 {  uriConstructor: 'http://www.microsoft.com/maps/isdk/ajax/layers/lidar/{quadkey}.png' }
);

However, for OSM tiles, or any other tile providers that not follow the basic quadkey numbering system, we instead need a function to construct the appropriate URI for each tile. I’ll do this in a function called getTilePath, and I’ll specify this in the TileSource uriConstructor as follows:

var tileSource = new Microsoft.Maps.TileSource({ uriConstructor: getTilePath });

The getTilePath function will return a string with the appropriate URI for the requested tile. For OSM tiles, a typical tile looks like http://tile.openstreetmap.org/zoom/x/y.png. The function to create this tile is therefore:

function getTilePath(tile) {
 return "http://tile.openstreetmap.org/" + tile.levelOfDetail + "/" + tile.x + "/" + tile.y + ".png";
}

Put this all together and your code should look like this:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0"></script>
<script type="text/javascript">
 function GetMap() {
 // Create a basic map
 var map = new Microsoft.Maps.Map(document.getElementById("mapDiv"),
 { credentials: "YOURBINGMAPSKEYHERE",
 center: new Microsoft.Maps.Location(56, 2),
 zoom: 5,
 // Don't load the Bing base map tiles
 mapTypeId: Microsoft.Maps.MapTypeId.mercator
 });

// Create the tile source
 var tileSource = new Microsoft.Maps.TileSource({ uriConstructor: getTilePath });

// Construct the layer using the tile source
 var tilelayer = new Microsoft.Maps.TileLayer({ mercator: tileSource, opacity: 1 });

// Push the tile layer to the map
 map.entities.push(tilelayer);
 }

function getTilePath(tile) {
 // Construct the URI path for an OSM tile based on tile zoom/x/y
 return "http://tile.openstreetmap.org/" + tile.levelOfDetail + "/" + tile.x + "/" + tile.y + ".png";
 }
</script>
</head>
<body onload="GetMap();">
 <div id='mapDiv' style="position:relative; width:640px; height:480px;"></div>
</body>
</html>

And here’s what it looks like:

If you want to try some other tile providers, replace the URI constructed by the getTilePath() function with some of the following:

DeLorme World Basemap

function getTilePath(tile) {
 return "http://server.arcgisonline.com/ArcGIS/rest/services/Specialty/DeLorme_World_Base_Map/MapServer/tile/" + tile.levelOfDetail + "/" + tile.y + "/" + tile.x;
 }

ESRI World Imagery

function getTilePath(tile) {
return "http://services.arcgisonline.com/ArcGIS/rest/services/World_Imagery/MapServer/tile/" + tile.levelOfDetail + "/" + tile.y + "/" + tile.x;
}