Question

How can I update attributes based on a SQL query?

  • 12 November 2021
  • 2 replies
  • 6 views
tdun
Rank
Badge +2

Hi all,

I have a CSV which I read into FME desktop.

 

It is bore hole data with attributes:

site: A

depth: 0

depth: 20

depth: 30

end depth: 35

 

I want to select the max(depth) for each 'site' and update that value to be 'end depth', then proceed with other transformations.

 

Note sure what transformer to use to do this?

I've looked at Tester, inlinequerier...

help ?

 

thanks

2 replies

jkr_wrk
Rank
Userlevel 3
Badge +18

Your question is a bit cryptic. The title says SQL query and update attributes. But your question says CSV.

 

I guess you have a dataset with more than 1 measurement per bore hole? Does the dataset contain multiple bore holes? Or just 1?

 

And your desired output is that all measurements have 1 extra attribute 'end depth' with the max('depth')

 

There is more than 1 way to Rome.

  1. You could use an Sorter-AttributeCreator Combination. The AttributeCreator has a function to look at the previous feature. If that feature is the same bore hole, use that features 'end depth' as 'end depth' of your current feature.
  2. Use the InlineQuerier as you where trying [ SELECT * FROM "Sampled" LEFT JOIN (SELECT "hole",max("depth") as 'end depth' FROM "Sampled" GROUP BY "hole") AS "Table" WHERE "Sampled"."hole" = "Table"."hole" ]
  3. You could use a StatisticsCalculator. The Complete Output has the values you need.
  4. The Sorter-Sampler-FeatureMerger combination is also possible. Sort so that the bore hole measurement with the largest value comes first. Sample 1 feature grouped by bore hole and merge this feature back to your original Dataset.

There are probably more solutions.

 

Sample: Every Nth Feature (N=1)
tdun
Rank
Badge +2

Your question is a bit cryptic. The title says SQL query and update attributes. But your question says CSV.

 

I guess you have a dataset with more than 1 measurement per bore hole? Does the dataset contain multiple bore holes? Or just 1?

 

And your desired output is that all measurements have 1 extra attribute 'end depth' with the max('depth')

 

There is more than 1 way to Rome.

  1. You could use an Sorter-AttributeCreator Combination. The AttributeCreator has a function to look at the previous feature. If that feature is the same bore hole, use that features 'end depth' as 'end depth' of your current feature.
  2. Use the InlineQuerier as you where trying [ SELECT * FROM "Sampled" LEFT JOIN (SELECT "hole",max("depth") as 'end depth' FROM "Sampled" GROUP BY "hole") AS "Table" WHERE "Sampled"."hole" = "Table"."hole" ]
  3. You could use a StatisticsCalculator. The Complete Output has the values you need.
  4. The Sorter-Sampler-FeatureMerger combination is also possible. Sort so that the bore hole measurement with the largest value comes first. Sample 1 feature grouped by bore hole and merge this feature back to your original Dataset.

There are probably more solutions.

 

Thanks mate.

In the end I went back to the InlineQuerier to perform the query to strip out the records i wanted to update.

Then i used FeatureMerger and Attribute Manager to update the records.

Probably not the most elegant solution but it works.

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
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.