[postgis-users] breake lines

[paul.malm at lfv.se]

Hi,
This is how far I got today:


LayerToBeSplitted is of type MultiLinestrings

DROP TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS tmp2;
CREATE TABLE tmp AS
WITH
data AS (
   SELECT * FROM "LayerToBeSplitted" AS t
),
cutter AS (
   SELECT the_geom FROM "LayerToSplitWith" AS t
)
SELECT *, ST_Split(d.the_geom,
           (SELECT ST_Collect(c.the_geom) geom
               FROM cutter c WHERE ST_Intersects(d.the_geom, c.the_geom)
           )) the_geom2
FROM data d;

Clean up the table tmp

ALTER TABLE tmp DROP COLUMN IF EXISTS the_geom;

Result is all the columns from LayerToBeSplitted the_geom2 = a geometryColletion with "linestrings" and empty geometries
the table only includes the splitted lines that were intersected by the cutting layer (LayerToSplitWith)

Create a table with a geometry column geom, who has no empty geometries and is of type Linestring

CREATE TABLE tmp2 as SELECT *, (ST_DUMP(
        st_collectionExtract( t.the_geom2, 2))).geom
FROM tmp AS t WHERE t.the_geom2 IS NOT null;

Clean up the table tmp2

ALTER TABLE tmp2 DROP COLUMN IF EXISTS the_geom2;
ALTER TABLE tmp2 RENAME COLUMN geom TO the_geom;

Result: a table with splitted Linsestrings.
there are no MultiLinestrings geometries and no empty geometries, and all of the columns from LayerToBeSplitted are included.

Now I would like to add all the lines with all attributes from LayerToBeSplitted, who were NOT intersected by the cutting layer (LayerToSplitWith).
I tried this among other things, without any luck: ☹

INSERT INTO tmp2 (the_geom)
    SELECT DISTINCT(
            St_dump(a.the_geom)                             -- multi to single
    ).geom
    FROM "LayerToBeSplitted" a, "LayerToSplitWith" AS b WHERE NOT ST_Intersects(a.the_geom, b.the_geom);

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.
I can't use: INSERT INTO tmp2 SELECT... since the order of the columns is different between the two tables, I think...

Kind regards,
Paul

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



On Wed, Sep 4, 2019 at 4:54 AM <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> wrote:
Hi,
If I understand your question correct, yes I would like to break all lines in the intersections with another layer.
No I have not tried MultiLinestrings comprised of all lines that are intersecting the lines to be broken. Don’t know how to do this. ☹

Here's an example query showing how to collect all cutting lines which intersect each data line and then use them to split the data line:

WITH
data AS (
    SELECT * FROM (VALUES
        ( 'LINESTRING (100 100, 400 100)'::geometry ),
        ( 'LINESTRING (100 300, 400 300)'::geometry ),
        ( 'LINESTRING (250 400, 400 400)'::geometry )
    ) AS t(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 ST_Split( d.geom,
            (SELECT ST_Collect(c.geom) geom
                FROM cutter c WHERE ST_Intersects(d.geom, c.geom)
            )) geom
    FROM data d;

I think this should work even if the inputs are MultiLineStrings, but you'll have to verify that.

One issue that might arise depending on your data is that ST_Split errors out if the splitting line has any portion which is collinear with a portion of the input.  I guess the theoretical rationale for this is that the splitting code doesn't know which "side" of the output to allocate the common line to.  But really this isn't very useful, since there's no easy way to detect this situation and prevent it from happening. If this is an issue for you then report back to the list, and we can think about how to fix this.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190905/5ce1eeed/attachment.html>