<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: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">Thanks, Alexandre!<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></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>Alexandre Neto<br>
<b>Skickat:</b> den 14 februari 2020 00:24<br>
<b>Till:</b> PostGIS Users Discussion<br>
<b>Ämne:</b> Re: [postgis-users] help with a SQL Command<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">Hi Paul,<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Your first Select statement, (before the UNION ALL) has no WHERE clauses, therefore it will return all rows of the "shifted" table. If the polygon geometry intersects the line, the geometry is splitted, otherwise it will return the original
geometry.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">So, you are getting all you need from the first SELECT and you don't need that the UNION ALL part.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Now, if the polygons table is very big and complex, this might not be the most effective method because you are still trying to split all the polygons even if they are not even close to that line. You can probably save some time by only
trying to split the polygons that intersects your line, something like this<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">WITH </span> blade AS (<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">), cutted as (</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">SELECT a.*, </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> CASE WHEN St_intersects (a.the_geom, b.geom) THEN</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> ST_Split(a.the_geom, b.geom)<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> ELSE<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> a.the_geom<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> END as geom1<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">FROM shifted AS a, blade AS b</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">SELECT *, (St_dump(geom1)),geom as geom</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Alexandre Neto</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Support team<br>
<a href="http://www.qcooperative.net">www.qcooperative.net</a><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">@alexnetogeo<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">A quinta, 13/02/2020, 13:55, <<a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</a>> escreveu:<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">Hi list!<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I have a problem with a sql command, I would be grateful if someone please could tell me what I’m doing wrong.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I have a polygon layer where some polygons exceeds the 180/-180 border.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I would like to cut the polygons with a line from 180,90 to 180,-90.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I’ve got this far:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Drop table if exists cutted;
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">CREATE TABLE "cutted" as WITH
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> blade AS (
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">SELECT a.*, (ST_Dump(ST_Split(a.the_geom, b.geom))).geom FROM shifted AS a, blade AS b
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">UNION ALL
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">SELECT *, the_geom FROM shifted AS a WHERE NOT EXISTS (
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> SELECT 1 FROM blade AS b WHERE ST_Intersects(a.the_geom, b.geom)</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">);</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">ALTER TABLE cutted DROP COLUMN IF EXISTS the_geom;</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">ALTER TABLE cutted RENAME COLUMN geom TO the_geom;</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:9.0pt"> </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:9.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">It cuts the exceeding polygons nice but the uncut objects are duplicated in the result layer (cutted).</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Thanks,</span><span lang="EN-US" style="font-size:9.0pt">
</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:9.0pt"> </span><span style="font-size:9.0pt"><b>Fel! Inget filnamn angivet.</b></span><o:p></o:p></p>
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style="border-collapse:collapse">
<tbody>
<tr style="height:36.85pt">
<td width="420" valign="top" style="width:315.0pt;padding:0cm 0cm 0cm 2.25pt;height:36.85pt">
<p class="MsoNormal" style="margin-top:2.0pt;mso-margin-bottom-alt:auto;line-height:10.0pt">
<span lang="EN-US" style="font-size:9.0pt;color:#333333"> </span><o:p></o:p></p>
<p class="MsoNormal" style="margin-top:2.0pt;mso-margin-bottom-alt:auto;line-height:10.0pt">
<b><span lang="EN-US" style="font-size:9.0pt;color:#333333"> Paul Malm</span></b><span lang="EN-US" style="font-size:9.0pt;color:#333333"><br>
<br>
Operations AIM</span><o:p></o:p></p>
</td>
</tr>
<tr style="height:24.1pt">
<td width="420" valign="top" style="width:315.0pt;padding:0cm 0cm 0cm 2.25pt;height:24.1pt">
<p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:12.0pt;line-height:9.0pt">
<span lang="EN-US" style="font-size:9.0pt;color:#333333"> Direct +46 (0)8 797 70 23 Mobile: +46 (0)708 601115<br>
<a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</a> </span>
<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;line-height:9.0pt">
<span lang="EN-US" style="font-size:9.0pt;color:#333333"> </span><span style="font-size:9.0pt;color:#333333">Mail & Visit: Löjtnantsgatan 25, 115 50 Stockholm, Sweden</span><o:p></o:p></p>
</td>
</tr>
<tr>
<td width="420" valign="top" style="width:315.0pt;padding:0cm 0cm 0cm 2.25pt">
<p class="MsoNormal" style="margin-top:2.0pt;mso-margin-bottom-alt:auto;line-height:9.0pt">
<span lang="EN-US" style="font-size:9.0pt;color:#333333"> Unit phone: +46 (0)8 797 70 20
<br>
</span><span style="font-size:9.0pt;color:#333333"><a href="http://www.lfv.se/" target="_blank"><span lang="EN-US">www.lfv.se</span></a></span><o:p></o:p></p>
</td>
</tr>
<tr style="height:3.7pt">
<td width="420" valign="top" style="width:315.0pt;padding:0cm 0cm 0cm 2.25pt;height:3.7pt">
<p class="MsoNormal" style="margin-top:6.0pt;mso-margin-bottom-alt:auto;mso-line-height-alt:3.7pt">
<span lang="EN-US" style="font-size:9.0pt;color:#00CC00"> Please consider the enviroment before printing this e-mail message.</span><o:p></o:p></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US" style="font-size:9.0pt"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal">_______________________________________________<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" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</blockquote>
</div>
</div>
</body>
</html>