Change geometry type in a dynamic workflow

I might be missing some of the question, but in writing to PostGIS you do not need to specify the geometry data type.  The PostGIS Writer with its embedded PostGIS API will do this for you.

Eg. Using an FME PostGIS Writer with Table Handling as “Create if Needed” will, within the Writer’s PostGIS API, execute something like this during the initial part of the FME write for you:

CREATE TABLE IF NOT EXISTS mytable (myfield varchar, geom geometry);

ie. FME will automatically create a new PostGIS Geometry Field for you in any newly created Table during the write.

In terms of how spatial data flow works in the FME workspace it will be:

1. Source data = SQL Server Geometry Data Type
2. → Transformed into FME Feature Spatial Data Type by the FME SQL Server Spatial Reader.  FME holds the geometry data as its own native FME spatial data type whilst processing within the workspace.
3. → FME spatial data transformed into PostGIS Geometry Data Type by the FME PostGIS Writer.

Noting that PostGIS geometry data type fields will store any type of vector shapes, and does not need separate tables for points, polygons, polylines etc.:  If there is however a requirement to split the data into separate Points, Lines, Areas tables, this can be done dynamically using the GeometryFilter Transformer.

The different Ports of this Eg. Point, Line, Area can then be sent to PostGIS Writer(s) that have their Writer Geometry Parameters set with additional settings to put an extra constraint on the PostGIS geometry field created for what geometry types PostGIS will allow to be inserted/updated so that during the Writers initial table creation it should execute something more like this.

CREATE TABLE IF NOT EXISTS mytable (myfield varchar, geom geometry(LINESTRING));

Thank you for your reply. 

I know that you dont need to specify the geomtry type in postgis. But in my case i need to. The use of native geometry type in SQL Server is actually not correct to the specification i am working with. It is also used as a method to validate the geometry. e.g not allow to store any other geometry type than specified for the dataset.

Using GeometryFilter does not affect how the table will be created. It will still be created as geom geometry(geometryz, 4326) or similar.
I need it to be geom geometry(polygon, 4326) etc

One problem is if you want to read the table from QGIS desktop. If you can store multiple geometry types in on table it becomes duplciate in the database list view.

Thats a great answer! Thank you for the details you included. Very useful, will give that a go! It looks like that would solve my Challenges. Thank you