[postgis-users] help with a SQL Command solved

[paul.malm at lfv.se]

Sorry I found what was wrong;

Drop table if exists cutted;
CREATE TABLE "cutted" as WITH
   blade AS (
   SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom)
SELECT a.*, (ST_Dump(ST_Split(a.the_geom, b.geom))).geom    FROM   shifted AS a,    blade AS b WHERE ST_Intersects(a.the_geom, b.geom)
UNION ALL
SELECT *, the_geom FROM shifted AS a  WHERE NOT EXISTS (
      SELECT 1  FROM blade AS b  WHERE ST_Intersects(a.the_geom, b.geom)
);
ALTER TABLE cutted DROP COLUMN IF EXISTS the_geom;
ALTER TABLE cutted RENAME COLUMN  geom TO the_geom;



Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Malm, Paul (Operations AIM)
Skickat: den 13 februari 2020 14:56
Till: postgis-users at lists.osgeo.org
Ämne: [postgis-users] help with a SQL Command

Hi list!
I have a problem with a sql command, I would be grateful if someone please could tell me what I'm doing wrong.
I have a polygon layer where some polygons exceeds the 180/-180 border.
I would like to cut the polygons with a line from 180,90 to 180,-90.
I've got this far:

Drop table if exists cutted;
CREATE TABLE "cutted" as WITH
   blade AS (
   SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom)
SELECT a.*, (ST_Dump(ST_Split(a.the_geom, b.geom))).geom    FROM   shifted AS a,    blade AS b
UNION ALL
SELECT *, the_geom FROM shifted AS a  WHERE NOT EXISTS (
      SELECT 1  FROM blade AS b  WHERE ST_Intersects(a.the_geom, b.geom)
);
ALTER TABLE cutted DROP COLUMN IF EXISTS the_geom;
ALTER TABLE cutted RENAME COLUMN  geom TO the_geom;


It cuts the exceeding polygons nice but the uncut objects are duplicated in the result layer (cutted).
Thanks,
    [2_LFV_engelsk_96]

   Paul Malm

   Operations AIM

   Direct  +46 (0)8 797 70 23  Mobile: +46 (0)708 601115
   paul.malm at lfv.se
   Mail & Visit: Löjtnantsgatan 25, 115 50 Stockholm, Sweden

   Unit phone: +46 (0)8 797 70 20
   www.lfv.se<http://www.lfv.se/>

   Please consider the enviroment before printing this e-mail message.



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200213/6132a857/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 4612 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200213/6132a857/attachment.jpg>