[GIS] Split Lines by Polygons

linepolygonpostgispostgis-2.2splitting

We have a need to split a table of lines(> 100 MM) lines by a table of polygons (~ 5000). ST_Split only accepts two arguments (not sets). What would be the best way to do this? That is, split each line by each polygon to generate a set of lines that are contained in each polygon? (To start with, let's not worry about the time; this is a pre-processing step and can compromise time for a better result)

Environment: PostGis 2.3

UPDATE 1:

I ran the following query; is that correct for this operation? (the result set I got is surprisingly large)

CREATE TABLE split_test AS (SELECT lines.id, mp.attribute1, mp.attribute2, ST_SPLIT(lines.geom, mp.geom) split_geom FROM (SELECT id, geom FROM lines_table LIMIT 1000) lines, (SELECT attribute1, attribute2, geom FROM polygons) AS mp);

UPDATE 2:

I posted the working query below as an answer.

Best Answer

Since v.2.2, PostGIS allows you to split by a multigeometry.

So you can first aggregate your 5000 polygons to multipolygons, using ST_Union() or ST_Collect(). Then the split should work. Something like:

SELECT ST_Split(lines.geom, mp.geom) FROM lines, 
 (SELECT ST_Union(geom) AS geom FROM polygons) AS mp

EDIT: If you want to keep the data from the original polygons too:

SELECT * FROM polygons, 
    (SELECT (ST_Dump(ST_Split(lines.geom, mp.geom)).geom FROM lines, 
       (SELECT ST_Union(geom) AS geom FROM polygons) AS mp) 
    AS splitlines
WHERE ST_Intersects(splitlines.geom, polygons.geom)
Related Question