7

I am able to create a geomtry column for 3d multiline using this:

    CREATE TABLE trajectories
(
  gid serial NOT NULL,
  vehid character varying(10),
  state integer,
  geom geometry(MultiLineStringZ),
  CONSTRAINT trajectories_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE trajectories
  OWNER TO postgres;

But when I tried to alter an existing table by modifying an existing geom column or adding a new one, I am getting he following error:

ERROR: Column has Z dimension but geometry does not

****** Error ******

ERROR: Column has Z dimension but geometry does not SQL state: 22023 ERROR: Column has Z dimension but geometry does not

ALTER TABLE cl ALTER COLUMN geom TYPE geometry(MultiLineStringZ)
  1. How do I modify and existing geometry column to geometry(MultiLineStringZ)?
  2. How do I add new geometry(MultiLineStringZ) column to an existing table?
15

When there is no implicit cast between two datatypes you need to state it explicitly. In your case ST_Force_3D (or ST_Force3D for PostGIS 2.1 and later) should do the trick:

ALTER TABLE cl 
  ALTER COLUMN geom TYPE geometry(MultiLineStringZ)
    USING ST_Force_3D(geom);
  • 1
    How do you do this the other way around? From MultiLineStringz to MultiLineString. – geobar Jan 5 '16 at 15:24
  • 3
    @geobar Off top of my head I'd say ST_Force_2D, if not then start a new question please. – Jakub Kania Jan 5 '16 at 15:43
1

The answer given by Jakub is correct but if you're using QGIS for editing, you may also need to update your project to reflect the new geometry type.

For example, if you've changed an existing table shapes from MultiPolygon to MultiPolygonZ, open your project file (filename.qgs) in a text editor and change the table's geometry type for each datasource it appears in. That is, change

<datasource>service='geodatabase' key='gid' srid=4326 type=MultiPolygon table="public"."shapes" (geom) sql=</datasource>

to

<datasource>service='geodatabase' key='gid' srid=4326 type=MultiPolygonZ table="public"."shapes" (geom) sql=</datasource>

Once this is done reload your project in QGIS and you should be good to go.

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.