I have a line layer and a polygon layer in PostgreSQL 9.2, PostGIS 2.0. How can I split every line each time it crosses a polygon's boundary?
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
Alright, I can't test it on larger data set so it might not be fastest but should work.
If the polygons don't intersect (seems to be what you want) this will work. With
ST_INTERSECTION(<line>,<polygon>) we can get lines inside of polygons (one line per row), with
ST_Difference(<line>,ST_UNION(<polygon>)) we can get a geometry colection that holds pieces of line that don't fit inside any of the polygons. So now we just have to put those two results in one format and
WITH lines_in_polygons AS( SELECT ST_INTERSECTION(line,polygon) as inter FROM line AS l, poly AS c ), diff AS ( SELECT ST_Difference(l.line,ST_UNION(polygon)) geom FROM line AS l JOIN poly AS p ON ST_INTERSECTS(l.line,p.polygon) GROUP BY l.line) SELECT ST_GeometryN(geom,n) FROM diff AS d CROSS JOIN (SELECT generate_series(1,ST_NumGeometries(geom)) as n FROM diff) n UNION SELECT inter FROM lines_in_polygons;
If the polygons do intersect...I'll try tommorow.