[GIS] Multilinestring to Linestring PostGIS 2.3

linestringpostgis

I am trying, as the title suggests, to transform a geometry that was a MultiLinestring into a Linestring. I first create a copy of the table I want to change, as a temporary table from which I can UPDATE the geom of the real one after altering the geometry. I then use this following query:

update cablage_pays_gex.cable set geom = NULL;
alter table cablage_pays_gex.cable alter column geom type geometry(Linestring, 2154);
update cablage_pays_gex.cable 
set geom = st_linemerge(cable_temp.geom) 
from cable_temp 
where cable_temp.id_cable = cable.id_cable;

But I get this error:

Geometry type (GeometryCollection) does not match column type (LineString)

What does it mean? What do I do wrong?

Best Answer

As per the function documentation, ST_LineMerge will return a Linestring when each linestring in a MultiLinestring connects. If they do not, it'll return a MultiLinestring:

SELECT ST_AsText(ST_LineMerge(
    'MULTILINESTRING((0 0, 1 1), (1 1, 3 3))'::geometry));

> LINESTRING(0 0, 1 1, 3 3)

SELECT ST_AsText(ST_LineMerge(
    'MULTILINESTRING((0 0, 1 1), (2 2, 3 3))'::geometry));

> MULTILINESTRING((0 0, 1 1), (2 2, 3 3))

However, your error says the function is returning a GeometryCollection. As per the documentation, there are only two options when that occur:

Only use with MULTILINESTRING/LINESTRINGs. If you feed a polygon or geometry collection into this function, it will return an empty GEOMETRYCOLLECTION

This means your table either has a Polygon or a GeometryCollection data point (or many of them). To find out, you can do:

SELECT DISTINCT ST_GeometryType(geom)
FROM cable_temp

Then you can find which data points have these geometries by doing:

SELECT * FROM cable_temp
WHERE ST_GeometryType(geom) IN ('ST_Polygon', 'St_GeometryCollection')

Afterwards, you can either fix these geometries or skip them in your processing.

UPDATE

Apparently, there are (at least) two other cases in which ST_LineMerge can also return a Geometry Collection, found by OP in the comments. These are if the geometry is empty or if it's invalid. I'll be condensing the information here for future reference.

The following mock-up query specifies the different possible returns for ST_LineMerge, as well as the original geometry and if it's valid and empty.

SELECT id, ST_GeometryType(ST_LineMerge(geom)) AS linemerge, 
    ST_AsText(geom) as geom, ST_IsEmpty(geom) as empty, ST_IsValid(geom) as valid
FROM mockup_table

id | linemerge             | geom                                     | empty | valid
---+-----------------------+------------------------------------------+-------+--------
1  | ST_LineString         | LINESTRING(0 0, 1 1))                    | f     | t
2  | ST_LineString         | MULTILINESTRING((0 0, 1 1), (1 1, 2 2))  | f     | t
3  | ST_MultiLineString    | MULTILINESTRING((0 0, 1 1), (1 1, 2 2))  | f     | t 
4  |                       |                                          |       | 
5  | ST_GeometryCollection | POLYGON((0 0, 1 0, 1 1, 0 0))            | f     | t 
6  | ST_GeometryCollection | GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1)) | f     | t 
7  | ST_GeometryCollection | MULTILINESTRING EMPTY                    | t     | t 
8  | ST_GeometryCollection | MULTILINESTRING((0 0, 0 0))              | f     | f

UPDATE 2

Some points about the above table:

  • Feature ID 1 represents Linestrings and Linestring-like geometries, such as CircularStrings.
  • Feature ID 2 represents continuous MultiLinestrings and MultiLinestring-like objects, such as CompoundCurve and MultiCurve.
  • Feature ID 3 represents discontinuous MultiLinestrings and MultiLinestring-like objects, such as CompoundCurve and MultiCurve.
  • Feature ID 4 does not have a geometry (geom column is NULL), which is different from having an empty geometry (ID 7).
  • Feature ID 5 represents non-Linestring-like geometries, such as Point, Polygon, MultiPoint, MultiPolygon, and CurvePolygon.
  • Feature ID 6 represents GeometryCollections and GeometryCollection-like geometries, such as MultiSurface.
  • Mesh-like geometries, such as PolyhedralSurface and TIN, as well as Triangle, are not acceptable inputs.

As a further testing, ST_LineMerge accepts Z and M coordinates, although they don't behave well. The Z ordinal is not checked when evaluating if the linestrings touch (so MULTILINESTRINGZ((0 0 0, 1 1 1), (1 1 2, 2 2 2)) returns a merged Linestring, with the first Z value it finds). The M value is not considered and is dropped entirely.

Related Question