Skip to main content
  • EN

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

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.

@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 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.

 

 

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


Community Stats

31,819
Posts
120,860
Replies
39,451
Members

Latest FME

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.