[postgis-users] help with a SQL Command

[Alexandre Neto]

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

@alexnetogeo



A quinta, 13/02/2020, 13:55, <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,
>
>     [image: 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
>
>    Please consider the enviroment before printing this e-mail message.
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> 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/20200213/76c0bd36/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 4612 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200213/76c0bd36/attachment.jpg>