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
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);
I posted the working query below as an answer.