Question

SQLExecutor for non-database querying and filtering?

  • 20 August 2015
  • 2 replies
  • 14 views

Badge
In FME Desktop 2014, I have a table of data that has gone through multiple transformers to test, filter, and create/calculate new attributes. One of the new attributes is a text field containing a SQL query statement (unique for each record) which was calculated from a combination of other attributes in the table.

 

 

I need to iterate through each of the table records, and use the new SQL text attribute to query records from another dataset. This other dataset is in an Oracle database, so I am using the SQLExecutor transformer (where the "Initiator" is my transformed table dataset, the "SQL Statement" parameter is my SQL attribute which queries my specified table in the database). The Result output of this transformer has a group of features filtered from the database table with each assigned attributes from the Initiator. Additional manipulation of this dataset includes a spatial Disssolver based on the "Initiator" attributes. This works perfectly.

 

 

But what if I don't have an Oracle database to query? What if the dataset I want to query/filter is another data format such as an Esri file geodatabase feature class (or any other non-database format)? I need to iterate through the first dataset (table) records, and for each iteration use attribute information from the table record to run a query/filter against the second dataset (file geodatabase) and extract the results as a group of features.

2 replies

Userlevel 4
Hi

 

 

The SQLExecutor passes the SQL statement to the underlying database, so it will only work with formats that natively supports SQL.

 

 

On the other hand, you could consider writing the data to a temporary SQL-enabled database and perform your queries there. Incidentally, this is what the InlineQuerier does behind the scenes.

 

 

I like to use SQLite or PostgreSQL/PostGIS for these use cases.

 

 

David

 

 
Badge
OK thanks - that's good to know that.

 

So now I'm wondering if there is any way to have this same functionality without using an actual database. What if I don't have a database installed on my machine?

 

Is there some way to iterate through a dataset, and run a tester or some kind of filter transformer on another dataset, bringing the results of each filter together into a single dataset?

Reply