Creating many workbenches over the years, and this is very rarely a problem.
But it is very rarely a problem because FME automagically selects the postgres writer writes the data using the correct geometry type, not because I have been able to specify.
However, here i have a workbench reading in spreadsheet data using a [vertex creator] on the X/Y columns, but when I write the data out to postgis/postgres i find that it arrives there in linestring format. At least, QGIS then sees the layers as linestring!
What is super wierd, is that this workbench has been reused for several years, each year with the postgis writer being modified to create a new table for the years data. All previous years it has created point data, this year it has decided on polygon. This happens when i modify the workbench in FME 2022.1.3, or in the current FME 2024.2.3.
So, obviously this is unsatisfactory, and I want to be able to tell FME to create that postgres.postgis table with the geometry type of point/linestring/poly/multi-x/etc.
Why is this impossible to do so?
It feels like it ought to be a function in the postgis writer, but it is not there.
This documentation indicates it might be a functions of the database connection: PostGIS Writer Parameters
“Create Generic Geometry Columns
This setting applies at generation time, not at translation time. The default setting of "no" indicates that you want to create geometrically constrained geometry columns on the destination tables. For example, a POINT geometry table would be restricted only to points. Now you have the option to create generic or non-constrained geometry column types.
Effectively this means you can insert multiple geometry types into one table. Specifically the geometry column is created to have the generic type GEOMETRY and there are no constraints placed on the geometry types allowed.”
However, I cannot find a “Create Generic Geometry Columns” parameter anywhere in the database connection here.
Questions:
Why does FME mostly get it right, but sometimes wrong?
Why isn’t selecting a geometry type an easily accessible parameter?
Where am I going wrong, and how do I rectify matters?
Kind regards. M
Best answer by bwn
There are quite a few things to touch on there.
First, PostGIS does not have tables that are specifically constrained to one geometry type. They can contain any combination of points, lines, polygons etc. Further, PostGIS is not restricted to one geometry field per table either.
So it isn’t FME itself that is getting a “wrong” table geometry type, it is instead downstream applications like QGIS that when it analysed that PostGIS table that have inferred from the PostGIS data an unexpected geometry type.
QGIS, from memory, will first look in the PostGIS geometry columns metadata table for a hint (In system table geometry_columns). The first diagnostic step I would have a look at is what geometry type, if any, has been set in the metadata table for the geometry field in question. If there is not an entry in this table, then again I think QGIS will determine this by sampling the data instead of say the first 1,000 rows to find if these are just one geometry type, or multiple.
The second diagnostic step is to check that FME is actually writing just Points. VertexCreator does not guarantee this, particularly if it is not in “Replace with Point” mode. Don’t forget that FME will in its Excel Readers etc. try to “Auto-Detect” X, Y fields and try to create a geometry from the Reader itself. Using VertexCreator in an “Add Point” mode can transform the Reader output geometry into an Eg. Polyline.
What I would do as a sanity check is use a GeometryFilter and create Ports for “Line” and “Area” and send these to an Inspector to see if anything arrives at the Inspector, indicating that FME is trying to Output Lines or Areas.
Thirdly, related to this is that PostGIS does have options to constrain Inserted rows to a single Geometry Type, but this is not automatic (as above it is capable of storing multiple geometry types per table by design). I would suspect that FME only tries to apply a constraint if the data arriving at the PostGIS Writer is only one single geometry type, else it will instead create the table with a generic “Geometry” data type field. This is then likely why there is no explicit setting in FME to try to force an Eg. Geometry Type = POINT etc. since it will, at Table “Generation” time, detect if the Features arriving are a single geometry type or not, and if not, will create the table without any Geometry Type constraint.
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
First, PostGIS does not have tables that are specifically constrained to one geometry type. They can contain any combination of points, lines, polygons etc. Further, PostGIS is not restricted to one geometry field per table either.
So it isn’t FME itself that is getting a “wrong” table geometry type, it is instead downstream applications like QGIS that when it analysed that PostGIS table that have inferred from the PostGIS data an unexpected geometry type.
QGIS, from memory, will first look in the PostGIS geometry columns metadata table for a hint (In system table geometry_columns). The first diagnostic step I would have a look at is what geometry type, if any, has been set in the metadata table for the geometry field in question. If there is not an entry in this table, then again I think QGIS will determine this by sampling the data instead of say the first 1,000 rows to find if these are just one geometry type, or multiple.
The second diagnostic step is to check that FME is actually writing just Points. VertexCreator does not guarantee this, particularly if it is not in “Replace with Point” mode. Don’t forget that FME will in its Excel Readers etc. try to “Auto-Detect” X, Y fields and try to create a geometry from the Reader itself. Using VertexCreator in an “Add Point” mode can transform the Reader output geometry into an Eg. Polyline.
What I would do as a sanity check is use a GeometryFilter and create Ports for “Line” and “Area” and send these to an Inspector to see if anything arrives at the Inspector, indicating that FME is trying to Output Lines or Areas.
Thirdly, related to this is that PostGIS does have options to constrain Inserted rows to a single Geometry Type, but this is not automatic (as above it is capable of storing multiple geometry types per table by design). I would suspect that FME only tries to apply a constraint if the data arriving at the PostGIS Writer is only one single geometry type, else it will instead create the table with a generic “Geometry” data type field. This is then likely why there is no explicit setting in FME to try to force an Eg. Geometry Type = POINT etc. since it will, at Table “Generation” time, detect if the Features arriving are a single geometry type or not, and if not, will create the table without any Geometry Type constraint.
Thank you very much for the detailed answer, it does provide a lot of useful context to why the various components behave the way they do.
I did find a solution: which was to upgrade all of the transformers/readers/writers when running the workbench in 2024.2.3. Running the workbench after that did drop-and-create a postgres table with geometry that QGIS correctly recognised as point data.
Not sure why this changed, as I was originally trying to re-run the same workbench on the new data in 2022.1.3 and saw the error first there with the new data, and then again in the newer FME.
Hey ho, it’s working now regardless. Thank you again.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
