<div dir="ltr">Here's a slightly different way to do the same thing. Instead of using a LEFT JOIN to find the non-split lines, it checks whether the result of the ST_Collect of intersecting lines, and then either computes the split or keeps the original input line.<div><br></div><div>WITH<br>data AS (<br> SELECT * FROM (VALUES<br> ( 1, 'LINESTRING (100 100, 400 100)'::geometry ),<br> ( 2, 'LINESTRING (100 300, 400 300)'::geometry ),<br> ( 3, 'LINESTRING (250 400, 400 400)'::geometry ),<br> ( 4, 'LINESTRING (-10 -10, -20 -20)'::geometry ),<br> ( 5, 'LINESTRING (-30 -30, -40 -40)'::geometry )<br> ) AS t(id, geom)<br>),<br>cutter AS (<br> SELECT * FROM (VALUES<br> ( 'LINESTRING (150 50, 150 350)'::geometry ),<br> ( 'LINESTRING (200 50, 200 150)'::geometry ),<br> ( 'LINESTRING (250 350, 250 250)'::geometry ),<br> ( 'LINESTRING (300 350, 300 50)'::geometry ),<br> ( 'LINESTRING (350 250, 350 450)'::geometry )<br> ) AS t(geom)<br>)<br>SELECT id, <br> CASE WHEN cutting IS NULL THEN geom <br> ELSE ST_Split( geom, cutting ) END AS geom <br>FROM (SELECT id, d.geom geom, <br> (SELECT ST_Collect(c.geom) geom<br> FROM cutter c WHERE ST_Intersects(d.geom, c.geom)<br> ) AS cutting<br> FROM data d) AS t<br>;<br></div><div>;<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Sep 5, 2019 at 2:44 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com">mtnclimb@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div><br></div></div><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Sep 5, 2019 at 6:38 AM <<a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="SV">
<div class="gmail-m_-7502413212689338110gmail-m_-6972839907290873907WordSection1">
<p class="MsoNormal"><br></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Result<b><u>: all of the lines</u></b> from LayerToBeSplitted where copied into tmp2<b><u>, not just the ones NOT intersecting</u></b>, and I don't
know how to<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">get the attributes to be transferred to tmp2 for those new lines.<br></span></p></div></div></blockquote><div><br></div>To get the lines from LayerToBeSplitted which don't intersect ANY lines from LayerToSplitWith, you need to use this SQL pattern:<div><br></div><div>SELECT d.* FROM LayerToBeSplitted d</div><div>LEFT JOIN LayerToSplitWith c ON ST_Intersects(d.geom, c.geom)</div><div>WHERE c.geom IS NULL</div><div><br></div><div>(Your query is including lines if there is SOME cutting line which isn't intersected, which will generally always be the case)</div><div><br></div><div>Here's an updated example query which does both split and non-split together:</div><div><br></div><div>WITH<br>data AS (<br> SELECT * FROM (VALUES<br> ( 1, 'LINESTRING (100 100, 400 100)'::geometry ),<br> ( 2, 'LINESTRING (100 300, 400 300)'::geometry ),<br> ( 3, 'LINESTRING (250 400, 400 400)'::geometry ),<br> ( 4, 'LINESTRING (-10 -10, -20 -20)'::geometry ),<br> ( 5, 'LINESTRING (-30 -30, -40 -40)'::geometry )<br> ) AS t(id, geom)<br>),<br>cutter AS (<br> SELECT * FROM (VALUES<br> ( 'LINESTRING (150 50, 150 350)'::geometry ),<br> ( 'LINESTRING (200 50, 200 150)'::geometry ),<br> ( 'LINESTRING (250 350, 250 250)'::geometry ),<br> ( 'LINESTRING (300 350, 300 50)'::geometry ),<br> ( 'LINESTRING (350 250, 350 450)'::geometry )<br> ) AS t(geom)<br>)<br>SELECT * FROM (SELECT id, ST_Split( d.geom, <br> (SELECT ST_Collect(c.geom) geom<br> FROM cutter c WHERE ST_Intersects(d.geom, c.geom)<br> )) geom<br> FROM data d) AS t WHERE geom IS NOT NULL<br>UNION<br>SELECT <a href="http://d.id" target="_blank">d.id</a>, d.geom<br> FROM data d<br> LEFT JOIN cutter c ON ST_Intersects(d.geom, c.geom)<br> WHERE c.geom IS NULL;<br></div><div></div><div> </div></div></div>
</blockquote></div>