Using the FeatureWriter Transformer with your Database

Introduction

In a conventional workflow, FME reads the data and writes using the reader and writer feature types - see the workspace in the attachment: FeatureWriter_Begin.fmwt. This workspace reads from a CAD file, transforms and validates the data and then writes the results to a GeoPackage database.

Note: Although this example writes to a GeoPackage database, it is applicable to any spatial database such as Esri Geodatabase, SQL Server, Oracle Spatial, PosGIS, etc.

This approach has only limited scope for additional processing after all the data has been written. You can use a Python or TCL shutdown script or a database writer SQL to Run Before/After Write, but these approaches have some limitations

The FeatureWriter expands the possibilities for workflows by allowing writing data mid-workflow, followed by additional data transformation. This is useful when it is desirable to do something with the data after it has been written.

Here are a few possibilities for the FeatureWriter, all accomplished within a single workspace:

1. Simple procedures previously accomplished with scripts or manually:

• Copy or move data after writing
• Email after job completes
• Load the file into FTP, S3, Dropbox or other Cloud storage after writing
• Additional SQL calls after the writing to a database such as create an index, validate the data

2. Complex tasks that required chained workspaces using WorkspaceRunner or FMEServerJobSubmitters:

• Quality Check > Quality Report with Notification > Database Insert

3. Notifications to FME Server

• After writing data with the FeatureWriter, it is easier to prepare notification messages, such as email for FME Server right in the same workspace

4. Integrations with third party tools for data transformation in FME without waiting for a new reader:

• Example are LASTOOLS or Orfeo Tool box, Image Magic
• Basic workflow is FeatureWriter - SystemCaller - FeatureReader - Cleanup

Source Data

Source data is the CAD file with a City of Vancouver water network, water mains, valves, hydrants. The dataset contains a number of attributes related to information about the water network objects.

Step-by-step Instructions

This FeatureWriter demo validates a CAD dataset and emails a validation report to the data validation manager after all the features have been written.

The FeatureWriter_Begin.fmw workspace includes the following basic steps:

1. Read the CAD data and transform attributes

• There are several attribute transformation transformers in the workspace. These include AttributeManager to rename attributes and set default values and AttributeValueMapper to build domain values. 2DForcer is used to drop Z values that often appear on CAD geometries.

2. Validate water network attribute values

• Validate the network dataset attributes against a number of tests with the AttributeValidator. A list of the failed tests is added to features routed through the Failed port. Later on, this list allows us to write data about which features failed against one or more tests to GeoPackage databases enabling data validation and quality assurance.

3. Extract error messages and validation tests of failed water network objects

• The list from the AttributeValidator, _fme_validation_message_list{}, is exploded into individual list items, so that a feature is for each test failed. See the bookmark "Detailed Validation Report".
• The results are written to the GeoPackage

4. Write the valid feature to the GeoPackage CADValidationReport table

• All the features that pass the validation are written to the appropriate table in the GeoPackage.

Run the FeatureWriter_Begin.fmw workspace and inspect the resulting GeoPackage in FME Data Inspector.

You're going to modify the workspace by:

• replacing the AutoCAD reader with FeatureReader
• replace the GeoPackage writer with a writer to a database of your choice - i.e. Esri Geodatabase, Oracle Spatial, SQL Server, PostGIS, etc. (optional)
• replacing the GeoPackage writer with FeatureWriter
  writer to the database of your choice i.e. Geopackage, Esri Geodatabase, Oracle Spatial, SQL Server, PostGIS, etc.
• adding a SQLExecutor to do some post-processing of the data

Adding FeatureReader

You can replace the AutoCAD reader with a FeatureReader. This would allow you to pre-process the data, clean-up database tables using SQLExecutor or trigger the read using an external input. In this example we'll just use the Creator transformer.

1. Open the workspace FeatureWriter_Begin.fmw

2. Disable, but do not delete, the AutoCAD reader.

3. Add a Creator transformer.

4. Add a FeatureReader and select the Autodesk AutoCAD Map 3D Object Data format and the AutoCAD DWG file: CofVWater_small.dwg. Configure the FeatureReader as shown below:

In the AutoCAD Parameters dialog, make sure you uncheck Explode Blocks into Entities as shown below:

Select all the AutoCAD layers under Feature Types to Read

5. Connect the output ports to the appropriate AttributeManager transformers (not all of the feature types need to be connected).

6. Test your FeatureReader by running the modified workspace. If the results look good, delete the AutoCAD reader.

7. Save the workspace.

Adding FeatureWriter

You're now going to add a FeatureWriter to the workspace, replacing the 'conventional' GeoPackage writer.

1. Disable, but do not delete, the GeoPackage writer

2. Add a FeatureWriter transformer and open the Parameters dialog. Configure the FeatureWriter parameters:

• Select the format, OGC GeoPackage, and the destination dataset, CoVWaterDatabase.gpkg
• Import the feature types using Import from Dataset... . For convenience, import the schema from the GeoPackage you just created. Import the feature types: wMain, wControlValve, wHydrant
• Connect the input ports to the appropriate AttributeValidator Passed ports.
• Test your workspace and save it.

Post-processing

Now we can do some simple post- processing or validation of the output. For example, you could create an attribute or spatial index using the SQLExecutor. In this workspace you are going to validate the number of features written. FME reports the number of features written but sometimes this might not match the actual number of features inserted into the database. This can occur if you are using transactions and one of the inserts fails. You're going to add a SQLExecutor to check the number of records in the database after all the features have been written by the FeatureWriter.

1. Add an Inspector transformer to the Summary port of the FeatureWriter and run the workspace. The Summary port of the FeatureWriter outputs a single feature with a list of all the feature types written:

• _feature_type{0}.count 1481
• _feature_type{0}.name wControlValve
• _feature_type{1}.count 514
• _feature_type{1}.name wHydrant
• _feature_type{2}.count 2314
• _feature_type{2}.name wMain
• _total_features_written 4309

Where 'name' is the table name and 'count' is the number of features FME thought it wrote.

2. Connect a ListExploder to the Summary port of the FeatureWriter. Select the _feature_type{} list. This will result in a separate summary feature for each feature type that was written.

3. Connect a SQLExecutor to the Elements port of the ListExploder. Build the following query:

SELECT COUNT(*) AS "rows" FROM "@Value(name)"

This queries the database and returns the row count of each table in the 'name' attribute.

4. Add a TestFilter to compare the 'count' value of features written vs. 'rows' value returned by the SQLExectutor.

5. Add Inspectors and then run the workspace and check the results.

6. The last step,if you comfortable that all your changes are working correctly, would be to delete the original AutoCAD reader and the original GeoPackage writer and clean-up your workspace. Save your work.

Other Post-Processing Options

The workspace in the attachment FeatureWriter_Final.fmwt has some additional ideas that you can investigate:

JSON reader: In the final workspace we use a JSON reader to initiate the FeatureReader - this would allow you to publish this workspace to FME Server and drive the validation using a directory watcher publication.

DropboxConnector: The DropboxConnector allows you to move the GeoPackage database file to a dropbox folder. If you don't have a dropbox account you'll need to disable this transformer. Or try one of the other connectors: GoogleDriveConnector, OneDriveConnector, BoxConnector.

Emailer: The final workspace includes an Emailer that will email the validation GeoPackage database file to a friend. You'll need to configure the Emailer with your own email account information or disable the transformer to avoid errors.

Note: The demo workspace will return a warning if the Emailer is not configured correctly for your email account. Necessary configuration can include configuring SMTP for Gmail, and generating a App specific password if two step authentication is used. Same for the DropboxConnector. If you don't have suitable accounts, just disable these transformers