1

I have a table in my data base. This table was created with differents fields as 'x','y' and 'z'.

Now I need to make a view to generate a geometry column, but I found problems, because the created geometry field is not correctly named. I need to force my column "geom geometry" to "geom geometry(PointZ, 25830)".

I did this:

st_force_3dz(st_setsrid(st_makepoint(table.x, table.y, table.z), 25830))

Any solutions?

  • 1
    What exactly doesn't work? When I do something like SELECT ST_Zmflag(ST_SetSRID(ST_MakePoint(50,100,5), 25830)) it returns 2 which signifies the geometry is 3dz. – Evil Genius Nov 12 '13 at 12:30
  • The problem is not the content, the problem is the name of the column. The right way is column geom of type geometry(PointZ, 25830), but my result is a column geom of type geometry (it's not exact). So when I try to see this table in QGIS, the table is not detected. – Acicate Nov 12 '13 at 14:31
  • 1
    I think your problem is more with QGIS than it is with PostGIS. ST_Force_3DZ only sets the Z to 0 if it doesn't exist. From the looks of this issue, QGIS doesn't support 3D geometry from PostGIS. – Evil Genius Nov 12 '13 at 14:45
  • I don´t think so about QGIS. I have some tables with 3D geometry and I can see them perfectly. – Acicate Nov 13 '13 at 15:16
  • 1
    Yes, the tables show up, but QGIS isn't aware of the Z. At least that's the case for QGIS 2.0.1 with PostGIS 2. Try identifying a 3D geometry...you'll only see X and Y coordinates. At any rate, I've tried creating a view with the example point from my first comment and it shows up just fine in QGIS. I can add the layer and see both it's (2D) geometry and attributes. – Evil Genius Nov 13 '13 at 17:39
2

I think the problem is your view does not have any primary key.

This is how I test:

  1. Create a test table with x, y, z fields as you described and insert some data:

    create table test.tbl
    (id serial primary key,
    x double precision,
    y double precision,
    z double precision);

    insert into test.tbl (x, y, z) values (0, 0, 0);
    insert into test.tbl (x, y, z) values (0, 2, 2);
    insert into test.tbl (x, y, z) values (2, 0, 0);
    insert into test.tbl (x, y, z) values (2, 2, 5);

  2. Create two views, one with primary key and one without:

    create view test.view_with_id
    as
    select id, st_force_3dz(st_setsrid(st_makepoint(x, y, z), 25830))
    from test.tbl;

    create view test.view_without_id
    as
    select st_force_3dz(st_setsrid(st_makepoint(x, y, z), 25830))
    from test.tbl;

  3. Load the two views into Qgis: enter image description here There will be error and details in PostGIS tab of the Log Messages for the view with no id column: enter image description here The view with id column can be viewed as normal layer.

|improve this answer|||||
  • Thanks Cao Minh Tu. But I must to say I thought my problem was in relation with primary key of my view, but my all my views have primary keys. – Acicate Nov 14 '13 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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