4

I am working with a PostGIS database where I want to alter the geometry type from MultiLineString to LineString. My table used to be a LineString, so I know that all lines are simple, single line features that should be able to be represented as LineString. But as QGIS stores everything in a temporary layer as MultiLineString after any processing, I now have a MultiLineString that is not supported by the plugin I want to use.

I found this question asking for the other direction. So I know about ST_Multi and using ALTER COLUMN in general.

Is there a way to go back to a simple LineString geometry type? Either within PostGIS or in any other way withiin QGIS without loosing attributes (i.e. creating a new LineString table in PostGIS; which would be way to tedious as my table is quite big)?

Improve this question

1 Answer 1

Reset to default
3

If you are sure every record is indeed a simple line string, you can use st_geometryN(geom,1) to get the 1st (and only) simple geometry.

alter table testml alter column geom type geometry(linestring,4326) using st_geometryN(geom,1);
Improve this answer
8
  • I take that back. Execution worked, all attributes are still intact, there are features in my table, but all geometries itself are not visible any more. So maybe the geometries weren't all simple geometries after all? I had a LineString layer that I have clipped with a polygon. I assumed, all features would still be simple lines... Commented Aug 17, 2023 at 14:03
  • 1
    This kind of assumption should have been validated before. If you had multiple segments per line, well, so long for all but the 1st one. In such case, the solution would have been to create a new table and to select/insert attributes + each individual parts using st_dump Commented Aug 17, 2023 at 14:24
  • 1
    This answer is incorrect. The alter table statement deleted all the geometry because st_geometryn is 1 based, not 0. So you set all the geometry to null with that update statement. Commented Aug 17, 2023 at 19:51
  • 1
    @jbalk well spotted, thanks, I have updated the answer! Hum, I wonder why st_geometryN doesn't throw an error when called with 0 (or negative) parameter Commented Aug 17, 2023 at 19:56
  • 2
    for those interested, here is the request so it throws an error Commented Aug 17, 2023 at 20:12

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.