[postgis-users] sql query

[paul.malm at lfv.se]

> 19 mars 2020 kl. 11:51 skrev Jorge Gustavo Rocha <jgr at di.uminho.pt>:
> 
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users