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()
orST_Collect()
. Then the split should work. Something like:EDIT: If you want to keep the data from the original polygons too: