# Conversion from Point 2D to Point 3D with measures

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?

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.

• 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