<div dir="ltr"><div dir="auto">Hi Paul,<div dir="auto"><br></div><div dir="auto">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.</div><div>So, you are getting all you need from the first SELECT and you don't need that the UNION ALL part.</div><div><br></div><div>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<br></div><div><br></div><div><p class="MsoNormal"><span lang="EN-US">WITH </span> blade AS (</p><p class="MsoNormal"><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></p><p class="MsoNormal"><span lang="EN-US">), cutted as (<u></u><u></u></span></p><p class="MsoNormal"><span lang="EN-US">SELECT a.*, </span></p><p class="MsoNormal"><span lang="EN-US"> CASE WHEN St_intersects (a.the_geom, b.geom) THEN</span></p><p class="MsoNormal"> ST_Split(a.the_geom, b.geom)</p><p class="MsoNormal"> ELSE</p><p class="MsoNormal"> a.the_geom</p><p class="MsoNormal"> END as geom1</p><p class="MsoNormal"><span lang="EN-US">FROM shifted AS a, blade AS b</span></p><p class="MsoNormal"><span lang="EN-US">)</span></p><p class="MsoNormal"><span lang="EN-US">SELECT *, (St_dump(geom1)),geom as geom</span></p><p class="MsoNormal"><span lang="EN-US"><br></span></p><p class="MsoNormal"><span lang="EN-US"><br></span></p><p class="MsoNormal"><span lang="EN-US">Alexandre Neto</span></p><p class="MsoNormal">Support team<br><a href="http://www.qcooperative.net">www.qcooperative.net</a></p><p class="MsoNormal"><a class="gmail_plusreply" id="plusReplyChip-0">@alexnetogeo</a><br></p><p class="MsoNormal"> <br></p></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">A quinta, 13/02/2020, 13:55, <<a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</a>> escreveu:<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>
<p class="MsoNormal">Hi list!<u></u><u></u></p>
<p class="MsoNormal"><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.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I have a polygon layer where some polygons exceeds the 180/-180 border.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I would like to cut the polygons with a line from 180,90 to 180,-90.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I’ve got this far:<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Drop table if exists cutted; <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE TABLE "cutted" as WITH <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"> blade AS ( <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"> SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT a.*, (ST_Dump(ST_Split(a.the_geom, b.geom))).geom FROM shifted AS a, blade AS b
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">UNION ALL <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">SELECT *, the_geom FROM shifted AS a WHERE NOT EXISTS (
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"> SELECT 1 FROM blade AS b WHERE ST_Intersects(a.the_geom, b.geom)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">);<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">ALTER TABLE cutted DROP COLUMN IF EXISTS the_geom;<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">ALTER TABLE cutted RENAME COLUMN geom TO the_geom;<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">It cuts the exceeding polygons nice but the uncut objects are duplicated in the result layer (cutted).<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Thanks,</span><span lang="EN-US" style="font-size:9pt">
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt"> </span><span style="font-size:9pt"><img width="132" height="107" id="gmail-m_4847520551514257770m_-7301692844645585665Bild_x0020_1" alt="2_LFV_engelsk_96"><u></u><u></u></span></p>
<table border="0" cellspacing="0" cellpadding="0" style="border-collapse:collapse">
<tbody>
<tr style="height:36.85pt">
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:36.85pt">
<p class="MsoNormal" style="margin-top:2pt;line-height:10pt">
<span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-top:2pt;line-height:10pt">
<b><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"> Paul Malm</span></b><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><br>
<br>
Operations AIM</span><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><u></u><u></u></span></p>
</td>
</tr>
<tr style="height:24.1pt">
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:24.1pt">
<p class="MsoNormal" style="line-height:9pt"><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"> Direct +46 (0)8 797 70 23 Mobile: +46 (0)708 601115<br>
<a href="mailto:paul.malm@lfv.se" rel="noreferrer" target="_blank">paul.malm@lfv.se</a> <br>
<br>
<u></u><u></u></span></p>
<p class="MsoNormal" style="line-height:9pt"><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"> </span><span style="font-size:9pt;color:rgb(51,51,51)">Mail & Visit: Löjtnantsgatan
25, 115 50 Stockholm, Sweden</span><span style="font-size:9pt;color:rgb(51,51,51)"><u></u><u></u></span></p>
</td>
</tr>
<tr>
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt">
<p class="MsoNormal" style="margin-top:2pt;line-height:9pt">
<span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"> Unit phone: +46 (0)8 797 70 20
<br>
</span><span style="font-size:9pt;color:rgb(51,51,51)"><a href="http://www.lfv.se/" rel="noreferrer" target="_blank"><span lang="EN-US">www.lfv.se</span></a></span><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><u></u><u></u></span></p>
</td>
</tr>
<tr style="height:3.7pt">
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:3.7pt">
<p class="MsoNormal" style="margin-top:6pt;line-height:9pt">
<span lang="EN-US" style="font-size:9pt;color:rgb(0,204,0)"> Please consider the enviroment before printing this e-mail message.</span><span lang="EN-US" style="font-size:9pt;color:rgb(0,204,0)"><u></u><u></u></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" rel="noreferrer" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>