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)?

1 Answer 1

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);
9
  • worked like a charm, thank you!
    – e-shirt
    Aug 17, 2023 at 14:00
  • 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
    – JGH
    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.
    – jbalk
    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
    – JGH
    Aug 17, 2023 at 19:56
  • 2
    for those interested, here is the request so it throws an error
    – JGH
    Aug 17, 2023 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.