8

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?

3 Answers 3

4

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.

2
  • Yeah, I know, but that dosen't awnser my question. However, I still want to be able to edit 3D-values in the future. Or use other tables which are stored in a 3 dimensional tables. Please take a look at my edit in the post. Something else is wrong, but cand find out where the problem comes from.
    – Eetmyhouse
    Feb 13, 2020 at 9:36
  • I edited my answer. Feb 13, 2020 at 10:10
4

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.

1
  • I will take a look at this, to see if it can work as a short term solution. I just can't understand why I can store points from digitizing when the "type" is rewritten to "PointZ" instead of "Point" in the project file. It seems to me that must be some kind of a bug.
    – Eetmyhouse
    Feb 13, 2020 at 14:19
1

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:

![Query result

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.