[postgis-users] help with a SQL Command

[paul.malm at lfv.se]

Thanks, Alexandre!

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Alexandre Neto
Skickat: den 14 februari 2020 00:24
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] help with a SQL Command

Hi Paul,

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.
So, you are getting all you need from the first SELECT and you don't need that the UNION ALL part.

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

WITH  blade AS (
   SELECT ST_MakeLine(ST_SetSRID(ST_MakePoint(180, -90),4326),ST_SetSRID(ST_MakePoint(180.000, 90), 4326)) AS geom
), cutted as (
SELECT a.*,
    CASE WHEN St_intersects (a.the_geom, b.geom) THEN
        ST_Split(a.the_geom, b.geom)
   ELSE
        a.the_geom
    END as geom1
FROM   shifted AS a, blade AS b
)
SELECT *, (St_dump(geom1)),geom as geom


Alexandre Neto
Support team
www.qcooperative.net<http://www.qcooperative.net>
@alexnetogeo


A quinta, 13/02/2020, 13:55, <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> escreveu:
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,
    Fel! Inget filnamn angivet.

   Paul Malm

   Operations AIM

   Direct  +46 (0)8 797 70 23  Mobile: +46 (0)708 601115
   paul.malm at lfv.se<mailto: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.



_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200214/a1e09eb8/attachment.html>