<div dir="ltr">Thanks for comparing them. I can't at the moment see why the LATERAL version would not be correct. Would it be possible to get a copy of the datasets (or a subset of them?)</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Sep 8, 2019 at 11:21 PM <<a href="mailto:paul.malm@lfv.se">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_-7381663027024142391WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Hi, I made 2 tests.<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Drop TABLE IF EXISTS result;<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Create table result as<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">WITH<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">data AS (<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> SELECT * FROM "origdata" AS t(id, geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">),<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">cutter AS (<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> SELECT * FROM "cut_Line" AS t(geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT id,
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> CASE WHEN cutting IS NULL THEN geom
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> ELSE st_collectionExtract(ST_Split( geom, cutting ),2) END AS geom
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> FROM (SELECT id, d.geom geom,
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> (SELECT ST_Collect(c.geom) geom<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> FROM cutter c WHERE ST_Intersects(d.geom, c.geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> ) AS cutting<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> FROM data d) AS t;<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">This took 1 min 30 sec, and the result was as I wanted (all lines present and cutted)<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)"><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)">And Martins new theorie:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Drop TABLE IF EXISTS result;<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Create table result as<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">WITH<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">data AS (<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> SELECT * FROM origdata AS t(id, geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">),<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">cutter AS (<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> SELECT * FROM "cut_Line" AS t(geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT id, CASE WHEN cut.geom IS NULL THEN d.geom
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> ELSE ST_Split( d.geom, cut.geom ) END AS geom
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> FROM data d<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> CROSS JOIN LATERAL
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> (SELECT ST_Collect(c.geom) geom<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> FROM cutter c WHERE ST_Intersects(d.geom, c.geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> ) AS cut;<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">1 min and 12 sec, all lines are cutted but here are missing a lot of cutted lines (is it because of lines intersected several times )<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:10pt;font-family:Tahoma,sans-serif">Från:</span></b><span style="font-size:10pt;font-family:Tahoma,sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>]
<b>För </b>Martin Davis<br>
<b>Skickat:</b> den 9 september 2019 00:10<br>
<b>Till:</b> PostGIS Users Discussion<br>
<b>Ämne:</b> Re: [postgis-users] breake lines<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">You have to love how SQL always provides multiple ways of expressing things (and the options increase with each new release of the standard). <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">So here's Solution #3 to this problem. This one uses the nifty Postgres JOIN LATERAL functionality. I think it's the simplest of the 3 alternatives, but I'm not sure how it's performance will compare. Perhaps Paul could provide some
performance numbers for comparison? (And of course confirm that it works in the same way as the other queries)<u></u><u></u></p>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">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, CASE WHEN cut.geom IS NULL THEN d.geom <br>
ELSE ST_Split( d.geom, cut.geom ) END AS geom <br>
FROM data d<br>
CROSS JOIN LATERAL <br>
(SELECT ST_Collect(c.geom) geom<br>
FROM cutter c WHERE ST_Intersects(d.geom, c.geom)<br>
) AS cut;<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">On Thu, Sep 5, 2019 at 3:01 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank">mtnclimb@gmail.com</a>> wrote:<u></u><u></u></p>
</div>
<blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;margin-right:0cm">
<div>
<p class="MsoNormal">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. <u></u><u></u></p>
</div>
</blockquote>
</div>
</div>
</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>