Skip to main content
  • EN

Getting stuck with a SQL statement that i'm using for the InlineQuerier. I normally do not work that much with SQL code and only use if to perform simple one on one joins. I am working with one data frame at the moment that contains multiple attributes and i want to create an extra attribute based on these. An short example:

<code>ID  road_name           road_type    Type     Traffic
1   bakerstreet         elements     B        light
2   bakerstreet         elements     B        light
3   piccadilly circus   asphalt      A        heavy
4   woodstreet          concrete     A        heavy
5   settlerstreet       concrete     A        heavy
6   woodstreet          concrete     A        heavy
7   settlerstreet       concrete     A        light
8   settlerstreet       concrete     B        heavy

I am trying to give all the features a unique RoadID based on all 4 attributes so i can merge them later on. The new attribute RoadID must be a data type Integer and the value should be matching a unique combination of the 4 attributes.

The results should be something like:

<code>ID  road_name           road_type    Type     Traffic   RoadID
1   bakerstreet         elements     B        light     1
2   bakerstreet         elements     B        light     1
3   piccadilly circus   asphalt      A        heavy     2
4   woodstreet          concrete     A        heavy     3
5   settlerstreet       concrete     A        heavy     4
6   woodstreet          concrete     A        heavy     3 
7   settlerstreet       concrete     A        heavy     4
8   settlerstreet       concrete     B        heavy     5

I tried to do this with the SQL function concat but this got this result: "no such function: CONCAT" Is there another way to do this in the inlinequerier or with another transformer?

I would not use the InlineQuerier, but try this:

Use DuplicateRemover on the required fields to get the unique values.

Use the Counter to generate the ID.

Then merge ID on the original data using the FeatureMerger with the original as Requester and the uniques as Supplier to add the ID.

From a SQL point of view SELECT DISTINCT road_name, road_type, Type, Traffic FROM ...

 

would return the unique combinations of those attributes. A SQLCreator, followed by a counter (ROAD_ID) and then a FeatureMerger with the road segment data.

If your data is already in FME, an alternative to erik_jan's suggestion is to Aggregator with a group by on the 4 attributes, and generate list ->counter (to create Road_ID) -> Deaggregator (List to Explode set to the list you created in the Aggregator). No need for a featureMerger.

Try using a GROUP with all you fields toget a uniqueoutputfor each combination andthenaddROW_NUMBER


SELECT a.FIELD1, a.FIELD2,ROW_NUMBER()OVER(ORDERBY a.FIELD2)


FROM TABLE_NAME a


GROUPBY a.FIELD1, a.FIELD2

Hi @jdh009, this is another solution using global variable and conditional value setting.

VariableRetriever
  • Variable Name: @Value(road_name)_@Value(road_type)_@Value(Type)_@Value(Traffic)

  • Attribute Receiving Value: RoadID
VariableSetter
  • Variable Name: @Value(road_name)_@Value(road_type)_@Value(Type)_@Value(Traffic)
  • Value: RoadID <attribute value>

Hi @jdh009

I would like to suggest two other implementations to get the wanted result. The first implementation creates a list for all features having the same values, uses a Counter to generate the RoadId and splits the list into features again afterwards:

The second implementation samples for distinct combinations which are send through a Counter generating the RoadId. Via a FeatureMerger this RoadId is then linked to all roads which the same combination:

I prefer the other implementation because it is more clear what functionality is implemented. But both do their job just fine.

To answer a part of the question regarding generating a unique RoadID of type int.

My first inclination is to concatenate all four attributes into one string (let's call it RoadIdString). then get md5() hash on that string and get an integer representation of that hash. You may want to check this post for some implementation ideas:

https://knowledge.safe.com/questions/24956/create-hash-md5-with-fme.html

Reply


Community Stats

30,126
Posts
114,372
Replies
37,764
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.