Skip to main content
Solved

Use transformer outputs in a SQL statement


hirooimaki
Contributor
Forum|alt.badge.img+3

Hi there. I am a FME beginner and have a simple question regarding constructing a SQL statement.

My goal is to use FeatureMerger to merge CSV table data with polygons. But this process is going to be a dynamic process, I want to create a flexible workspace. For example, I want to use different CSV input files but the rest of the process stay same.

Here is my question. I want to use one of output value from a transformer in my SQL statement. How I can do that? I described my workflow below.

1. There are a CSV file that has a join key field (in the image below it is called "area") and polygon data (join key name in this polygon is called key_code in the image below). This CSV data include lots of duplicated join key values.

2. In order to use FeatureMarger between the CSV data and the polygon layer, I wanted to get a unique join key values from the CSV table. I used DuplicateFilter and Aggregator to get a comma separated join key values to use in a SQL statement later. At the end of process 2 in the image, I obtained a value like following.

33946011,533946012,533946013,533946014,533946021,533946023,533946111,533946112,533946113,533946114,533946121,533946123

3. Then, I set up a connection to my PostgreSQL database.

4. To limit the number of features to load (there are more than 2 million polygons), I want to set up a SQL statement to just read features which have the same join key values as the CSV table. So, I wrote SQL statement like,

Select * From mesh500 where "key_code" in (XXXXXXX);

Now, I want to insert the aggregated join key value (33946011,533946012,533946013,533946014,53....) to the above SQL statement (XXXXXX part).

Someone who know how to do this, please please let me know. I tried a private parameter to store the unique key values but it didn't work in the SQL statement. It looks simple but I cannot figure out.

Thank you in advance.

Hiroo

Best answer by takashi

Hi @hirooimaki, you cannot use attribute values to configure a reader parameter. Consider using the FeatureReader transformer instead of the reader. You can define a conditional expression using attribute values and set it to the WHERE Clause parameter.

Alternatively, the SQLExecutor can also be used. Note you will have to expose required attribute names through the Attributes to Expose parameter.

View original
Did this help you find an answer to your question?

4 replies

takashi
Influencer
  • Best Answer
  • May 6, 2018

Hi @hirooimaki, you cannot use attribute values to configure a reader parameter. Consider using the FeatureReader transformer instead of the reader. You can define a conditional expression using attribute values and set it to the WHERE Clause parameter.

Alternatively, the SQLExecutor can also be used. Note you will have to expose required attribute names through the Attributes to Expose parameter.


hirooimaki
Contributor
Forum|alt.badge.img+3
  • Author
  • Contributor
  • May 7, 2018

@takashi thank you so much for your prompt response! It works like magic for me. I spent hours and could not figure this out but you solve my problem so quick. Now I don't need to read over 2 million polygons to get thing done.

I am going to keep learning FME!

Thank you again.

Hiroo


takashi
Influencer
  • May 7, 2018
hirooimaki wrote:

@takashi thank you so much for your prompt response! It works like magic for me. I spent hours and could not figure this out but you solve my problem so quick. Now I don't need to read over 2 million polygons to get thing done.

I am going to keep learning FME!

Thank you again.

Hiroo

Good to hear.

 

By the way, perhaps do the key codes represent Japanese standard mesh codes? If you just need to get tens mesh-polygons from Japanese mesh codes, the JpMeshCodeReplacer transformer from FME Hub could also be a solution. FYI.

 

 


hirooimaki
Contributor
Forum|alt.badge.img+3
  • Author
  • Contributor
  • May 7, 2018

Thank you for the tip! I am going to check your custom transformer to see how it works. I was testing how NTT's mobile census data can be used with FME. I am a newbie but really excited about FME's capabilities. I noticed that you are in Japan and I would like to see you when I visit Japan.

Hiroo


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings