how to perform a selective ST_Split?

Question

I need to cut different lines with an identical geometry but different attributes (eg. colour) with a set of points. The points also have the attribute colour. My knife points should only cut the lines with the same colour value. Red points should cut only red lines, green points should only cut green lines and so on...
I tried the following:

with knife as(
    select st_union(geom) as geom, colour
    from points
    group by colour)
select lines.colour,(st_dump(st_split(lines.geom,knife.geom))).geom as geom
from lines, knife
where lines.colour=knife.colour

Sadly, my 'selective knife' isn't so selective and cuts all lines regardless of their colour. Can anybody help?

Edit:
@JimJones I couldn't find a SQL-fiddle that supports the PostGIS extension. But with my data sample the knife somehow works perfectly.
I have no idea why whats wrong with my real data. The real data lines are in fact multilinestrings, could that be a problem? (I somehow struggling in creating multilinestrings with the insert-statement)
Edit2:
found a fiddle with PostGIS
db<>fiddle here

create table points(
id serial,
colour varchar,
geom geometry(point,4326)
);

create table lines(
id serial,
colour varchar,
geom geometry(linestring,4326)
);

insert into lines(colour, geom)
VALUES
('red','linestring(1 1,10 1)'),
('green','linestring(1 1,10 1)'),
('blue','linestring(1 1,10 1)');

insert into points(colour, geom)
VALUES
('red',(st_makepoint(2,1))),
('red',(st_makepoint(4,1))), 
('red',(st_makepoint(6,1))), 
('red',(st_makepoint(8,1))), 
('green',(st_makepoint(2.5,1))), 
('green',(st_makepoint(5,1))),
('green',(st_makepoint(7.5,1))),
('blue',(st_makepoint(3,1))),
('blue',(st_makepoint(6,1))),
('blue',(st_makepoint(9,1)));

with knife as(
    select st_union(geom) as geom, colour
    from points
    group by colour)
select lines.colour,(st_dump(st_split(lines.geom,knife.geom))).geom as geom
from lines, knife
where lines.colour=knife.colour
´´´


  [1]: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8642640bb690dfee7d31006a673e2dcf

user_13077778 · Answer

Disclaimer: Im a postgres beginner. Even though I'm quite sure that I found the solution to my problem, there could be something wrong. So feel free to correct me if necessary
If you want to ST_Split a line, which you formerly merged by using ST_Union, you sould use ST_Linemerge before the ST_Split and ST_Dump.
Even after a ST_Union, PostGIS seems to 'remember' that theese lines were formerly playing for different teams. So when you ST_Slit with you new knife and ST_Dump the GeometryCollection, the linestring is cut at the 'old soldering points' as well. If you ST_Linemerge after the ST_Union, PostGIS seems to iron out theese connection points and you can safely use your knife.

In this fiddle, I created 2 linetypes and some knife points. There is one solid line (blue) and some yellow line segments which are partly overlapping but all together have the same spatial extent as the blue line. I used ST_Union and ST_Collect respectively (grouped by colour) to merge the yellow line segments into one single line (with no effect on the blue line, obviously). In a second step I splitted and dumped the lines again with my knife points, one time with st_linemerge and one time without st_linemerge for the 'unioned' und 'collected' lines respectively.
The results (I counted the number of new segments and the cummulated line length for each colour) show, that only the split after the linemerged st_union gives the correct result. Using only St_Union results in the right length, but the number of linesegments is wrong. ST_Collect will keep the formerly overlapping parts,so the length as well as the number of segments are not usefull at all(for my purpose).

how to perform a selective ST_Split?

1 Answers