Solved

PostGIS writer: converts 3D geometry to 2D on existing table

  • 14 April 2020
  • 3 replies
  • 42 views

Badge

When attempting to use an existing table in a PostGIS database, the PostGIS writer is coercing my 3D geometry objects into 2D. I have not seen this issue with a PostGIS writer that uses automatic table creation and the data type for the geom column is defined as GEOMETRY in both cases.

I have seen 2 different warnings regarding this:

  • POSTGIS Writer: PostGIS existing table has a 2D constraint, changing input data into 2D data
  • coerced to 2D in table 'cad.cad_geom'
  1. Why are the objects being coerced?
  2. Are there writer settings to prevent this from happening?

Here is the table definition:

CREATE TABLE IF NOT EXISTS cad.cad_geom
( id                      BIGSERIAL    PRIMARY KEY
, created                 TIMESTAMPTZ  DEFAULT CURRENT_TIMESTAMP
, updated                 TIMESTAMPTZ  DEFAULT CURRENT_TIMESTAMP
, xchg_ts                 TIMESTAMPTZ  DEFAULT NULL
, _refid                  BIGINT       DEFAULT NULL
, autocad_block_name      TEXT 
, autocad_block_number    INTEGER
, autocad_entity_handle   TEXT
, autocad_entity          TEXT
, autocad_source_filename TEXT
, geom                    GEOMETRY     NOT NULL
);

Furthermore, is there a way to have the writer respect default values when inserting into the table? For example, all timestamp values that are defaulted in the table definition are being written as NULL upon insert.

 

Thank you in advance!

icon

Best answer by markatsafe 16 April 2020, 18:55

View original

3 replies

Badge +2

@jspratt can you check the geometry_columns view using PGAdmin and make sure that the metadata for your cad.geom geometry has a coord_dimension of 3? FME automatically creates the geometry_columns entry when it creates the table based on the characteristics of first feature's geometry.

Small example: postgis3d.fmwt

Badge

@jspratt can you check the geometry_columns view using PGAdmin and make sure that the metadata for your cad.geom geometry has a coord_dimension of 3? FME automatically creates the geometry_columns entry when it creates the table based on the characteristics of first feature's geometry.

Small example: postgis3d.fmwt

I see. I've been working with multiple datasets in which the first feature read may not have been 2D previously. The geometry_columns entry shows 2 for the coord_dimension like you suspected.

I will be working with datasets that include both 2D and 3D geometries. If a 3D feature was processed first, how would the 2D objects be written? I am hoping that they would have their Z values defaulted to 0. Is there a way to force reading 3D objects before 2D?

Based on your example it seemed like the '3DForcer' transformer may be able to handle the behavior I am looking for but even after applying the transformer to the output of the reader the same result occurs.

Badge +2

I see. I've been working with multiple datasets in which the first feature read may not have been 2D previously. The geometry_columns entry shows 2 for the coord_dimension like you suspected.

I will be working with datasets that include both 2D and 3D geometries. If a 3D feature was processed first, how would the 2D objects be written? I am hoping that they would have their Z values defaulted to 0. Is there a way to force reading 3D objects before 2D?

Based on your example it seemed like the '3DForcer' transformer may be able to handle the behavior I am looking for but even after applying the transformer to the output of the reader the same result occurs.

@jspratt You can use the 3DForcer to set a default Z with Preserve Existing Z Values to set a value of 0 for 2D features. But it looks like PostGIS will use a default value of 0 if you pass a 2D feature to a 3D table.

But FME won't change an existing entry in the geometry_columns metadata table. So you'll have to make sure you make that 3D before you write your data.

Reply