Friday, September 28, 2007

Creating Features with FME by combining Graphics From CAD files and a Database

It is not uncommon to have CAD files (such as Autocad or Microstation) whose elements have embedded keys to an external database (such as an Access file or even Oracle). While these systems are not traditional GIS systems, the data can be combined with FME to appear like GIS features when viewing them in GTViewer. When I say appears like GIS features, I mean that if you review the graphic representation of a feature, you will get one or more tabular records related to that feature, and you can locate on a feature based on its associated tabular attributes.

Using FME and the GTViewer plug-in, you can take CAD files and a database and pretty easily make a GIS-like dataset for GTViewer. In this posting, I am going to take an Autocad file (.dwg) and an Access File (.mdb) with two tables. To make this task a little more interesting, the feature in the CAD file is a customer location with an embedded customer number (as a string value) and the Access file has two tables (Customer and Tap) where one record out of each table will be associated with each graphic.

Fortunately, FME provides the Joiner transformer to do all of the hard work. If we only had one table to join to the graphic feature, the task is trivial, so I will begin by using a Joiner to add one of the records to the geometry and create a new GTViewer destination feature called Customer.

Using the Joiner transformer, we can specify a table (Customer) in the .mdb file and then a key from the CAD feature (Con_Number) and key from the database records (Cust_Num). The destination is a GTViewer feature and it has the attributes copied from the transformer to look like the following:

This simple configuration will do all that is required for joining one record into the graphics. But in our case, we have records from two different tables that need to be associated with the geometry and the configuration is a little more complicated.

First, we need to add a Counter transformer to generate a unique key that we can be use for the geometry and the two records. This key links the geometry to the tabular data and vice versa.

The count output attribute can be named gti_key2 and will be one part of the key used by GTViewer. The count should start at 1 too, since 0 is not a valid key in GTViewer.

On the Customer output feature, two format attributes need to be exposed: gti_key1 and gti_key2. Normally, these keys are automatically populated by FME to keep the graphic and tabular components associated with one another, but we need to control the key generation process in this example so that we can use the same keys on another record. Since we renamed the count output value as gti_key2, it is automatically linked up (it doesn’t have to be exposed, but the green arrow is a good confirmation that you have things setup). The gti_key1 value needs to be set to some arbitrary value (I picked 9999). The key 1 value is usually a table id, but can be assigned anything. We just need to make sure that the graphic and its tabular data and the additional tabular record all use the same key1 and key2 values. The schematic below will now work for the first table (producing the same output as the previous schematic), but we have control over the keys:

The next step is to add another GTViewer Destination Feature for the additional tabular record. Since this is going to be records for the Tap table, I will call it Tap:

Now comes the fun part. We add a second Joiner (called Joiner_Tap) and specify the Tap table in our .mdb file. We then set the key from the autocad feature (Con_Number) and a key from the tap table (AccountNum). Our newly created Tap output feature needs to have three format attributes exposed: gti_key1, gti_key2, and gti_type. The key attributes are the same as in our first feature. The gti_type needs to be set to gti_data because we don’t want another graphic generated for the feature, just the tabular record. We then copy the attributes from the Joiner_Tap transformer, and voila!

In GTViewer, you can review one of the customer graphics and get both of the record just like it was real GIS feature:

No comments: