Question

InlineQuerier Question

  • 8 September 2014
  • 6 replies
  • 8 views
R
Badge
Hi

 

 

(I am using FME Desktop 2013 SP1 Build 13448)

 

 

 

Can InlineQuerier only be use to query the tables in their native data structure i.e. as they are in when they are initially read in? Or (ideally) can I use the transformer to query tables which I have alter part way through a workflow?

 

 

Ideally I want to read in some data, complete various processes and create a series of new attributes and then use the InlineQuerier to query some of new and original attributes. I am reluctant to create 1 workspace to read in the tables, create the attributes I need, then write the results to an intermediate file, and then create another workspace with the InlineQuerier elements?

 

 

 

I could use the FeatureMerger but this is really slow by comparison as I have to initially join all values from A to B and then subsequent filter, I have done this before but it is REALLY slow, and essentially can be completed using SQL Plus I am concerned that the process will run out of memory).

 

 

 

Any ideas this possible?

 

 

 

Regards,

 

 

Rob

6 replies

david_r
Rank
Userlevel 4
Hi,

 

 

the InlineQuerier will read all features into a local SQLite database and perform the queries locally on the same machine that's running FME.

 

 

If you query is complicated and/or is made on a fairly large dataset, you might get better results from a SQLExecutor, which will perform the query on the underlying database.

 

 

David
david_r
Rank
Userlevel 4
Also, if you're looking to pick up a lookup value from a secondary table using a foreign key, consider using the Joiner. If the number of lookup values is fairly limited, it can be very fast and memory efficient, particularly if you take the time to properly set up the prefetch query.

 

 

David
R
Badge
Hi David,

 

 

Thanks for the information. However i am not sure if this addresses the issue (but i have probably missunderstod).

 

 

Is the following possible, as I need  to use values which are created DURING the workflow to join and query on; rather than ones that are present when the tables are initially read in

 

 

IE

 

 

step 1

 

Read In CSV A, colA1, colA2

 

Read in CSV B, colB1 colB2, colB3

 

 

step 2

 

normal workflow processes are used to create 'new attributes' on both seperate stream

 

workflow A, becomes colA1, colA2, colA3, colA4, colA5

 

workflow B, becomes colB1, colB2, colB3, colB4,colB5, colB6, colB7

 

 

 

step 3

 

the structure of the workflow tables is now different to when they were read in. BUT can I use InlineQuerier to join on query on 

 

 

ie

 

SELECT *

 

WHERE colA3=colB4

 

AND colA4=colB5

 

AND colA5>=colB6

 

AND colA5<=colB7

 

 

 

 

 

step 4

 

use the results returned for other workflow processes

 

 

 

 

 

In this senario all the results from step 2 are NOT exported to intermidaite files and then joined

 

 

Thanks

 

 

 

Rob

 

david_r
Rank
Userlevel 4
Hi,

 

 

if you introduce new attributes in the course of the workspace, those will of course not be available (without your intervention) as a join criteria in the database. In this case, the simplest solution is using either a FeatureMerger or the InlineQueryer, as they will have access to your workspace attributes.

 

 

If your dataset is too big for those, you will probably have to rething your solution somewhat.

 

 

David
R
Badge
Hi David,

 

 

OK thanks, I thought that this was probably the case but needed to check. I really hoped that the InlineQuerier could be used on the fly throughout the course of the workspace flow. I will need to try an alternative route.

 

 

 

Thanks again,

 

 

rob

 

david_r
Rank
Userlevel 4
Just a tip: for huge datasets, I've had good experiences with creative use of the SQLCreator rather than readers and the SQLExecutor rather than the FeatureMerger and others. The less you have to move the data in and out of a workspace, the better your performance (generally speaking).

 

 

Good luck.

 

 

David

Reply


Community Stats

28,661
Posts
109,324
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.