Sometimes my blog posts are like a courtroom novel. The recent article on precision is an example of this. I reach a successful verdict, but getting there involves an opening statement, a journey through obscure aspects of theory, cross-examining our functionality, and making a final argument for various design decisions. Only after all that can I prove beyond doubt that our work is fit for purpose.

That sort of article is fun to write and it does illustrate Safe’s thought processes in designing FME functionality. But sometimes – like reading a courtroom novel – you want to skip to the final chapter. You just want the juicy part where our hero attorney has already got the result, and simply illuminates the solution in logical steps.

Well, today, I am that hero attorney. I am the Perry Mason of the FME world, and this article is the final chapter of “Updating Databases with FME”. No theory or cross examination involved. Step by step I’ll cast light on how FME made it easy to push mass updates to a database.

There is just one design issue I’ll touch on, and it is important. In law there are multiple jurisdictions, and a technique Perry Mason tries in a California court may not work for Atticus Finch in Alabama. Similarly, in our world of data there are multiple databases. But! FME techniques you use for Oracle will work for SQL Server, and FME techniques you use for Postgres will work for an Esri Geodatabase.

How can this happen? It’s because we’ve worked really hard to harmonize (or standardize) the interfaces inside FME. So whatever database applies in your jurisdiction – or even if your work involves several database types – this post covers you.

So let’s get on with it…

Setting up an FME Database Connection

When you want to read to or write from a database – including database updates – you need authorization. FME defines authorization parameters using a connection tool. It’s accessed through Tools > FME Options in FME Workbench. So start Workbench, select Tools > FME Options and you will get this dialog:

When I click on Database Connections, I get a list of available connections. If I want to create a new one I press the plus button and get this dialog:

Just enter your database details in there, test and then save. Now you have a connection defined, you can use it wherever you like in FME. So first let’s use it to insert some data into a database…

Inserting Data to a Database with FME

To carry out database updates, first you need data in the database! Let’s say you want to read a dataset – maybe an Excel spreadsheet – and write it to a database, creating a table at the same time. That’s simply done in FME by generating a new workspace and choosing a database format. The generate option exists on the start page of FME Workbench, or you can use the shortcut Ctrl+G. That opens the basic dialog for defining a translation:

Here I have filled in the fields to define a translation of data about parks in the city of Vancouver. The translation is from MapInfo TAB (a spatial data format) to PostGIS (a standard PostgreSQL database with a spatial extension). The spatial part is not necessary – the same setup works for data without a spatial component – but maps are cool so I’ll go with it.

The database connection I selected is the one I defined above, saving me the effort of re-entering my authentication details.

Click OK and that dialog creates a workspace that looks like this:

Each object on the left is a table, layer or class in your source data. Each object on the right is a table in your database.

Setting Database Parameters

When using databases the key settings for each table are accessed by clicking the cogwheel icon on those objects, like this:

The table name is the first parameter, and so I can rename the table to be something different; and I can choose which schema (Table Qualifier) to write to as well.

But the most important parameter (Feature Operation) tells us that we are INSERTing data, and I can also choose in what way the table is created:

So I can choose to create the table regardless of if it exists (Drop and Create), create it if it doesn’t already exist (Create if Needed), just add to the existing table (Use Existing), or empty it if it already exists (Truncate Existing).

Which I use depends on the scenario I am working through, but in this case – to create and fill a table – I’ll use Create if Needed. The advantage over Drop and Create is that if another user already has a table with that name (and I haven’t checked) then at least I won’t delete their content first.

Anyway, I run the workspace and FME loads the data:

Of course, at some point in the future I might find the source of the data (Parks.tab) has changed, and I need to update my data based on that changed dataset…

Updating Records in a Database with FME

Say I receive a dataset named ParksUpdates.tab. The simplest way to update my database is to do the same process as above, but to use Drop and Create as the table operation. That way I am just replacing everything:

But that relies on ParksUpdates.tab being the FULL replacement dataset. What if it only includes the records that need an update? Well in that scenario I simply change the operation to UPDATE (instead of INSERT) and choose to Use Existing table:

Notice that when I pick UPDATE, then another parameter becomes available to me: Match Columns. I need this to define which feature updates which record. In this case I have an attribute called parkid in my source data and a field (column) named parkid in my database table; so that’s the attribute I select:

So if an incoming feature has the attribute parkid=13, then its contents are used to update the database record where parkid=13.

That’s simple enough, but to add a little complexity (not too much) there is also a WHERE clause I can use instead. This lets me define the match where the attribute and field names are not the same (for example ParkNumber and parkid) but also allows me to add extra conditions using field names:

Here, for example, I’m updating records where ParkNumber = parkid, but also only where the neighborhoodname field is “Downtown”. So records outside of the Downtown area aren’t updated, even if the park ID matches. I could do a similar test for a status field (active, inactive) among many other examples.

So we do updates here, and that’s simple enough; but sometimes we also want to delete records…

Deleting Records from a Database with FME

Let’s assume my UpdatedParks dataset is a list of records to delete from the database table, not add. To do that I simply change the operation from UPDATE to DELETE:

I get the same Match Column parameter (or WHERE clause) to define which incoming features should delete which existing records, and this is again easy to define.

So deletes are no more complex than updates; the key question is what happens when I want to both delete and update records simultaneously?

Updating AND Deleting Database Records with FME

Let’s say some of the incoming records are updates, while others are deletions:

Obviously I can’t set the operation parameter to both DELETE and UPDATE for the entire table. What I do instead is tag each feature with the operation it will carry out. I do this using an attribute called fme_db_operation:

You can see here that I have added an attribute to each stream of data, using an AttributeCreator transformer. The attribute name is fme_db_operation. For one lot of data I set the value to UPDATE. The other set of data has a value of DELETE. This is how I tag each feature with its own operation.

I still have to set the operation type on the table itself. But this time, rather than choosing Insert, Update, or Delete, I choose the option labelled fme_db_operation:

Now when I run the workspace, the features tagged UPDATE update database records, while the features tagged DELETE delete database records. The Match Column (or WHERE clause) provides a match between features and records.

The one assumption is that we already know which feature are deletes and which are updates. In the above example, the source data is already divided into two. If we aren’t sure of that then we might need to do what is called Change Detection…

Change Detection and Database Updates with FME

Change Detection is where we have a new dataset and want to compare it to existing records to find what has changed. Here is such a workspace:

It looks quite simple, and in fact it is. I have added a reader (Readers > Add Reader) to read the existing contents of my database table, and an UpdateDetector transformer to compare these records to the UpdatedParks dataset, to identify where changes have occurred. I can detect changes either on field values, or spatial contents, or both.

Then it’s just a case of writing the results back to the database table. I don’t even need to create the fme_db_operation attribute; the UpdateDetector has done that for me. I must just check that the table is set with the correct operation (fme_db_operation) and that Match Column is set.

At this point you probably know more than enough to carry out database updates; but there is one more scenario I can perhaps mention. What if each feature has a different match column? In that case you can write your match in the form of a where clause, and store it as an attribute. Then use that attribute for the match in the table parameters:

…just like that!

NOTE: In FME2019 the ChangeDetector has undergone various improvements and should be your go-to transformer instead of the UpdateDetector.

Wrap Up

I hope that was a good explanation for database updates, short on theory and long on practical examples. Speaking of which, we have an online tutorial on database updates, where you can carry out exercises, step by step, using similar examples to what I showed today. So if you want to try some of these techniques in a safe practice environment, click the above link to visit the tutorial.

As I mentioned, although I used Postgres here, most of our database formats use an identical interface because we’ve invested a lot of time standardizing them all. That work is still ongoing, so one of the tutorial articles includes a list of standardized formats, and what to do if your format is not yet updated.

And now, if you’ll excuse me, I’m going to do like Perry Mason and wrap up my case by going out to celebrate with steak and cocktails!

PS: Safe Software now has an Instagram page. I’m not sure what an instagram is, but here’s a picture of me presenting at a prior user conference (oh, and riding an inflatable horse):

No better time than the present to migrate your geographic data to a replacement Maps Engine solution. But where to go? What technology can give you everything GME did?

• A place to store your geographic data in a web-based environment.
• Powerful visuals for inspecting and analyzing your data.
• Easy collaboration on maps.
• Real-time capabilities like dynamic rendering, alerts and notifications.

Let’s look at a few of the alternatives we’re aware of, and how you can migrate your data there without losing anything important. Please add your own suggestions in the comments.

You might find that one solution suits your requirements better than the others. I’d suggest testing out as many alternatives as you can before making your own decision about which is best.

Check out my follow-up post, How to Migrate your Google Maps Engine Data, to see how to get your data into whatever system you choose.

1. ArcGIS Online

If you want everything you had in Maps Engine, then ArcGIS Online is probably your solution of choice. It’s made for creating interactive web maps and apps, and comes complete with all the analytical power and authoritative data you’d expect from an Esri product. Like GME, AGOL renders maps dynamically, which means it offers better visualization for data that’s constantly being updated.

The ‘apps’ component is interesting: you can turn any of your feature services into apps, adding a new level of power to your data. AGOL also has no quotas or rate limits, offering more to organizations with high data volumes. They also have ArcGIS Open Data, which, if you’ve been using Google Map Gallery with Maps Engine, is an equivalent public dataset collection and distribution platform.

Getting your data out of Google Maps Engine and into ArcGIS Online is a one-step process using FME. Note you don’t need to have ArcGIS Desktop in order to use ArcGIS Online.

2. CartoDB

CartoDB is also a full solution that would work nicely as a GME replacement. In fact, they’ve just published a blog comparing the two and describing how easy it is to move your data from one to the other.

In CartoDB, the web-based environment and spatially aware database components are handled by PostGIS in the cloud, while the visualization, collaboration, and analysis aspects are presented simply and beautifully in a web interface.

I need to stress the ease-of-use here. Last week I needed CartoDB for a demo video, and was apprehensive about needing to learn how to use it first. I created an account, logged in, and—that was it. I already knew how to use it. The simplicity of importing data, creating a map, and choosing how to represent my data was immediately obvious. Kudos, CartoDB.

CartoDB also offers dynamic rendering and geo-temporal visualizations, giving you real-time maps and the ability to see data as it happens.

Again, you can extract your data from Google Maps Engine and load it into CartoDB in one step using FME.

3. iSpatial

iSpatial is a complete map creation, visualization, storage, collaboration, and analysis solution. From their website: “iSpatial is a web-based collaborative framework that leverages Google Earth and Maps in a flexible, task-based approach to solving complex problems.”

In addition to location-based inspection and analysis, it’s set up for real-time reporting and alerts management.

This technology is based on Google Earth/Maps, so if it’s the visual Google experience you’re after, this could be your solution. It also leverages Postgres/PostGIS in the back end.

And, yep, FME can extract your data from Google Maps Engine and put it into PostGIS in one step too.

4. A combination of Google products

Google, of course, suggests replacing GME with a combination of their own solutions. Google Maps/Earth is obviously great for visualizing and creating maps, but what about the rest of what Maps Engine offered?

My Maps is basically the map creation, visualization, and collaboration aspect of GME (up until a few months ago, it was actually called Google Maps Engine Lite). Storage, then, can be handled by migrating your data to Google Cloud SQL—a MySQL database in the cloud. Great, so now we have the web-based environment and database storage aspects.

Next, to bridge the two. Once you get your data from Google Maps Engine into the cloud-hosted MySQL database, you’ll have to follow their guide to build a spatial app using the Google Maps API. They’ve also provided an example vector data app and self-serve raster app for reference. Building stylized maps then involves a bit of JavaScript programming. Not an impossible migration, but not as straightforward as some of the solutions above, either.

If this is your solution, then guess what? You can extract your data from Google Maps Engine and put it into Google Cloud SQL in one step using FME. Google even published a page describing how.

5. Mapbox

Mapbox is a great way to publish your geographic data. It’s scalable, cheap, and open source. This, however, is more of a “partial” GME replacement, and will need to be combined with other technology if you want to have everything you knew and loved about Maps Engine. For instance, it lacks some of the powerful data management and analysis tools found in the solutions above.

Storage of your map tiles is handled by a SQLite database (which FME also migrates to). On the front end, Mapbox uses TileMill for creating interactive maps—a design environment that leverages the stunning cartographic abilities of Mapnik. Creating web map tiles with FME is a common scenario, and MBTiles is on our radar for an upcoming release (potentially FME 2016).

6. Your own custom integration

In addition to those above, FME supports writing to many other cloud spatial data storage systems. If you’re not afraid of writing a bit of JavaScript, you can pick your favourite storage system (e.g. Amazon RDS or Aurora), your favourite map creation library/framework, and bridge the two using JavaScript.

Regardless of which technology you choose, rest assured you can easily migrate your data without losing anything. Google provided this page to walk you through exporting your stuff, where FME is one of the recommended migration strategies.

Extracting your data from Maps Engine with FME is as simple as dropping down a reader and entering your credentials. From there, you can move it to any of 335+ alternate systems, including those listed above. FME also offers hundreds of data transformation and quality control options if you need to manipulate the content or structure before loading it into another system.

As always, don’t hesitate to contact our most excellent support team if you need help getting your data wherever it needs to go.

If you’ve been using Google Maps Engine for your geographical data, please share: What alternative solution do you plan on using? Can you suggest any others?

See also: How to Migrate your Google Maps Engine Data

“Open source tools play well with others.” Paul Ramsey, PostGIS founder, talks Open Source at the FME International User Conference 2014.

As a software developer, coding something that’s already been designed, implemented, and optimized is a pretty gigantic waste of time.

Open source libraries help developers avoid re-inventing wheels. At this year’s FME UC, PostGIS founder Paul Ramsey took the stage to talk about the awesomeness that is Open Source. He explained how it’s important that developers use available knowledge—and FME devs understand this, adding value by building useful functionality on top of open source libraries.h

Safe Software proudly supports the Free and Open Source Software (FOSS) community and regularly sponsors the North American FOSS4G conferences. FME uses many FOSS libraries, and when the devs here at Safe aren’t playing soccer or LEGO or a game of oversized Jenga, they’re working hard to make these libraries more accessible.

Our use of these libraries is a friendly two-way street. A two-way Yellow Brick Road, if you will. FOSS adds value to FME’s advanced data transformation and automation capabilities, while our contributions to these technologies have included funding, coding, testing, and bug fixes. Here are ten such libraries.

10 FOSS Libraries that FME Uses and Contributes to

This Mario-like map is made possible by the integration of Mapnik and FME.

1. Mapnik

Mapnik is a toolkit that renders raster and vector data into beautiful cartographic maps. FME’s Mapnik transformer lets users leverage Mapnik’s capabilities with any of the 325+ data formats FME supports. With Mapnik and FME, you can also create Web Map Tiling Services, make a map out of your face, map yer way to buried treasure arr matey!, and other such highly critical tasks. FME’s intuitive graphical interface makes it easy for any user to benefit from this complex library.

2. SpatiaLite

SpatiaLite extends SQLite into a simple Spatial DBMS. FME’s SpatiaLite Reader / Writer allows users to connect this format with hundreds of other formats, and leverage all of FME’s data transformation and automation capabilities. For example, you can instantly import SpatiaLite data into Google Maps Engine using our free Data Loader for Google Maps Engine.

FME reads and writes a variety of open source databases.

3. PostGIS

PostGIS is a spatial database that adds support for geographic objects and location queries to PostgreSQL. FME’s PostGIS Reader / Writer lets users integrate hundreds of data sources with this format. Watch Paul Ramsey’s FME UC talk to see the evolution of PostGIS, from the ideas that inspired it to its widespread use today.

4. JTS and GEOS

JTS and GEOS (Geometry Engine – Open Source) provide spatial predicates and functions for processing 2D geometry. JTS is the Java library and GEOS is the C++ library. Some of FME’s powerful geometry transformations are based on GEOS/JTS algorithms.

FME OSGeo. Safe Software is at FOSS4G this year.

5. OGR and GDAL (Geospatial Data Abstraction Library)

These OSGeo libraries support reading/writing over 50 raster formats (GDAL) and 20 vector formats (OGR). FME uses these libraries to add several readers and writers to its list of supported formats, which means these formats gain a wide range of enterprise transformation and automation capabilities.

Bonus: if you use a GDAL-based format in FME, you can feel comfortable knowing it was probably implemented by our in-house world Tetris champion.

6. FDO

FDO works with a variety of geospatial data. This OSGeo API brings additional formats to FME, for example SQLite. Safe was one of the earliest adopters of this API (version 3.0) and is acknowledged as an official Product Using FDO.

7. Ingres

Ingres is an RDBMS that supports large commercial and government applications. Safe’s partnership with Actian resulted in an open-source FME reader/writer for Ingres tables. This means increased data accessibility for Ingres users, making it easy to integrate spatial and enterprise data.

FME Workbench and the FME Data Inspector are available on Linux.

8. Qt

Qt is an application framework that we’ve used to help make FME fully cross-platform. Since FME 2013, FME has been available in beta on Mac and Linux platforms.

9. Interlis

Interlis is a geodata modeling and integration language adopted by the Swiss government as their national standard. Eisenhut Informatik is one of Safe’s longest standing open source partners, and their ili2fme plug-in allows Interlis data to be translated to/from any of the formats supported by FME.

10. LASzip

LASzip is a specialized library by Martin Isenburg that compresses LiDAR data without information loss—turning chubby LAS point clouds into lean, manageable LAZ files. The incorporation of this technology into FME has resulted a great improvement in the ability to archive and exchange point cloud data.

But Wait, There’s More

For the full list of libraries licensed under the LGPL, visit our FOSS page. You can also view more information about FME’s open source libraries by opening FME Desktop and selecting About > Legal Notices.

FME for Free

If you want to use FME for non-commercial purposes, you qualify for a free FME Home Use License.

We also offer a couple of free tools: CityGML Importer for InfraWorks, FME Revit Exporter Plug-in, and Easy Translator (a web service for immediately translating data into your required format and coordinate system).

How have you integrated your software or project with Free and Open Source Software? Are you attending FOSS4G this year, or have you in the past? Let us know in the comments!

This aesthetically pleasing raster image was created by combining a variety of data sources using FME and Mapnik.

Adding a Mapnik transformer to FME is like adding a top hat to Abraham Lincoln: it brings a whole new level of style and class. While FME can already take plain vector data and render it as a raster, Mapnik brings a new level of elegance and quality offered by subpixel anti-aliasing. It also offers a cleaner replacement to many FME transformers that do regular image processing. Rasters that once needed dozens of transformers to render now need only a handful. Mapnik is certainly a top hat on FME’s rasterizing capabilities—so what does FME bring to Mapnik? We offer 5 improvements below. Be sure to join our webinar on May 7, 2014 at 8am PDT to see Mapnik examples that will change the way you think about cartography!

1. Read and write more data formats than Mapnik currently supports

Create more meaningful Mapnik cartography with the ability to integrate hundreds of data formats.

When you work with spatial data, being limited to a handful of formats is like having a cartographer sit on you and stuff your arms in a straitjacket. (Constricting, yes, but also painful and awkward.) At the time of writing, Mapnik can read Esri Shapefiles, PostGIS, TIFF raster, OSM XML, Kismet, and OGR/GDAL formats. FME broadens that format reach to include 325+ vector, raster, 3D, tabular, database, web, XML, cloud (and more) formats.  This means it’s easier than ever to work with Mapnik from a variety of systems and applications. FME’s data integration capabilities offer precise control over the source and destination data, including over coordinate systems and reprojection.

2. Automate Mapnik workflows

This graphical workflow was created using FME Desktop and reads from 5 disparate sources. The workflow can easily be scaled and automated.

When you design a workflow involving Mapnik, you can use any of FME’s 400+ transformers to validate the data, optimize data before processing it, or restructure it to meet requirements. From there it’s easy to turn all your Mapnik rendering tasks into standard, repeatable actions. You can perform the same data transformations on many datasets, or on a single dataset that constantly changes. This is really good news, because the data workflow you spent so long creating shouldn’t have to disappear forever. This isn’t an ice sculpture contest. (By the way, Mapnik currently runs on Windows, Mac, and Linux, and so does FME.)

3. Wile those tiles, OSM style! (i.e., set up a Mapnik WMTS/WMS with FME Server)

FME makes it simple to set up a Mapnik web map tile service.

With FME you can take advantage of scalable automation by creating a Mapnik workflow and uploading it to FME Server or FME Cloud. Whether your server needs are big or small, it’s easy as that to make your data available and real-time. You can create a web service that serves map tiles in a standard way; for instance, you could serve OpenStreetMap (OSM) data as a backdrop, stylized in a way that meets your needs.

4. Easy Mapnik styling – goodbye Python, hello GUI

FME’s friendly interface makes it simple to style Mapnik output without code.

Mapnik offers precise control of symbology and styling, including fill color, dash pattern, fonts, etc. FME makes it intuitive to control it all. Back in 2011, FME users could leverage Mapnik with Python. Today, the MapnikRasterizer makes this much simpler with a friendly GUI. Thank God, because I didn’t want to touch that Python script with a 39½ foot pole. The MapnikRasterizer transformer doesn’t require any CSS or XML skills: it simply accepts parameters in a point-and-click interface like the rest of the FME transformers.

5. Win stuff for sharing your Mapnik art

Keeping true to the world of Open Data, we think Mapnik projects are worth sharing. Check out our contest.

Ok, Picasso. We’re giving out prizes for your Mapnik art! What can you create using FME and Mapnik? Whatever it is, I hope it’s less creepy than this. Together, Mapnik and FME help create beautiful and meaningful cartography worth sharing. If you think you’re the ultimate Mapnik Master, enter our Mapnik contest. (Deadline: June 30, 2014)     I said adding Mapnik to FME is like adding a top hat to Abraham Lincoln – but maybe adding FME to Mapnik is a shift into Abraham Lincoln: Vampire Hunter. FME brings hardcore data transformation and automation capabilities to your Mapnik projects. With FME Desktop, FME Server, and FME Cloud at your fingertips, the potential to create amazing cartography is endless. Thanks to the Mapnik team for making such great cartography possible in FME. What will you do with Mapnik? Be sure to share your creations with us in our contest, and tune in to next week’s webinar to learn more. For technical resources on the MapnikRasterizer, check out the MapnikRasterizer FME transformer documentation, and these Mapnik tutorials on FMEpedia.

Hello FME’ers,
By now you’ll have (I hope) upgraded to 2014 and taken advantage of some of the new features and functionality. From my point of view updates can classified in one of three areas and, having already covered Workbench improvements and Transformer updates, it’s time for me to look at the third of these: Readers and Writers.

As usual we’ve added a whole bunch of new formats – twenty-one (21) by my count – and these seem to reflect some modern trends in spatial data. There are many cloud formats and some important updates to 3D and BIM data formats too. But there are also some updates to older CAD and GIS formats, filling in the gaps I suppose you could say.

Anyway, I realize that not everyone is going to be interested in every format, so for this top-ten I’ve tried to pick out updates that I think will be of most interest to the most people, and made the points fairly brief so it’s easier to skip items that don’t interest you. I haven’t mentioned null support because we included it in so many formats – check out the list here if you want to know which.

AIXM 5

I wouldn’t blame you if you’ve never used – or even heard of – the Aeronautical Information Exchange Model. But if you ever journey by air then you’ll be grateful for this format because it’s the means by which navigational and other airspace information is passed between systems. For an interesting read on the subject check out Wikipedia first, then a site like the UK National Aeronautical Information Service.

For FME, the big update is that we now have a Writer for Version 5 (actually v5.1) of this format. Version 5 is an important update so it was vital FME supports it. Technically, it being an XML-based format, you could have written data with the XMLTemplater transformer, but a dedicated writer like this is way better.

JPEG

Did you know that FME doesn’t just read JPEG files, but also the Exif information that comes attached?

Exif (Exchangeable image file format) is metadata for a JPEG file, and includes location data from geotagged files. FME has been able to read these tags for a while; there’s to be a JpegGPSPointReplacer transformer in the FME Store and Certified Professional Ulf Mansson blogged about how FME could read these Exif tags.

So Ulf will probably be happy to know that in FME 2014 we’ve expanded the JPEG Reader to add support for some additional tags:

• jpeg_exif_contrast
• jpeg_exif_exposureprogram
• jpeg_exif_focallengthin35mmfilm
• jpeg_exif_lightsource
• jpeg_exif_saturation
• jpeg_exif_sharpness
• jpeg_exif_software

You can get at all these attributes in the Format Attributes tab in a feature type properties dialog.

Trimble SketchUp Reader and Writer

In case you didn’t know, SketchUp was acquired by Trimble in 2012, and shortly after an excellent new API was released. FME has had support for the Google version of SketchUp for a while, but 2014 introduces a whole new Reader and Writer that is based around the new Trimble API.

The new Reader/Writer provides support for versions back to Google v3 and up to the latest Trimble 2013 release. Besides this there are some useful improvements over the older R/W:

• Coordinate System and Geo-location support. Data will be read in its true location, so with the FME Data Inspector you can see a background map (in 2D).
• Support for Non-Affine Textures (those with a perspective correction)
• Textures on a group feature

Esri Geodatabase

The Geodatabase update I wanted to highlight is support for GUID column types. GUID stands for Globally Unique Identifiers, which are 36 character fields that uniquely identify features or objects in a Geodatabase. FME used to read them as string attributes but now we handle them as proper GUID fields and have an actual field type that can be selected in the Feature Type properties dialog:

Besides this update we’ve also worked on improving the handling of Chinese attribute names, updated the API (non-ArcObjects) to use v1.3 of the API, and added Raster Catalog support.

Google Earth KML

A new update to the KML writer in FME 2014 was in response to a number of customer requests. The issue was this: when you view a KML dataset, the attribute information shows up inside a balloon. That balloon content can be displayed using HTML content, but there was no way in FME to include auxiliary files such as images or scripts.

So, the update in FME 2014 was to provide a way to do this. The magic is carried out in a section of the KMLPropertySetter transformer’s parameters:

Now when I run this workspace, and write to KMZ, all of those files will be included with the HTML content.

MicroStation Design

Two nice updates here, filling in a couple of gaps. Firstly we added the ability to write to multiple models. A MicroStation seed file can contain multiple models – basically the ability to apply different working units, global origins, etc. FME has long been able to read which model the data was coming from, now we can write it to a specific model to. You simply need to set either igds_model_id or igds_model_name on the features to be written. The model must already exist in the seed file too, as we can’t create it from scratch.

The second update is the ability to set draw order, aka “element priority”. MicroStation doesn’t have a method for setting the order in which layers are drawn one above the other. Instead each element (feature) is give its own priority. FME now has the ability to read and write this priority using the format attribute igds_element_priority. This will work for 2D datasets, or 3D datasets where the features have common Z values.

So, good news if you are a MicroStation user needing this functionality.

AutoCAD

For AutoCAD, FME 2014 introduces the ability to read and write raster features with our AutoCAD formats. I didn’t realize that raster data could be added to an AutoCAD drawing, but apparently it can – the DWG file simply includes a reference to the raster file.

When FME reads the raster data it includes a set of attributes that define any clip boundaries that were set in AutoCAD, so you can clip to the same extents in FME. But if you send the data back to AutoCAD then you don’t need to  manually clip the data, it’s automatically done on writing. This way you can round-trip your AutoCAD raster data without any loss of information. Of course, you can just write raster features directly to AutoCAD too.

Another AutoCAD update is the ability to read from one of multiple layouts in the paper space. I’m not an AutoCAD expert but to me this looks similar to the DGN models. Basically a paper space can contain multiple layouts, each with different page setup/layout settings. So FME now gives the user the option to read in only the active layout or all layouts.

Finally I have to mention the Revit Reader that is new to FME 2014. To watch a recorded demo click this link and skip to 56 minutes into the presentation. Basically we’ve created a Revit extension (add-in) that let’s you export data in a file format that FME is able to read using a new Revit Reader. The Reader includes a parameter called Element Type, which allows you to select the type of data to be read (2D Floorplan, 3D with Textures, Building Envelope, etc). That way the data is partly pre-processed, and easier to use than a simple dump of all elements.

PostGIS

The simple update here is that the Postgres-based Readers and Writers now include parameters for a SQL Statement to Execute Before Translation, and a SQL Statement to Execute After Translation. Other database formats have had this capability for a while, so it was high time that PostGres users got the same opportunity.

The other PostGIS item I wanted to mention is the ability to use an Amazon RDS Hosted PostGIS database. Amazon RDS (Relational Database Service) is a web service that allows you to set up a database in the cloud. Their latest offering is the ability to run a cloud-based Postgres/PostGIS database and, having tested it, we’re happy to report that FME 2014 works just fine Reading and Writing from such a database. And yes, that includes raster. You can read a bit more about FME support for this on Don’s blog post.

Amazon

Speaking of Amazon, there are two new Amazon-related formats in FME 2014: Amazon Redshift and Amazon DynamoDB. Both are databases offered by Amazon as a cloud service.

Redshift is for massive (petabyte-scale) datasets. Basically it’s fast and it’s large and it’s supported by FME!

DynamoDB is a NoSQL (non-relational) database service. It’s schema-less, really fast, and – again – it’s supported by FME!

Google

As for Google, we added three new formats in FME 2014, two for cloud services and one for a raster file format.

The raster file format is Google WebP. Google says this format has about 25% smaller file sizes than jpeg and is therefore useful in web applications. FME has both a Reader and Writer for this format.

Google Cloud SQL is (obviously) a cloud-based format. We have a Reader and Writer for both spatial and non-spatial versions of this online database.

We also have a new Reader and Writer for Google Maps Engine, so you can read data from your FME-supported format and push it out to your GME users. We also have a new “GME Compatibility” parameter on the KML writer, which you should set if you’re creating KML for loading into GME.

By my count we now have eight (8) Readers and Writers for various Google formats and products:

You might have noticed I didn’t include GML in my top-ten. That’s because the changes there are so significant, and their effects so wide-reaching, I’m going to create a single post all about that format. I also missed out IFC because there are some interesting updates still to come that I want to wait for.

Anyway, for a full list of new formats – and updates to formats – be sure to visit the What’s Great page here on this blog. And while I remember, the FME 2014 launch quiz is still on (until February 28th) so answer now and answer often (once a day) to win some great prizes.

Regards,

Mark

I crossed my fingers as I read the announcement, hoping that PostGIS was part of this. “No Way!” I thought after reading that PostGIS was indeed! I knew I had to try it out.

Image via Techcrunch article: http://fme.ly/techcrunch

Running PostGIS on RDS

I fired up an RDS instance of PostgreSQL to see for myself. After connecting using the standard PostgreSQL admin tool, pgAdmin, and executing the statement CREATE EXTENSION postgis; I now had a scalable spatial database in the cloud at my fingertips.  With this there are now two spatial data solutions within RDS: SQL Server and PostGIS!  Spatial data in the cloud just keeps getting easier.

I used FME to read and write data to this PostGIS database as it would any other PostGIS. Both Raster and Vector worked flawlessly. This whole process, from creating the database to using it, took about 10 minutes. Wow!

FME and PostGIS in the Cloud

The implications of this are huge, especially when I think about our new FME Cloud iPaaS service. FME Cloud currently has a PostGIS database as part of its installation. Now with AWS RDS supporting PostGIS, it is simple for users to use this cloud-based PostGIS database instead.

Doing this is a game changer.

With PostGIS now supported by RDS, anyone can easily deploy a class-leading fault-tolerant database that spans multiple data centers (or AZ in Amazon speak) in minutes.  These databases can also be scaled up or down to meet changing requirements. And of course all of the management and backup issues are handled by Amazon. No hardware to buy or manage.

Best thing is that all FME users, from FME Desktop to FME Server to FME Cloud, can take advantage of this today!

Hi FME’ers,
In a previous Evangelist post (from 2012) I talked about nulls in data and how FME handled them. In 2014 we have exciting new functionality that deals with null values in a proper way.

Because we’ve changed how FME behaves, if you think your source data may contain nulls then it’s important you read this article.

You were nothing to me once, and I was contented; you are now nothing to me again. But how different the second nothing is from the first! – Thomas Hardy in Far From the Madding Crowd critiques our new functionality!

Nulls in General
First let’s recap what we mean by null. In general there are three types of ‘nothing’ values:

• The attribute exists and is an empty value (Empty)
• The attribute exists and is NULL (NULL)
• The attribute doesn’t exist (Non-existent)

I say ‘three’ types, but really there are many more. A numeric attribute with a value of zero could be taken to mean nothing. Also there are NaN (Not-A-Number) values, nils in XML, Nodata values in raster, and many others (did you know Excel has its own special version of ‘nothing’?) – in fact I’m told by our developers that they found fifteen (15) types of nothing; but here we’re looking at the three primary types of nothing and why null is special.

Yes, fifteen types of nothing! When you get all of them in a single dataset it causes a vortex in space-time! True.

But for most people nulls will occur in a database. A true null means a field has been deliberately set to ‘nothing’. It’s not the same as an empty value at all. The closest non-IT analogy I can think of is when you fill in your name on a questionnaire. You could fill in your middle name (in which case it has a value) or you could write “n/a” or cross the field out. That would be equivalent to an empty field; it’s a positive value and we know you have no middle name. However, if you left that field blank, that would be a null; we can’t tell whether or not you have a middle name. It’s a value that signifies “unknown”, sort of a placeholder for real data.

Nulls and FME
Up until now FME did not have explicit support for nulls; we supported empty values and non-existent attributes (plus other forms of nothing, like NaN) but null wasn’t a concept that our internal data model understood. If an attribute was null then it was treated as if it didn’t exist. However, in 2014 we’ve completely overhauled that data model to incorporate null value support. So FME can now read null values, write null values, and carry out transformations on them too.

In terms of databases, it means you can now much more easily identify values that are null and write nulls to new rows, plus change existing rows to be null (which you couldn’t do before).

But, like people, formats come in all shapes and sizes and no two are the same. Interestingly, some formats will support all three of our primary nothing types; some will support just one or two. For example, Shape supports empty values, but not nulls, whereas GeoMedia supports nulls, but doesn’t recognize empty values. Databases generally store both null and empty values, and let you query them too. JSON nicely supports the concept of all three, whereas VPF has at least five types of nothing, maybe six depending on the field type (getting close to a space-time ripple there)! So even with null support, the behaviour of FME’s Readers and Writers will vary according to format, and you have to be aware of that.

Reading and Writing Nulls
If your source data includes null values the FME Reader will now emit null values. You don’t need to update or replace the Reader in your workspace, it will happen automatically. When you write the data out then the Writer will write null values too. If the format you are writing doesn’t support nulls, then the data will be handled appropriately – replaced with zero, or an empty value, or whatever that format requires.

So a translation involving nulls should just work. You don’t need to do anything special to your workspace, even if it’s an older one.

Which formats does this apply to? I’ve created a list at the bottom of the article for you to check, and you can find that list on FMEpedia too. We cover all the major databases, but if there’s a format you think we’ve missed then please do let us know.

Data Inspection
So now nulls have been incorporated into the FME Data model, we need a way to inspect the data, and a way to distinguish true nulls from other forms of “nothing”.

When you use the FME Data Inspector to examine your data before translation (you are inspecting your data before translation, aren’t you?) you’ll see null values displayed in the table view:

…and in the Feature Information window:

When an attribute exists, but is empty, the Table View window will show it as an empty field (i.e. the cell in the table is blank) but when the attribute is missing completely (i.e. just doesn’t exist for that feature) then it flags it as such:

Here TaxCoord is part of the source schema, but these features don’t have it at all. So now you can totally identify which attributes are null, which are empty, and which are missing.

Nulls and Transformers
OK, so if we’re reading this data, and have an internal representation, then we also need a way for you to handle it in Workbench. Therefore we’ve updated several transformers to specifically handle nulls, updated many others to simply cope with null data, and added a new transformer to carry out new null functionality.

The list of transformers with null support is, like the formats, listed as an appendix below.

To give you some examples, though, let’s first look at the AttributeCreator.

Notice how you can set (or create) a null attribute using the new entry on the drop-down list, or by typing “<null>” into that field. You’ll find you can also do this with other attribute-setting transformers, such as the AttributeValueMapper:

The Tester transformer doesn’t look much different to before, except for rewording the operators to be more precise, but now when you choose “Attribute is Null” it really is checking for a true null value:

Other examples would be the ChangeDetector and the Matcher. The ChangeDetector and Matcher will now truly compare nulls so that “null” is not the same as “missing” as it was before. The change in the parameters here is just the option to turn this behaviour on or off – that way your workspace can be made to run the way it always has (and in fact – for backwards compatibility reasons – it will default to doing so):

Like I mentioned, other transformers have been updated to better cope with nulls, even if their parameters and outward appearance haven’t changed. For example, the StringPadder won’t attempt to pad a null string, because that would change it to no longer being null. It will still let you pad empty strings, because that won’t affect the attribute’s status.

And then we’ve added a new transformer to handle nulls, called the NullAttributeMapper.

The NullAttributeMapper is a replacement for the NullAttributeReplacer transformer. Basically the function here is to map attribute values to or from null, depending on the original attribute value:

For example here, if my ParkId attribute is missing, or is empty, or has a value of 9999, then I will replace it with null. I’d probably do this when I’m reading from a format that doesn’t support nulls, and writing to one that does. That way I get true null values in my output dataset. Similarly I could map null values to an empty field or a specific value:

I probably wouldn’t need to map to an empty field just for a Writer, because if the format doesn’t support nulls, FME will automatically convert null values to something appropriate for the format. But I might do that if I deliberately wanted to give them a new value or make them empty to match other data.

The other great things about this transformer are that it maps multiple attributes, and that it’s not just limited to nulls; so you could even use it instead of the AttributeValueMapper for some scenarios.

Benefits and Consequences
I think the benefits of this are fairly obvious, so I won’t say anything except that to mention we’ve also updated our APIs to include this functionality too, to ensure developers get the benefits as much as regular users.

However, you do need to be prepared for the effect of reading nulls as “real” attribute values, rather than as a missing attribute.

Many transformers will ignore missing attributes, and you may have come to rely upon that behaviour. But that data might be read as a null now, and that’s going to be treated differently. For example, the ListElementCounter won’t include attributes that are missing. It will, however, include nulls – because these are real values. The two transformers that I think need most careful scrutiny are the Tester and the FeatureMerger.

With the Tester you might check for an attribute that is empty (attr=””) or that “doesn’t exist”. In FME 2014 that attribute might now be read as null instead; in that case the test would fail, where it passed before. Basically, the functionality is better in 2014, it’s just that you’ve come to rely on the older (less good) behaviour. In this scenario you could change the test to be “Attribute is Null” instead. Otherwise, the new NullAttributeMapper transformer can be inserted to change nulls to missing and so revert the behaviour.

For the FeatureMerger, think of what happens when you merge attributes from one feature to another, particularly when a “requestor” attribute has the same name as a “supplier” attribute. The requestor attribute will be overwritten, except for where the supplier attribute is missing. But now, if your missing records are represented as nulls, that requestor would get overwritten as a null. Again it’s because null is a “real value”, and again the NullAttributeMapper can be used to workaround this.

Yes, cases like this are going to be fairly rare and obscure, but it’s worth being aware of them. We’d like you to embrace the new behaviour, but if you want to revert then use the NullAttributeMapper. Basically if you put a NullAttributeMapper into your workspace, directly after your Readers, and map “Null” to “Missing” then the behaviour in the rest of the workspace would be the same as before (although it wouldn’t be as good!)

For the latest info on null behaviour (and the formats/transformers supported) see our null support page on FMEpedia.

Conclusion
I think this update is going to be oh so useful to many of our users. Our developers have done a super job implementing this big change (any time you update the core data model it’s a big change) and then incorporating it into all these different Readers, Writers, and transformers.

If you notice any parts that could be improved (either functionality or user experience) then please do let us know about your experiences (good or bad).

Appendix 1: List of null-complete formats:

• FFS (obviously)
• ArcSDE formats
• Autodesk SDF
• DB2
• Geodatabase API
• Geodatabase ArcObjects and family
• GeoMedia
• GML
• Informix
• Ingres
• MS Access and Excel
• MSSQL Server (Windows Azure SQL Database)
• MySQL (MariaDB) (Google Cloud SQL)
• Netezza
• ODBC2
• Odata
• Oracle and family
• PostGIS and family (inc Redshift)
• Teradata (JDBC and TPT formats)
• TFS
• VPF

Appendix 2: List of null-support transformers (ones with exposed null parameters or functionality):

• AttributeClassifier
• AttributeCreator
• AttributeFilter
• AttributeRenamer
• AttributeValueMapper
• ChangeDetector
• Matcher
• NullAttributeMapper
• Tester

NB: Do be aware that many other transformers will now handle nulls without parameters; for example the Logger will log null values, the InlineQuerier can handle null values in its database, the ListHistogrammer will count the number of nulls, and so on.

Hi FME’ers,
Since FME2014 beta versions are now available on our download page, I thought I’d cover – in brief – a couple of the obvious changes.

Mac and Linux
OK, this is obviously the big news and you probably know this already: FME Workbench is now available on Linux and Mac operating systems.

It wouldn’t be the first time I’ve heard a user say they only use Windows because of FME; so very shortly you’ll be able to switch completely (or have to find another excuse for sticking)!

New GUI
We didn’t set out to redesign the interface but, because we wanted to add new platform support, we had to change the technology that drives the Workbench canvas. So you’ll see a lot has changed in the basic appearance of FME:

We tried to keep the same look and feel but differences were inevitable. After an initial moment of surprise this design is really growing on me and I hope it will for you too. Of course, we’re tweaking things as we’re going along and the new technology does provide some additional benefits that we can make use of (more on that below).

Nulls
In a previous Evangelist post I talked about updates to our support for null values. Basically it’s been a bit of a compromise but now, in 2014, we do have nulls fully incorporated into our data model.

You can see here how reading from an Oracle database I actually get <null> as a value, rather than it being empty or missing completely. Support is there too for SQL Server, PostGIS, and Geodatabase.

Writing data allows this too, and you can set null values in various ways. Firstly we have a new NullAttributeMapper transformer:

…where I can change an attribute from being empty or missing (or any other value) into a true null.

We also have updates to other transformers, such as the AttributeCreator:

We’re continuing to work on this null support and add it to other transformers too, where appropriate.

Other Stuff
There are, of course, a bunch of other changes. In the GUI, for example, you can now select features that overlap a selection window, like so:

Previously you would have had to completely surround the objects with the selection window to get them. We think this is an improvement over that older behaviour (I certainly find it easier).

Another item in the What’s New file is:

Workbench: Added pinch gesture support for zooming canvas

…suggesting to me that we support gestures on a touch screen device (I’m guessing Windows 8).

Bookmarks change too in 2014. Now objects in a bookmark are added/removed by dragging them in/out; not by moving the bookmark over them. This means you won’t pick up objects by moving a bookmark, as you could inadvertently do in older versions. Give it a try (I can’t really illustrate the concept with a screenshot!)

Summary
Safe’s founders – Don and Dale – are always thinking about ways to make FME better.

Dale says that change is good… as long as it makes things better.

Don talks about the Restaurant Model, and how we strive to make both the food (FME) and the service (Support) as good as we can.

Well I guess you could say the biggest change here is in our restaurant’s decor and, like a restaurant, it should make for a more appetizing and palatable experience. So, does this decor work for you? Would you award us a three-star review? We’d really like to hear your thoughts, ideas, and – even – criticisms of FME2014.

So, give it a try and let us know what you think.

Regards,

Hi FME’ers,
FME 2013-SP2 has been released! It should be about build 13499 meaning there have been 50 builds since SP1 was released; 50 builds that include a whole bunch of updates. Usually by now we’re mostly adding just bug fixes and minor updates – but for 2013 we’re actually adding some new functionality that we believe you shouldn’t have to wait until 2014 for.

So, here’s my rundown on the top 10 new items in FME 2013-SP2.

OK, I wanted to show 10 new things, and I only managed to whittle the list down to 22. So I’m merging some items together (and dropping a few others).

If you want to just see the REALLY big-ticket items, then simply skip down to the top 5, all of which will be worth your time to examine. Likewise, if you are a full-on FME geek who wants to see the full list, then check out the What’s New file on safe.com – just be sure to finish reading here first!

10) Most-Needed Fixes
Here we have the most-needed updates; i.e. updates that have the most user requests against them. I list them here because if this many users wanted these fixed I’m guessing others may find them useful too.

– Fixed problems connecting to the FME License Server via a VPN (6 cases)
– Added a new Postgres/PostGIS reader parameter to control what tables are shown in the table picker (7 cases)
– Added support for Civil3D 2013 plus support for pipe and structure entities (12 cases)

Incidentally, this just goes to show you should always let us know of any suggestions or issues – the more requests we get, the more likely we are to do something!

9) New Transformers
SP2 has a couple of new transformers of note: the AttributePivoter and the JSONUpdater.

The JSONUpdater – as you can probably guess – is the JSON equivalent to the XMLUpdater. These updater transformers are great for applying changes to an existing snippet of JSON/XML without having to convert them to an FME (GIS) relational data structure.

The AttributePivoter is going to be less familiar to you and I have to play with it some more to fully understand the capabilities. It’s all about what we’re calling Data Analytics. Pivot tables generally involve the grouping and summing or counting of records in a group.

What I tried this on was election results in a spreadsheet with this format:

Poll Number, Name, Votes
1, Mark, 346
1, Dale, 321
1, Don, 412
2, Mark, 400
2, Dale, 402
2, Don, 232
etc

The AttributePivoter lets me group that data by ROW, so I can group by Poll Number to start with and find out how many votes were cast in any particular poll. Then I can start grouping by COLUMN too, to create the idea of a pivot (how many votes for which candidate in a poll).

I’m going to have to play with this some more, but I think I can see the potential uses that are now available.

8) Performance
Just one item of note here. From the What’s New file:

Spatial Index: Improved spatial indexing performance in the FFS writer, and the Recorder and Inspector transformers. This reduces memory use and allows greater data volumes.

Why this is important is because the FFS writer gets used all over FME, particularly when sending data from Workbench to a Data Inspector. With all the talk about “Big Data” recently, this helps FME handle datasets larger than ever before.

7) Database Format Stuff
These are items of the type I find hard to categorize. They aren’t really major… unless you happen to be one of the users who will need them! Then they are of great interest.

Netezza: There are various improvements here. If you’re unaware, Netezza produces data warehousing hardware and software, and I’d say is at the “Big Data” end of the market. For SP2 we’ve added support on x64 for Netezza 6 and 7, but have also added writing support for their new spatial database – an Esri-flavour of Netezza that handles spatial data SDE-style (reading was introduced in 2013-SP1). So if you want to get spatial data into and out of Netezza, FME is the tool for you!

MariaDB: This is interesting. We had to pull MySQL support out of FME recently, for non-technical reasons. So we’ve instead implemented a new Reader/Writer for MariaDB (on 32-bit Windows, 64-bit Windows, and 64-bit Linux). Apparently MariaDB is a “drop-in replacement for MySQL” and so if you were pained at MySQL’s removal, you should be able to use this reader and writer with no visible difference to your workflow.

6) CSV Updates
There have been some interesting updates for CSV, including a change in methodology.

The Parameters dialog now lets you edit the source schema (attribute types) so FME knows what sort of values to expect in the fields. This is important especially when you only scan the first few rows (perhaps you have a huge dataset).

Secondly, we’ve added the format attribute csv_total_number_of_columns to indicate the total number of columns in a row. This compliments the existing csv_number_of_fields format attribute which indicates the number of non-empty values in a row

Finally, the CSV reader has been made more like the Text File reader, in that you only get a single feature type regardless of how many files you select. You could think of this as being automatically dynamic. If you do want individual types you can control this using the new parameter “Group By Filename”.

5) FeatureMerger
I love this update, and we have at least 7 cases that show users will agree.

This transformer is a way to merge or join data values and sometimes a join is made by multiple keys; for example if you are trying to match an address you might need to match: Number, Street, City. The new FeatureMerger lets you use all of those as a match. Previously you could only use one. The common workaround was to concatenate Number+Street+City into one and use that. Now you don’t need to.

As an additional bonus, the whole parameters dialog has gotten a nice redesign too:

4) AttributeCreator Updates
OK, this might not be something you’ll use all the time, but it will be fairly obvious in the AttributeCreator dialog, so you’ll need to be aware of this. In brief, we’ve added “multi-feature” functionality. This allows each feature to access attributes from prior/subsequent features in the workflow.

See, in 2012 we added the ability to construct strings within our “Integrated GUI“. That would look something like this:

So for any feature you could manipulate its attributes to construct a string or calculate a value. Now, in 2013-SP2, you can do the same thing, but using the attributes of other features; ones that have already passed through the transformer or are yet to come.

For example, I have a spreadsheet that counts rainfall for a given location;

Jan,47
Feb,85
Mar,128
Apr,178
May,229
etc

You can see that these numbers are actually the cumulative value for the year. So, if I want to figure out the rainfall per month I must subtract the previous value from the current value, and I can do this very nicely now with the new AttributeCreator. Here I activate and set up the multi-feature functionality:

Notice how I specify what to happen with a null value (that’s because the first record won’t have a prior value). Now I can set my actual mathematical expression:

The actual expression is this:

(@Value(feature[0].Cumulative Rainfall))-(@Value(feature[-1].Cumulative Rainfall))

Notice how we have a sort of array here, so I am using feature (0) – feature (-1). I’m using the Arithmetic Editor dialog here (because this is a numeric calculation) but the same multi-feature functionality will work in the Text/String Editor as well. The result of my workspace is this:

…which is just what I wanted.

3) Dynamic
I think this is going to be BIG! I just won’t go into detail here because this isn’t that type of article. I’ll just say that dynamic schemas can now be very dynamic, as you can set them using incoming features.

So, instead of having to point a dynamic writer to an existing schema, you can create that schema dynamically within the workspace. You basically create a bunch of attributes to describe the schema. That gives you a lot of power there, and solves some scenarios I don’t think we could have supported before.

But what’s more, FME seems to create some of these schemas automatically. As an example, look at the output from the AttributePivoter I mentioned earlier:

Can you see that a schema has been created? If you have a dynamic writer and send this data to it then the output will be in this schema. Of course, it makes sense here because the AttributePivoter will create data in a way that we are less likely to know the schema in advance; therefore this dynamic-ness is a great match for that transformer. But in future this may be applied to similar transformers too.

I look forward to seeing what other uses you FME’ers put this to! For more information check out FMEpedia. My colleague, Iris, is writing an article about this functionality and it should be there shortly (if it’s not already).

2) Background Maps
Here’s one that’s more simple to explain: the Data Inspector now has the ability to show background maps. Simply choose Tools > FME Options in the Data Inspector to find the settings for this:

The maps can come from ArcGIS Online (needs an account of some sort) or Stamen Maps, which I believe is derived from OpenStreetMap data. The other possibility is to use an FME Format as the source of background data. For example, you could point to any set of raster data to be a backdrop, or you could use a vector dataset, or even an FME Custom Format!

The nice part is that FME handles any reprojection dynamically, so the data being inspected doesn’t need to be in the same coordinate system as the background data.

1) Excel R/W
Excelsior! We now have a new Excel Reader and Writer, and it’s smooth. It’s like Barry White compared to the old Reader/Writer’s Green Day; that level of smooth!

Here’s the Parameters Dialog:

Notice that you can now select which sheet(s) to read from the Excel file (top left). Also notice you can read a particular set of cells (top right) by specifying a range in the spreadsheet. Then there is the wonderful preview window and, finally, a representation of the schema for the sheet (bottom right).

In the schema dialog note that you can edit the field types to ensure FME interprets them correctly (sometimes it’s hard for FME to be sure whether a column is meant to be a string or a number) and there is also the option to set cells as being coordinates.

If you do this then the fields automatically get converted to geometry, without you having to use a 2DPointReplacer. Don is really happy about this because it saves him a transformer in his demo workspaces! But just remember to set a coordinate system on any data you create this way, if you want it to appear with a background map in the Data Inspector.

The new Excel Writer is similarly advanced. It allows you to update cells, rather than just overwrite everything. In fact, there are five different writer modes:

So you can choose to add data as a new row, or overwrite the existing rows. You can also update individual cells, delete cells, or even delete entire rows. Incidentally, this functionality you will find in the Feature Type properties dialog, under the Parameters tab.

I believe we support the fme_db_operation format attribute (the where has to be a row number, not a cell ID) and we also support “named ranges” in Excel (reading and writing). You may also set colours, fonts, fill, etc on the output with the writer.

Notice how you access these – through the actual attribute definitions. I imagine we do it like this because you wouldn’t want to set format attributes for every individual feature/cell. In this dialog you can set the format of a cell to be simple (e.g. red font, yellow fill) or can be more complex by using a custom definition. For example this custom string:

[Green];[Red];[Magenta];[Blue]

…means colour positive numbers green, negative numbers red, zero is magenta, and strings are blue. The syntax for these strings is defined by Excel (it’s not an FME construction) and you can find more information in the Excel Help system.

So, there we are. That’s FME2013-SP2. You can also see a movie showing the updates on our YouTube channel. And – of course – when you want to download this new version you can get it from the Downloads Page on our web site.

Regards,

I was focused on two questions:

1. Do they all produce essentially the same output? (This gives me confidence about their quality without having to independently determine the “right” answers.)
2. As noted previously, only SQL Server models round-earth curves. Is that a benefit?

Experiment 1:

How far is it from Vancouver, Canada to Tokyo, Japan?

I loaded a two-point round-earth line into each system and asked for its length. Here are the (uncontroversial) results:

Experiment 2:

What is the shortest path from Vancouver to Tokyo?

Here, I’m interested in getting a densified line which allows for (a) comparison and (b) visualization in round-earth-unaware applications.

This is trickier; in each system the round-earth spatial functions have evolved from flat-map ones, and it doesn’t make sense to densify a line on a flat map, so there isn’t a call to do it. My first attempt was to intersect the Vancouver-Tokyo line with a large number of north/south lines and plot the intersection points. This worked well for Oracle and SQL Server, but less so for PostGIS:

Motivated to find an answer, I tried an approximation: Cover the earth with a grid at 1×1 degree intervals, find the distance between each point and the line, keep the ones within 200 km, and buffer these together. This produced a consistent result:

Experiment 3:

What area is within 1 km of the shortest path from Vancouver to Tokyo?

I asked each database to buffer the line (from Experiments 1 and 2) by 1 km. Only SQL Server produced the correct result; both Oracle and PostGIS followed the path PostGIS took in Figure 1.

Further, if I used SQL Server’s BufferWithCurves() method, it was able to produce a compact result consisting of five arcs.

Experiment 4:

What area is within 100 km of Vancouver’s airport?

I asked each database to buffer a point representing the airport by 10, 100, and 1000 km. The answers in each case were consistent and reasonable.

The majority of the differences between the three database’s answers came down to the density of the output (i.e., how many vertices made up the boundary). Oracle returned more vertices when the tolerance value was decreased (as expected), SQL Server appeared to ignore the tolerance value (unless I buffered as arcs, and then used CurveToLineWithTolerance), and PostGIS didn’t provide an option.

Experiment 5:

Are there any surprises when using round-earth curves for small objects?

The previous examples demonstrate SQL Server’s ability to use round-earth arcs to represent large areas on the earth (e.g., 100 km around an airport, 1 km around a flight path). Given that SQL Server’s round-earth arc representation is new, it makes sense to ask if it behaves intuitively for small objects.

I digitized a cul-de-sac near the Safe Software office using three-point arcs. Then I wrote the resulting (curve) polygon to SQL Server. I also wrote a stroked version of the polygon (replacing the curved segments with shorter straight lines) to Oracle, PostGIS, and (again) SQL Server. To test that the curved polygon behaved similarly to its stroked equivalent, I asked each database for the polygon’s area:

This suggests curved data may be loaded into round-earth columns and processed normally.

Conclusion

Returning to my two opening questions:

> Do they all produce essentially the same output?

Yes. These databases generally return consistent results for round-earth queries. There are minor variations, and not everything works in all cases, so care is required.

> Only SQL Server models round-earth curves. Is that a benefit?

Yes. SQL Server’s round-earth curves allow geometry to be stored more compactly without loss of detail. Perhaps more importantly, curved data can be loaded into round-earth columns without degrading the data (i.e., by converting the curves into lines).

During this exercise, I noticed an error in my previous post. I had thought SQL Server had introduced the buffering-with-curves idea. Not so: For example, Oracle has had this ability for flat-map buffering for a very long time.

Spatial databases can efficiently and effectively store, query, and transform data, all while honoring the curvature of our round world. How are you taking advantage of these capabilities?