[postgis-users] sql query

[Jorge Gustavo Rocha]

Hi Paul,

Maybe you can do it in a second step, with a more simple query, since
the polygons overlap.

Regards,

Jorge

On 19/03/20 10:18, paul.malm at lfv.se wrote:
> 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
> 
>  
> 
>    
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> 

J. Gustavo
-- 
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Gabinete 3.29 (Piso 3)
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor