<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=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
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-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.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 lang="EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I have a layer with lines that I have buffered to a polygon layer, those polygons (not multipolygons) are unioned and containes holes.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I would like to create a line layer (from another line layer) with the line parts that are within buffered area in the polygon layer.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I’ve tried like this:<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">update linelayer b set the_geom = ST_MULTI(ST_Intersection(b.the_geom, p.the_geom)) FROM polygonlayer p WHERE ST_Intersects(b.the_geom,
p.the_geom)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">This leaves me with the line parts inside the buffered area and all lines that had no intersection with the buffered polygons. That’s ok, but now I have to erase the lines that had no intersection with the polygons.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I run makevalid on the tables, to be sure<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF;background:#E8F2FE">UPDATE polygonlayer SET the_geom = ST_Makevalid(the_geom) WHERE st_isvalid(the_geom)=false</span><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF;background:#E8F2FE">UPDATE
</span><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">linelayer
<span style="background:#E8F2FE">SET the_geom = ST_Makevalid(the_geom) WHERE st_isvalid(the_geom)=false</span><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I create a primary key<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">ALTER TABLE linelayer ADD COLUMN \"pkkey\" serial NOT NULL PRIMARY KEY<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I reindex the tables, to be sure:<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">REINDEX TABLE linelayer</span><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">REINDEX TABLE polygonlayer<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I change to LineStrings just to be sure not having several linestings in a MultiLineString
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">CREATE TABLE dumpedlines AS SELECT *, (ST_Dump(the_geom)).geom AS the_geom2 FROM lineLayer<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">ALTER TABLE dumpedlines DROP COLUMN IF EXISTS the_geom</span><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">ALTER TABLE dumpedlines RENAME COLUMN the_geom2 TO the_geom</span><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">ALTER TABLE dumpedlines ALTER COLUMN the_geom TYPE geometry(LineString, 32631)</span><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#BB005E"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Then I try to delete the lines outside the buffered polygons:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;font-family:Consolas;color:#2A00FF">Delete from dumpedlines b WHERE b.pkkey NOT IN (SELECT b.pkkey FROM dumpedlines b, polygon layer c WHERE ST_within(b.the_geom, c.the_geom))<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">The result is not correct according to me, all the lines outside the buffered polygons are erased, but also SOME lines inside the buffered polygons (that are already cut by the polygons). I’ve also tried with _<i>ST</i>_within
but with the same result.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Any ideas?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Thanks in advance,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Paul<o:p></o:p></span></p>
</div>
</body>
</html>