2

I have a number of PostgreSQL empty tables I am trying to setup for my data. I added the Point Geometry to one but later discovered it is X,Y aware and not X,Y,Z aware. How can I change this? I could only find 3D to 2D on the forms and ST_Force_3D didn't work. Also I am going to need X,Y,Z,M capability on some of my geometries; what do I need to do to impose these values to my table geometries?

1 Answer 1

7

You can alter the type and use, what I believe are called regclasses, to specify the type of geometry (and its SRID). So, assuming your table is called points, you can do,

ALTER TABLE points ALTER COLUMN geom type geometry (POINTZM, 4326);

However, as Postgis can't tell if you want to convert the existing 3rd value to an M or a Z value, you will get errors like:

column has M dimension but geometry does not

column has Z coordinate but geometry does not

which is also why ST_Force_3D will not work directly.

The solution is to use a USING clause to ALTER TABLE, reconstructing the point you already have, and providing a dummy variable for the missing Z or M value.

Putting this together, you can run something like,

ALTER TABLE points ALTER COLUMN point type geometry (POINTZM, 4326) 
USING ST_SetSRID(ST_MakePoint(ST_X(point), ST_Y(point), 0, ST_M(point)), 4326);

to go from XYM to XYZM

or

ALTER TABLE points ALTER COLUMN point type geometry (POINTZM, 4326) 
USING ST_SetSRID(ST_MakePoint(ST_X(point), ST_Y(point), ST_Z(point), 0), 4326);

to go from XYZ to XYZM.

Clearly, you need to supply your own SRID, or leave out -- generally not advisable.

1
  • Whereas my tables are currently empty I shouldn't have an issue with existing 3rd values, correct? Using your notes I ran ALTER TABLE (PointZM, 2954); and got a success. In QGIS the table comes in as a PointZM feature but in editing I still can only edit XYR values; not sure if I am doing something wrong in PostGIS or in QGIS. Mar 11, 2016 at 18:36

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.