Question

Error writing to Postgis table with both 2D and 3D geometry columns

  • 4 March 2021
  • 2 replies
  • 25 views
R
Rank
Badge

Hi,

 

I have a postgis (12) table with 2 geometry columns.  

The first column is defined as geometry (geometry, 3857). 

The second column is defined as geometry (geometryZ, 3857). 

Consequently, the postgis geometry_columns view shows 2 entries, indicating 2 dimensions for the first column, and 3 dimensions for the second column.

 

When writing 3D data to this table, I select the 3D column in the writer properties.

 

This gives a warning in the log:

 

POSTGIS Writer: PostGIS existing table has a 2D constraint

 

My data is forced to 2D, but this is not accepted by Postgis, and my translation fails.

 

When I remove the 2D geometry column form my table, all 3D data is inserted without errors.

Apart from just using the 3D column and forcing all data to 3D in FME and then back again to 2D in my application, I do not see another solution.

 

Is there another way to work around this? 

 

I'm using FME FME(R) 2020.1.0.1 (20200710 - Build 20596 - WIN64)

 

Thanks,

 

2 replies

redgeographics
Rank
Userlevel 4
Badge +25

It looks like there's currently no support for PostGIS multiple geometry columns in FME, although the most recent information I could find for it was from 2020.

There are two ideas regarding this:

https://community.safe.com/s/idea/0874Q000000TlswQAC/detail

https://community.safe.com/s/idea/0874Q000000TksqQAC/detail

The first one seems to imply that writing the data through an SQLExecutor would work, but that'd be a pretty cumbersome way.

R
Rank
Badge

Hi, thanks for the reply.

An SQLExecuter is cumbersome indeed, and does not really perform that well since records are inserted 1 by 1, if I'm not mistaken. With 10000+ records to go I'd rather avoid that.

 

Even though there are multiple geometry columns in the table I write to, I can only specify 1. This is perfectly fine for my use case.

Personally I think the problem is that FME does not check the dimension restriction correctly. When I specify a 3D column, FME should not force my features to 2D based on a dimension restriction for a column I'm not writing to.

But probably things are more complicated under the hood. Good to know there is not easy solution available.

I'll do some reengineering to work around this.

Reply


Community Stats

28,661
Posts
109,318
Replies
36,528
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.