I am trying to migrate my Esri enterprise geodatabase (10.2.2) on MSSQL server 2012 to PostGIS version 2.0/PostgreSQL version 9.4 using trial version of FME. I am getting the following errors;
bulk copy failed on table 'table name' using delimiter ':'. Error was Error column has Z dimension but geometry doesnot Context: Copy 'table name', line 1, column geom:''
Any suggestions on how I can resolve this issue?
The most obvious thing to me about the log is the message "Column has Z dimension, but geometry does not". That seems to imply that the actual issue is 2D geometry being written to a 3D geometry column.
There are three ways I can see this occurring, one of which relates to a known issue.
1: If your data is 2D, but you're writing to an existing table that already has a 3D geometry column, then the error would occur. To resolve this add a Z value to all features. You'd do this with the 3DForcer transformer. Even if the elevation is set to zero, it would still solve the problem. I suspect this might be the issue if you're writing to a table that already existed, rather than have FME create it.
2: If the table doesn't already exist, and you are creating it with FME, then I suspect the problem is with this known issue. Basically, the scenario is that your source data is 2D with measures, and FME misinterprets the measures and tries to create a 3D table.
I can see two solutions to that scenario. Firstly - if you don't want the measures - use a MeasureRemover transformer to get rid of them before writing the data. Secondly - if you do want the measures - create the table manually in PostGIS rather than have FME create it. That way you can set it up as 2D+measures, and FME will happily write to it.
3: One other possibility surrounds the Table Handling parameter. This can be set up to write to an existing table, create a new table if it doesn't already exist, drop and (re)create an existing table, or truncate an existing table. If - for example - you wrote 3D data and asked FME to create the table, then it would create a 3D geometry column. But if you then switched the data to a 2D version, and left the Table Handling as "Create if Needed" then FME would not drop or re-create the table, but keep the 3D version and try to write 2D data to it, thus erroring out.
The solution would be to set Table Handling to Drop and Create. You would also need to do that in scenario 2 above, after using the MeasureRemover transformer, to create the proper geometry column type.
Of course there's always the possibility that there is some other issue going on, but basically the 3DForcer or MeasureRemover transformers are what I'd try first (remembering to drop/recreate the table as necessary).
