Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • PostGIS writer used dynamically: How to reduce schemata? How to set correct geometry types?

I need to write features, coming from a WFS, into PostGIS (I'm using FME 2017.1 Build 17532).

Requirements:

 

1. As the WFS delivers lots of feature types I want to do it dynamically (Dynamic Schema Definition)

 

2. I need to change table names to lowercase (not only attribute names)

 

3. I want to dynamically delete all attributes containing "gml_" or "xsi_nil" in their names.

 

No. 2:

 

I copy the format attribute fme_feature_type to a new attribute that I call lowercase_feature_type and change its case. The original fme_feature_type goes into "Schema Definition Name" (so that the writer finds the original schema from the reader), lowercase_feature_type goes into "Table Name".

 

Problem: Apparently there's still a bug in the PostGIS writer so that in the dynamic case the "Table Qualifier" parameter is not respected at all. I solved this by concatenating the desired schema name to lowercase_feature_type separated by a point before assigning it to "Table Name". So far so good.

No 3:

 

How can I delete attributes from the schema before writing the PostGIS schema?

 

The BulkAttributeRemover (with my regular expression "(gml_)|(xsi_nil)") does the job but only in the data flow, it doesn't change the schema written by the PostGIS writer (as the writer reads the schema directly from the reader).

 

How can I somehow generate dynamically reduced Schemas (build schema Features?) so that I can select "Schema From Schema Feature" in the "Schema Source" parameter in the writer's feature type properties?

No 1:

 

Apparently, there's another bug in the PostGIS writer (https://knowledge.safe.com/questions/32101/featurewriter-postgis-writes-generic-geometry-colu.html):

 

When using it dynamically, it always defines the generic PostGIS geometry type (geometry(Geometry,ySRID])) although in the feature type properties (of the writer) the parameter "Geometry" is set to "From Schema Definition" or to "First Feature Defines Geometry Type". I need that the specific geometry types from the reader (Point, Line, Polygon...) are preserved because QGIS has problems with the generic PostGIS geometry type (duplicate entries when you try to add PostGIS layers).

 

Is there a workaround to make the writer define the correct geometry types apart from defining the whole workspace in static mode (what is not really an option because the table schemata can change in production)?

Any suggestions are very welcome.

I'm new to dynamic schema writing, but this is how I got mine to work with a requirement similar to your #1 and #2.

I have a set of point and polygon feature classes from a GDB. For some layers, I have to rename them before writing to PostGIS. Here's my layout:

Reading:

 

FeatureReader of GDB exposes both schema and data. Output ports to one, generic port. Connect both Schema and Generic output ports to the next transformer.

Table renaming:

 

Use Tester search among the fme_feature_type attributes for the tables to rename. Then AttributeCreator to replace those fme_feature_type's with the name I want. You can probably also use StringReplacer here.

Mapping to Postgres table:

 

AttributeCreator for dst_table = @Value(postgis_schema).@Value(fme_feature_type)

Writing:

 

PostGIS Writer has these params set:
  • General tab
    • Table name: dst_table
    • Table qualifier: postgis_schema
    • Geometry: rthis is grayed out for me]
    • Dynamic Properties: Check

       


      • Schema sources: "Schema From Schema Feature"
      • Schema definition name: fme_feature_type
  • User Attributes tab
    • Attribute Definition: Dynamic
    • For attributes, I only set an auto-increment and primary key OID field and another field that tracks loading, LOADDATE

I hope this helps.

I am also interested to know how to set the geometry type when loading data. The older versions used to have a selection called "Create
Generic Geometry Columns" but I don't see this in 2018.0. I see that you can expose postgis_type, and I tried setting that, but it seemed to have no effect.

 

 

Link to documentation: https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/postgis/PostGIS_writer.htm

 

 

Bryan

 

 

I created something similar for pulling content from wfs to spatialite, I suspect you will find it useful to answer Note 3 and Note 2 wfs-to-spatialite-example.fmw

Reply


Community Stats

30,108
Posts
114,295
Replies
37,744
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.