Solved

dynamic merge (or join) without knowing attribute names

jeroen
Rank
Badge +7

I'm generating a large GML based on data that i get from the SQL creator. The whole workbench is meant to be database driven. There is a main workflow that triggers this one:

SQL_creator has dataset part A and SQL_creator2 contain part B, both are the same dataset and should be merged by ID (only attribute that they both have). They are split because of the amount of joins that the SQLcreator can handle is reached.

I have a parameter $(sql_statement) that contains the SQL statement that is used in the SQL Creator. The attributes itself are dynamically named, so they cannot be exposed by setting value "attributes to expose". The Attribute exposer exposes the ID of the records, this is the only attribute that both creators have in common.

Schemasetter exposes the dynamic attributes so they will be exported in the dynamic GML export.

It works with the tester directly to GML but it does not show the dynamically created attributes that are created with the SQL_creator2 after the merge is done. Is there a solution to take these attributes into account and join them together and also show them in the GML?

icon

Best answer by jeroen 16 July 2018, 14:13

View original

6 replies

takashi
Rank
Userlevel 2
Badge +17
Do you intend to merge the features in the order of arriving? i.e. merge the 1st supplier to the 1st requestor, merge the 2nd supplier to the 2nd requestor, merge the 3rd supplier to the 3rd requestor, ... and so on

 

david_r
Rank
Userlevel 4

If possible, consider deciding that the "sql_statement" should always return a column with a fixed name using column alias, e.g. "oid". For example:

select t.objectid as OID, t.*
from my_table t

That way you can expose "OID" in your workspace and use it in the FeatureMerger, without having to know in advance which column it represents.

jeroen
Rank
Badge +7

Thanks!

atm i use this:

SELECT M.id as ID
,Kf1.f1 AS Street,
Kf2.f2 AS city 
K.fx.fx AS other attributes till max join limit is reached
FROM 2m1 M
LEFT JOIN 1k2 Kf1 ON M.f1=Kf1.id
LEFT JOIN 1k2 Kf2 ON M.f2=Kf2.id
etcetera

The id is the attribute used for merging the objects and identifying an object during the process. This is the only attribute that is present in both the A and B (and later C, D ect.) part of the dataset. Thats why i use the attribute exposer so i can use it in the feature merger. 

Without the schemasetter I only get the geometry in the GML and it does not export the other attributes. Now atleast i get the atributes of part A (requestor). Could it be that hidden attributes get removed when entering a featuremerger?

 

takashi
Rank
Userlevel 2
Badge +17

Thanks!

atm i use this:

SELECT M.id as ID
,Kf1.f1 AS Street,
Kf2.f2 AS city 
K.fx.fx AS other attributes till max join limit is reached
FROM 2m1 M
LEFT JOIN 1k2 Kf1 ON M.f1=Kf1.id
LEFT JOIN 1k2 Kf2 ON M.f2=Kf2.id
etcetera

The id is the attribute used for merging the objects and identifying an object during the process. This is the only attribute that is present in both the A and B (and later C, D ect.) part of the dataset. Thats why i use the attribute exposer so i can use it in the feature merger. 

Without the schemasetter I only get the geometry in the GML and it does not export the other attributes. Now atleast i get the atributes of part A (requestor). Could it be that hidden attributes get removed when entering a featuremerger?

 

No, hidden attributes won't be removed through merging process itself.

 

However, supplier's attributes that conflict with requestor's attributes on their names will be removed by default, regardless of whether exposed or hidden. See also the Conflict Resolution parameter in the FeatureMerger.

 

Here, you should be aware that the conflict resolution rules will also be applied to list attributes.

 

The SchemaSetter generates a list attribute called "attribute{}" consisting of "attribute{}.name" and "attribute{}.fme_data_type". The list is the body of schema definition.

 

In the FeatureMerger, if supplier's "attribute{i}.name" and "attribute{i}.fme_data_type" (i = 0, 1, 2, ....) conflict with requestor's "attribute{i}.name" and "attribute{i}.fme_data_type", supplier's schema definition won't be preserved. It could be your problem.

 

 

jeroen
Rank
Badge +7
No, hidden attributes won't be removed through merging process itself.

 

However, supplier's attributes that conflict with requestor's attributes on their names will be removed by default, regardless of whether exposed or hidden. See also the Conflict Resolution parameter in the FeatureMerger.

 

Here, you should be aware that the conflict resolution rules will also be applied to list attributes.

 

The SchemaSetter generates a list attribute called "attribute{}" consisting of "attribute{}.name" and "attribute{}.fme_data_type". The list is the body of schema definition.

 

In the FeatureMerger, if supplier's "attribute{i}.name" and "attribute{i}.fme_data_type" (i = 0, 1, 2, ....) conflict with requestor's "attribute{i}.name" and "attribute{i}.fme_data_type", supplier's schema definition won't be preserved. It could be your problem.

 

 

So in theory i could force the listname to be different and then the FeatureMerger does not see it as a conflict, am i correct? So, I tried copying the schemasetter to a new custom transformer and edit the pythonscript. So i have 2 different schemasetters.

 

 

Old:

 

else:
   #add schema attributes to feature
   nfeature.setAttribute("attribute{"+str(i)+"}.fme_data_type",type)
   nfeature.setAttribute("attribute{"+str(i)+"}.name",six.text_type(att))
   nfeature.setAttribute(att,feature.getAttribute(att))
   i+=1;

 

New:

 

else:
   #add schema attributes to feature
   nfeature.setAttribute("attributes{"+str(i)+"}.fme_data_type",type)
   nfeature.setAttribute("attributes{"+str(i)+"}.name",six.text_type(att))
   nfeature.setAttribute(att,feature.getAttribute(att))
   i+=1;

 

But it seems to be that this does not work in my flow. 

 

 

jeroen
Rank
Badge +7

OK... Moving the SchemaSetter after the merge did the trick.

With this flow I can now create fully dynamically generated GML

Reply


Community Stats

28,688
Posts
109,428
Replies
36,543
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.