SchemaMapper
From fmepedia
SchemaMapper is a Workbench Transformer.
| Table of contents |
Description
The schema mapping table can be contained in a database (Oracle, POSTGRES, ODBC, etc), or a CSV file. The SchemaMapper wizard can be used to define the rows that contain the filters, and the rows that contain the attribute mappings.
Instead of using workbench links to define attribute correlation, the schema mapping is taken from an external table (CSV, Oracle, Access, Postgres) which lists a series of constraints and mappings, for both feature types and attributes. This is useful when correlation is very complex, or when the correlation needs to be maintained by someone unfamiliar with FME.
WorkBench: SchemaMapper The SchemaMapper is used to map the schema (attributes and feature types) of features based on a schema mapping table. The table defines a series of conditions that are to be met (filters), and a series of attribute manipulations that will be executed when the conditions are met. For each feature that enters the factory, each row in the table is searched, from top to bottom, and if the feature matches the filters specified, the attribute mappings are executed. The geometry of each feature is left untouched.
If a feature matches the rules in any row of the table, it is output via the MAPPED port. Otherwise it is output via the UNMAPPED port.
As you can imagine, this can get rather involved depending on the type and complexity of schema mappings involved. That is why this transformer usually requires implementation help from Safe's Professional Services department.
Use Case
The best way to understand the SchemaMapper is to look at a few use cases and an example. One way you can use it is to go through a database and systematically change attribute names from an old name to a new name. Another way to use it is to go through a database or set of feature tables and find all occurances of a specific field name, and replace a code in that field with a corresponding description based on a domain lookup defined in a schema table for that field.
Consider the example below:
Sample Paramters:
1) DB_DATASET "domainSchema.csv"
2) DB_TABLE "DomainCodedValues"
3) FILTER_COLUMNS
attribute name field: field
attribute value field (lookup key): code
4) ATTR_MAPPING_COLUMNS
source attribute name: none
destination attribute name: field
default value (really destination replacement value): description
For every table associated with each input feature, filter for the attributes whose name appears in the csv column 'field' and whose value appears in the csv column 'code'. Then replace the code with the corresponding value in the description column.
The result is a set of features where every table with a given field / code combination found in the domainSchema.csv file is replaced with the corresponding description associated with that field.
domainSchema.csv contents:
DomainName code description field D_LENGTH_UNITS 1000 CENTIMETERS LEN_UNIT D_LENGTH_UNITS 1010 FEET LEN_UNIT D_LENGTH_UNITS 1020 INCHES LEN_UNIT D_LENGTH_UNITS 1030 KMS LEN_UNIT D_LENGTH_UNITS 1040 METERS LEN_UNIT D_LENGTH_UNITS 1050 MILES LEN_UNIT D_LENGTH_UNITS 1060 MILLIMETERS LEN_UNIT D_TIME_UNITS 1000 DAYS TIME_UNIT D_TIME_UNITS 1010 HOURS TIME_UNIT D_TIME_UNITS 1020 MINUTES TIME_UNIT D_TIME_UNITS 1030 MONTHS TIME_UNIT D_TIME_UNITS 1040 SECONDS TIME_UNIT D_TIME_UNITS 1050 WEEKS TIME_UNIT D_TIME_UNITS 1060 YEARS TIME_UNIT
Input table
ID RoadName Length LEN_UNIT Age TIME_UNIT 1 Hwy21 54 1050 3 1060 2 Main St 2.5 1030 9 1030 3 3rd Ave 4500 1040 42 1060 4 Hwy 17 132 1050 35 1050
Output table
ID RoadName Length LEN_UNIT Age TIME_UNIT 1 Hwy21 54 MILES 3 YEARS 2 Main St 2.5 KMS 9 MONTHS 3 3rd Ave 4500 METERS 42 YEARS 4 Hwy 17 132 MILES 35 WEEKS
Note that the schema mapping table (domainSchema.csv) is often derived from a database metadata document such as ESRI's XML database schema description which can be exported from ArcCatalog for any selected geodatabase (Export - XML Workspace Document - Schema Only).
Example 1
The attached workspace shows an example use of the SchemaMapper transformer acting as a type of advanced lookup table.
A zoning dataset is read in. Each zone is denominated by a code (eg SF-4A).
The first SchemaMapper matches the zone code to an entry in a CSV file and uses this match to retrieve values for the zone description (eg Single Family Residence) and taxcode.
The second SchemaMapper matches the taxcode to an entry in a different CSV file. It uses this match to determine the tax rate ($ per sq-foot) for that zone.
Finally an AreaCalculator and ExpressionEvaluator are used to determine the tax value of a piece of land, based on its area and taxrate.
Workspace Screenshot
Wizard Screenshots
Here the user sets the SchemaMapper options. It will look to create a match where the attribute with the name stored under sField in the CSV file has the value stored under sValue in the CSV file. In this case the first entry in the CSV reads...
TYPE,LA,zone,Lake Austin Residence District,taxcode,res5
...which in effect means create a match where...
TYPE = LA
In our CSV file all of the sField values are TYPE - but this doesn't necessarily have to be the case. Also multiple match tests seem to be permissable - eg TYPE = LA and XXXX = YYYY. It doesn't appear that mathematical operators are permitted though (eg < > !=)
Here the user is determining the actions to take on finding a match. It will look to set the attribute whose name is stored under dField1 to a value of dValue1, and similarly with dField2 and dValue2. To go back to the first line of the CSV file...
TYPE,LA,zone,Lake Austin Residence District,taxcode,res5
...in effect this means where there is a match (TYPE = LA) then set...
zone = Lake Austin Residence District and taxcode = res5
Example 2
The attached workspace provides a second example workspace, this time showing thirteen different ways to use the SchemaMapper transformer.
The various uses of the SchemaMapper demonstrated in the workspace are:
Type 1: Simple feature type to feature type mappings (1:1)
Type 2: Feature type to feature type where one features maps to many features
1:M feature mapping based on a source attribute value
Type 3: Feature type to feature type where many features map to a single feature
M:1 feature mapping
Type 4: Attribute to attribute mapping
1:1 attribute mapping
Type 5: The same attribute (type) in different tables map to different attributes in the destination feature type i.e.
Pole/type > pole_type Manhole/type > manhole_type Multple entries per attribute, based on feature
Type 6: Attributes in a table map to the different attributes based on the value of the source attribute i.e.
Pole/type = 1 > pole_type = 1 Pole/type = 2 > material = 2 Multiple entries per attribute, based on feature & value
Type 7: Attributes in a table map to the different attributes based on the value of the source attribute i.e.
Pole/type = 1 > pole_type = 1 Pole/type = 2 > material = 2 Multiple entries per attribute, based on feature & value
Type 8: Feature & attribute mapping
Feature to feature mapping 1:1 Attribute to attribute mapping 1:1 i.e. Pole/typeA > pole_type Pole/typeB > material
Type 9: Simple attribute value mapping (domain mapping)
Requires a separate schemamapper for each domain (basically the idea is that this could be a dynamic ValueMapper)
Type 10: Attribute value mapping
Attribute to attribute mapping 1:1 Value mapping 1:1 type X > A type Y > B material X > A material Y > B
Type 11: Attribute value mapping - for several different attributes for
different feature types.
Attribute to attribute mapping 1:1 Value mapping 1:1 Pole type X > A Pole type Y > B Pole material X > A Pole material Y > B Manhole type u > 1 Manhole type v > 2
Type 12: Attribute value mapping - for several different attributes for
different feature types.
Attribute to attribute mapping 1:1 Value mapping 1:1 Pole type X > pole_type A Pole type Y > pole_type B Pole material X > material_type A Pole material Y > material_type B Manhole type u > manhole_type 1 Manhole type v > manhole_type 2
Type 13: Attribute value mapping - for several different attributes for
different feature types.
attribute to attribute mapping 1:1
value mapping 1:1
Pole type X > supportStructures pole_type A Pole type Y > supportStructures pole_type B Pole material X > supportStructures material_type A Pole material Y > supportStructures material_type B Manhole type u > AccessPoint manhole_type 1 Manhole type v > AccessPoint manhole_type 2
Example 3
Purpose
This workspace reads source old_names.csv and renames the field names based on the field name lookup table in rename.csv and writes the results out to new_names.csv.
Background
Instead of using workbench links to define attribute correlation, the schema mapping is taken from an external table (CSV, Oracle, Access, Postgres) which lists a series of constraints and mappings, for both feature types and attributes. This is useful when correlation is very complex, or when the correlation needs to be maintained by someone unfamiliar with FME.
If a feature matches the rules in any row of the table, it is output via the MAPPED port. Otherwise it is output via the UNMAPPED port.
Use Case
The best way to understand the SchemaMapper is to look at a an example. One of the simplest use cases is go through a feature attribute names and systematically change the field names from an old name to a new name.
Consider the example below: Source data: old_names.csv
nam,add,pho Jim,123 4th St.,987-6543 Sue,321 B St.,234-5678
Suppose we want to change the field names to something a bit more descriptive. We could use SchemaMapper with the field name lookup table: rename.csv
old,new nam,Name add,Address pho,Phone
To generate the new result: new_names.csv
Name,Address,Phone Jim,123 4th St.,987-6543 Sue,321 B St.,234-5678
All you need to do is set up a workspace that reads old_names.csv, writes to new_names.csv and passes through a SchemaMapper.
The Schemamapper just needs to be configured to point to rename.csv, and then leave all the other config fields blank except the last pane: Select Mapping Fields: Current Mapping: old,new,"".
