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?

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: There will be error and details in PostGIS tab of the Log Messages for the view with no id column: The view with id column can be viewed as normal layer.