[postgis-users] breake lines

[Martin Davis]

Thanks for comparing them.  I can't at the moment see why the LATERAL
version would not be correct.  Would it be possible to get a copy of the
datasets (or a subset of them?)

On Sun, Sep 8, 2019 at 11:21 PM <paul.malm at lfv.se> wrote:

> 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> 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.
>
> _______________________________________________
> 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/20190909/0bd9da65/attachment.html>