How to check if multilinestring really is multilinestring?

Question

I have a huge database with a road network, and the geometry type is MULTILINESTRING. I would like to filter out the MULTILINESTRINGS with topological errors. Both the lines on the left side and on the right side are one-one record, made of two lines. Now on the right side they connect, so it doesn't really bother me, I can merge them later without a topological error. However on the left side they don't connect, but they still are one record. What I've tried so far:

SELECT gid 
FROM myschema.roads 
WHERE (
NOT ST_Equals(ST_Endpoint(ST_GeometryN(the_geom,1 )),ST_Startpoint(ST_GeometryN(the_geom,2 ))) 
    AND NOT ST_Equals(ST_Endpoint(ST_GeometryN(the_geom,2 )),ST_Startpoint(ST_GeometryN(the_geom,1 )))
)

If I could say that the MULTILINESTRINGS are made up of maximum two lines, it would work I assume. Unfortunatelly some of them are made up of 10-20 lines, and I cannot be sure that the line parts are folowwing each other in an ascending or descending order. So extending my SQL script is not an option in my opinion.

(I'm using QGIS with a PostGIS database, but I also posess ArcMap.)

Would ST_GeometryType do what you want? `SELECT ST_GeometryType('LINESTRING (30 10, 10 30, 40 40)'), ST_GeometryType('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'), ST_GeometryType('MULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))');` at least so you can identify which records are multilinestrings — Jim Jones, Jul 09 '20 at 08:16
Unfortunatelly the problem is, that most of them are multilinestrings. — 588chm, Jul 09 '20 at 08:18
So I am not sure if I understood your question. You want a way to identify multilinestrings and at the same time check if they are following each other like a path? — Jim Jones, Jul 09 '20 at 08:22
Sorry, I've sent only the first half of my comment. So yes, if a multilinestring is continuous it's alright for me. I only want to identify the ones with a gap. So ST_GeometryType wouldn't do what I need. Neither this (even though this is a nice tool to identify the real multilinestrings): `SELECT * FROM myschema.roads WHERE ST_GeometryN(the_geom,2) IS NOT NULL` — 588chm, Jul 09 '20 at 08:26

Jim Jones · Accepted Answer · 2020-07-11T07:33:14.267

If you're simply looking for a way to identify which MultiLineStrings contain more than one line you can simply use ST_LineMerge, then ST_Dump and count the returning LineStrings. In case a geometry contains non continuous lines the query will return a count bigger than 1, e.g.

WITH j (geom) AS (
  VALUES ('MULTILINESTRING((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))'),
         ('MULTILINESTRING((10 10, 20 20, 10 40),(10 40, 30 30, 40 20, 30 10))'))
SELECT geom,(SELECT count(*) FROM ST_Dump(ST_LineMerge(geom))) 
FROM j;

                                geom                                 | count 
---------------------------------------------------------------------+-------
 MULTILINESTRING((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10)) |     2
 MULTILINESTRING((10 10, 20 20, 10 40),(10 40, 30 30, 40 20, 30 10)) |     1
(2 Zeilen)

Another alternative is to use ST_NumGeometries after applying ST_LineMerge, e.g.

WITH j (geom) AS (
  VALUES ('MULTILINESTRING((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))'),
         ('MULTILINESTRING((10 10, 20 20, 10 40),(10 40, 30 30, 40 20, 30 10))'))
SELECT geom,ST_NumGeometries(ST_LineMerge(geom)) AS count
FROM j;
                                geom                                 | count 
---------------------------------------------------------------------+-------
 MULTILINESTRING((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10)) |     2
 MULTILINESTRING((10 10, 20 20, 10 40),(10 40, 30 30, 40 20, 30 10)) |     1
(2 Zeilen)

Thank you! Seems like once again I couldn't phrase my real problem. I also find a solution similat to yours, based on your idea. Since `ST_LineMerge` returns `MULTILINESTRINGS` when it cannot create `LINESTRING`, where this code returns `MULTILINESTRINGS`, there must be an error: `SELECT gid, ST_GeometryType(ST_LineMerge(the_geom)) FROM myschema.roads` Even though this error is not necessarily a gap, it can be a `MULTILINESTRING` made up of three lines connecting in one point, but I would like to repair those as well. — 588chm, Jul 09 '20 at 10:28

score 1 · Answer 2 · answered Jul 09 '20 at 08:31

1

You could use this function to check if the multilinestring is connected:

CREATE OR REPLACE FUNCTION is_connected(g geometry(MultiLineString)) RETURNS boolean
   LANGUAGE plpgsql AS
$$DECLARE
   i integer;
   point geometry := NULL;
   part geometry;
BEGIN
   FOR i IN 1..ST_NumGeometries(g) LOOP
      part := ST_GeometryN(g, i);

      IF NOT ST_Equals(point, ST_Startpoint(part)) THEN
         RETURN FALSE;
      END IF;

      point := ST_Endpoint(part);
   END LOOP;

   RETURN TRUE;
END;$$;

answered Jul 09 '20 at 08:31

Laurenz Albe

209,280
17
206
263

Thank you for your help! I can see the logic behind it, but for some reason it gives me back false even for some single linestrings. I think I just found a simple solution to my problem. – 588chm Jul 09 '20 at 10:23
2

Right, that function expects a multilinestring as input. If you gave other geometries as well, you have to modify the function. – Laurenz Albe Jul 09 '20 at 10:51

How to check if multilinestring really is multilinestring?

2 Answers2