2

I have lines that I would like to split at polygon boundaries. The line table is MULTI and the polygon table is MULTI and was derived from a MULTILINESTRING table that was buffered then unioned to create one geometry.

The pink is the polygon and the white lines are what needs to be split at the polygon boundaries.

enter image description here

This is the returned geometry after running my query. Note the missing geoms, both inside and outside of the polygon, relative to the first pic.

enter image description here

I am almost there but my query is not returning all the lines that are in my original table and I can't figure out why.

CREATE TABLE split As

WITH dmp_geom As ( -- used to dump from MULTILINESTRING to LINESTRING
SELECT (ST_Dump(multiline.geom)).geom As dmp_geom
FROM multiline
),

split_geom As ( --splitting dumped lines at polygon boundaries
SELECT ST_Split(t1.dmp_geom, t2.geom) As split_geom
FROM dmp_geom t1, unioned_poly t2 
)

SELECT ST_CollectionExtract(split_geom, 2) As geom --select only lines
FROM split_geom;

My question is: how can adjust my query so that I will get all the features in my original table, just split at the polygon boundaries?

1
  • I'm not sure the pink line is a polygon Sep 9, 2019 at 7:33

2 Answers 2

0

I never figured out why the strange behavior described in the question occurred, but I did fix the problem. I was able to get the expected geometries returned by simply converting the multilines to single geometries.

After that, this code block successfully split the lines at the polygon boundaries:

CREATE TABLE split.split As

WITH split As (
SELECT (ST_Dump(ST_Split(t1.geom, t2.geom))).geom As split_geom
FROM lines t1, poly t2 
) 

--select only lines from geom collection that resulted from ST_Dump()
SELECT ST_CollectionExtract(split_geom, 2) As geom 
FROM split;

-- redefine geom col to LINESTRING
ALTER TABLE split.split
ALTER COLUMN split_geom TYPE geometry (linestring,3857) 
USING ST_GeometryN(geom, 1); 
0

I had the same problem, and you're solution didn't fix it.

I first found a workaround splitting the multilines twice : it worked better, but the split were not always precisely at the intersection with the multipolygons boundaries.

In the end, I found out another option, very much efficient and faster if you have a gist index on both tables. In my case, I just need the pieces of multilines inside the multipolygons, this way :

SELECT ST_MakeValid(ST_Intersection(l.geom, p.geom)) as geom
FROM multilines l, multipolygons p
WHERE ST_Intersects(l.geom, p.geom)

If you need to keep all the lines, but splitted, this should do the job :

SELECT ST_MakeValid(ST_Intersection(l.geom, p.geom)) as geom
FROM multilines l, multipolygons p
WHERE ST_Intersects(l.geom, p.geom)
UNION
SELECT ST_MakeValid(ST_Difference(l.geom, p.geom)) as geom
FROM multilines l, multipolygons p

You can add in the SELECT statement the fields you want to keep from tables l and p

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.