/*  Waldanteil der hessischen Gemeinden aus Gemeinde-Geomemtrien und dem DLM 250 ermitteln */

drop table  if exists waldschnitt;			-- Vorhande Tabelle löschen

create table waldschnitt as				-- Neue Tabelle erzeugen
	with  verschnitt as							-- Die Unterabfrage bekommt den Namen verschnitt		
      ( --Start Unterabfrage
      select
	  row_number() over() as id,			-- Eindeutige ID für das Ergebnis
      g.RS as RS,							-- Der Reginalschlüssel wird aus der Gemeindetabelle geholt (g ist Alias der Gemeinde)
	  g.gen as gemeinde,					-- gen ist der Gemeindename, umbenannt zu gemeinde
      round((st_area(g.geom)/ 1000000),3) as qkm,  -- Berechnung der Gemeindefläche aus der Gemeindegeometrie g.geom
       round(		-- Runden						-- mit der Funktion ST_area Fläche berechnen, dann Division durch 1000000 für qkm
			sum(				-- Summierung der Fläche sämtlicher Waldschnipsel (Gruppierung nach RS)
				st_area(  		-- Flächenberechnung aus Geometrie
					st_intersection(g.geom,w.geom)  --as geom	-- Dies ist die Intersection-Funktion (Verschneidung von Wald und Gemeinden wie im GIs)
					    ) / 1000000			-- St_area schließen und durch 1000000 dividieren für qkm
					)		                 -- Summierung schließen
			 ,3)                             -- auf dritte Stelle runden und schließen
		  as qkm_wald,			-- Ergebnis ist die Waldfläche, die aus der verschnittenen Gemeinde/Waldgeometrie berechnet wird
		  g.geom as geom		-- Die einfache Gemeindegeometrie
	
        from  gemeinden  as g -- dlm250 as w			             -- Eingabe Gemeindelayer
				left join 					                 -- Die Anbindung eines anderen Layers über eine Beziehung, alle Objekte von Layer 1
				dlm250 as w on st_intersects(g.geom,w.geom)  --Angebunden wird DLM 250, wenn sich beide Geometrien überschneiden.
        where w.nutzung = 'AX_Wald'					        -- Wie im Ausdruckseditor, die Waldflächen werden ausgewählt
                         AND w.ROWID IN                    -- für schnellere Verarbeitung: Räumlichen Index ansprechen
                        (SELECT ROWID FROM SpatialIndex WHERE f_table_name='dlm250' AND search_frame=g.geom)
		group by g.rs	      					             --Gruppieren nach gemeindeschlüssel rs
		) -- Ende Unterabfrage
select					-- Hauptabfrage, damit es bei der Flächenberechnung übersichtlich bleibt
--*
id, RS, gemeinde, qkm, qkm_wald,
round((ifnull(qkm_wald,0) / qkm) * 100) as anteil,  --Hierwird der prozentuale Anteil berechnet
geom as geom
from verschnitt		-- Eingabe ist das Ergebnis der Unterabfrage verschnitt, die with eingeleitet wurde
;

Select RecoverGeometryColumn('waldschnitt','geom',25832,'MULTIPOLYGON');  -- Geometry anmelden, damit die Tabelle als Layer im QGIS angezeigt werden kann.
Select CreateSpatialIndex('waldschnitt','geom');
SELECT UpdateLayerStatistics('waldschnitt');
--Select disableSpatialIndex('waldschnitt','geom');

/* Verschneiden und verschmelzen*/
select
g.RS,
g.gen,
-- Dies ist eine Kombi von verschmelzen und verschneiden 
--(Verschneidung mit st_intersection, verschmelzen mit st_union, Geometriesplitter ausschliessen mit st_collectionextract
casttomulti(ST_union(st_collectionextract(st_intersection(g.geom,w.geom),3))) as geom		
from  gemeinden  as g -- dlm250 as w			             -- Eingabe Gemeindelayer
            left join 					                 -- Die Anbindung eines anderen Layers über eine Beziehung, alle Objekte von Layer 1
            dlm250 as w on st_intersects(g.geom,w.geom)  --Angebunden wird DLM 250, wenn sich beide Geometrien überschneiden.
where w.nutzung = 'AX_Wald'					        -- Wie im Ausdruckseditor, die Waldflächen werden ausgewählt
            AND w.ROWID IN                    -- für schnellere Verarbeitung: Räumlichen Index ansprechen
                (SELECT ROWID FROM SpatialIndex WHERE f_table_name='dlm250' AND search_frame=g.geom)
group by g.rs
;--Gruppieren nach gemeindeschlüssel rs                                                                        
                                                


/* Mit Ermittlung der Walfläche je Einwohner ohne Kommentare */
drop table  if exists wald_ant_mit_ew
create table wald_ant_mit_ew aS
 with  verschnitt as	
      (
      select
      g.RS as RS,
      round((st_area(g.geom)/ 1000000),3) as qkm,
       round(
			sum(st_area(
					st_intersection(g.geom,w.geom) 
                        ) / 1000000	
                ),3)
    as qkm_wald
    
    from  gemeinden  as g left join dlm250 as w on st_intersects(g.geom,w.geom) 
    where w.nutzung = 'AX_Wald'
    AND w.ROWID IN
            (SELECT ROWID FROM SpatialIndex WHERE f_table_name='dlm250' AND search_frame=g.geom)
    group by g.rs	      					  
    )
select					
a.fid, a.RS, a.gen as gemeinde, b.qkm, b.qkm_wald,
round((ifnull(b.qkm_wald,0) /b.qkm) * 100) as anteil, 
round((b.qkm_wald * 1000000) / c.ewz) as wfqm_ew,
c.ewz as ewz,
a.geom as geom
from gemeinden as a left join verschnitt as b on (a.RS = b.RS)
                    left join ewz as c on (a.rs = c.rs)
;


Select CreateSpatialIndex('wald_ant_mit_ew','geom');
Select RecoverGeometryColumn('wald_ant_mit_ew','geom',25832,'MULTIPOLYGON');
SELECT UpdateLayerStatistics('wald_ant_mit_ew');
--Select disableSpatialIndex('wald_ant_mit_ew','geom');





/* Strukturiert mit zwei Unterabfragen */
with 
   inters as  -- Verschneidung und Flächenberechnung
        (
        select
        a.RS as RS,
        round((st_area(a.geom)/ 1000000),3) as qkm,
        round((st_area(st_intersection(a.geom,b.geom)) / 1000000),3) as qkm_wald

        from gemeinden as a left join dlm250 as b on st_intersects(a.geom,b.geom)
        where b.nutzung = 'AX_Wald'
        	 AND b.ROWID IN
            (SELECT ROWID FROM SpatialIndex WHERE f_table_name='dlm250' AND search_frame=a.geom)			
        )
        ,
    wafl as   --- Summieren je Gemeinde
        (
        select
        RS,
        qkm,
        sum(qkm_wald) as qkm_wald
        from inters
        group by RS
        )
select
row_number() over() as id,
a.RS as RS,
a.GEN as gemeinde,
b.qkm as qkm,
b.qkm_wald as qkm_wald,
round((b.qkm_wald / b.qkm) * 100) as anteil,
a.geom as geom
from gemeinden as a left join wafl as b on (a.RS = b.RS)
;       
            
    
            
        
/* Abfrage mit stark verschachtelten Funktionen */
Select
row_number() over() as id,
g.RS AS RS,
g.GEN AS GEN,
round((st_area(ST_union(ST_Intersection(g.geom,d.geom))) / 1000000),3) AS qkm_wald,
round((st_area(ST_union(ST_Intersection(g.geom,d.geom)))) / (st_area(g.geom))* 100) AS anteil,
g.geom AS geom
from
gemeinden as g left join dlm250 as d on intersects(g.geom,d.geom) 
where
    d.nutzung = 'AX_Wald'
    	 AND d.ROWID IN
            (SELECT ROWID FROM SpatialIndex WHERE f_table_name='dlm250' AND search_frame=g.geom)

Group by g.RS;


/* Abfrage mit stark verschachtelten Funktionen ohne räumlichen Index */
Select
row_number() over() as id,
g.geom AS geom,
g.RS AS RS,
g.GEN AS GEN,
e.EWZ AS EWZ,
round((area(ST_union(ST_Intersection(g.geom,d.geom))) / 1000000),2) AS WFL,
round((area(ST_union(ST_Intersection(g.geom,d.geom)))) / (area(g.geom))* 100) AS anteil,
round((area(ST_union(ST_Intersection(g.geom,d.geom)))) / e.EWZ)  AS WF_EW
from
(gemeinden as g left join dlm250 as d ON intersects(g.geom,d.geom)) left join EWZ as e ON e.RS = g.RS
where
    d.nutzung = 'AX_Wald'
Group by g.RS;


/* Abfrage mit stark verschachtelten Funktionen mit räumlichen Index */
Select
row_number() over() as id,
g.geom AS geom,
g.RS AS RS,
g.GEN AS GEN,
e.EWZ AS EWZ,
round((area(ST_union(ST_Intersection(g.geom,d.geom))) / 1000000),2) AS WFL,
round((area(ST_union(ST_Intersection(g.geom,d.geom)))) / (area(g.geom))* 100) AS anteil,
round((area(ST_union(ST_Intersection(g.geom,d.geom)))) / e.EWZ)  AS WF_EW
from
(gemeinden as g left join dlm250 as d ON intersects(g.geom,d.geom)) left join EWZ as e ON e.RS = g.RS
where
    d.nutzung = 'AX_Wald'
    	 AND d.ROWID IN
            (SELECT ROWID FROM SpatialIndex WHERE f_table_name='dlm250' AND search_frame=g.geom)
Group by g.RS;



/* Landkreise aus Gemeinden */

create landkreise as 
with gem as
    (
    select
    rs,
    gen,
    geom
    from gemeinden
    order by gen
    )
select
c.krid as krid,
c.name as name,
count(a.rs) as anzahl,
sum(b.ewz) as ewz,
group_concat(a.gen,','||char(13)) as liste,
st_union(a.geom) as geom


from "gem" as a left join "EWZ" as b on (a.rs = b.rs)
					left join "lkr" as c on (c.krid = substr(a.rs,2,4))
group by substr(a.rs,2,4)
;

Select CreateSpatialIndex('landkreise','geom');
Select RecoverGeometryColumn('landkreise','geom',25832,'MULTIPOLYGON');
SELECT UpdateLayerStatistics('landkreise');


