[postgis-users] sql query

[paul.malm at lfv.se]

HI,
I'm trying to improve my current function to split polygons with lines.
I have 2 layers, a polygon layer and a line layer.
I Would like to punch out new polygons from a polygon layer using lines in a linelayer.
I've tried this:
CREATE TABLE split_polys_tmp AS
WITH RECURSIVE
    split AS (
        SELECT  (SELECT MAX(fid) FROM linelayer) AS line_id
               ST_Collect(a.the_geom) AS geom
        FROM polygonlayer AS
        UNION ALL
        SELECT  line_id - 1 AS line_id,
               ST_Split(c.geom, b.the_geom) AS geom
        FROM split AS c,
             linelayer AS b
        WHERE b.fid = c.line_id
    )
SELECT (ST_Dump(geom)).geom
FROM split
WHERE line_id = 0;

It works perfekt...
But the I would like an attribute (DMGS_ID) from polygonlayer (unique for each polygon) to be tranferred the splitted polygons in the new layer.
Then I tried this:

CREATE TABLE split_polys_tmp AS
WITH RECURSIVE
    split AS (
        SELECT  (SELECT MAX(fid) FROM rest) AS line_id,  a."DMGS_UID",
               ST_Collect(a.the_geom) AS geom
        FROM polygonlayer AS a
        UNION ALL
        SELECT  c."DMGS_UID" ,line_id - 1 AS line_id,
               ST_Split(c.geom, b.the_geom) AS geom
        FROM split AS c,
             linelayer AS b
        WHERE b.fid = c.line_id

    )
SELECT "DMGS_UID", (ST_Dump(geom)).geom
FROM split
WHERE line_id = 0;

It will not work. It seems like I have to add: GROUP BY "DMGS_UID" at the end of sql-line 5 and then the result (polygon) is different.
How can I transfer an attribute from an original polygon to the new cutted polygons?
If someone has the time and could take the effort to explain how to do this, I would be grateful.

Kind regards,
Paul


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200319/1b719121/attachment.html>