3

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?

  • Are you sure cable_temp.geom is not a GeometryCollection? – lynxlynxlynx Feb 2 '18 at 19:30
5

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.

  • Thank you for your answer. After running the first query I have only two results for the type existing in my table : Null (for objects without geometry) and ST_MultilineString. So no GeometryCollection there. Could it be the empty geometries ? – GuiOm Clair Feb 5 '18 at 8:21
  • Hmm, weird. I checked here, ST_LineMerge with Null geometry only returns Null object. Try executing: SELECT id_cable, ST_GeometryType(ST_LineMerge(geom)), ST_GeometryType(geom) FROM cable_temp WHERE ST_GeometryType(ST_LineMerge(geom)) = 'ST_GeometryCollection'. This should give you a list of features that are returning problematically, together with id and original geom type. Are there particular features with this behaviour? or is your entire table returning a collection? – Roberto Ribeiro Feb 5 '18 at 10:10
  • Indeed, I've got 8 features being returned. I'll check them to see, and give feedback when I do. I suspect more than 1 vertex on the same place... – GuiOm Clair Feb 5 '18 at 10:20
  • And so, 4 of the features where positive to ST_IsEmpty(), and the 4 others where negative to ST_IsValid(). No more GeometryCollection. Thanks a lot :) – GuiOm Clair Feb 5 '18 at 10:37
  • 1
    @GuiOmClair Great that you found the problematic geometries. I didn't even know that a Linestring could be invalid! Living and learning. Updated my answer to reflect your findings. – Roberto Ribeiro Feb 5 '18 at 14:28

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.