Attribute Processing Example Workspace (CSV to MapInfo)

Introduction

There are many ways to manipulate and manage attributes using FME. This article will cover one of the ways. We will take a CSV file of cell signal data, and based on a combination of attribute values, produce point features which represent the quality of the signal.

Step-by-step Instructions

1. Read in the DataPoints CSV file

Add a CSV reader to the canvas, reading in CellSignals.csv. In the parameters, set the Data Type for longitude and latitude to x_coordinate and y_coordinate, respectively. Set the coordinate system to LL84 and click ok.

2. Test for Null, Missing or Empty Values

After reading in the CSV file, the first thing we need to do is filter out null/missing/empty values. Add a NullAttributeMapper transformer to the workspace. The NullAttributeMapper transformer is used to evaluate a selection of attributes for null, missing or empty values, map those attribute values to a new value of 'missing' and route the newly mapped missing values to the inspector for further evaluation. In the NullAttributeMapper parameters, set Map to All Attributes, click the ellipses next to If Attribute Value Is, select all three values and click ok. Then for Map To set it to Missing, this will change all null, missing or empty values to missing, which will aid in filtering later.

Using the NullAttributeMapper, select all values for If Attribute Value Is, and then Map To Missing

3. Remove missing values

Now that we have mapped all the null, missing, or empty values to read as missing, we can easily filter them out. Add a Tester transformer to the canvas. The Tester transformer is used to evaluate one or more tests on a feature. We will use the Tester to test to see if Latitude, Longitude, Power, Quality, and num_measures contain missing values. In the Tester parameters, set the Left Value to latitude, set the Operator to Is Missing, then enable Negate, repeat this for longitude, Power, Quality, and num_measures, you can use the Duplicate button and just change the attribute. Finally, set the Pass Criteria to All Tests (AND).

Test out all missing attributes for Power, Quality, num_measures, latitude and longitude

4. Remove Duplicates

We have a lot of records and sometimes duplicates happen, luckily we can use the DuplicateFilter to easily remove duplicate latitude/longitude pairs. Add a DuplicateFilter and connect it to the Passed Output port on the Tester. Open up the parameters and for the Key Attributes select both Latitude and Longitude.

5. Format date

Next, we need to reformat the date field record_tstamp because it contains hours and minutes and we are only interested in the date. For this, we will use the DateTimeConverter transformer, which is used to take an input date/time formatted in a specific way and output it in another way. In order for the DateTimeConverter to work correctly, you need to know the way the input date is formatted, so it is best to inspect your data beforehand.

The record_tstamp is formatted 4/12/2011 1:04, which means the day can contain one or two values, the month contains one or two values, the year is the full year, the hour contains one or two values in 24-hour format, and the minutes contain two values. This looks like this for the Input Format:

%e/%N/%Y %k:%M

We want the day to have two values, the month to have two values, and the year to be full, all separated by a backslash, which would look like 04/12/2011, for the Output Format it is:

%m/%d/%Y

To find out which query you need, there is a quick reference menu in the DateTimeConverter parameters.

In the DateTimeConverter, set the Input Format to %e/%N/%Y %k:%M and the Output Format to %m/%d/%Y

6. Pattern match using Regular Expression

The ‘Code’ attribute coming from the CSV file has a specific pattern. The first three characters are letters and the remaining characters are numbers (ABC3098330997). A simple regular expression can be used to extract them and create our codeTag and codeValue attributes respectively.

Using the StringSearcher we will isolate only alphabetic characters from the Code attribute value to create a codeTag attribute. Add a StringSearcher transformer to the canvas, and connect it to the Output port on the DateTimeConverter. In the parameters, Search In: Code, then set the Regular Expression to [A-Z][A-Z][A-Z] finally, set the Matched Result Attribute to codeTag.

Using Regular Expression, search for three alphabetic characters in Code

7. Extract the remainder of the Code

Now that we have created codeTag using the StringSearching and Regular Expression, we can use the same Regular Expression to extract the remainder of Code. Using the StringReplacer transformer, we will replace the alphabetic characters with nothing to isolate a codeValue attribute. Add a StringReplacer to the canvas, connecting it to the Matched Output port on the StringSearcher. In the parameters, set the Attributes to Code, and then set the Mode to Replace Regular Expression. For Text To Replace, use the same Regular Expression we used in step 5, [A-Z][A-Z][A-Z] . Leave the Replacement Text blank and click ok.

Using the same Regular Expression, remove the first 3 alphabetic characters to just keep the numeric values

8. Rename Code to CodeValue

We changed the values of Code using the StringSearcher, but the StringSearcher doesn’t allow for attribute renaming, so we will need to use the AttributeRenamer transformer. Add an AttributeRenamer to the canvas, in the parameters, for Input Value select Code, and for Output Value, rename it to codeValue.

9. Filter attributes to narrow down results

We are only interested in the Stations with a CodeTag of ABC or ABD, so we will need to filter them out. To do this we can use an AttributeFilter transformer which routes values to specific output ports. This process can also be achieved with other transformers like the tester. Add an AttributeFilter to the canvas and select codeTag as an Attribute to Filter By. Add ABC and ABD as Possible Attribute Values, then use the up arrow to move both of these to the top of the list above <empty>.

*Note: Steps 9-11 can be achieved using only the AttributeCreator and a Tester, the following method was chosen to highlight the use of the various transformers. To view the condensed method, open up the CSVAttributes.fmw workspace and look for the Condensed Method bookmark.

10. Filter attributes to narrow down results

Now we would like to organize and filter our data by Power level at each station, to do this we will use an AttributeRangeFilter transformer. With the AttributeRangeFilter we can create groups based on ranges using the Power attribute and then process the groups individually or as a group. Add an AttributeRangeFilter to the canvas connecting to both the ABC and ABD Output ports on the AttributeFilter. In the parameters, for Source Attribute select Power, then for the Range Lookup enter the following:

11. Create an attribute that indicates the quality of the signal

Now that we have filtered out which Power levels are required, and then tested for the quality of the signal, we need to identify these in an attribute. After each Tester transformer add an AttributeCreator transformer, which we can use to create the attributes. For the AttributeCreator connected to the first Tester, create an attribute called QualityOfSignal and give it a value of Best. For the next AttributeCreator connected to the second tester, create the same attribute, but this time give it a value of Reasonable. For the final AttributeCreator, use the same attribute name, but give it a value of Worst.

12. Create geometry from the attributes

Now that the data is cleaned up, we can visualize each cell signal on a map using the Latitude and Longitude attributes. We can use the VertexCreator to create points. Add a VertexCreator to the canvas and connect it to the AttributeCreator. In the parameters, set the X Value to longitude and the Y Value to latitude, keep the Mode as Add Point.

13. Color the points based on QualityOfSignal

Now that the points are created, we want to visualize the QualityOfSignal attribute by using different colors, this can be achieved using the AttributeValueMapper, which looks up attributes and assigns values based on other attributes. We will use this transformer to use the three values in the QualityOfSignal attribute and assign it a color using the fme_color value. In the parameters for Source Attribute select QualityOfSignal, then for Destination Attribute enter fme_color. Then for Value Map enter the following:

Use the AttributeValueMapper to color the points based on their value

14. Write to MapInfo TAB

Add a MapInfo TAB (MITAB) Writer and set the Attribute Definition to Automatic. Then connect it to the Output port on the AttributeValueMapper and then run the translation. Inspecting the output you should see that the points are colored by QualityOfSignal.

Output of the CellSignals colored by the QualityOfSignal.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.