PostGIS – Convert Geometry Type from MultiLineString to LineString

geometrygeometry-conversionpostgispostgresqlqgis

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

Best Answer

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);
Related Question