1

I am struggling to make work the ST_Split function, since it seems to be the way to go. I have this situation, and I want to end with the line split in three on according to the points: enter image description here

And here is my code, the cable2's geometry is MultiLineString and the boite :

WITH brs AS (SELECT row_number() over(), boite.geom FROM boite, cable2
    WHERE st_intersects(boite.geom, cable2.geom)
    ORDER BY st_line_locate_point(st_linemerge(cable2.geom),boite.geom)),
    cables AS (SELECT (st_dump(st_split(cable2.geom, brs.geom))).geom FROM brs, cable2)
SELECT st_astext(geom) FROM cables WHERE st_endpoint(st_linemerge(geom)) = (SELECT geom FROM brs WHERE brs.row_number = 4)

Here I am just trying to get the last geometry, but I get this as a result, where the first and last line are the same, and anyway the endpoint is always the same :

"LINESTRING(949758.692587021 6567419.6350433,949790.867103036 6567425.81704638,949825.149120101 6567402.63453484,949835.265125137 6567380.857024)"
"LINESTRING(949825.149120101 6567402.63453484,949835.265125137 6567380.857024)"
"LINESTRING(949790.867103036 6567425.81704638,949825.149120101 6567402.63453484,949835.265125137 6567380.857024)"
"LINESTRING(949758.692587021 6567419.6350433,949790.867103036 6567425.81704638,949825.149120101 6567402.63453484,949835.265125137 6567380.857024)"

Would you happen to know what I am doing wrong? I tried to look in other similar requests here but unsuccessfully.

**EDIT ** Using the solution proposed by Redoute, and since I am trying to implement that in a trigger, I fail to do so, and here is my code :

CREATE OR REPLACE FUNCTION public.split_cable()
  RETURNS trigger AS
$BODY$
DECLARE compte integer;
DECLARE i integer := 2;
BEGIN
compte = (SELECT count(*) FROM boite WHERE st_intersects(boite.geom, new.geom));

WHILE i < compte LOOP
    WITH brs AS (SELECT row_number() over(), boite.geom FROM boite, cable2
        WHERE st_intersects(boite.geom, new.geom)
-- here the ORDER BY serve to get the "boite" objects in a specific order
        ORDER BY st_linelocatepoint(st_linemerge(new.geom),boite.geom)),
        brs2 AS (SELECT st_union(geom) AS geom FROM brs),
        cables AS (SELECT (st_dump(st_split(new.geom, brs2.geom))).geom FROM brs2)
    INSERT INTO cable2 (geom) VALUES (
    SELECT st_multi(cables.geom) FROM cables WHERE st_startpoint(geom) = (SELECT geom FROM brs WHERE brs.row_number = i));
    i = i + 1;
END LOOP;

new.geom = (WITH brs AS (SELECT row_number() over(), boite.geom FROM boite, cable2
        WHERE st_intersects(boite.geom, new.geom)
        ORDER BY st_linelocatepoint(st_linemerge(new.geom),boite.geom)),
        brs2 AS (SELECT st_union(geom) as geom from brs),
        cables AS (SELECT (st_dump(st_split(new.geom, brs2.geom))).geom FROM brs2)
        SELECT st_multi(cables.geom) FROM cables WHERE st_startpoint(geom) = (SELECT geom FROM brs WHERE brs.row_number = 1));
RETURN new;
END
$BODY$
  LANGUAGE plpgsql;

EDIT 2 As suggested by Redoute, I asked the question here : https://stackoverflow.com/questions/46444147/postgis-2-3-split-a-line-by-points

  • It's possible, but not very easy to split linestrings on a variable number of arbitrary points. The image looks as if you want to split your linestrings on all intermediate vertices. That would be more easy and doesn't need table boite. Which version do you need? – Redoute Sep 25 '17 at 14:58
  • I am not entirely sure to understand what you mean, I have a set of points which occur to be vertices, and I want in that case to split the line in 3. The thing is not all vertices have a point, and when they don't I don't want to split. – GuiOm Clair Sep 25 '17 at 15:03
  • I don't have a problem writing a loop to adapt the function to my need, it is just that I cannot make work the actual action of the loop. – GuiOm Clair Sep 25 '17 at 15:03
  • Can you simplify your SQL, remove the where id_cable = 10719 type things, as these do not help understanding, and please use caps for SQL keywords, it is much easier to read. Also, what on earth does ORDER BY ST_Line_Locate_Point do? If the linestring is properly formed that seems unecessary. – John Powell Sep 25 '17 at 15:19
  • @JohnPowellakaBarça Thank you for your comment, I did use caps for the SQL keywords in my edit. As for the ORDER BY I thought it would allow me to get the "boite" objects in a specific order, do you think it is useless or a bad idea? – GuiOm Clair Sep 26 '17 at 12:10
1

Problem with your code is: ST_Split() gets one linestring and one point and returns two linestrings. You pair the linestring with each split point, so you get two parts for every point. The two parts together are the whole original linestring. EDIT: If you want to apply several splits on one linestring, you would have to split by a multipoint:

-- define multilinestring
WITH ls (geom) AS (VALUES
    ('MULTILINESTRING((949758.692587021 6567419.6350433,949790.867103036
6567425.81704638,949825.149120101 6567402.63453484,949835.265125137
6567380.857024))'::geometry(multilinestring))),
-- define split points
sp (geom) AS (VALUES
    ('POINT(949790.867103036 6567425.81704638)'::geometry(point)),
    ('POINT(949825.149120101 6567402.63453484)'::geometry(point))),
-- make multipoint and split
s1 AS (SELECT
   ST_Split(ls.geom, (SELECT ST_Collect(sp.geom) FROM sp)) geom
   FROM ls)
-- print WKT
SELECT ST_AsEWKT(d.geom)
    FROM s1, ST_Dump(s1.geom) AS d (path, geom);

Yesterday I posted a more complicated query: It splits your example linestring in three parts as wanted. It first builds single rows for every segment you need (should also work for really multipart line geometries). It then uses ST_LineSubstring() instead of ST_Split(), because we need the location anyway in the earlier sorting step. Advantage is you can get results ordered by direction of the line. Of course you may merge several subqueries in one. I think the form with several CTE is easier to read and understand.

-- define multilinestring
WITH ls (geom) AS (VALUES
    ('MULTILINESTRING((949758.692587021 6567419.6350433,949790.867103036 6567425.81704638,949825.149120101 6567402.63453484,949835.265125137 6567380.857024))'::geometry(multilinestring))),
-- define split points
sp (geom) AS (VALUES
    ('POINT(949790.867103036 6567425.81704638)'::geometry(point)),
    ('POINT(949825.149120101 6567402.63453484)'::geometry(point))),
-- dump multilinestring
s1 AS (SELECT
    d.path,
    d.line
    FROM ls, ST_Dump(ls.geom) AS d (path, line)),
-- combine and add start-/endpoints
s2 AS (SELECT
    s1.path, s1.line, sp.geom point
    FROM s1, sp
    UNION SELECT
    path, line, ST_StartPoint(line)
    FROM s1
    UNION SELECT
    path, line, ST_EndPoint(line)
    FROM s1),
-- interpolate points and get rid of unvalid pairs
s3 AS (SELECT
    path, line,
    st_line_locate_point(line, point) pos
    FROM s2
    WHERE ST_Intersects(line, point)),
-- create one row per new segment
s4 AS (SELECT
    path, line,
    pos pos1,
    lead(pos) OVER (ORDER BY pos) pos2
    FROM s3),
-- build new linestring
s5 AS (SELECT
    path,
    pos1,
    ST_LineSubstring(line, pos1, pos2) geom
    FROM s4
    WHERE pos2 is not null)
-- print WKT
SELECT ST_AsEWKT(geom)
    FROM s5
    ORDER BY path, pos1;
  • Thank you for your answer it is very helpful. I tried to implement the first part of it in the trigger (the ultimate use for this process) that is presented in my edited question. But for some reason I do not get good results. In the example shown in the question, I have the middle part which get a NULL geometry, any idea why? – GuiOm Clair Sep 26 '17 at 12:09

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.