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';
  • 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. Nov 6, 2013 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) Nov 6, 2013 at 13:40

1 Answer 1


You'll need to pass the attributes through both CTEs. Using * for all attributes:

lines_in_polygons AS(
  SELECT l.*,ST_INTERSECTION(l.geom,c.geometry) as inter
    lines AS l,
    temppolys AS c
diff AS (
  SELECT l.*,ST_Difference(l.geom,ST_UNION(p.geometry)) geom FROM 
  lines AS l
  JOIN temppolys AS p ON
  GROUP BY l.fid)

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;
  • I am new and tried this code but get this: ERROR: column reference "geom" is ambiguous LINE 19: generate_series(1,ST_NumGeometries(geom)) as n FROM diff) n
    – MJM
    Feb 28, 2023 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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