Two New Data Modules for Bing Maps V7

 

By Ricky Brundritt, EMEA Bing Maps Technology Solution Professional

In September of 2011 we started the Bing Maps v7 Module CodePlex Project. The purpose of this project is to create a centralized location where developers could find and share useful modules that expand the functionality of the Bing Maps V7 API. Since the start of the project, we’ve had 15 modules submitted.

Today, I would like to highlight the two newest modules added to the project and provide a few updates to existing modules.

GeoJSON Module

Download here

This module was created by Brian Norman a Microsoft Bing Maps MVP from Earthware Ltd.

This module allows you to import GeoJSON files into Bing Maps. A GeoJSON feed will be downloaded and parsed into an EntityCollection which can then be added to the map. Additional properties are captured and stored in a Metadata tag on each shape making it easy to relate shapes to their metadata.

GeoJSON is a data format standard used for representing geospatial objects in JSON (JavaScript Object notation). JSON is much more compact than XML which makes it a great format for sharing spatial data online. In fact the AJAX Map DataConnector uses GeoJSON to send spatial data from SQL Server to Bing Maps.

Well Known Text Reader/Writer Module

Download here

I created this module because I wanted a simple tool for quickly visualizing Well Known Text on Bing Maps. This module allows you to easily read and write Well Know Text data from Bing Maps. When reading Well Known Text data it is parsed into Bing Maps shapes; MultiPoint, MultiLinstring, MultiPolygon and GeometryCollection are turned into an EntityCollection of shapes. To write Well Known Text simply pass in a Bing Maps shape and the Well Known Text equivalent will be returned.

clip_image004

Well Known Text (WKT) is an OGC (Open Geospatial Consortium) standard for representing Geospatial Data. In fact WKT is supported by the spatial types in Microsoft SQL Server 2008 and above as well as SQL Azure.

*Project Idea: Combine this module with the Shape Toolbox Module and make it easy for your users to draw on the map and upload the shape data into Microsoft SQL Server.

*Demo Tip: Use this module to quickly create demos that render complex spatial data. Simply store your Well Known Text in a JavaScript file as a string to save time setting up a web service to connect to your database. Note: this approach is not recommended for production applications as loading all the spatial data via a JavaScript file can make for slow loading of your application.

Other Data Related Modules

GeoRSS Module – GeoRSS is a common XML file format for sharing spatial data as a syndication feed. This module also supports GML, an OGC compliant XML format. This module has been updated to support Complex Polygons (polygons with holes).

GPX Module – GPX is a common XML data format that is used by GPS devices. Many GPS devices allow you to stave points, routes and tracks which can then be exported from the device in GPX format. This module makes it easy to view these files on Bing Maps.

Handling Multiple Spatial Columns

Some spatial data formats can store only a single logical set of features. Any given ESRI shapefile, for example, can store only POINTs, LINESTRINGs, or POLYGONs – not a mixture of each type of geometry. What’s more, all of those geometries must all be defined using the same spatial reference system, as defined in the associated .PRJ file.

Some other formats are more flexible. A SQL Server table, for example, can have many separate geometry or geography columns, and the values can contain both a mixture of geometry types, and even different spatial reference systems within the same column. I frequently use multiple spatial columns as a way of storing pre-calculated geometries relating to each feature at different levels of approximation, or different coordinate systems. For example, suppose that I have a table of features that were originally supplied in EPSG:27700 (OS National Grid of Great Britain). In addition to keeping the original geometry, I re-project those features into EPSG:4326 (WGS84) for displaying on Bing Maps. I also frequently simplify those features (using Reduce()) to create simpler approximations for display at far zoom levels. I keep each of these modified versions of the geometry in separate columns of the same table, named according to the datatype, SRID, and any modifications that have been performed, such as this:

 

CREATE TABLE (
  FeatureID int,
  geom27700 geometry,
  geog4326 geography,
  geog4326_reduce1000 geography
);

 

I came across a situation earlier today trying out Safe FME against a table with this structure that held me up for a few hours, so I thought I’d write about it here.

If you create a Feature Reader from a SQL Server table that has only a single geometry or geography column (as, I suppose, is probably the norm), you don’t need to explicitly specify the name of the column, as Safe FME is intelligent enough to work it out for you. Looking in the log, you’ll see a line that says

MS SQL Server (Spatial) Reader: Spatial column not specified for Feature Type 'dbo.Mastermap'.
Using Column 'GEOMETRY' with type 'geometry'

 

If you’ve only ever used tables with one spatial column, you’ve probably not even noticed this as it shoots past in the log screen – you just drag your MS SQL Feature Reader onto the workspace and use it just as you would use a ESRI shapefile reader, or any other single data source. FME loads the values in the spatial column as features, and the values of all other columns of the table as attributes of those features.

But what if, like me, your source table has more than one spatial column? How do you specify which geometry/geography column should be used in the MSSQL reader to populate the feature type? Well, if you go to the parameters tab of the feature type you’ll see a couple of options:

  • “Specify Geography (geodetic) Column” – Yes if you’re using the geography datatype, or No if you’re using a column of the geometry datatype.
  • Geometry Column Name / Geography Column Name – The name of the column to retrieve.

In my dataset, the source column was of the geometry datatype and named Geom27700, so I set the parameters in my reader feature type as follows:

clip_image001[7]

I then carried on with building my (rather simple) workflow, as follows – check for valid data, reproject to WGS84, orient polygons using the left-hand rule, and then output to a writer:

image

Rather than insert the records into a new dataset, the writer in this case was designed to update the same table from which I’d read the features, but populate the geog4326 column (remember that features had been read from the geom27700 column).

So, I set the Writer Mode to UDPATE, selected the FEATURE_ID (the primary key of the table) as the key on which features would be matched, and set the other  parameters as follows:

Then, I clicked execute.

10 hours later, and FME was reporting that the transformation had been a success, with 48 million rows loaded into the destination table. But when I came to look at the table in SQL Server Management Studio, the geog4326 column that should have been populated by my writer was empty. Blank. NULL. Zilch. Zip.

So, what had gone wrong? I went back and looked through the FME log – everything seemed sound. I tried searching the internet and found some old posts about whether Safe FME supported multiple spatial columns per feature (like this one), so wondered if this was a limitation of the software, but nothing concrete came up. So then I contacted Safe FME’s “Doctors” over twitter. They asked me to send them a simple repro script, so I ditched my original package and started again. I built up a simple script, this time just looking at a test table with 100 rows, and…. it worked.

Much head-scratching later and I found the reason – when you specify the Geometry Column Name or Geography Column Name in a Feature Reader, the entered column is CASE-SENSITIVE, and must match exactly the definition of that column in the database. Frustratingly, there is no drop-down list of columns from which to choose this value – it must be manually typed. What’s more, if you enter the correct column name but using the wrong case, no error will be thrown. The package will run quite happily, in fact, loading all the attribute values from the table, but not the spatial data from the geometry/geography column itself. So your transformation will still load ‘000s of rows from the reader, but not with the (generally rather crucial) spatial feature itself.

For some reason, in this particular case, I’d named the column from which I was reading data GEOM27700 rather than geom27700. This is why, in my earlier example, the geog4326 column was NULL – it’s not that my UPDATE query hadn’t executed – the column had in fact been updated, but with NULL values that had been carried right the way through the FME transformation, since the reader had never successfully retrieved any values from the geom27700 column (since it was actually called GEOM27700).

I found this slightly odd behaviour, since my SQL Server collation itself is not case-sensitive, and will quite happily accept either GEOM27700 or geom27700 in a SELECT statement. What’s also odd is that this same behaviour is not mirrored in the Safe FME MS SQL Writer. In the writer, case sensitivity of the spatial column name is still significant, but if you attempt to specify a column with the wrong case, an error is thrown straight away and you’ll be unable to run the transformation, rather than the “let you carry on but don’t read anything” approach of the reader.

So, a slightly frustrating experience, and 10hrs of processing spent updating a table with a load of NULLs, but never mind – fortunately I’m only still learning Safe FME and this was just an experiment, so no angry clients ringing up asking where their data is (this time)

And, to give credit where it’s due, having found the problem myself I then contacted the FME doctors again over twitter and they immediately replied, acknowledging the problem and informing that an update was in the works. I can think of a few software companies who could learn a thing or two about customer service like that….

by Alastair “Bing” Aitchison

The Replacement for the Bing Maps 3D Control

 

So at the end of this year, on December 1st 2011, the Bing Maps 3D control is being retired. This is rather a shame – I kinda liked being able to visualise geography data from SQL Server on a round earth rather than having to project it onto a flat 2d map. And no more will I be able to enjoy this beautiful vista from the top of Mount Snowdon from the comfort of my own chair:

image

Fortunately, there is an alternative.

Given the recent announcement that Silverlight 5 beta will be released at Mix11, you might think that I’ve got some insider information on a new Silverlight 3d Bing Maps control, but no.

And you can do some pretty cool stuff with HTML5 and CSS 3D transforms (like this Katamari Damacy hack), but that’s not it either.

I’m thinking of something much more modest, requiring only a single cross-platform plugin that almost every user already has installed on their system….

Flash? Nope…. Adobe Acrobat.

Using some STRM topography data, an image from the Bing Maps imagery service, and a splash of Safe FME, you can create fully zoomable, rotatable terrain models in PDF format. Now, ok, this doesn’t quite provide the ability to navigate the whole world in 3D – it works best for only certain limited areas of geographic interest. But look at the advantages: you can distribute interactive 3d spatial data to anyone with Adobe Acrobat reader installed – no other software required, no special licences needed, no internet connection….

Here’s the Safe FME workflow I used (inspired largely by one of Don Murray’s demos at the Safe FME world tour last week):

SafeFME_workflow

And here’s a screenshot from the Adobe 3D PDF output, showing the Bing Maps imagery for Snowdonia national park in Wales overlaid on a STRM height map model. Download the PDF file and try it yourself.

BingMaps3DTile

There are several navigation tools in Adobe PDF reader allowing you to pan, rotate, and zoom around the model. There’s even a ‘Fly’ navigation mode that allows you to re-enact the 3D control style of Ricky’ Brundritt’s VE3D flight simulator.

image

And now I can once again enjoy views like this on my monitor:

BingMaps3DTile2

(Note – there is a known bug with Adobe Reader 9.3 that might produce a “3D data parsing error has occurred” message – see http://forums.adobe.com/message/2651682 . This error can be resolved by upgrading to Adobe Reader X.)