1

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.

2

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)
  • Unioning the whole table might decrease the performance drastically. Why not to JOIN on ST_Intersects predicate? – Michal Zimmermann Oct 28 '16 at 19:39
  • @MichalZimmermann, if you join with a ST_Intersects, a line that crosses several polygon would not be correctly split correct? – c00der Oct 28 '16 at 19:50
  • Agreed with user7032246, although it depends what you mean by "correctly split". You'd also have to think through what would happen with overlapping polygons (it could split them multiple times). The post also said that performance as not a concern. – amball Oct 28 '16 at 19:52
  • @amball, when a union is done, all the attributes related to each polygon is lost, correct? The whole point of the operation is to split and retain the polygon associations with the split line segments. Do you know whether this could be achieved in some way? – c00der Oct 28 '16 at 20:06
  • @amball, I updated my question. Could you let me know whether the query I've used is correct? I got a 1000x more split rows in the resulting data set. Think something is wrong. – c00der Oct 28 '16 at 20:40
2

Following is the query we ended up using. When both the geometry columns are indexed runs considerably fast:

SELECT CASE WHEN ST_COVERS(b.geom, l.geom) THEN l.geom ELSE ST_INTERSECTION(b.geom, l.geom) END AS geom, l.line_attribute, b.polygon_attribute FROM (SELECT id line_attribute, linestring geom FROM lines_table) l INNER JOIN polygons_table b ON ST_Intersects(l.geom, b.geom)

Basically, what the query does is, if the line is contained in the polygon, use the line, if the line crosses the polygon boundary, split.

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.