Is there any possibility to change the default geometry column 'geom' on postgis readers to some other column with a different geometry like geom2 ?
Thanks
If you're reading PostGIS then by default FME will pick the first geometry column. If you have multiple geomety columns then FME will only read the first column. FME does not yet support multiple geometry columns on PostGIS. I'm not sure if you could workaround this by formulating a query in SQLExecutor or SQLCreator to select the appropriate geometry
+17- Contributor
PostGres has the option to create a view (see this link: create view).
FME can select from views
So if you include only the required geometry column in the view and use the view in FME that should work.
+2- Contributor
markatsafe wrote:
If you're reading PostGIS then by default FME will pick the first geometry column. If you have multiple geomety columns then FME will only read the first column. FME does not yet support multiple geometry columns on PostGIS. I'm not sure if you could workaround this by formulating a query in SQLExecutor or SQLCreator to select the appropriate geometry
Thanks Mark . Is readers ready for multi geometry columns support planned for near future releases ?
Regards
+2- Contributor
erik_jan wrote:
PostGres has the option to create a view (see this link: create view).
FME can select from views
So if you include only the required geometry column in the view and use the view in FME that should work.
Thanks erik_jan will look into that workaround - or simple create new table -
- Contributor
markatsafe wrote:
If you're reading PostGIS then by default FME will pick the first geometry column. If you have multiple geomety columns then FME will only read the first column. FME does not yet support multiple geometry columns on PostGIS. I'm not sure if you could workaround this by formulating a query in SQLExecutor or SQLCreator to select the appropriate geometry
markatsafe Do you know if multiple geometry columns on PostGIS will be supported in the future? I'm facing the same issue as jorge_vidinha with 2020.0 and it's annoying (I saw that in Oracle and SQL Server it's supported).
Thanks in advance for the feedback.
+35- Celebrity
rbovard wrote:
markatsafe Do you know if multiple geometry columns on PostGIS will be supported in the future? I'm facing the same issue as jorge_vidinha with 2020.0 and it's annoying (I saw that in Oracle and SQL Server it's supported).
Thanks in advance for the feedback.
As as tip for using the SQLCreator or SQLExecutor to see what kind of structure you need for the request you can do the following.
Set up a FeatureReader (or reader) to the table to you want to read and put in an invalid where clause. In the warning or error that you get FME will return the SQL request it makes to the database including all the column names and how it handles the geometry.
For PostGIS FME will use something like the below for the geometry column.
SELECT ST_AsBinary(<geom_column>) (https://postgis.net/docs/ST_AsBinary.html)
If you use the same call in an SQLCreator or executor you should get the same as from the writer (as in FME should automatically set the geometry)
If you want to use a different column you can change which column is using the ST_AsBinary.
If you use both columns with ST_AsBinary (fme will pick one to use as the geometry) and the other will come in as an attribute which you can use the GeometryReplacer set to fme-binary to get the other column as geoemtry as well in a separate path and then aggregate together based on an ID.
This is the best workaround I have be able to come up with which doesn't require first creating and extra view and reading from two tables. In the end the view approach could be faster but I'm so far unconvinced.
Good luck
- Contributor
virtualcitymatt wrote:
As as tip for using the SQLCreator or SQLExecutor to see what kind of structure you need for the request you can do the following.
Set up a FeatureReader (or reader) to the table to you want to read and put in an invalid where clause. In the warning or error that you get FME will return the SQL request it makes to the database including all the column names and how it handles the geometry.
For PostGIS FME will use something like the below for the geometry column.
SELECT ST_AsBinary(<geom_column>) (https://postgis.net/docs/ST_AsBinary.html)
If you use the same call in an SQLCreator or executor you should get the same as from the writer (as in FME should automatically set the geometry)
If you want to use a different column you can change which column is using the ST_AsBinary.
If you use both columns with ST_AsBinary (fme will pick one to use as the geometry) and the other will come in as an attribute which you can use the GeometryReplacer set to fme-binary to get the other column as geoemtry as well in a separate path and then aggregate together based on an ID.
This is the best workaround I have be able to come up with which doesn't require first creating and extra view and reading from two tables. In the end the view approach could be faster but I'm so far unconvinced.
Good luck
Thanks a lot for your workaround!
I like the idea to not create an extra view in the DB and the way you propose to handle these multiple geom fields and keep the link between the features with an ID.
Best regards.
+7- Contributor
rbovard wrote:
Thanks a lot for your workaround!
I like the idea to not create an extra view in the DB and the way you propose to handle these multiple geom fields and keep the link between the features with an ID.
Best regards.
Faced the same issue today, used an sql creator and executor to do the tric. Enhancement idea is here :
https://knowledge.safe.com/idea/117563/native-support-for-postgresql-multiple-geometry-co.html? ;)
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.