Skip to main content

I'm trying to map a Smallworld table to a different schema in our Postgres SDE. I have one Excel file with the schema for the writer and another Excel file with a worksheet for mapping from the smallworld field to the fields that exist in the schema for the writer. This works when I translate to ffs (even though I get some error messages). But when I use the same workspace to translate to Postgres SDE it fails. Eventually I will need to map multiple Smallworld tables to the Postgres SDE. I have attached the workspace, the log file and the two Excel files. I'm wondering if having two excel files is part of the problem. But when I tried to accomplish all this with a single Excel file I was getting errors. Thoughts?

Hello @angela

 

I was unable to see the parameters of your 'schemamapper' due to the difference in location of the Excel file. However, do you have both rules set up(as with it being dynamic, having to map feature types along with the attributes). If you try setting the SchemaMapper with the following rules:

 

Set SchemaMapping table: FeatureTypeswFields

 

 

Here is a screenshot of the rule settings:

If that does not work please let me know, and I will keep trying some alternatives.


So in your example you're not using the excel file added as a resource? In my example I have one added as a resource. I was actually using Attribute mapper to change the fme_feature _type name. So that was being handled there. I'm confused why you can't see the Excel files I sent.


In your example, where do the attributes get their types etc?


So in your example you're not using the excel file added as a resource? In my example I have one added as a resource. I was actually using Attribute mapper to change the fme_feature _type name. So that was being handled there. I'm confused why you can't see the Excel files I sent.

Hi @angela

 

I can't see the rules on your schema mapper, due to it thinking the excel file is located on your drive. When I change the excel location to my drive all the parameters start fresh.

 

 

I added your excel file to the SchemaMapper(file titled: sw-esrimapper2.xlsx) and set the rules to the ones linked in the picture. You can carry out further mapping(creating an additional rule for the datatypes, or anything else you want to map that is contained within the Excel file) does that make sense?

 

 

Some articles/tutorials that may give you some examples for the SchemaMapper configuration:

 

https://knowledge.safe.com/articles/1136/schemamapper-transformer-tutorial.html

 

https://knowledge.safe.com/articles/1159/schemamapper-use-case-example.html

 

 

Let me know if that helps.

 

 


Hello @angela

 

I was unable to see the parameters of your 'schemamapper' due to the difference in location of the Excel file. However, do you have both rules set up(as with it being dynamic, having to map feature types along with the attributes). If you try setting the SchemaMapper with the following rules:

 

Set SchemaMapping table: FeatureTypeswFields

 

 

Here is a screenshot of the rule settings:

If that does not work please let me know, and I will keep trying some alternatives.

I switched my workspace to try to use the method you described but that brought up the same issue I had earlier this week (and which I posted as a question) where it requires a second connection to Smallworld and that second connection hangs. So unfortunately this isn't an option for us.

 

 


Hello @angela

I am going to take a look at that Smallworld hanging issue, I suspect this may be a PR. I am going to try and reproduce it here. In the meantime, I have made some adjustments after talking to a dynamic expert. The following adjustments have been made to try out:

 

 

1. Remove the Excel Reader Resource, and add a Resource Reader using the Schema From Table format(using the 'swtoesrifeaturetypemappers' file).

 

2. Set the SchemaMapper to the 'swtoesrimapper2' file, this will map the attributes only.

 

3. Set the writer to Dynamic, and change the schema definition to the Schema From Table option.

If you notice features from the reader not matching the features in your SchemaMapper, you will have to do a Feature Type map as well(I mentioned that in a previous post in the Question).

 

 

Let me know if that helps. I have attached the workspace with the above adjustments added.

 

modifiedworkspace.fmwt

resultwhenscalingup.png Thanks @trentatsafe. Your example works with a single reader. The problem is when we start to scale up. See attachment to see what happens when we add a second reader (which actually is from the same Smallworld table so has the same attributes but a different geometry).


Just an update for this question: the issue turned out to be a PR as a result of the Schema feature(in this case an excel file) containing more fields than required. The workaround was to have two separate excel files(one contains the schema for the SchemaMapper, while the other is for the writer)

As such I have filed a PR. Once it is resolved I will update this posting with the fix.

In the mean time, if doing Feature-type mapping and Attribute mapping in a dynamic setting, it is recommended to have two separate schema files to ensure the writer does not get confused.


Reply