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