Hi,I am using the FeatureWriter to write data to postGIS. I am using the featureWriter instead of the postGIS writer because I must run a series of SQL after write. I am using the writer in dynamic mode.I need to set the data as MultiPolygon, but the geometry type it is writing is just the generic "geometry". I set the Geometry under the General settings to postgis_multipolygon but it does not "stick". Every time I a open the Writer it is set back to unfilled and red. Appear to be a bug!? I am running FME Desktop 2018.Suggestions how I can overcome this?Kind Regards

FeatureWriter postGIS cannot set geometry type

+7

Hi @kristianm

Thanks for your question! This seems to be working for me so far in 2018.1.

I believe you can set SQL to run after writing with the normal PostGIS writer as well. This might be a more stable option if you can't get the FeatureWriter to work.

Hope this helps!

Nathan

+2

Hi @kristianm, I looked at our latest FME version (2018.1). You are correct, that PostGIS writer doesn't have the option to specify geometry. A few earlier build might have had this parameter exposed by mistake. Since each PostGIS table is allowed to have mixed geometry types, the writer doesn't provide the parameter to restrict it to one type.

However, if you need to only write features that are postgis_multipolygon, you can use some transformers to filter the features by geometry type, before they get into the writer.

I'm not 100% sure what postgis_multipolygon type can or can't have, or what exact restrictions you want to use for your scenario. Some likely transformers to look at would be GeometryFilter and AggregateFilter.

If you can give a bit more background on why you want to restrict the writer geometry, and what is not allowed to be written, our other experts should be able to give more detailed suggestion on how to filer the features.

K

Hi @kristianm, I looked at our latest FME version (2018.1). You are correct, that PostGIS writer doesn't have the option to specify geometry. A few earlier build might have had this parameter exposed by mistake. Since each PostGIS table is allowed to have mixed geometry types, the writer doesn't provide the parameter to restrict it to one type.

However, if you need to only write features that are postgis_multipolygon, you can use some transformers to filter the features by geometry type, before they get into the writer.

I'm not 100% sure what postgis_multipolygon type can or can't have, or what exact restrictions you want to use for your scenario. Some likely transformers to look at would be GeometryFilter and AggregateFilter.

If you can give a bit more background on why you want to restrict the writer geometry, and what is not allowed to be written, our other experts should be able to give more detailed suggestion on how to filer the features.

Thanks for your answer. I am already using some transformers to filter the features based on geometry type, that is working fine. So I am only feeding the writer the correct geometry type to the table.

The writer has a field to set geometry type. If I use the same option using the regular postGIS writer the table is correctly created with the geometry specified. Using the featureWriter this is not the case

If I set it and go re-open the writer it is se back to the default generic geometry type.

For now I am overcoming it by run SQL after write and cast the geometry e.g

ALTER COLUMN geom type geometry(MultiPolygon, 27700) using ST_Multi(geom);

But would be great to use the writer as intended.

K

Hi @kristianm

Thanks for your question! This seems to be working for me so far in 2018.1.

I believe you can set SQL to run after writing with the normal PostGIS writer as well. This might be a more stable option if you can't get the FeatureWriter to work.

Hope this helps!

Nathan

Thanks for your help. To be able to run SQL after write using the postGIS writer would solve my problem, but I cannot find the option? Do you mean hook a SQL executor after the writer? I am not sure how I can chain a SQL executor to run after the writer is complete.

Many thanks

+7

Thanks for your help. To be able to run SQL after write using the postGIS writer would solve my problem, but I cannot find the option? Do you mean hook a SQL executor after the writer? I am not sure how I can chain a SQL executor to run after the writer is complete.

Many thanks

@kristianm

In the Navigator window (at left in workbench) if you expand the PostGIS writer or FeatureWriter parameters you should be able to see an option on each to specify SQL To Run After Write.

As for the geometry type resetting on the FeatureWriter - that is bizarre. Can I ask what build and platform of FME you are using? I have checked in 2018.1 18520 on MacOS and both the PostGIS writer and FeatureWriter honour my geometry setting correctly. Perhaps other builds are affected.

Best,

Nathan

+2

Thanks for your answer. I am already using some transformers to filter the features based on geometry type, that is working fine. So I am only feeding the writer the correct geometry type to the table.

The writer has a field to set geometry type. If I use the same option using the regular postGIS writer the table is correctly created with the geometry specified. Using the featureWriter this is not the case

If I set it and go re-open the writer it is se back to the default generic geometry type.

For now I am overcoming it by run SQL after write and cast the geometry e.g

ALTER COLUMN geom type geometry(MultiPolygon, 27700) using ST_Multi(geom);

But would be great to use the writer as intended.

@kristianm Sorry, I'd missed the fact the Geometry parameter only appears if we uncheck "Create Generic Geometry Column". I can confirm Nathan's observation now. Hopefully by updating to the latest 2018.1, the problem will be resolved.

K

I have installed 2018.1 but its the same, I still cannot set the geometry type. The reader source is multipoint.

I use "From Schema Definition" This is the result in postGIS.

QGIS is trying to read the geometry type, but fails, that's why you see the same layer twice in the Data Source Manager. QGIS manage to see its a Multipoint, but ads another layer. This does not happened when its properly set to Multipoint in the table definition.

+2

I have installed 2018.1 but its the same, I still cannot set the geometry type. The reader source is multipoint.

I use "From Schema Definition" This is the result in postGIS.

QGIS is trying to read the geometry type, but fails, that's why you see the same layer twice in the Data Source Manager. QGIS manage to see its a Multipoint, but ads another layer. This does not happened when its properly set to Multipoint in the table definition.

Hi @kristianm, Thanks for showing us the behavior in a short gif. Unfortunately, we are still not able to reproduce the behavior that you are seeing.

One last thought, after upgrading to FME 2018.1, did you add a new FeatureWriter? If in a fresh new FeatureWriter, you are still seeing this behavior. Can you send us your workspace, so we can investigate further? (When you do, can you please mention this Q&A; thread, so our expert can get the context of what we discussed?) Thank you!