[postgis-users] breake lines

paul.malm at lfv.se paul.malm at lfv.se
Sun Sep 8 23:20:55 PDT 2019


Hi, I made 2 tests.
Drop TABLE IF EXISTS result;
Create table result  as
WITH
data AS (
    SELECT * FROM "origdata" AS t(id, geom)
),
cutter AS (
    SELECT * FROM "cut_Line" 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;
This took 1 min 30 sec, and the result was as I wanted (all lines present and cutted)

And Martins new theorie:
Drop TABLE IF EXISTS result;
Create table result  as
WITH
data AS (
    SELECT * FROM origdata AS t(id, geom)
),
cutter AS (
    SELECT * FROM "cut_Line" AS t(geom)
)
SELECT id, CASE WHEN cut.geom IS NULL THEN d.geom
                ELSE ST_Split( d.geom, cut.geom ) END AS geom
    FROM data d
    CROSS JOIN LATERAL
        (SELECT ST_Collect(c.geom) geom
            FROM cutter c WHERE ST_Intersects(d.geom, c.geom)
        ) AS cut;
1 min and 12 sec, all lines are cutted but here are missing a lot of cutted lines (is it because of lines intersected several times )

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Martin Davis
Skickat: den 9 september 2019 00:10
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] breake lines

You have to love how SQL always provides multiple ways of expressing things (and the options increase with each new release of the standard).

So here's Solution #3 to this problem.  This one uses the nifty Postgres JOIN LATERAL functionality.  I think it's the simplest of the 3 alternatives, but I'm not sure how it's performance will compare.  Perhaps Paul could provide some performance numbers for comparison?  (And of course confirm that it works in the same way as the other queries)

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 cut.geom IS NULL THEN d.geom
                ELSE ST_Split( d.geom, cut.geom ) END AS geom
    FROM data d
    CROSS JOIN LATERAL
        (SELECT ST_Collect(c.geom) geom
            FROM cutter c WHERE ST_Intersects(d.geom, c.geom)
        ) AS cut;

On Thu, Sep 5, 2019 at 3:01 PM Martin Davis <mtnclimb at gmail.com<mailto:mtnclimb at gmail.com>> wrote:
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190909/388eba19/attachment.html>


More information about the postgis-users mailing list