Creating View with new geometry field in PostGIS

Question

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?

• Evil Genius: 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.
• Acicate: 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.
• Evil Genius: 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.
• Acicate: I don´t think so about QGIS. I have some tables with 3D geometry and I can see them perfectly.
• Evil Genius: 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.

Answer 1

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.

• Acicate: 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.