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.


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?

  • I'm not sure the pink line is a polygon – Daniel Demonceau Sep 9 '19 at 7:33

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); 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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