You can try kmeans-postgresql. For the installation you can follow this instructions.
Kmeans is clustering point data, with a predefined so-called K integer (number 5 in the query).
SELECT kmeans(ARRAY[ST_X(geom), ST_Y(geom)], 5)
Nevertheless it would be difficult to get correct polygons, because of the irregular distribution of your points.
Maybe you can combine the points and assign them a gid or something.
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.
Best Answer
Select features in QGIS and click Merge Selected Features button on Advanced Digitizing toolbar.