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 <[email protected]> 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:[email protected]] *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 <[email protected]> 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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
