<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.E-postmall17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="SV" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">This is how far I got today:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">LayerToBeSplitted is of type MultiLinestrings<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">DROP TABLE IF EXISTS tmp;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">DROP TABLE IF EXISTS tmp2;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">CREATE TABLE tmp AS
<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">WITH<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">data AS (<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> SELECT * FROM "LayerToBeSplitted" AS t<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">),<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">cutter AS (<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> SELECT the_geom FROM "LayerToSplitWith" AS t<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">)<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">SELECT *, ST_Split(d.the_geom,
<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> (SELECT ST_Collect(c.the_geom) geom<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> FROM cutter c WHERE ST_Intersects(d.the_geom, c.the_geom)<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> )) the_geom2<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">FROM data d;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Clean up the table tmp<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">ALTER TABLE tmp DROP COLUMN IF EXISTS the_geom;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Result is all the columns from LayerToBeSplitted the_geom2 = a geometryColletion with "linestrings" and empty geometries<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">the table only includes the splitted lines that were intersected by the cutting layer (LayerToSplitWith)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Create a table with a geometry column geom, who has no empty geometries and is of type Linestring
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">CREATE TABLE tmp2 as SELECT *, (ST_DUMP(<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> st_collectionExtract( t.the_geom2, 2))).geom
<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">FROM tmp AS t WHERE t.the_geom2 IS NOT null;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Clean up the table tmp2<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">ALTER TABLE tmp2 DROP COLUMN IF EXISTS the_geom2;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">ALTER TABLE tmp2 RENAME COLUMN geom TO the_geom;<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Result: a table with splitted Linsestrings.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">there are no MultiLinestrings geometries and no empty geometries, and all of the columns from LayerToBeSplitted are included.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Now I would like to add all the lines with all attributes from LayerToBeSplitted, who were NOT intersected by the cutting layer (LayerToSplitWith).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I tried this among other things, without any luck:
</span><span lang="EN-US" style="font-size:11.0pt;font-family:Wingdings;color:#1F497D">L</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D">INSERT INTO tmp2 (the_geom)
<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> SELECT DISTINCT(<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> St_dump(a.the_geom) </span></i></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">--
multi to single<b><i><o:p></o:p></i></b></span></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> ).geom
<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><b><i><span lang="EN-US" style="font-family:"Calibri","sans-serif";color:#1F497D"> FROM "LayerToBeSplitted" a, "LayerToSplitWith" AS b WHERE NOT ST_Intersects(a.the_geom, b.the_geom);<o:p></o:p></span></i></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">get the attributes to be transferred to tmp2 for those new lines.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I can't use: INSERT INTO tmp2 SELECT... since the order of the columns is different between the two tables, I think...<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Kind regards,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Paul
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Från:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>För </b>Martin Davis<br>
<b>Skickat:</b> den 5 september 2019 00:02<br>
<b>Till:</b> PostGIS Users Discussion<br>
<b>Ämne:</b> Re: [postgis-users] breake lines<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Wed, Sep 4, 2019 at 4:54 AM <<a href="mailto:paul.malm@lfv.se">paul.malm@lfv.se</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi,
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">If I understand your question correct, yes I would like to break all lines in the intersections
with another layer.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">No I have not tried MultiLinestrings comprised of all lines that are intersecting the
lines to be broken. Don’t know how to do this. </span><span lang="EN-US" style="font-size:11.0pt;font-family:Wingdings;color:#1F497D">L</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><o:p></o:p></p>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal">Here's an example query showing how to collect all cutting lines which intersect each data line and then use them to split the data line:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">WITH<br>
data AS (<br>
SELECT * FROM (VALUES<br>
( 'LINESTRING (100 100, 400 100)'::geometry ),<br>
( 'LINESTRING (100 300, 400 300)'::geometry ),<br>
( 'LINESTRING (250 400, 400 400)'::geometry )<br>
) AS t(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 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;<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I think this should work even if the inputs are MultiLineStrings, but you'll have to verify that.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">One issue that might arise depending on your data is that ST_Split errors out if the splitting line has any portion which is collinear with a portion of the input. I guess the theoretical rationale for this is that the splitting code doesn't
know which "side" of the output to allocate the common line to. But really this isn't very useful, since there's no easy way to detect this situation and prevent it from happening. If this is an issue for you then report back to the list, and we can think
about how to fix this.<o:p></o:p></p>
</div>
</div>
</div>
</div>
</body>
</html>