The InlineQuerier Transformer

Introduction

The InlineQuerier transformer provides a great capability for joining together multiple datasets and then extracting the data any way you want to see it. It is an interesting transformer in that it utilizes a SQLite database underneath so provides you with the full power of SQL to do data extraction.

If you find that you are using multiple FeatureJoiner or FeatureMerger transformers in your workspace, the InLineQuerier might be a good alternative.

Still trying to decide which is the most appropriate join transformer to use? See the article Merging or Joining Spreadsheet or Database Data

When the InlineQuerier transformer is added to a workspace it has no input or output ports.

Input ports are built on the fly as you connect source data to the transformer and output ports are added as SQL statements are built inside the transformer. e.g.

See the user documentation for details of the InlineQuerier transformer. The example workspace can be downloaded here.

Defining Inputs

Tables

After connecting the input data to the "Connect Input" port a list of Tables will be added to the Inputs dialog box in the transformer. You can connect any reader feature type or any transformer, i.e. the DuplicateRemover shown above.

Table names are derived from the feature type or transformer name. You can rename the Input Tables, if necessary.

Columns

The Columns list defines the various attributes which will be used in SQL queries defining the Outputs below. This is auto populated as each table is connected to the transformer. It pulls in all the attributes in each table. However this is not necessary - only the Query attributes need to be explicitly defined. All other attributes and geometry will be held in an fme_feature_content attribute which is carried through the transformer. There is a memory overhead to adding all the individual attributes, so keep these lists as short as possible.

Also, make sure the data type for the attribute is correct or joins may fail. For example, in the above image, PID is a varchar in the AddressData and a float in PropertyTax, but it is actually an integer value.

The Import button will also pre-load these attribute lists for you but again brings in all attributes from each table.

Performance: reducing the number of attributes listed in the Columns parameter, as described above, can have a impact on the performance of the InlineQuerier. FME creates a table in an in memory SQLite database and reducing the number of attributes in that table will reduce memory resources. Recall that all attributes are also preserved in fme_feature_content. Also, attributes listed in the Columns parameter are indexed to speed up queries, so for larger datasets with many attributes that's a lot of indexing if all attributes are left in the Columns parameter. 

Defining Outputs

Output Ports

Output ports are added as SQL statements are defined. There can be a single output port or multiple output ports depending on what you are extracting from the data. Not all input tables need to be used in all SQL statements but only attributes which have been defined in the Input Columns can be used in the SQL. The Output ports can be named descriptively to help document the transformer.

SQL Query

The SQL Queries can be built using the SQL Editor and can involve a single table or multiple tables as required. Any attributes referenced in the SQL Statement must be defined in the Columns list above. All other attributes and geometry are maintained in an fme_feature_content attribute. If the result of a query includes the fme_feature_content attribute (either explicitly or through the use of a SELECT *), then the content of the original features will be included on the output feature.

SQLite is the underlying database used by InlineQuerier, so the SQL statements must use the SQLite SQL syntax and a SQLite tutorial.

The Generate button can be used to generate a single SELECT * statement for each table in the Inputs section.

Geometry

The Geometry option controls how the geometry is generated on the output features. If set to "First Feature" - then the geometry of the first source table in the SQL statement FROM will be output on the generated features. For example, if you want to join the AddressData (point geometry) to PropertyTax (no geometry) you could use:

SELECT * FROM "AddressData" a,"PropertyTax" b where a.PID = b.PID;

or

SELECT * FROM "PropertyTax" b, "AddressData" a where a.PID = b.PID;

In the first you'll get the point geometry from the AddressData and in the second you'll get no-geometry from the PropertyTax

If Geometry is set to "Aggregate of All Features"then the features will have geometry that is an aggregate of all geometries encountered.

Additional Resources

Using the InlineQuerier as a Replacement for Multiple FeatureMergers

Geometry Options in the InlineQuerier Transformer

Merging or Joining Spreadsheet or Database Data

Merging or Joining Spatial Data

InlineQuerier Documentation