I would like to create a workspace that reads all tables that start with the same prefix. I then want to save the result per table as a shape file. If a table is modified by a add or remove an attribute, the workspace should pick this up automatically. Is that possible?
My first thought was to work with a dynamic reader, but then I get stuck that it will retrieve all tables, including in other schemas within the database.
The second approach is, I create a query that generates select statements and puts these statements in a text file. I then read the text file with a textline reader and install an SQLExecuter. That's all going well. What I run into is that the SQLExceuter cannot automatically expose all attributes.
Page 1 / 1
How I do this:
Depending on the database type, find a source where all tables are listed. In PostGIS this is information_schema.tables. This can be done using a SQLExceuter or a FeatureReader. Initiating a FeatureReader can be done using a Creator.
Feed the resulting features with the table names into a second FeatureReader. This will be a generic one. Also read the schemafeatures.
Connect the schema- and datafeatures to a FeatureWriter and set it to dynamic.
Issues youy might have are with attribute name length, for shapefiles these are limited to 10 characters. If possible, I would not use shape but GeoPackage.
If you really really really need shapes, alternative is to truncate attribute names (BulkAttributeRenamer) and generate schema features (SchemaScanner) but I would not advise this as a first option as it is tricky to set up.
Hi @fme_hans,
as far as I can understand, you don’t need to explicitely expose the attributes.
You definitely need to setup a dyamic workspace (at least a dynamic writer), using a SchemaScanner to read dynamically the schema from all the feature types you want to write and send both output of ports Output and <Schema> to the shapefile writer (see attached screenshot of the configuration of a CSV writer, which is basically the same as the shapefile writer). This will pick up every added or removed attributes from the tables.
Be aware that the feature types attributes are hidden during the flow, but you can see them in the Feature Information panel of the Data Inspector.
Hope that helps!
My tables are in Oracle so I can get the necessary table names from cols with a check to all_objects where the type should be TABLE. Then I have a list of table names of which I want to export all attributes. The feature reader appears to read all attributes but does not expose them. I seem to have to do that with the <generic> port option. But then I have to enter them all myself again. That is not useful because each table has different attributes. So how can I make the feature reader automatically expose all attributes?
Yes, generic is good, you can now use the Schema Feature to use to write the result.
This way, the attributes and the geometry type are set by the Schema Feature.
What should be written in the field "Features Types to read" in the feature reader?
What should be written in the field "Features Types to read" in the feature reader?
The tablename you got with the previous step.
I don't understand it yet.I now have the following. a creator, SQLExecuter that retrieves the table names, then a FeatureReader. This reads the correct number of records but has no attributes in the output.
The features have attributes, but they are not exposed. And they do not need to be exposed to be written, that is what the schema feature fixes for you.
I attached a working sample simulating what you need.
Creator to initiate
SQLExecutor which returns a feature for each table in the geopackage.
FeatureReader to read the data for each table in the geopackage.
FeatureWriter to write the data dynamically to shape. Shapefile name, attributes and geometry type are defined by the SchemaFeature.
I believe the confusion new users have with unexposed attributes originates from the “Visual Preview” window. I believe that is a bad development in workbench for a couple of reasons:
As it takes up space, it is often positioned over the “Translation Log”. Because of this, users do not see if warnings or errors passes the log when running a translation.
Because the “Visual Preview” is so small, the “Feature Information” window is off by default. And I believe the “Visual Preview” is important to analyse what information is actually attached to each feature.
All the data specialists I work with, be it GIS, CAD, Excel / database, all work with multiple screens. I believe this is the standard for our kind of job. So if this is the case, go to Tools → Options, tab Workbench and check “Inspect with Data Inspector when Visual Preview window is closed.” Then close the “Visual Preview” window. Now, if you click on a magnify icon on a cached transformer, it will open in the Data Inspector. Select a feature and look for the “Feature Information” window. Look for “Unexposed Attributes”. This will change how you work.
Thanks for the exampe. I finally got it working. In the FeatureWriter I have to explicitly indicate which geometry type it concerns. If I leave the choice to FME, nothing is written. Strange but now it seems to work. (at least for my test table).
