2014-01-18

Range-based Attribute Mapping with InlineQuerier

(FME 2014 build 14232)

I remembered by chance that an issue on range-based attribute mapping had been discussed in this thread. > Community: Schema mapper attribute range

Of course the AttributeRangeMapper transformer could be used if the configuration of range-value mapping was static. However, the subject is a case that the range-value mapping will be configured dynamically via an external table at run-time. It's unknown when creating a workspace.
I provided a solution using Python script at that time, but I noticed that the InlineQuerier could be another solution.

Assume "Data" table contains attributes of target features as followings.
IDHeight
1-5
25
315
425
535

And "RangeToLevel" table defines mapping rule of height range and value. For example:
MinHeightMaxHeightNewValue
010Level1
1020Level2
2030Level3

Then, merge those tables using an InlineQuerier.












This is the SQL statement specified to the InlineQuerier. The point is "cross join".
-----
select s.ID, s.Height, t.NewValue
from Data as s left outer join (select a.ID, b.NewValue
    from Data as a cross join RangeToLevel as b
    where b.MinHeight <= a.Height and a.Height < b.MaxHeight) as t on t.ID = s.ID
-----
Result Table:










Here, <null>s have appeared :-)
Yes, the NullAttributeMapper transformer can be used to map every <null> to a preferable value (e.g. "Undefined Level") if necessary.
I think this is also a typical use case of the NullAttributeMapper.

But this SQL statement is also possible to do that. There is always more than one way!
-----
select s.ID, s.Height,
case
    when t.NewValue is null then 'Undefined Level'
    else t.NewValue
end as NewValue
from Data as s left outer join (select a.ID, b.NewValue
    from Data as a cross join RangeToLevel as b
    where b.MinHeight <= a.Height and a.Height < b.MaxHeight) as t on t.ID = s.ID
=====
2014-01-19: If features whose height is out of range can be discarded, or if it's guaranteed that there aren't "out of range" features, this SQL statement can be used simply.
-----
select a.ID, a.Height, b.NewValue
from Data as a cross join RangeToLevel as b
where b.MinHeight <= a.Height and a.Height < b.MaxHeight
-----

1 comment:

  1. Works like a charm, thanks for your detailed example :-)

    ReplyDelete