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

Google Earth:Interactive Digital Atlas

To me, the most interesting thing about today’s release of Google Earth 6 isn’t any of the new features… but rather the announcement itself; specifically, the announcement’s very first sentence:

Today we’re introducing the latest version of Google Earth, our interactive digital atlas. [emphasis mine]

It’s interesting to me that Google labels Google Earth as an “atlas”—not because of the terminology (it certainly seems to fit the definition of an “atlas”)—but rather because Google seems to be labeling it as the company’s sole “atlas”. (Notice that they didn’t say “one of our” interactive digital “atlases”.) Apparently, Google Maps is not also an “atlas”.

This actually makes quite a bit of sense. For instance, I can’t ever recall seeing an “atlas” that didn’t denote national capitals. While Google Earth (aka Google’s “atlas”) certainly does, Google Maps doesn’t. Consider also that Google Earth shows many secondary political boundaries, like county borders, that are entirely missing from Google Maps. Indeed, in many ways, Google Earth actually is a better “atlas” than Google Maps.

In other ways, though, Google Earth is an inferior “atlas” to Google Maps. Case-in-point: Google Earth’s sparse and uneven city labels. Cities with populations of under 20,000, for instance, typically don’t appear until you’re in full view of their street grids. In my own experience, this makes navigating Google Earth quite difficult, and it’s incredibly easy to get lost while browsing the maps. (I’m not alone in this view.) [1] Previous comments about Bing’s sparse city labels could easily be applied Google Earth—doubly so, in fact. You simply have to zoom-in way too far before most smaller cities appear on the maps. [2] Besides that, most of the information on Google Maps’s “Terrain” maps (elevation contours, terrain coloring, etc.) seems to be entirely missing from Google Earth. And consider that Google Earth doesn’t even have Google Maps’s diversity of city “dots”: unlike on Google Maps, all of Google Earth’s cities (other than its capitals) have the exact same city “dots” and label sizes, precluding meaningful comparisons amongst cities.

All this aside, I don’t understand why Google Maps can’t also be an “atlas”. Why show information like country capitals and county labels on Google Earth, but not on Google Maps? I think there’s an incredibly easy (and elegant) solution to this, and I don’t understand why Google hasn’t implemented something similar to it. (They could even call it an “Atlas” tab.) [3]

_____________________________________________________________________________________

Notes

[1] For a direct comparison of the city label densities on Google Earth and Google Maps, click here.

[2] Given how “advanced” Google Earth is, I’m surprised users are unable to customize the maps’ city label densities.

[3] Consider also that Google Maps, as opposed to Google Earth, is generally more accessible to greater number of people. There’s nothing to install; all you need is a somewhat-modern browser.