QGIS and PostGIS, dimensions problems

Question

I have a point-layer i Postgis v12 which is ST_point, ST_NDims = 3.

When creating a new point i QGIS and then tries to save it, I get an error:

Column has Z dimension but geometry does not

When looking in to the object identifier the derived attributes only contains X and Y. However, the rest of the points that loads from the database has a Z-value set to 0 attached to them. So i get that my created point dosen´t have a Z-value.

Isn't it possible to create new data in 3 dimensional tables from Postgis in QGIS? Even with a deafult value of 0, I don´t even need to specify the Z-value at this moment.

EDIT

If I save the layer in a QGIS-project and open the .qgs-file and look into the datasource-tag for the the layer I have:

type=Point

When changing this to:

type=PointZ

saves the project and try to create a new object, it works fine.

I then go on to Postigs to run the following alter to the table:

ALTER TABLE cl ALTER COLUMN geom TYPE geometry(PointZ) USING ST_Force3D(geom);

However, when checking the geometry type again it still remain ST_POINT... Also when trying to create a new point after the "ALTER" of the table, type=Point in the .qgs-file.

How come that I can create a new feature when I change the type to "type=pointZ" in the project? Even if the table is in 3D I can create and store features. And why can't QGIS read this geometry from Postgis without have to set i manually in a project? As suggested below, I get the correct typ from the query: ST_AsText Seems to me that QGIS reads ST_GeometryType istead of ST_AsText, or?

Answer 1

Since the values of Z are at 0, you can create a new table on PostGIS (or update) without a z value by using ST_Force2D function.

For example :

DROP TABLE IF EXISTS new_table_without_z_value ; -- replace with the name of your future table
CREATE TABLE new_table_without_z_value AS -- replace with the name of your future table
SELECT ST_Force2D(geom) as geom -- replace with the name of your geometry column
FROM your_table_of_points ; -- replace with the name of your table

Later, you can add a Z column on QGIS or PostGIS.

Edit of my answer:

The ST_GeometryType returns ST_Point even if you have a Z-point geometry. On the other hand, if you use the ST_AsText function, you'll see that it is a Z-point.

Answer 2

QGIS works in 2 dimensions for most its implicit functionality; there are tools to add Z values to the source layer, either from attribute or secondary data, but the edit mode will coerce the geometry type into 2D.

I suggest to work with a View here, having 2D geometries and an INSTEAD OF trigger in place to reroute edits to the 3D base table:

• create the View that will be the working layer
  Needs a column to hold Z values (_z); you can also add all relevant columns here.

  CREATE OR REPLACE VIEW <name_your_view> AS 
      SELECT  id,
              ST_Z(geom) AS _z,
              ST_Force2D(geom)::GEOMETRY(POINT, 4326) AS geom
      FROM    <your_table>
  ;
  

• create the trigger function that will reroute any DDLs on the View
  Note that you will need to handle any additional column in the View in each rerouted DDL!

  CREATE OR REPLACE FUNCTION <name_your_view>_delupsert_trigger_func()
    RETURNS TRIGGER AS
  
    $TRIGGER$
    BEGIN
  
      IF (TG_OP = 'UPDATE') THEN
  
        UPDATE <your_table>
            SET geom = ST_SetSRID(ST_MakePoint(ST_X(COALESCE(NEW.geom, OLD.geom)), ST_Y(COALESCE(NEW.geom, OLD.geom)), COALESCE(NEW._z, ST_Z(OLD.geom))), 4326)
            WHERE id = OLD.id
        ;
  
        RETURN NEW;
  
      ELSIF (TG_OP = 'INSERT') THEN
  
        INSERT INTO <your_table> (geom)
            VALUES (ST_SetSRID(ST_MakePoint(ST_X(NEW.geom), ST_Y(NEW.geom), COALESCE(NEW._z, 0)),4326))
        ;
  
        RETURN NEW;
  
      ELSIF (TG_OP = 'DELETE') THEN
  
        DELETE FROM <your_table>
            WHERE id = OLD.id
        ;
  
        RETURN NULL;
  
      END IF;
  
    END;
    $TRIGGER$
  
  LANGUAGE plpgsql
  ;
  

• Create the actual trigger on the View

  CREATE TRIGGER <name_your_view>_delupsert_trigger
      INSTEAD OF INSERT OR UPDATE OR DELETE ON <name_your_view>
      FOR EACH ROW EXECUTE PROCEDURE <name_your_view>_delupsert_trigger_func()
  ;
  

When adding or updating a point, the given _z attribute value will be added as true Z coordinate, or defaults to 0 for new points.

Answer 3

This problem happens to me everytime the tables I work with have not the appropriate dimension, geometry type or srid. To check this, you should query the geometry_columns table in Postgis:

SELECT * FROM GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA='your_schema' and F_TABLE_NAME='your_table';

You should get something like:

Then, look at the coord_dimension, type and srid columns to check everything is working fine. If you wish to work with 3D data, your type should not be "GEOMETRY", but "POINT", "LINESTRING" or any other specific type. To change the relevant records in the geometry_colums table, run the following query:

ALTER TABLE your_schema.your_table   
ALTER COLUMN your_geometry_column TYPE geometry(new_geometry_typeZ,your_srid)
USING ST_Force3D(your_geometry_column );

After this, your tables will be correctly defined, but to be able to work properly with them in QGIS, it is not enough to reload them: you need to remove the layers, update the connection to the database and then add the layers again (otherwise, you will still see the layer as 2D). Once this is done, QGIS should be able to recognize the layer as a 3D layer and the new elements created will be 3D.

Hope this helps.