[postgis-users] breake lines

[Martin Davis]

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> wrote:

>
> On Thu, Sep 5, 2019 at 6:38 AM <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, 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/20190905/764bcc11/attachment.html>