Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • I have an Excel reader with counties. I want to filter another layer based on the counties in the Excel file
Reader 1: Excel Data with a County column

 

 

Reader 2: Ownership Parcels for Northeast United States with a County column

 

 

I want to filter out all counties that are not listed in the Excel Data. This will greatly reduce processing time since the Excel file never has more than a 100 records daily. While the Ownership always has millions of records.

 

 

If today's Excel Data has County A, B, and C then I only want Ownership Parcels that are in County A, B, and C. I hope that makes sense.

 

 

What's the best way to filter the Ownership Parcels with the everchanging Excel Data by the county field in the Excel Data?
Hi,

 

 

U can use the Joiner for that.

 

Reader 1 to Joiner and in joiner use Ownership Parcels .

 

 

The joining etc. is selfexplanatory i think.

 

 

 

..after Joiner u can test for the _matched_records attribute to select only the matches..of course..

 

Hi,

 

 

If the format of Ownership Parcels is a database, I think the SQLExecutor is worth to be considered.

 

 

Takashi
I think the Longhonrfan said they are excell files.

 

 

In a db you would do a join using sql.

 

And if u still want or prefer to use sql joining, you can insert both excell in a workspace and use a InlineQuerier.

 

If the Ownership dataset is not a database, the InlineQuerier would be one of candidates of course. But I'm afraid that the InlineQuerior might consume long time to create a temporary database storing millions of records...
Yes, i assume millions of records would reside in a db.

 

Then a joiner is still viable.

 

 

 

Comparison Joiner/Merger/SqlExecutor:

 

 

http://blog.safe.com/2011/05/fmeevangelist79/
Agree that the Joiner is still viable.

 

 

Regarding the blog article about performance comparison, I think the SQLExecutor use case is a "bad role model". The SQL was executed more than a million times to combine every geometry and attributes. Naturally it takes long time. The SQLExcutor is not suitable to such a usage.

 

 

In this case, if the Ownership is a database, you can fetch all the required records from the database, executing just one query. The SQL statement for the SQLExecutor would be something like this:

 

select * from Ownership where County in (@Value(CountyList))

 

 

Here, "CountyList" is an attribute which is held by the input feature, it stores comma-separated target county names. The input feature can be created based on the Excel table.

 

I think this query is very efficient. Of course the source dataset has to be a database 😉
The source is indeed a database. I'm going to spend some time messing with these based on the input you've both given me. Thanks for the help and I'll get back to you.

 

 

It turns out the ExpressionEvaluator works perfectly for doing this. I must have been entering the query wrong. User error right?

Reply


Community Stats

30,108
Posts
114,295
Replies
37,743
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.