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

This relates to this question but what about keeping the attributes. enter image description here


  • line_layer (id, geom, attribute1, attribute2)
  • poly_layer (id, geom, attribute3, attribute4)

How do I split the lines after polygons and preserve the attributes for each segment?

I would also like to take attribute3 from the overlapping polygons but this is only optional. Thank you!

My current code is:

SELECT row_number() OVER () AS id, *
   FROM ( WITH lines_in_polygons AS (
                 SELECT st_intersection(ln.geom, a.geom) AS inter
               FROM line_layer ln,
                polygon_layer a
            ), diff AS (
             SELECT st_difference(ln.geom, st_union(pl.geom)) AS geom
               FROM line_layer ln
          JOIN polygon_layer pl ON st_intersects(ln.geom, pl.geom)
         GROUP BY ln.geom
             SELECT st_geometryn(d.geom, n.n) AS geom
               FROM diff d
         CROSS JOIN ( SELECT generate_series(1, st_numgeometries(diff.geom)) AS n
                       FROM diff) n
             SELECT lines_in_polygons.inter
               FROM lines_in_polygons) coco
  WHERE geometrytype(coco.geom) = 'LINESTRING';
share|improve this question
Have you tried just adding your params to this: ? It should just work if you add the correct columns. – Jakub Kania Nov 6 '13 at 13:29
I have tried but it seems my sql skills are not that good (or maybe there are some tricks that I am not yet aware of) – tudorbarascu Nov 6 '13 at 13:40
It might be good idea to include your code. – Jakub Kania Nov 6 '13 at 13:48

Your Answer


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

Browse other questions tagged or ask your own question.