[postgis-users] breake lines

[paul.malm at lfv.se]

Hi, I’ve succeeded in creating the table as you suggested Martin (Thanks!). With some small modifications (CollectionExtract etc.)
The problem is that I would like to transfer all attributes from the original file to the result file. I’ve tried so many ways but I can’t get it right.

Drop table if exists tmp;
Drop table if exists tmp22;

Create table tmp as
WITH
data AS (
   SELECT * FROM "LayerToBeCut" AS t(id, geom)
),
cutter AS (
   SELECT * FROM "LayerToCutWith" AS t(geom)
)
SELECT id,
    CASE WHEN cutting IS NULL THEN geom
        ELSE st_collectionExtract(ST_Split( geom, cutting ),2) END AS geom
FROM (SELECT id, d.geom geom,
            (SELECT ST_Collect(c.geom) geom
                FROM cutter c WHERE ST_Intersects(d.geom, c.geom)
            ) AS cutting
    FROM data d) AS t;

CREATE TABLE "tmp2" AS SELECT *, (ST_Dump(geom)).geom AS the_geom FROM "tmp";
ALTER TABLE "tmp2" DROP COLUMN IF EXISTS geom;
Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Martin Davis
Skickat: den 6 september 2019 00:02
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] breake lines

Here's a slightly different way to do the same thing.  Instead of using a LEFT JOIN to find the non-split lines, it checks whether the result of the ST_Collect of intersecting lines, and then either computes the split or keeps the original input line.

WITH
data AS (
    SELECT * FROM (VALUES
        ( 1, 'LINESTRING (100 100, 400 100)'::geometry ),
        ( 2, 'LINESTRING (100 300, 400 300)'::geometry ),
        ( 3, 'LINESTRING (250 400, 400 400)'::geometry ),
        ( 4, 'LINESTRING (-10 -10, -20 -20)'::geometry ),
        ( 5, 'LINESTRING (-30 -30, -40 -40)'::geometry )
    ) AS t(id, geom)
),
cutter AS (
    SELECT * FROM (VALUES
        ( 'LINESTRING (150 50, 150 350)'::geometry ),
        ( 'LINESTRING (200 50, 200 150)'::geometry ),
        ( 'LINESTRING (250 350, 250 250)'::geometry ),
        ( 'LINESTRING (300 350, 300 50)'::geometry ),
        ( 'LINESTRING (350 250, 350 450)'::geometry )
    ) AS t(geom)
)
SELECT id,
    CASE WHEN cutting IS NULL THEN geom
        ELSE ST_Split( geom, cutting ) END AS geom
FROM (SELECT id, d.geom geom,
            (SELECT ST_Collect(c.geom) geom
                FROM cutter c WHERE ST_Intersects(d.geom, c.geom)
            ) AS cutting
    FROM data d) AS t
;
;

On Thu, Sep 5, 2019 at 2:44 PM Martin Davis <mtnclimb at gmail.com<mailto:mtnclimb at gmail.com>> wrote:

On Thu, Sep 5, 2019 at 6:38 AM <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> wrote:


Result: all of the lines from LayerToBeSplitted where copied into tmp2, not just the ones NOT intersecting, and I don't know how to
get the attributes to be transferred to tmp2 for those new lines.

To get the lines from LayerToBeSplitted which don't intersect ANY lines from LayerToSplitWith, you need to use this SQL pattern:

SELECT d.* FROM LayerToBeSplitted d
LEFT JOIN LayerToSplitWith c ON ST_Intersects(d.geom, c.geom)
WHERE c.geom IS NULL

(Your query is including lines if there is SOME cutting line which isn't intersected, which will generally always be the case)

Here's an updated example query which does both split and non-split together:

WITH
data AS (
    SELECT * FROM (VALUES
        ( 1, 'LINESTRING (100 100, 400 100)'::geometry ),
        ( 2, 'LINESTRING (100 300, 400 300)'::geometry ),
        ( 3, 'LINESTRING (250 400, 400 400)'::geometry ),
        ( 4, 'LINESTRING (-10 -10, -20 -20)'::geometry ),
        ( 5, 'LINESTRING (-30 -30, -40 -40)'::geometry )
    ) AS t(id, geom)
),
cutter AS (
    SELECT * FROM (VALUES
        ( 'LINESTRING (150 50, 150 350)'::geometry ),
        ( 'LINESTRING (200 50, 200 150)'::geometry ),
        ( 'LINESTRING (250 350, 250 250)'::geometry ),
        ( 'LINESTRING (300 350, 300 50)'::geometry ),
        ( 'LINESTRING (350 250, 350 450)'::geometry )
    ) AS t(geom)
)
SELECT * FROM (SELECT id, ST_Split( d.geom,
            (SELECT ST_Collect(c.geom) geom
                FROM cutter c WHERE ST_Intersects(d.geom, c.geom)
            )) geom
    FROM data d) AS t WHERE geom IS NOT NULL
UNION
SELECT d.id<http://d.id>, d.geom
    FROM data d
    LEFT JOIN cutter c ON ST_Intersects(d.geom, c.geom)
    WHERE c.geom IS NULL;

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190906/fdbe0044/attachment.html>