Exporting Spatial Data From SQL Server to ESRI Shapefile

I love the site stats you get from WordPress. This morning I noticed that, in the 9 months that I’ve been writing this blog, nearly 200 people have come to my site after searching for the term “OGR2OGR” in a search engine. That’s more than any other single word search term. Around another 200 people have come here after searching for variations and combinations on this theme, including “OGR2OGR Sql Server”, “OGR2OGR MSSqlSpatial”, “OGR SQL”, “OGR Import shapefile” etc.

What I’m not sure, though, is how to interpret this statistic or best act upon it. Does it mean, for example, that my articles about OGR2OGR – the open source toolkit for spatial data conversion and manipulation – are helpful, or high quality? Sure, several of them get listed on the first page of results for a Google search, but does that simply mean that nobody else on the internet is writing about OGR2OGR? And do the people that come here searching for information about OGR2OGR actually find what they’re looking for, or do they end up leaving empty-handed?

I don’t know the answers to these questions, but since there are obviously some people out there looking for this kind of information (and since I know, from experience, that OGR2OGR can be an absolute bugger to get working correctly) here’s another post about OGR2OGR anyway… and this time it’s about exporting spatial data from SQL Server. There’s plenty of information on the ‘net describing how to import spatial data from ESRI shapefiles into SQL Server (including my own posts, such as here, here and here, for example). However, what I haven’t seen any examples of yet is how to do the reverse: taking geometry or geography data from SQL Server and dumping it into an ESRI shapefile.

There’s plenty of reasons why you might want to do this – despite its age and relative limitations (such as the maximum filesize per file, limited fieldname length, the fact that each file can contain only a single homogenous type of geometry, etc…), the ESRI shapefile format is still largely the industry standard and pretty much universally read by all spatial applications. Recently, I needed to export some data from SQL Server to shapefile format so that it could be rendered as a map in mapnik, for example (which, sadly, still can’t directly connect to a MS SQL Server database).

So, here’s a step-by-step guide, including the pitfalls to avoid along the way.

Setup

To start with, make sure you’ve got a copy of OGR2OGR version 1.8 or greater (earlier versions do not have the MSSQL driver installed). You can either build it from source supplied on the GDAL page or, for convenience, download and install pre-compiled windows binaries supplied as part of the OSgeo4W package.

Now, let’s set up some test data in a SQL Server table that we want to export. To test the full range of OGR2OGR features (or should that say, “the full range of error messages you can create”?), I’m going to create a table that contains two different geometry columns – an original geometry and a buffered geometry, populated using a range of different geometry types:

CREATE TABLE OGRExportTestTable (
  shapeid int identity(1,1),
  shapename varchar(32),
  shapegeom geometry,
  bufferedshape AS shapegeom.STBuffer(1),
  bufferedshapearea AS shapegeom.STBuffer(1).STArea()
);

INSERT INTO OGRExportTestTable (shapename, shapegeom) VALUES
('Point #1', geometry::STGeomFromText('POINT(13 10)', 2199)),
('Point #2', geometry::STGeomFromText('POINT(7 12)', 2199)),
('Line #1', geometry::STGeomFromText('LINESTRING(0 0, 8 4)', 2199)),
('Polygon #1', geometry::STGeomFromText('POLYGON((2 2, 4 2, 4 4, 2 4, 2 2))', 2199)),
('Line #2', geometry::STGeomFromText('LINESTRING(0 10, 10 10)', 2199));

Here’s what the contents of this table looks like in the SSMS Spatial Results tab:

Exporting from SQL Server to Shapefile with OGR2OGR (via a string of errors along the way)

The basic pattern for OGR2OGR usage is given at
http://www.gdal.org/ogr2ogr.html
, with additional usage options for the SQL Server driver at
http://www.gdal.org/ogr/drv_mssqlspatial.html
. So, let’s start by just trying out a basic example to export the entire OGRExportTestTable from my local SQLExpress instance to a shapefile at c:\temp\sqlexport.shp, as follows: (change the connection string to match your server/credentials as appropriate)

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;tables=OGRExportTestTable;trusted_connection=yes;"

This will fail with a couple of errors, but the first one to address is as follows:

ERROR 1: Attempt to write non-point (LINESTRING) geometry to point shapefile.

SQL Server will allow you to mix different types of geometry (Points, LineStrings, Polygons) within a single column of geometry or geography data. An ESRI shapefile, in contrast, can only contain a single homogenous type of geometry. To correct this, rather than trying to dump the entire table by specifying the tables=OGRExportTable in the connection string, we’ll have to manually select rows of only a certain type of geometry at a time by specifying an explicit SQL statement. Let’s start off by concentrating on the points only. This can be done with the –sql option, as follows:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT * FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"

This time, a new error occurs:

ERROR 1: C:\temp\sqlexport.shp is not a directory.

ESRI Shapefile driver failed to create C:\temp\sqlexport.shp

Although our first attempt to export the entire table failed, it still created an output file at C:\temp\sqlexport.shp. Seeing as we didn’t specify the behaviour for what to do when the output file already exists, when we run OGR2OGR for the second time it has now errored. To correct this, we’ll add the –overwrite flag.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT * FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite

Running again and the error is now:

ERROR 6: Can't create fields of type Binary on shapefile layers.

Ok, so remember that our original shapefile had two geometry columns. When you create a shapefile, one column is used to populate the shape information itself, while every other column becomes an attribute of that shape, stored in the associated .dbf file. Since the remaining geometry column is a binary value, this can’t be stored in the .dbf file. To resolve this, rather than using a SELECT *, we’ll explicitly specify each column to be included in the shapefile. You could, if you want, then omit the second geometry column completely from the list of selected fields. Instead, I’ll use the ToString() method to convert it to WKT, which can then be stored as an attribute value:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite

This should now run without errors, although you’ll still get a warning:

Warning 6: Normalized/laundered field name: ‘bufferedshapearea’ to bufferedsh

The names of any attribute fields associated with a shapefile can only be up to a maximum of 10 characters in length. In this case, OGR2OGR has manually truncated the name of the buferedshapearea column for us, but you might not like to use the garbled “bufferedsh” as an attribute name in your shapefile. A better approach would be to specify an alias for long column names in the –sql statement itself. In this case, perhaps just “area” will suffice:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite

And now, for the first time, you should be both error and warning free:

So, now, just repeat the above but substituting the POINT, LINESTRING, and POLYGON geometry types, and creating three separate corresponding shapefiles:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" -sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'" -overwrite

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" -sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'" -overwrite

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" -sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'" -overwrite

All done, right? Let’s just have a quick check on the created files to make sure they look ok. You can do this using ogrinfo with the –al option, which will give you a summary of the elements contained in any spatial data set. I mean, I’m sure they’re fine and everything, but…. hang on a minute:

ogrinfo -al c:\temp\sqlexport_point.shp

Here’s the POINT shapefile:

Initially looks ok, the shapefile contains 2 Point features – they’ve got the correct attribute fields, but both points seem to have been incorrectly placed at coordinates of POINT(0.0 2.0). Huh?

What about the LINESTRING shapefile:

This is even worse – even though we specified that only LineString geometries should be returned from the SQL query, the created shapefile thinks it contains 2 Point features (Geometry: Point, near the top). And those points both lie at POINT (0.0 0.0)…

And the POLYGON shapefile:

Same problem as the LineString – it’s effectively an empty Point shapefile.

The problem seems to be that, even though we’re only returning geometries of a certain type from the SQL query, we haven’t explicitly stated that to the shapefile creator, so OGR2OGR is creating three empty shapefiles first (each set up to receive the default geometry type of POINT), and then trying to populate them with unmatching shape types, leading to corrupt data. To explicitly state the geometry type of the shapefiles created, we need to supply the SHPT layer creation option for each shapefile as specified at
http://www.gdal.org/ogr/drv_shapefile.html
, by adding –lco “SHPT=POLYGON”, –lco “SHPT=ARC” (for LineStrings) etc. as follows:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite
-lco "SHPT=POINT"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'"
-overwrite
-lco "SHPT=ARC"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'"
-overwrite
-lco "SHPT=POLYGON"

Unfortunately, that brings us round to almost exactly the same error as we first started with when creating the LineString and Polygon shapefiles (Gah! I thought we’d got rid of them!):

ERROR 1: Attempt to write non-linestring (POINT) geometry to ARC type shapefile

ERROR 1: Attempt to write non-polygon (POINT) geometry to POLYGON type shapefile

We are only selecting geometries of the matching type for each shapefile by filtering the query based on STGeometryType(), so why does OGR2OGR think that we are selecting other types of geometries? What’s more, we haven’t yet explained why the point shapefile (which was, after all, being populated only with point geometries), incorrectly placed both points at coordinates POINT(0.0 2.0). It seems that something is corrupting the results of the SQL statement.

And here’s the “Ta-dah!” moment. According to
http://www.gdal.org/ogr/drv_mssqlspatial.html
, when retrieving spatial data from SQL Server, “The default [GeometryFormat] value is 'native', in this case the native SqlGeometry and SqlGeography serialization format is used”. However, this doesn’t actually appear to hold true. SQL Server stores geometry and geography data in a format very similar to, but slightly different from Well-Known Binary (WKB). The SQL Server binary values for the two points in the OGRExportTestTable are:

0x00000000010C0000000000002A400000000000002440
0x00000000010C0000000000001C400000000000002840

The Well-Known Binary of these two points is , instead, as follows:

0x01010000000000000000002A400000000000002440
0x01010000000000000000001C400000000000002840

As you can see, they’re very similar – the coordinate values are serialised as 8-byte floating point binary values in both cases, but the MSSQL Server native serialisation has a different and slightly longer (i.e. one byte more) header. Thus, if OGR2OGR is expecting to receive one type of data, but actually gets the other, all the bytes will be displaced slightly. This could explain both why OGR believed it was receiving the incorrect geometry types and also why the point coordinates were wrong.

To correct this, rather than retrieving the shapegeom value directly, use the STAsBinary() method in the SQL statement to retrieve the WKB of the shapegeom column instead.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite
-lco "SHPT=POINT"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'"
-overwrite
-lco "SHPT=ARC"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'"
-overwrite
-lco "SHPT=POLYGON"

Right, we are definitely making progress now. Trying ogrinfo again reveals that all of the layers have the correct number of features, or the appropriate type, and all have the right coordinate values. Yay!

There’s just one nagging thing and that’s the Layer SRS WKT: (unknown). When we converted from the SQL Server native serialisation to Well-Known Binary, we lost the metadata of the spatial reference identifier (SRID) associated with each geometry. This information contains the details of the datum, coordinate reference system, prime meridian etc. that make the coordinates of each geometry relate to an actual place on the earth’s surface. In the shapefile format, this information is contained in a .PRJ file that usually accompanies each .SHP file but, since we haven’t supplied this information to OGR2OGR, none of the created shapefiles currently have associated .PRJ files, so ogrinfo reports the spatial reference system as “unknown”.

To create a PRJ file, we need to append the –a_srs option, supplying the same EPSG id as was supplied when creating the original geometry instances in the table. (Of course, if you have instances of more than one SRID in the same SQL Server table, you’ll have to split these out into separate shapefiles, just as you have to split different geometry types into separate shapefiles)

The Final Product

So here’s the final script that will separate out points, linestrings, and polygons from a SQL Server table into separate shapefiles, via the WKB format but maintaining the SRID of the original instance, and retaining all other columns as attribute values in the .dbf file. Tested and confirmed to work with columns of either the geometry or geography datatype in SQL Server 2008/R2, or SQL Server Denali CTP3.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite
-lco "SHPT=POINT"
-a_srs "EPSG:2199"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'"
-overwrite
-lco "SHPT=ARC"
-a_srs "EPSG:2199"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'"
-overwrite
-lco "SHPT=POLYGON"
-a_srs "EPSG:2199"

And here’s the resulting shapefile layers loaded into qGIS:

Creating a Windows 8 Metro Slippy Map Application

So (along with half the world, it seems), I grabbed myself a copy of the Windows 8 Developer Preview earlier in the week and have been trying to get to grips with it, both as a user and also with the interest of developing some applications.

I’m not going to go into detail about the (fairly radical) changes made in Windows 8, partly because many other people have already written about these, and partly because I’m very unqualified to do so – I’m finding things out myself as I go along. Instead, seeing as Windows 8 is all about the user interface and aimed at supporting touch-enabled devices, the main thing I was interested in was seeing what’s involved in developing a simple “Metro” application with a slippy map interface. This post is a summary of my initial investigations.

What’s Metro, anyway?

Metro is the (current) name of the new, touch-centric style that is exposed as the default interface in Windows 8. It’s certainly a radical departure from traditional Windows UI, and more resembles the interface you’d get on a mobile phone handset or other portable device. (This is not coincidence – the big growth market in the PC world at the moment lies not in desktops or laptops, but with tablet PCs, and it’s clear that Microsoft is designing Windows 8 to be run on devices that will “compete” against Apple’s iPad and Android tablets). Apart from the touch interface, there are other similarities as well – Metro applications are typically small in size, relatively simple, have a full-screen UI, are responsive (due to asynchronous method calls), and they can be packaged up and distributed via the “Windows Application Store” – a model much like Chrome’s web store, Android’s marketplace or Apple’s App Store.

So, I thought it would be nice to create a simple little Metro app that displayed a slippy map interface, possibly showed points of interest near to you (using the built-in geolocation), allowed you to plot routes etc. – fairly regular stuff. Since this is Microsoft Windows, after all, you’d think that this would be relatively easy to do with the Bing Maps API, right? Well….

Developing for Metro – Choose your Weapon

First things first – the core system component that powers Metro Apps is something called WinRT. WinRT contains APIs for handling devices, graphics, communications etc. that are exposed via COM interfaces. You can write Metro applications that reference these WinRT assemblies using a variety of languages – managed code (C#/VB) or unmanaged code (C/C++) with a XAML front-end, or using Javascript with an HTML/CSS front-end. The Windows 8 technology stack is summarised in this slide shown at the recent BUILD conference:

Bing Maps comes in lots of different flavours too, including (amongst others) a Silverlight control, a WPF control, and a Javascript control. Since WPF and Silverlight both make use of XAML for UI and managed code-behind, you might think that it would be relatively easy to port an application that uses the Bing Maps Silverlight/WPF control to Metro XAML and c# code-behind, but actually I’ve not heard of anybody successfully do this yet. Also, considering that the WPF Bing Maps control is still itself only in beta, and the Silverlight control has apparently been in stasis for several years, I certainly wasn’t about to commit any effort to making these work on Windows 8 when I’m not convinced about their future longevity….

Instead, I thought I’d opt for the HTML/Javascript approach – which are technologies I’m more familiar with anyway, and have a more mature associated Bing Maps AJAX control. However, even then, I’ve been surprised at the number of changes required to port a regular HTML/Javascript Bing Maps web application and get it to work in a Metro app.

HTML/Javascript in Metro – it’s all about Context

Most people think of HTML/Javascript as being exclusively used on the web – the two technologies being responsible for structure/client-side code of webpages respectively, and executed only within the context of a web browser. This, in itself, has historically made the Javascript execution environment somewhat sandboxed. Even though projects such as nodejs have shown that Javascript can also be used in other contexts, it has not been widely used for creating desktop applications before. (In a Windows context, Javascript is used for creating sidebar gadgets in Windows Vista/7,  but these have generally been little more than mini-webpages pinned to the desktop, and have little access to system resources)

In Metro, however, a Javascript application can be used to access core parts of the operating system (including the file system, networking, and other devices) by calling into the WinRT APIs. This sounds kind of dangerous, doesn’t it? Javascript, with its <script> injection, dynamic DOM manipulation et al. having access to your system resources? To minimise the risk of malicious code in this scenario, Windows 8 introduces different security contexts for Metro apps.

The main HTML landing page for a Metro app runs in the local context. It has access to the WinRT APIs, and can also do some other things not possible using Javascript running in a regular webbrowser, such as making cross-domain XmlHttpRequests. However, there are two main restrictions placed on pages running within the local context:

• You can only load locally-packaged scripts. i.e. you can’t include a <script> tag whose src attribute points to a file on a remote server.
• Any methods that attempt to change the DOM (i.e. by setting an element’s innerHTML or calling the document.write method) are sanitised.

Unfortunately, that means using the Bing Maps AJAX API is not possible from within a Metro app running within the local context, because the Bing Maps control needs to be referenced from a remote URL (in other words, you need to include the library using <script type=”text/javascript” src=”http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0></script&gt;), which is not allowed within the local context. Even if you created an offline copy of the Bing Maps AJAX library (which is not supported), it would still not work because of the way in which it manipulates the DOM of the HTML page.

So, what options do you have?

Option 1: Embed the Map Control in a iFrame using the Web Context

Although the main HTML page of a Metro app must run in the local context, you can have additional HTML pages that are loaded within the web context. When running in the web context, your HTML/JS code behaves almost exactly as it would do if loaded within a regular Internet Explorer browser – in other words, you lose the ability to tap into the WinRT APIs and other new Metro features, but you can include remote script references again as normal. For a full comparison of what’s possible in the local context and in the web context, refer to
http://msdn.microsoft.com/en-us/library/windows/apps/hh465373%28v=VS.85%29.aspx

However, I wanted to embed a slippy map as the main frontend UI on my Metro App – I don’t want the user to have to navigate to a separate HTML page in the web context just to load the map. So, is there a way to get the best of both worlds – having elements loaded from within the web context displayed within the main page in the local context? One approach is to create a regular Bing Maps application in a separate HTML page in the web context, and then embed that in our main application in a separate iframe.

To do so, create an iframe in the main page in which the src element points to your Bing Map page, prefixed by  ms-wwa-web:/// to show that the content of the iframe is to be loaded in the web context. So, here’s a simple default.html page for a Metro App:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=1024, height=768" />
    <title>WinWebApp1</title>
    <!-- WinJS references -->
    <link rel="stylesheet" href="/winjs/css/ui-dark.css" />
    <script src="/winjs/js/base.js"></script>
    <script src="/winjs/js/wwaapp.js"></script>
    <script src="/winjs/js/ui.js"></script>
    <script src="/winjs/js/controls.js"></script>
    <!-- WinWebApp1 references -->
    <link rel="stylesheet" href="/css/default.css" />
    <script src="/js/default.js"></script>
</head>
<body>
    <header role="banner" aria-label="Header content">
        <div class="titleArea">
            <h1 class="pageTitle win-title" role="button" aria-label="Groups" tabindex="0">
                Metro Map App</h1>
        </div>
    </header>
    <div>
      <iframe id="mapIframe" src="ms-wwa-web:///map.html" width="1024px" height="400px"></iframe>
    </div>
</body>
</html>

and here’s the map.html page that is loaded within the web context of the iframe:

<!DOCTYPE html>
<html>
<head>
    <title>Map</title>
    <!-- We can include remote script because this page will be loaded in the web context (ms-wwa-web:///map.html)  -->
    <script src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0" type="text/javascript"></script>
    <script type="text/javascript">
        var map = null;

        function initialize() {
            var mapOptions = {
                credentials: "AkeAFl99ZABCDEFG7Kb2D12345678lRQm8vnZMpfMV7HsfNqwertyuiopd",
                center: new Microsoft.Maps.Location(52, 0),
                mapTypeId: Microsoft.Maps.MapTypeId.road,
                zoom: 7,
                showLogo: false,
                showDashboard: false
            };
            map = new Microsoft.Maps.Map(document.getElementById("map"), mapOptions);
        }
        document.addEventListener("DOMContentLoaded", initialize, false);
    </script>
    <style type="text/css">
        body, html
        {
            margin:0;
        }
        
        #map
        {
          position:absolute;
          width:100%;
          height:100%;
        }
    </style>
</head>
<body>
    <div id="map"></div>
</body>
</html>

Hit F5 to debug the app and you’ll get something like this – a regular slippy map interface using Bing Maps, contained in an iframe that seamlessly integrates into the container of your Metro App.

So far so good. But the problem with this approach is that, although visually it may appear integrated, the content of the iframe is essentially isolated from the main application (that, after all, is the whole reason why the iframe is allowed to be loaded in the web context – if it could directly access the WinRT APIs available to the parent application you’d be exposing the security risks that the whole local context scenario is meant to avoid). So how do we go about creating interface elements in the parent container page that update (or are updated by) elements in the map iframe?

You can communicate between the host application running in the local context and the map iframe running in the web context by using the HTML5 postMessage method. If you search the internet, you’ll find plenty of examples of postMessage, but they’re very simplistic – most of the examples involve sending a single text string from one window to another and, when it’s received, simply alerting that message to the user. But when you actually want to try to expose entire interfaces between iframe windows, you’ll quickly create a mess of code – the postMessage method is, after all, designed for…. posting messages... so any data passing between iframes must be passed as text. You then need to setup a messageHandler on the receiving window that will act appropriately based on the text data received.

For example, I set up a simple row of button controls in my container HTML page to pan the map and zoom in/out.

<button onclick="Pan(-1,0);">Pan Left</button>
<button onclick="Pan(1,0);">Pan Right</button>
<button onclick="Pan(0,1);">Pan Up</button>
<button onclick="Pan(0,-1);">Pan Down</button>
<button onclick="ZoomOut();">Zoom Out</button>
<button onclick="ZoomIn();">Zoom In</button>

The methods attached to these buttons do not pan or zoom the map directly. Rather, they create an instruction that will be sent via postMessage to the map iframe. Fortunately, the postMessage API implemented in Internet Explorer 10 (which is the engine in which Metro Apps running in the web context are executed) accepts postMessages either as single strings or as JSON objects. So,  the “instruction” passed to the map is contained in a JSON object in which method is the action to perform on the map, and args contains any arguments to pass to that method. Notice that I specify that the target domain of the postMessage call lies in the ms-ww-web web context:

function Pan(dx, dy) {
    var xMsg = { method: 'pan', args: {x: dx, y: dy} };
    mapIframe.postMessage(xMsg, "ms-wwa-web://" + document.location.host);
}

function ZoomIn() {
    var xMsg = { method: 'zoomin' };
    mapIframe.postMessage(xMsg, "ms-wwa-web://" + document.location.host);
}

function ZoomOut(dx, dy) {
    var xMsg = { method: 'zoomout' };
    mapIframe.postMessage(xMsg, "ms-wwa-web://" + document.location.host);
}

Then, in the map iframe, I need to set up a listener to any postMessages received:

window.addEventListener("message", receiveMessage, false);

and, in the receiveMessage() callback, unravel and handle the requested JSON instruction (which is contained in the event.data parameter):

function receiveMessage(event) {
  switch (event.data.method) {
    case 'pan':
      var dx = event.data.args.x;
      var dy = event.data.args.y;
      var pos = map.tryPixelToLocation(new Microsoft.Maps.Point(map.getWidth()/2 + dx, map.getHeight()/2 + dy, Microsoft.Maps.PixelReference.viewport));
      map.setView({ center: pos });
      break;

    case 'zoomin':
      var currentZoom = map.getZoom();
      map.setView({ zoom: currentZoom + 1 });
      break;

    case 'zoomout':
      var currentZoom = map.getZoom();
      map.setView({ zoom: currentZoom - 1 });
      break;
  }
}

Here’s a diagram of what’s going on:

Bear in mind that all I’m doing is adding the simplest of interactivity here – just panning and zooming the map. The more functionality you want to add, the more complicated the data structures you have to pass in the postMessage call (consider things like the set of points returned in a route requested from a routing service).

Also, notice that at the moment I’m only doing one-way communication from the parent container to the map iframe. But there are plenty of situations in which you’ll also want to pass messages the other way – to notify the container app when a certain action has happened on the map for example. Thus you’ll also need to setup an event listener on the container page to listen to incoming messages from the map iframe. Using the nested iframe approach and postMessage calls to create two-way interfaces like this quickly becomes unmanageable…

Option 2: Use an AJAX Map Control that works within the Local Context

In order to be executed directly from an HTML page in the local context, a Javascript library must be loaded locally. So how about we ditch the Bing Maps control altogether and use an alternative control that can be run from a local script. How about Leaflet, say?

If you don’t know, Leaflet is a new open source AJAX map control from CloudMade – a company with very close associations to the Open Street Map project. In the past, developers looking for an open source slippy map control have tended to opt for OpenLayers – but Leaflet looks set to provide a more modern, lightweight alternative. In fact, even though it’s only a few months old, Leaflet already offers many features not found in commercial map controls from the likes of Bing and Google (including support for WMS services, <canvas> tiles, and extensible map objects). But, for the purposes of developing a Metro application, Leaflet’s best feature is that it’s a very small download, consisting of just a single .js file, one .css file, and a handful of images that you can include in your own project.

Fortunately, the Leaflet API also follows very similar syntax to Bing Maps / Google Maps and will seem familiar to anyone who’s used to the idea of pushpins, infoboxes, polylines and polygons. Within a few minutes, I was therefore able to rewrite my Bing Maps app to use Leaflet instead. And, because I was loading the Leaflet library from a local <script> resource I didn’t have to worry about embedding an iframe or any of that postMessage nonsense – I could incorporate the map directly in my main HTML page in the local context. Creating a basic Leaflet map involves the following:

var map = new L.Map('divMap');
map.setView(new L.LatLng(52,0), 7);

Then, with a few calls to the Bing Maps REST service I created a new L.TileLayer using Bing Maps road style tiles to match the look and feel of my previous attempt (by default, Leaflet uses Open Street Map tiles). The result looked like this:

Visually, not much difference, but the pushpin popup says it all.

Summary

I don’t know the performance difference between these approaches – I guess there must be an overhead in having to marshal postMessage calls between iframes but I don’t know if its significant or not. Anecdotally, using Leaflet in the local context seems to be more responsive than the Bing Maps iframe approach.

Better performing or not, the main thing to note is that it’s a lot easier to write manageable code using the second approach. And there’s the irony – until (if?) a native WinRT maps control comes out (or unless you can figure a better way to handle the local/web context dilemma than me), if you’re looking to develop a slippy map interface for a Windows Metro application it’s actually much easier to do so using a third-party map API such as Leaflet than it is to use Microsoft’s own Bing Maps API…

Windows 8 Developer Preview

Like, it seems, many people, I’ve spent some portion of today downloading and playing with the first preview version of Windows 8, released in the early hours of this morning. You can grab the release either from the windows dev centre or from MSDN. If you’ve got an MSDN account, I strongly recommend using the latter (together with the download manager) – it gave me download speeds of 25Mbps rather than 0.5Mbps from the public download link.

Once you’ve nabbed the .iso file, you’ll want to install it. If you go for the x64bit version with the developer tools, as I did, the first thing I noticed is that the file is 4.8Gb – too big to burn onto regular DVD media. Not a big deal, as I was planning to install this under a virtual machine on the computer onto which I’d downloaded the ISO anyway, but possibly worth noting.

Scott Hanselman posted a blog article explaining how to boot Windows 8 from a VHD created from this ISO, but the steps involved seemed rather convoluted to me (requiring a USB stick, manual disk partitioning, and lots of disclaimers in case you irreparably break your PC…). What’s more, I’m in the habit of installing any CTP/Beta software under virtual machines that I can run from within my host OS rather than replacing it, so I wasn’t too keen to follow his approach. So I set about creating a virtual machine on which I could run the preview instead.

I normally use VMWare Player as my virtualisation client of choice (not the fastest, but free, reliable, and fairly feature-rich). However, as has been noted by several people on Twitter, the current developer preview release simply doesn’t work in VMWare Player. Trying to create a new Virtual Machine based on the ISO file simply generates the error vcpu-0:NOT_IMPLEMENTED as shown below:

I haven’t personally tried it, but I understand that Microsoft Virtual PC suffers from the same problem, so don’t bother going there either.

Fortunately, (and slightly ironically) Oracle’s Virtual Box seems to have more success loading the Windows 8 image than either VMWare’s or Microsoft’s offerings. So, grab a Virtual Box if you haven’t already (why not? It’s free!) and create a new system, using “Other Windows” as the OS type. I don’t know what the minimum required specs are for Windows 8, but I gave the VM 4096Mb of RAM and 60Gb hard drive, which should be plenty. Choose to create a new start-up hard disk using the VDI file format. (More detailed instructions on these steps can be found at
http://www.windows7hacker.com/index.php/2011/09/install-windows-8-developer-preview-on-virtualbox/
)

Once the machine is created, start it up and on the “installation media” dialog box, navigate to the WindowsDeveloperPreview-64bit-English-Developer.iso image on the host machine. Then finish the wizard and, if all goes well, you should shortly be seeing this:

Note that, if you don’t see the above, but see an error status 0xc0000225 An unexpected error has occurred instead …

… then it’s possibly because you haven’t got the APIC setting enabled on the BIOS for your virtual machine. Shutdown the VM, then open the VirtualBox settings and ensure that the checkbox highlighted below is checked:

Now try booting the VM up again.

The final question, of course, is that having got Windows 8 to boot, what the heck are going to do with it? I’ve just spent 10 minutes swiping aimlessly around…

Grand Theft Metropolis Auto Street Racer. With Bing Maps.

Remember Grand Theft Auto (the first one)? It was one of the first free-roaming top-down driving games, and looked a bit like this:

How about Metropolis Street Racer? The Sega Dreamcast game was one of the first to allow you to drive around accurate road tracks and backdrops set in real world cities including Tokyo and London:

 

Now I know what you’re thinking – “Wouldn’t it be great if I could re-live and combine the fond memories of those classic videogames, by driving around a true street network like in MSR but with a cute little car like in the first Grand Theft Auto?”. And what about if you weren’t limited to just a few cities, but could drive around anywhere in the world (well, anywhere that Bing Maps has road information for)?

Well, now you can.

Presenting….. (drum roll, please)….

Bing Maps Grand Theft Metropolis Auto Street Racer!

…ok, so my marketing department still needs to do a little bit of work on the name, but hopefully you get the idea. And, to be honest, there’s not much to do other than drive around (but, then again, arguably there isn’t that much to do in any driving computer game other than, well, drive around).

Instructions

• Double-click anywhere on the map to set a destination.
• To jump straight to a location, enter a placename/address/postcode and click “Teleport” instead.
• Marvel as the dinky little car takes the real route to the chequered flag at the chosen destination, driving past photo-realistic imagery (well, actual photographic imagery) along the way.
• Click the “follow” checkbox to automatically pan the map to follow the car as it travels. Uncheck the box if you want to pan manually.

Features

• Features such exotic locations as….

London	Paris
Sydney	Lowestoft

Credits

• Car sprite courtesy of:
  http://www.reinerstilesets.de/
  
• Concept inspired by: Grand Theft Auto, Metropolis Street Racer (produced by the, sadly now-defunct UK games developer Bizarre Creations), and Mike Williams’ Car Trip 
• Everything else: me.

Ok, so I don’t expect it will win any videogame awards – it’s not much of a game, but it does demonstrate a range of Bing Maps AJAX v7 functionality – animation, dash-styled polylines, the geocoding and routing REST services, dynamically assigning CSS styles to a pushpin, adjusting for orientation of the map… if anyone wants to take this as a starting point to create an actual racing game, that would be awesome.

Click Here to Play the Game!