Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

enter image description here

share|improve this question
up vote 5 down vote accepted

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 UNION them:

    lines_in_polygons AS(
      SELECT ST_INTERSECTION(line,polygon) as inter
        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
      GROUP BY l.line)

    SELECT ST_GeometryN(geom,n) 
    FROM diff AS d
     generate_series(1,ST_NumGeometries(geom)) as n FROM diff) n
    SELECT inter
    FROM lines_in_polygons;

If the polygons do intersect...I'll try tommorow.

share|improve this answer
Are "line" and "polygon" geometry columns in their layer? I've found that ST_UNION() is very slow on my polygon layer. Can you modify the query so that "line" will difference with st_union of "lines_in_polygons"? I think the result will be much faster. As I am new to PostGIS and databases in general, please bear with me. Thank you very much! – Cao Minh Tu Apr 3 '13 at 2:13
It works (with line layer named 'line' and geometry column named 'line', polygon layer named 'poly' and geometry column named 'polygon'). Thanks! – Cao Minh Tu Apr 16 '13 at 2:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.