Solved

Match features when multiple attributies are the same

  • 30 January 2017
  • 7 replies
  • 15 views

Badge +2

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?

icon

Best answer by jeroenstiers 31 January 2017, 04:57

View original

7 replies

Userlevel 2
Badge +12

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.

Badge +22

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.

Badge

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

Userlevel 2
Badge +17

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>
Badge

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:

Badge

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.

Badge

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