This relates to Splitting line layer at polygon's boundary of polygon layer using PostGIS 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?

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';
| improve this question | | | | |
  • Have you tried just adding your params to this: gis.stackexchange.com/a/56998/16594 ? 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.