
/* FOSSGIS 2025 
Zaubern mit dem Geopackage
SQL mit räumlichen Funktionen imGeopackage zur Geodatenverarbeitung nutzen
Das Geopackage kann nicht nur Daten verpacken sondern ganz schnell im Hintergrund  Arbeit verrichten, die  anschließend im QGIS wie Zauberei sichtbar wird.  Die Verschneidung zweier Layer  aktualisiert sich, wenn die Quell-Layer geändert werden, ohne das ein weiterer Prozess aufgerufen werden muss. Aggregierungen und Auswertungen sind stets auf dem Stand. 
Im Workshop schreiben wir  Trigger und Views , die dynamische Auswertungen, Aggregierungen und auch Geodaten-Verarbeitung mit Puffern, Verschneidungen und Verschmelzungen umsetzen. Räumliche Indexe werden genutzt.
Grundlegende SQL-Kenntnisse und Erfahrungen mit QGIS sind notwendig für den Workshop

*/

"#######################################################################"
"SOFTWARE UND ZUGRIFF"
"######################################################################"
Geopackages sind SQLite-Datenbanken, auf die mit jeder Software zugegriffen werden kann, die SQLite versteht.
Um räumliche Funktionen zur Geodatenverarbeitung zu verwenden, muss auf die SpatiaLite-Bibliothek zugegriffen werden.
Das ist im DB-Browser von QGIS sofort möglich. Andererseits hat der DB-Browser verschiedene Nachteile.
So kann nur auf eine SQL-Abfrage auf einma abgesetzt werden.

'Info 1 SQlite SQL Dokumentation: Alle Infos zum SQlite-SQL-Dialect '
https://www.sqlite.org/lang.html

'Info 2: Infos zum Geopackage auf GDAL'
https://gdal.org/en/stable/drivers/vector/gpkg.html

'Info 3: SpatiaLite => Räumliche Funktionen / SpatialSQL für SQlite-Datenbanken'
https://www.gaia-gis.it/fossil/libspatialite/index
https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.1.0.html
https://www.surfaces.co.il/spatialite-speedup-your-query-with-spatial-indexing/	--Zu räumlichen Idexen
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex		-- Zu räumlichen Indexen

'Info 3 Entwicklungswerkzeuge für SQLite-Datenbankebn'
	' 1. SQLite-Browser '
	https://sqlitebrowser.org/
	https://sqlitebrowser.org/dl/
	
	' 2. SQlite-Studio '	  
	  https://sqlitestudio.pl/
	  https://github.com/pawelsalawa/sqlitestudio/releases
'Beide Umgebungen sind gut zu benutzen. ein wenig Geschmakssache'
'Auch Dbeaver ist eine Alternative'

"#######################################################################"
"Spatialite einbinden"
"######################################################################"
Die Spatialite_Bibliothek aus der QGIS-Installation kann auch in externer Software genutzt werden.

Ort des Modules in der QGIS-Installation:
	Windows: 				
		QGISInstallation\bin\mod_spatialite.dll
		(z.B. "C:\Program Files\QGIS 3.40.4\bin\mod_spatialite.dll")
	Linux Ubuntu:
		/usr/lib/x86_64-linux-gnu/mod_spatialite.so
		
	MacOs: 
		/Applications/QGIS.app/Contents/MacOS/lib/mod_spatialite.dylib
		oder
		/Applications/QGIS-LTR.app/Contents/MacOS/lib/mod_spatialite.dylib


"Einbinden Im SqliteBrowser:"
	Bearbeiten > Einstellungen
	Dort im Reiter Erweiterungen den pfad hinterlegen

"Einbinden im SQLite-Studio"
	Werkzeuge > Erweiterungsmanager öffnen
	Dort den Pfad eingeben.


Alternativ den Pfad direkt als SQL eingeben und vorher:
PRAGMA trusted_schema=1;
Windows:
select load_extension('C:\Program Files\QGIS 3.40.4\bin\mod_spatialite.dll');PRAGMA trusted_schema=1;
Linux:
select load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');PRAGMA trusted_schema=1;
Mac:
select load_extension('/Applications/QGIS-LTR.app/Contents/MacOS/lib/mod_spatialite.dylib');PRAGMA trusted_schema=1;

"######################################################################################"
" In jeder Sitzuung vorab, die Konvertierung von Spatialite zu GPKG einschalten"
SELECT EnableGpkgMode();
Oder in Abfragen die geometrie konvertieren:
castautomagic(geom)
" Damit wird gewährleistet, dass Geopackage-Geometrien mit Spatialite verarbeitet werden können
"#####################################################################################

"##################################################################"
"THEORIE: WIE WIRD AUS EINER GEWÖHNLICHEN SQLITE-DATENBANK EINE GEOPACKAGE UND WAS UNTERSCHEIDET DAS GEOPACKAGE VON EINER SPATIALITE-DATENBANK'
"######################################################################"

Das Geopackage ist eine SQlite-Datenbank mit einer Erweiterung zu Speicherung räumlicher daten.
Mit Hilfe von Views und Triggern sind Auswertungen z.B. Aggregierungen und Datenverarbeitung direkt im Geopackage möglich.
Über die SpatiaLite-Bibliothek lassen sich auch räumliche SQL-Funktionen wie in klassischen SpatiaLite-Datenbanken oder PostGis nutzen.

Eine SQLite-Datenbank wird zum Geopackage, wenn es folgende Tabellen gibt:
1. "gpkg_contents
	'Es werden die Tabellen aufgelistet, die über GIS-Inhalte verfügen'
	'Nur Tabellen, die hier aufgeführt sind, werden von QGIS als Layer gesehen'
	'Über die Spalte data_type wird beschrieben, ob es sich um reine Tabellen oder Tabellen mit Geometrien handelt.'
2. "gpkg_geometry_columns
   'Tabellen mit Geometrien, die als GIS-Layer angesprochen werden, müssen hier gelistet sein.'
   'Der Name der Geometrie-Spalte sowie der Geometrie-Typ und der EPSG-Code des Koordinatenbezugssystems sind hier gelistet'
3. "gpkg_ogr_contents
   'Die Statistik der FeatureAnzahl  wird hier geführt'
4. "gpkg_spatial_ref_sys
   ' Die Tabelle mit den zur verfügung stehenden Koordinatenbezugs-Systemen'

Es  gibt noch weitere Tabellen, die mit der Rasterdaten und der Metadaten-Verwaltung zu tun haben. 

Wird ein Geopackage aus QGIS angelegt, werden all diese Tabellen automatisch angelegt.
In eine auf anderem Wege angelegten SQLite-Datenbank lassen sich die Tabellen folgendermaßen anlegen,
wenn Spatialite verfügbar ist:

"SpatiaLite laden:"
PRAGMA trusted_schema=1;
--Beispiel Linux
select load_extension('C:\Program Files\QGIS 3.40.4\bin\mod_spatialite.dll');PRAGMA trusted_schema=1;
--Beispiel Windows
select load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');PRAGMA trusted_schema=1;
--Beispiel Mac
select load_extension('/Applications/QGIS-LTR.app/Contents/MacOS/lib/mod_spatialite.dylib');PRAGMA trusted_schema=1;

"Basis-Tabellen anlegen"
select gpkgCreateBaseTables(); --Geopackage-basistabellen

"#######################################"
'Neue räumliche Tabelle mit SQL anlegen'
"########################################"
/* Anlegen eines Punktlayers über SQL */
1. Tabelle ohne Geometry-Spalte anlegen
	select DropTable('hydranten','');
	create table hydranten(
	fid   INTEGER     PRIMARY KEY Autoincrement,
	hyd_name text,
	xc integer,
	yc integer
	);

2. Geometrie-Spalte anlegen, Geometrie-Trigger und räumlichen Index anlegen
	select gpkgAddGeometryColumn('hydranten','geom','POINT',0,0,25832);
	select gpkgAddGeometryTriggers('hydranten','geom');
	select gpkgAddSpatialIndex('hydranten','geom');
	
	"Koordinatenbezugssystem einfügen"
	select gpkgInsertEpsgSRID(25832);

3.	
	"Geopackage-Modus einschlaten
	SELECT EnableGpkgMode();

"#######################################"
'Neue räumliche Tabelle mit SQL angelegt '
"########################################"
	
"Soll das Koordinatenbezugssystem umprojeziert werden, ist folgende notwendig:"
CREATE TABLE spatial_ref_sys (
  srid       INTEGER NOT NULL PRIMARY KEY,
  auth_name  VARCHAR(256),
  auth_srid  INTEGER,
  srtext     VARCHAR(2048),
  proj4text  VARCHAR(2048)
);

INSERT INTO spatial_ref_sys SELECT
  srs_id AS srid,
  organization AS auth_name,
  organization_coordsys_id AS auth_srid,
  definition AS srtext,
  NULL
FROM gpkg_spatial_ref_sys;
"#########

"#######################################"
"Möglich ist auch das physische Umwandeln von Geopackage und Spatialite-Geometrien ineinander:"
"########################################

	---Geopackage-Geometrie zu Spatialitegeometrie
	update LAYER
	set
	geom = GeomFromGPB(geom)
	;
	
	---Spatialite-Geometrie zu Geopackage-Geometrie
	update LAYER
	set
	geom = AsGPB(geom)
	;
	
"###################################"
'Neue nicht räumliche Tabelle mit SQL anlegen'
"#####################################
create table kostenliste(
fid integer PRIMARY KEY AUTOINCREMENT,
material text,
jahreskosten_qm double
);
'Tabelle anmelden'
insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('kostenliste', 'attributes', 'kostenliste', 0)
;
insert into kostenliste
(material,jahreskosten_qm)
VALUES
	('Asphalt',0.1),
	('Boden unbefestigt',0.3),
	('Naturstein-Pflaster',0.2),
	('Pflaster / Beton',0.1),
	('Rasen',0.8),
	('Wassergebunden',0.8),
	('Sonstiges',0.3)
	;


"###################################"
"Räumliche und nicht räumliche  Sichten (Views) mit SQL anlegen'
"#####################################
"NEU ANGELEGTE VIEWS MÜSSEN  IM GEOPACKAGE ANGEMELDET WERDEN, DAMIT QGIS DIESE SIEHT!"

1. Nicht räumliche Tabellen
	Beispiel für einen View TestView
	--Inhalt anmelden
	insert into gpkg_contents (table_name, data_type, identifier, srs_id)
	values ('TestView', 'attributes', 'TestView', 0)
	;

2. Tabellen mit Geometrie (GIS-Layer)
	Beispiel für einen Polygon-View TestFlaeche im KBS EPSG:25832
	
	--Inhalt anmelden
	insert into gpkg_contents (table_name, data_type, identifier, srs_id)
	values ('TestFlaeche', 'features', 'TestFlaeche', 25832)
	;
	-- Geometrien anmelden
	insert into gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m)
    values ('TestFlaeche','geom','MULTIPOLYGON',25832,0,0)
	
	-- Feature Count einrichten
    insert into gpkg_ogr_contents
    (table_name, feature_count)
    values('TestFlaeche', (select count(fid) from TestFlaeche))

"Eine ähnliche jedoch syntaktisch andere Vorgehensweise ist in SpatiaLite-Datenbanken notwendig"

"###################################"
"ACHTUNG, WENN ÜBER ANDERE TOOLS ALS QGIS AUF DIE DATENBANK ZUGEGRIFFEN WIRD, MUSS DIE SPATIALITE-BIB-GELADEN WERDEN 
UND 
"DIE GEOMETRIE VON GEOPACKAGE NACH SPATIALITE KONVERTIERT WERDEN: "
"Bei Abfragen der Geometriespalte auf
castautomagic(geom) statt geom
"oder vorab:
SELECT EnableGpkgMode();
"###################################"

"#################################################################
'LEKTION 0: Automatische Koordinatenberechnung und Transformation der Baum-Layer'
"################################################################

1. Öffnen des Projektes FossGis_2025_Start.qgz im QGIS
2. Im QGIS-Browser unter Projektverzeichnis Rechtsklick auf FossGis_2025_Start.gpkg  und "Verbindung hinzufügen" wählen.
3. Im QGIS den DB-Manager öffnen (Datenbank > DB-Verwaltung

-- Geometrie der Bäume wählent
select -- fehlte beiu den TN
fid,
geom
from baeume
;
 -- Koordinaten zur Geometrie ermitteln
select
fid,
st_x(geom)  as xc,
st_y(geom)  as yc
from baeume
;

--Grundete Koordinaten
select
fid,
round(st_x(geom))  as xc,
round(st_y(geom))  as yc
from baeume
;
-- Auf Ganzzahl gesetzt mit cast (ausdruck as integer)
select
fid,
cast(round(st_x(geom)) as integer)  as xc,
cast(round(st_y(geom)) as integer) as yc
from baeume
;

/* die Koordinaten sollen in Dezimalgrad ermittelt werden 
und das Ganze in einer Sicht dauerhaft zur Verfügung gestellt werden*/

create view kor_trans as   -- Abfrage als Sicht in die Datenbank schreiben
with trans as -- Definition der Unterabfrage  mit dem Namen trans
	(
	SELECT
	fid,
	st_transform(geom,4326) as geom  -- Transformation der Geoemtrie zu WGS 84 (EPSG:4326)
	from 
	baeume
	)
SELECT
fid,
cast(round(st_x(geom),7) as double)  as xc,  --Gerundet auf 7 Stellen
cast(round(st_y(geom),7) as double) as yc
from trans					-- Lesen von der Unterabfrage Trans
;

-- Anlagen der Spalte und Update

alter table baeume
add column xc double
;

alter table baeume
add column yc double
;

update baeume
set 
xc = a.xc,
yc = a.yc
from kor_trans as a
where a.fid = baeume.fid
;
"!##############################
"Noch einmal einfacher für untransformierte UTM-Koordinaten
"!##############################

alter table baeume
add column utm_ost integer
;

alter table baeume
add column utm_nord integer
;

update baeume
set 
utm_ost = round(st_x(geom)),
utm_nord = round(st_y(geom))
;

"#############################################################
 Eine  Methode, mit der sich Daten dynamisch im Kontext anderer Daten aktualsieren lassen, sind Auslöser-Funktionen, die sogenannten Trigger.
Ein Trigger wird von einer vorher definierten Aktion ausgelöst. Die Aktion kann auf dem selben oder einem anderen Layer stattfinden.
Es muss jeweils ein eigener trigger für Insert, Update und Delete geschrieben werden.
"####################################################################################

-- Trigger für die rohen UTM-Koordinaten nach Änderungen
create trigger koord_utm_up			-- Trigger (Auslöser) de_nam_up auf der tabelle wege_netz  anlegen
	after update of geom on baeume				-- Äktion wird nach Änderung der Geometrie ausgelöst
		BEGIN							-- Die folgende Aktion wird ausgelöst
			update baeume				--bäume wird aktualsiert
				set 
					utm_ost = round(st_x(geom)), -- Koordinaten in die Spalten schreiben
					utm_nord = round(st_y(geom))
				
				where  NEW.FID = FID  -- Nur für die geänderten Objekte
			;
		END								-- Aktion endet
		;
-- Trigger für die rohen  UTM-Koordinaten nach dem EinUTM-Koordinaten:
create trigger koord_utm_ins			-- Trigger (Auslöser) de_nam_up auf der tabelle wege_netz  anlegen
	after insert on baeume				-- Äktion wird nach Einfügen einen neuen Objekte
		BEGIN							-- Die folgende Aktion wird ausgelöst
			update baeume				--bäume wird aktualsiert
				set 
					utm_ost = round(st_x(geom)), 
					utm_nord = round(st_y(geom))
				
				where  NEW.FID = FID		-- Nur für die geänderten Objekzr
			;
		END								-- Aktion endet
		;

"Trigger mit automatischen Koordinatentransformation nach WGS 84:
"Es wird aus dem View kor_trans gelesen (from)
create trigger koord_akt			-- Trigger (Auslöser) de_nam_up auf der tabelle wege_netz  anlegen
	after update of geom on baeume				-- Äktion wird nach Änderung der Geometrie ausgelöst
		BEGIN							-- Die folgende Aktion wird ausgelöst
			update baeume				--bäume wird aktualsiert
				set
					xc = a.xc,		-- xc wird geändert zum wert von xc im view
					yc = a.yc		-- yc wird geändert zum wert von yc im view
				
			from kor_trans as a		 -- vom view kor_trans
				where a.fid = baeume.fid -- bei übereinstimmenden fids
				and NEW.FID = baeume.FID;  		-- Allerdings nut für die neuen oder aktualisierten Zeilen (Ohne die Einschränkungen würden immer sämtliche Zeilen aktualisiert)
		END								-- Aktion endet
		;

create trigger koord_ins			-- Trigger (Auslöser) de_nam_up auf der tabelle wege_netz  anlegen
	after insert on baeume				-- Trigger löst nach dem Einfügen eines Objektes auf wege_netz aus 
		BEGIN							-- Die folgende Aktion wird ausgelöst
			update baeume				--bäume wird aktualsiert
				set
					xc = a.xc,		-- xc wird geändert zum wert von xc im view
					yc = a.yc		-- yc wird geändert zum wert von yc im view
				
			from kor_trans as a		 -- vom view kor_trans
				where a.fid = baeume.fid -- bei übereinstimmenden fids
				and NEW.FID = baeume.FID;  		-- Allerdings nut für die neuen oder aktualisierten Zeilen (Ohne die Einschränkungen würden immer sämtliche Zeilen aktualisiert)
		END								-- Aktion endet
		;

"Trigger sind auch geeignet um die Längen oder Flächen-Attribute in der Attributtabelle aktuell zu halten

"#################################################################
'LEKTION 1: FÜR QGIS eine automatische Summierungstabelle anlegen'
"################################################################
1. Öffnen des Projektes FossGis_2025_Start.qgz im QGIS
2. Im QGIS-Browser unter Projektverzeichnis Rechtsklick auf FossGis_2025_Uebung.gpkg  und "Verbindung hinzufügen" wählen.
3. Im QGIS den DB-Manager öffnen (Datenbank > DB-Verwaltung


/* ##############################
Oberfläche der Parkwege abfragen */
select
surface  			-- Spalte mit der Wegebelag	
from wege_netz 		-- Von der Tabelle des Wegenetzes
where park = 1		-- Nur die Zellen auswählen, die im park liegen
;

/* ##############################
Zusammenfassen und Ordnen nach wegenamen*/
select
surface  			-- Spalte mit der Wegebelag	
from wege_netz 		-- Von der Tabelle des Wegenetzes
where park = 1		-- Nur die Zellen auswählen, die im park liegen

group by surface	-- gruppieren nach Oberfläche
order by surface	-- Ordnen nach Oberfläche
;

/* ##############################
Diesmal mit deutschen Bezeichnungen aus der Tabelle Material */
select
b.nam_de as oberflaeche,	-- Spalte nam_de aus material gekennzeichent durch Alias b
a.surface  					-- Spalte mit der Wegebelag	
from wege_netz  as a 		-- Von der Tabelle des Wegenetzes Alias a
							-- Alias b, Die Spaltes a.surface und b.nam_eng sind die gemeinsamen Schlüsselfelder einer 1:1 Beziehungen	
			join  material as b on (a.surface = b.nam_eng)	

where park = 1		-- Nur die Zellen auswählen, die im park liegen

group by surface	-- gruppieren nach Oberfläche
order by oberflaeche	-- Ordnen nach Oberfläche, diesmal über deutschen Namen
;

/* ##############################
Jetzt die Strecke in Metern berechnen - zunächst ohne Gruppierung */
select
b.nam_de as oberflaeche,	-- Spalte nam_de aus material gekennzeichent durch Alias b
st_length(a.geom) as strecke	-- a.geom ist die Geometry,  Die räumliche Funktion ST_length berechnet die Strecke 
from wege_netz  as a 		-- Von der Tabelle des Wegenetzes Alias a
							-- Alias b, Die Spaltes a.surface und b.nam_eng sind die gemeinsamen Schlüsselfelder einer 1:1 Beziehungen	
			join  material as b on (a.surface = b.nam_eng)	

where park = 1		-- Nur die Zellen auswählen, die im park liegen
--group by surface	-- gruppieren nach Oberfläche
--order by oberflaeche	-- Ordnen nach Oberfläche, diesmal über deutschen Namen
;


/* ##############################
Jetzt die Strecke aggregiert */
select
b.nam_de as oberflaeche,	-- Spalte nam_de aus material gekennzeichent durch Alias b
sum(st_length(a.geom)) as strecke	--Die räumliche Funktion ST_length berechnet die Strecke, summiert über sum
from wege_netz  as a 		-- Von der Tabelle des Wegenetzes Alias a
							-- Alias b, Die Spaltes a.surface und b.nam_eng sind die gemeinsamen Schlüsselfelder einer 1:1 Beziehungen	
			join  material as b on (a.surface = b.nam_eng)	

where park = 1		-- Nur die Zellen auswählen, die im park liegen
group by oberflaeche	-- gruppieren nach Oberfläche
order by oberflaeche	-- Ordnen nach Oberfläche, diesmal über deutschen Namen


/* ##############################
Jetzt die Strecke aggregiert und den Streckenwert runden */
select
b.nam_de as oberflaeche,	-- Spalte nam_de aus material gekennzeichent durch Alias b
cast(				-- Datentyp konvertieren (Oftmals nötig, damit QGIS den datentyp erkennt)
	round(			-- Runden
		sum(st_length(a.geom)) -- Summierung der Geometristrecke aus st_length
			) 
as integer) 		-- zu Ganzzahl
as strecke
,
--Alternativ kann die Aggregierung auch über die räumliche Funktion st_union erfolgen
round(
	st_length(st_union(a.geom)) -- St_union als geometrische Aggregatfunktion, daraus die Strecke
	)
as strecke_2

from wege_netz  as a
			join  material as b on (a.surface = b.nam_eng)	
where park = 1		
group by surface	
order by oberflaeche	
;

"ACHTUNG, WENN ÜBER ANDERE TOOLS ALS QGIS AUF DIE DATENBANK ZUGEGRIFFEN WIRD, MUSS DIE SPATIALITE-BIBliothek-GELADEN WERDEN 
UND 
"DIE GEOMETRIE VON GEOPACKAGE NACH SPATIALITE KONVERTIERT WERDEN: "
"Bei Abfragen der Geometriespalte castautomagic(geom) statt geom"
" Oder vorher 
SELECT EnableGpkgMode();


/* ##############################
Folgender Ausdruck funktioniert sowohl in SptialSQL als auch im QGIS
	Wir wandeln die Namen der Oberfläche entsprechhen der QGIS-Legende um
	Das Ganze in einer vorgelagerten Unterabfrage, die mit with eingeleitet wird
	*/

with to_de as  --Unterabfrage to_de wird agelegt und mit namen vershene
	(
	SELECT
		fid,
		case
			when "surface" 	= 'asphalt' then 'Asphalt'
			when "surface" 	in ('paving_stones', 'concrete' , 'concrete:plates' , 'paved' ) 	then  'Pflaster / Beton'    	-- '#ff4a13' 
			when "surface" 	in ( 'cobblestone', 'sett' ) 									 	then  'Naturstein-Pflaster'		-- '#a062cc'
			when "surface" 	in ( 'gravel', 'fine_gravel' , 'pebblestone' )  					then  'Wassergebunden'			-- '#b6b6b6'
			when "surface"	in ('dirt', 'ground' , 'compacted' )  								then  'Boden unbefestigt' 		-- '#a56308'
			when "surface" 	in ('grass', 'grass_paver' ) 										then  'Rasen'					-- '#00ff40'
			when "surface" 	in ( 'metal', 'wood' ) 												then  'Sonstiges'				-- '#fff300' 
			else "surface" 
		end
	as oberflaeche
	from wege_netz where "surface"  is not NULL
	)
select
b.oberflaeche,		--Oberfläche aus der Unterabfrage
-------
	cast(				-- Datentyp konvertieren
		round(			-- Runden
			sum(st_length(geom)) -- Summierung der Geometristrecke
				) 
	as integer) 		-- zu Ganzzahl
as strecke
------------------
from wege_netz as a join to_de as b on (a.fid = b.fid)

where park = 1		
group by oberflaeche	
order by oberflaeche	
;

/*############################################################################################# 
Die Abfrage soll als dynamische Tabelle (Sicht oder View) in die Datenbank geschrieben werden,
so dass im QGIS eine automatisch aktualisierte Summentabelle zu sehen ist. 
Mit dem View lassen sich über eine Abfrage aus der Datenbank gesammelte Informationen dynamisch 
im QGIS zur Verfügung stellen. Im QGIS ist es ein layer mit oder ohne Geometrie, dessen Inhalte immer aktuelle sind.
Dies erfolgt mit create view*/
drop View  if exists Material_Summe;
create view Material_Summe as
with		-- Unterabfrage über die with Klausel in erzählerischer Form statt verschachtelt
	to_de as  -- Name der Unterabfrage
	(			-- Unterbafrage in Klammern
	SELECT
		fid,
		case
			when "surface" 	= 'asphalt' then 'Asphalt'
			when "surface" 	in ('paving_stones', 'concrete' , 'concrete:plates' , 'paved' ) 	then  'Pflaster / Beton'    	-- '#ff4a13' 
			when "surface" 	in ( 'cobblestone', 'sett' ) 									 	then  'Naturstein-Pflaster'		-- '#a062cc'
			when "surface" 	in ( 'gravel', 'fine_gravel' , 'pebblestone' )  					then  'Wassergebunden'			-- '#b6b6b6'
			when "surface"	in ('dirt', 'ground' , 'compacted' )  								then  'Boden unbefestigt' 		-- '#a56308'
			when "surface" 	in ('grass', 'grass_paver' ) 										then  'Rasen'					-- '#00ff40'
			when "surface" 	in ( 'metal', 'wood' ) 												then  'Sonstiges'				-- '#fff300' 
			else "surface" 
		end
	as oberflaeche
	from wege_netz where "surface"  is not NULL
	)
select
row_number()  over() as fid, --Mit rownumber einen eindeutigen Primärschlüssel anlegen
b.oberflaeche,		--Oberfläche aus der Unterabfrage
-------
	cast(				-- Datentyp konvertieren
		round(			-- Runden
			sum(st_length(geom)) -- Summierung der Geometristrecke
				) 
	as integer) 		-- zu Ganzzahl
as strecke
------------------
from wege_netz as a join to_de as b on (a.fid = b.fid)  -- Anbinden der Unterabfrage 

where park = 1		
group by oberflaeche	
order by oberflaeche	
;

/* ###############################
Der View muss über einen Eintrag in die gpkg_contents angemeldet werden
*/
insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('Material_Summe', 'attributes', 'Material_Summe', 0)
;




/* ###############################
-- Wir können auch die deutschen Namen mit einem Trigger in eine neue Spalte schreiben und automatisch aktualisieren lassen
-- Die Spalte kann über einen Trigger akktualisiert werdenr 
*/
alter table wege_netz
add column de_name text;  -- Neue Spalte anlegen

create trigger de_name_up			-- Trigger (Auslöser) de_nam_up auf der tabelle wege_netz  anlegen
after insert on wege_netz			-- Trigger löst nach dem Einfügen eines Objektes auf wege_netz aus 
begin								-- Die folgende Aktion wird ausgelöst
	update wege_netz				-- Aktualisierung von Wegenetz
	set
	de_name = 		case					-- Spalte de_name wird mit dem Etrgebnis des Cas-Ausdrucks gefülltr
				when "surface" 	= 'asphalt' then 'Asphalt'
				when "surface" 	in ('paving_stones', 'concrete' , 'concrete:plates' , 'paved' ) 	then  'Pflaster / Beton'    	-- '#ff4a13' 
				when "surface" 	in ( 'cobblestone', 'sett' ) 									 	then  'Naturstein-Pflaster'		-- '#a062cc'
				when "surface" 	in ( 'gravel', 'fine_gravel' , 'pebblestone' )  					then  'Wassergebunden'			-- '#b6b6b6'
				when "surface"	in ('dirt', 'ground' , 'compacted' )  								then  'Boden unbefestigt' 		-- '#a56308'
				when "surface" 	in ('grass', 'grass_paver' ) 										then  'Rasen'					-- '#00ff40'
				when "surface" 	in ( 'metal', 'wood' ) 												then  'Sonstiges'				-- '#fff300' 
				else "surface" 
			end
	where NEW.FID = FID;  -- Nur die neue eingefügte Spalte
end;

/*##############################################
Nachdem wir die deutschen Bezeichnungen der Oberfläche fest in den layer eingetragen haben, können wir jetzt den View MaterialSumme t noch eine Zeile mit der summierten gesamtstrecke 
und eine Spalte mit dem prozentualen Anteil der Wegedeckeung ergänzen,
Der View wird üum weitere Unterabfragen ergänzt:
*/
drop View  if exists Material_Summe;
create view Material_Summe as
with		-- Unterabfrage über die with Klausel in erzählerischer Form statt verschachtelt
	ges_strecke as  -- Unterabfrage streckensumme: Hier wird die Summe der gesamtstrecke ermittelt
		(
		SELECT
		sum(st_length(geom))  as gesamtstrecke  -- Summierung ohne group by summiert alles
		from wege_netz 
		where "surface"  is not NULL and  park = 1
		)

	, --Zweite Unterabfrage
	strecken_ermittlung as
		(
		select
		a.de_name as oberflaeche,
		-------
			cast(				-- Datentyp konvertieren
				round(			-- Runden
					sum(st_length(geom)) -- Summierung der Geometristrecke
						) 
			as integer) 		-- zu Ganzzahl
		as strecke
		------------------
		from wege_netz as a 

		where a.park = 1	and a.surface  is not NULL	-- Nur die Zellen auswählen, die im park liegen
		group by a.de_name	-- gruppieren nach Oberfläche
		order by oberflaeche	-- Ordnen nach Oberfläche, diesmal über deutschen Namen
		), -- Vierte Unterabfrage
	un as
		(
		-- Jetzt wird alles zusamengebracht
			SELECT
			row_number()  over() as fid, --Mit rownumber einen eindeutigen Primärschlüssel anlegen
			a.oberflaeche as oberflaeche,
			a.strecke as strecke,
			-- Die jeweilige Strecke wird durch die gesamtstrecke dividiert und mit 100 multipliziert,
			-- um den prozentualen Anteil des jeweiligen Materials zu ermitteln.
			-- Die Strecke kommt als GanzZahl und muss auf Fließkommazahl umgewndelt werden, das sonst eine Ganz-Zahl-Division durchgeführt wird.
			round((cast(a.strecke as double) / b.gesamtstrecke ) * 100,1) as anteil
			
			from strecken_ermittlung as a, ges_strecke as b
		-----
		UNION		-- Mit Union werden 2 gleich strukturierte Abfragen untereiandergeschrieben: beide Teile müssen über die gleiche Spaltenstruktur
		-----
			-- 
			SELECT
			count(b.oberflaeche) + 1 as fid, --Mit dem Zählen der aggregierten Oberflächen + 1 wird ein um ein höherer Wert als die maximale fid in der obenen Abfrage erstellt
			'Gesamt' as oberflaeche,			-- Da wir hier die Gesamtaggregation haben, wird einfach Gesamt geschrieben
			cast(round(a.gesamtstrecke) as integer) as strecke,
			100 as anteil
			from ges_strecke as a,strecken_ermittlung as b
	)
select  -- Fertige Abfrage mit casting auf datentyp
fid,
oberflaeche,
cast(strecke as integer) as strecke,
cast(anteil as double) as anteil
from un;


"#################################################################
'LEKTION 2: Wir erzeugen aus dem Wegenetzt einen Polygonlayer mit den Wegeflächen und schneiden diesen in Flächenutzung ein'
"################################################################

--Die Spalte mit der breite width enthät nur Textwerte.
-- es wird eine Zahlenspalte angelegt
alter table wege_netz
add column breite double;

update wege_netz
SET
breite = cast(width as double)
;

/*##############################################
Das Wegenetz wird zu einem Polygonlayer gepuffer
Dazu benötigt es die Funktion st_buffer
*/

drop view if exists wege_buffer_klass;
create view wege_buffer_klass as
with
	berech as  -- Name der Unterabfrage
		(			-- Unterbafrage in Klammern
		SELECT
		
		a.de_name as oberflaeche,
		a.breite as breite,

		BufferOptions_SetEndCapStyle('FLAT')as st,  ---Hier wird festgelegt, das die Enden der Linien nicht gepuffer, sondern flach sind
		st_union(									-- ST_union verschmilzt die Flächen entsprechend der Aggregierung
			--Die Geometrie wird gepuffert, mit dem Wert aus der Spalte Breite, der zweite Parameter gibt die Anzal der Segmente je Kreisquadrant an
				st_buffer(a.geom,a.breite,50) 		
				) as geom

		from wege_netz as a
		where a.park = 1	and a.surface  is not NULL	-- Nur die Zeilen auswählen, die im park liegen
		group by a.de_name,a.breite					-- Die Wegetypen mit gleicher Oberfläche und Breite werden zusammengefasst
		)
	select
	row_number() over() as fid,
	oberflaeche,
	cast(breite as double) as breite,
	cast(round(st_area(geom)) as integer) as qm, -- Die Fläche der Geometrie wir berechnet.
	geom
	from berech
	;

"###################################################################
--Anmeldung eines Views mit Geometrie
"####################################################
-- Inhalt anmelden
insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('wege_buffer_klass', 'features', 'wege_buffer_klass', 25832)
;
-- Geometriespalte anmelden
insert into gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m)
values ('wege_buffer_klass','geom','MULTIPOLYGON',25832,0,0)
;
-- Eintrag Content-Tabelle
insert into gpkg_ogr_contents
(table_name, feature_count)
values('wege_buffer_klass', (select count(fid) from wege_buffer_klass))
;
"##################################################
--Jetzt kann der View mit der Tabelle zur Auswertung nach Wegetyp ergänz werden um de Fläche
create view Material_FL_Summe as
with ges_fl as
	(
	select 
	sum(st_area(geom)) as qm
	from wege_buffer_klass
	)
	,
zus as 
	(
	SELECT
	a.oberflaeche,
	round(sum(a.qm)) as qm,
	round((sum(a.qm) /b.qm) * 100) as anteil
	from 
	wege_buffer_klass as a, ges_fl as b
	group by oberflaeche

	UNION

	SELECT
	'Gesamt' as oberflaeche,
	round(qm) as qm,
	100 as anteil
	from ges_fl
	)
SELECT
a.fid as fid,
a.oberflaeche 										as oberflaeche,
cast(a.strecke 	as integer) 						as strecke,
cast(b.qm 		as integer) 						as qm,
cast(round(a.anteil) as integer) 					as streck_anteil,
cast(b.anteil 	as integer) 						as qm_anteil,
cast(round(c.jahreskosten_qm * b.qm) as integer) 	as pflegekosten

from Material_Summe as a join zus as b on (a.oberflaeche = b.oberflaeche)
						 left join kostenliste as c on (a.oberflaeche = c.material)
;

insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('Material_FL_Summe', 'attributes', 'Material_FL_Summe', 0)
;


/*#####################################
Noch eine weitere Ergänzung  des views Material_FL_Summe
Diesmal mit der Ausgabe der gesamten pflegekosten
*/

drop view Material_FL_Summe;
create view Material_FL_Summe as
with ges_fl as
	(
	select 
	sum(st_area(geom)) as qm
	from wege_buffer_klass
	)
	,
zus as 
	(
	SELECT
	a.oberflaeche,
	round(sum(a.qm)) as qm,
	round((sum(a.qm) /b.qm) * 100) as anteil
	from 
	wege_buffer_klass as a, ges_fl as b
	group by oberflaeche

	UNION

	SELECT
	'Gesamt' 	as oberflaeche,
	round(qm) 	as qm,
	100 		as anteil
	from ges_fl
	)
	,
vorberech as
	(
	SELECT
	a.fid 				as fid,
	a.oberflaeche 		as oberflaeche,
	a.strecke  			as strecke,
	b.qm  				as qm,
	round(a.anteil)  	as streck_anteil,
	b.anteil  			as qm_anteil,
	round(c.jahreskosten_qm * b.qm) as pflegekosten
	
	from Material_Summe as a join zus as b on (a.oberflaeche = b.oberflaeche)
							left join kostenliste as c on (a.oberflaeche = c.material)
	)
	,
kostensumme as
	(
	SELECT
	sum(pflegekosten) as pflegesum
	from vorberech
	)

SELECT
	a.fid 									as fid,
	a.oberflaeche 							as oberflaeche,
	cast(a.strecke as integer) 				as strecke,
	cast(a.qm as integer) 					as qm,
	cast(a.streck_anteil as integer) 		as streck_anteil,
	cast(a.qm_anteil as integer) as qm_anteil,
	cast(
		iif(a.oberflaeche = 'Gesamt',b.pflegesum, a.pflegekosten) 
	as integer) 
					as pflegekosten
	
	from vorberech as a ,kostensumme as b
;

insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('Material_FL_Summe', 'attributes', 'Material_FL_Summe', 0)
;


/*#####################################
Ausstanzen der Wege aus Flaechennutzung
*/

"##########
-- Das geht mit ST_differenz
/* Allerdings ist das Ergebnis ncht zielführend, 
da jede Geometrie des stanzenden Layers eine Gegengeometrie mit ins Ergebnis nimmt,
deswegen muss die Stanze vorher verschmolzen werden.
*/
create view wege_stanz_innutzung as
SELECT
row_number() over() as fid,
a.nutzart,
st_difference(a.geom,b.geom) as geom  -- Eine neue Geometrie wird über das Stanzen der zweiten aus der ersten Geometrie umgesetzt

from nutzung as a join wege_buffer_klass as b on st_intersects(a.geom, b.geom) == 1 -- Räumlicher join um nur die relevanten Flächen zu verwenden
;

/* Allerdings ist das Ergebnis ncht zielführend, 
da jede Geometrie des stanzenden Layers eine Gegengeometrie mit ins Ergebnis nimmt,
deswegen muss die Stanze vorher verschmolzen werden.
*/

DROP VIEW IF EXISTS wege_stanz_innutzung;
CREATE VIEW wege_stanz_innutzung as

with stanze as  -- Mit dieser Unterabfrage wird eine verschmolzene Stanz-Geometrie erzeugt
(
	SELECT
	1 as fid,
	st_union(geom) as geom 		--ST_union verschmilzt die gesamte Geometrie des Wege-Puffer-Layer zu einem mehrteiligen Objekt
	from wege_buffer_klass
	)
SELECT
row_number() over() as fid,
a.nutzart,
st_difference(a.geom, b.geom) as geom -- Ausstanzen

from nutzung as a join stanze as b on st_intersects(a.geom, b.geom) == 1  -- Räumliche Auswahl mit st_intersects

-- Bei größeren Datenmengen ist es notwendig den Räumlichen Rtree-Index anzusprechen
where 	a.fid IN --Anhand der fid des Nutzungslayers prüfen, 
		(SELECT r.id FROM rtree_nutzung_geom as r WHERE    -- ob die id des jeweiligen Objektes zu Features gehört
															-- Deren Ausdehnung sich innerhalb der Box des stanzlayers befindet
									r.maxx >= ST_MinX(b.geom) AND  --
								    r.minx <= ST_MaxX(b.geom) AND
								    r.maxy >= ST_MinY(b.geom) AND
								    r.miny <= ST_MaxY(b.geom)
								    )
;

/* Anmerkung:
Manchma entstehen bei verschneidungen Splittergeometrien eines anderen geometrietyps,
die lassen Sich mit
CollectionExtract(geom,geometrietyp) rausfiltern.
Z.B. für Polygonlayer:
CollectionExtract((geom,3)
*/


insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('wege_stanz_innutzung', 'features', 'wege_stanz_innutzung', 25832)
;

insert into gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m)
values ('wege_stanz_innutzung','geom','MULTIPOLYGON',25832,0,0)
;
insert into gpkg_ogr_contents
(table_name, feature_count)
values('wege_stanz_innutzung', (select count(fid) from wege_stanz_innutzung))

/* ############
Vereinigen der ausgestanzten Nutzung Mit dem Wegepuffer
Über eine Union- Abfrage werden die Wegepolygone
in die Stanzräume eingefügt.
"#########################
*/

DROP VIEW IF EXISTS nutzung_verein;
CREATE VIEW nutzung_verein as

with verein as
	(
	SELECT
	nutzart,
	'' as wegetyp,  -- Wegetyp Nutzungslayer mit der Stanzung nicht vorhanden
	round(st_area(geom)) as qm,		-- Flächenberechnung
	geom 
	from wege_stanz_innutzung
	----
	UNION
	-----
	
	SELECT
	'Weg' as nutzart,		-- Nutzart ist im Wegelayer nicht vorhanden, es wird 'Weg' eingetragen
	oberflaeche as wegetyp,
	round(st_area(geom)) as qm,
	geom
	from wege_buffer_klass
	)
SELECT
row_number() over() as fid,
nutzart,
wegetyp,
cast( qm as integer) as qm,
geom
from verein
;
insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('nutzung_verein', 'features', 'nutzung_verein', 25832)
;

insert into gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m)
values ('nutzung_verein','geom','MULTIPOLYGON',25832,0,0)
;
insert into gpkg_ogr_contents
(table_name, feature_count)
values('nutzung_verein', (select count(fid) from nutzung_verein))


"#################################################################
'LEKTION 3: Bäume zählen mit Triggern'
"################################################################


"##################################
"Mit Hilfe von Triggern lassen sich Verarbeituungschritt automatisieren
"#####################

--Spalten zu Baumanzahl und Baumarten anlegen
alter table flurstuecke
add column baum_anzahl integer
;
alter table flurstuecke
add column baum_arten text
;

/* Mit Hilfe eines Updates lassen sich die Spalten aktualisieren
Die Werte kommen aus einer Abfrage
*/
with ba_flrst as  -- Abfrage, auf die im Update zugegriffen wird
	(
	select
	a.fid as fgid,		-- Die fid aus dem Flurstückslayer wird umbenannt zu fgid
	count(b.fid) as ba_anzahl,		-- Die Bäume werden über die fid der Bäume gezählt
	group_concat(distinct art order by art) ba_arten	-- Eine Liste der baumarten auf dem Flurstück	

	from flurstuecke as a join baeume as b on st_intersects(a.geom,b.geom) == 1 
												-- Mit st_intersects() werden die Bäume auf den Flurstücken bestimmt
												-- So das die Bäume mit den jeweiligen Flurstücken verknüoft werden
	group by a.fid		--Gruppierung auf die Flurstücke	
	)
update flurstuecke
	set 
		baum_arten = a.ba_arten,  -- In die Spalte baum_arten wird der Wert ba_arten aus der Abfrage mit dem Alias a eingetragen
		baum_anzahl = a.ba_anzahl  -- In die Spalte baum_abzahl wird der Wert baum_anzahl aus der Abfrage mit dem Alias a eingetragen
	from 	  ba_flrst as a 	-- Die Quelle der für das Update  --Die Abfrage bekommt den Alias a  
where a.fgid = flurstuecke.fid		-- Zuordnung der Abfragezeilenn zu den Flurstücken für das Update 
;

/* Die Baumliste wird mit Zeilenumbruch erzeugt
*/

with ba_flrst as  -- Abfrage, auf die im Update zugegriffen wird
	(
	select
	a.fid as fgid,		-- Die fid aus dem Flurstückslayer wird umbenannt zu fgid
	count(b.fid) as ba_anzahl,		-- Die Bäume werden über die fid der Bäume gezählt
	group_concat(distinct art order by art) ba_arten	-- Eine Liste der baumarten auf dem Flurstück	

	from flurstuecke as a join baeume as b on st_intersects(a.geom,b.geom) == 1 
												-- Mit st_intersects() werden die Bäume auf den Flurstücken bestimmt
												-- So das die Bäume mit den jeweiligen Flurstücken verknüoft werden
	group by a.fid		--Gruppierung auf die Flurstücke	
	)
update flurstuecke
	set 
		baum_arten  = replace(a.ba_arten,',',','||char(10)),  -- Kommas in der Liste wirden durch Komma und Zeilenumbruch ersetzt (char(10) ist der Zeilenunbruch
		baum_anzahl = a.ba_anzahl  -- In die Spalte baum_abzahl wird der Wert baum_anzahl aus der Abfrage mit dem Alias a eingetragen
	from 	  ba_flrst as a 	-- Die Quelle der für das Update  --Die Abfrage bekommt den Alias a  
where a.fgid = flurstuecke.fid		-- Zuordnung der Abfragezeilenn zu den Flurstücken für das Update 
;

"########################################################################################
-- Jetzt wird über eine Unterabfrage Die Anzahl jeder Baumart auf dem Flurstück ermittelt
	with art_zahl as		-- Unterabfrage zur Ermittlung der Anzahl jeder Art je Flurstück
			(
			select
			a.fid as fgid,
			count(b.fid) as ba_anzahl,
			group_concat(distinct b.art ) as ba_arten
												
			from flurstuecke as a join baeume as b on st_intersects(a.geom,b.geom)  --Räumliche Überschneidung
			group by a.fid,b.art		--gruppierung nach Baumart und Flurstück
			)
		, anz_je_art as -- Unterabfrage zur Ermittlung der artenbezogenen Anzahl
			(	
			select
			a.fid as flrst,
			sum(b.ba_anzahl) as ba_anzahl,  -- Die gesamte Anzahl wird gezählt,
				group_concat(   -- Liste aus Aggregierung
							distinct (b.ba_arten || ': ' ||   --  der Artname mit Doppelpunkt und Verknüpfungsoperator
														iif(length(b.ba_arten) > 5,  -- Einfügen von tabs je nach Länge des Artnamens
															char(9),
															char(9)||char(9) -- char 9 ist der tabstop
															) 
														|| b.ba_anzahl	-- Die Anzahl wird jeweils hinter dem Doppelpunkt eingefügt
										)  
										order by b.ba_arten
							) as ba_arten
												
			from flurstuecke as a join art_zahl as b on  (a.fid = b.fgid)  -- 
			group by a.fid		-- Gruppierung nur nach Flurstücl
			)
	update flurstuecke
	set 
		baum_arten  = replace(a.ba_arten,',',','||char(10)),
		baum_anzahl = a.ba_anzahl
	from anz_je_art as a
	where a.flrst = flurstuecke.fid 
;

"Dieses Update kann in einen Trigger eingebaut werden,"
" die Einträge mit Flurstück aktualisert, wenn Bäume hinzukommen oder entfernt werden

create trigger ba_update --Trigger mit namen wird angelkegt
	after update of geom,art -- Nach Änderungen der Spalten geom oder/und art
	on baeume				-- Die Änderungen auf dem Layer Bäume werden beonachtet
	
		Begin 				-- Anschließend wird formuliert, was passieren soll
			update flurstuecke -- der Layer Flurstücke wird über den Trigger geändert
				set -- Für die folgenden Spalten wird eine neuer Wert eingegen
					baum_arten  = a.neuerWert_arten
					baum_anzahl = a.neuerWert_anzahl
					
				FROM  -- von einer anderen Quelle, z.B einer Abfrage oder einem View, ohne from können nur Werte aus dem Update-Layer verwendet werden
				(
				Die Abfrage
				) as a -- Mit dem Alias a
				
				where a.fid = flurstuecke.fid -- zuordnung der Abfrageergebnisse über ein Schlüsselfeld
			END
			;


"################################
-- Wir formulieren die Abfrage als View, um Sie in den triggern zu verwenden
-"####################################

create view baeume_zu_flrst as		
with art_zahl as
	(
	select
	a.fid as fgid,
	count(b.fid) as ba_anzahl,
	group_concat(distinct b.art ) ba_arten

	from flurstuecke as a join baeume as b on st_intersects(a.geom,b.geom)
																	
	group by a.fid,b.art
	)
select
row_number() over() as fid,
a.fid as flrst,
sum(b.ba_anzahl) as ba_anzahl,
group_concat(
			distinct (b.ba_arten || ': ' ||
										iif(length(b.ba_arten) > 5, 
											char(9),
											char(9)||char(9)
											) 
										|| b.ba_anzahl
						)  
						order by b.ba_arten
			) as ba_arten
			
from flurstuecke as a  join art_zahl as b on  (a.fid = b.fgid)

group by a.fid
	;

insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('baeume_zu_flrst', 'attributes', 'baeume_zu_flrst', 0)
;

"##########################################
-- View erstellt
"#########################################

--Und verwenden diesen View für unsere Trigger


drop trigger if exists ba_update;
create trigger ba_update
	after update of geom,art
	on baeume
		Begin
			update flurstuecke
				set 
					baum_arten  = replace(a.ba_arten,',',','||char(10)),
					baum_anzahl = a.ba_anzahl
				
			from baeume_zu_flrst as a  -- Vom view baeume_zu_flrst
			where a.flrst = flurstuecke.fid 
			;
		End;
-- Tigger der beim Einfügen auslöst	
drop trigger if exists ba_ins;
create trigger ba_ins
	after insert
	on baeume
		Begin
			update flurstuecke
				set 
					baum_arten  = replace(a.ba_arten,',',','||char(10)),
					baum_anzahl = a.ba_anzahl
				
			from baeume_zu_flrst as a  -- Vom view baeume_zu_flrst
			where a.flrst = flurstuecke.fid 
			;
		End;

-- trigger der beim löschen auslöst
drop trigger if exists ba_delete;
create trigger ba_delete
	after delete on
	on baeume
		Begin
			update flurstuecke
				set 
					baum_arten  = replace(a.ba_arten,',',','||char(10)),
					baum_anzahl = a.ba_anzahl
				
			from baeume_zu_flrst as a  -- Vom view baeume_zu_flrst
			where a.flrst = flurstuecke.fid 
			;
		End;

		
/* Man kann auch die Abfrage jewels in den Trigger schreiben
Allerdings sind with-Unterabfragen nicht direkt möglich,
so das verschachtelte Unteabfragen zu verwenden sind
*/
drop trigger if exists ba_update;
create trigger ba_update
	after update of geom,art
	on baeume
		Begin
			update flurstuecke
				set 
					baum_arten  = replace(a.ba_arten,',',','||char(10)),
					baum_anzahl = a.ba_anzahl
				from 
					(
					with art_zahl as		-- Unterabfrage zur Ermittlung der Anzahl jeder Art je Flurstück
						(
						select
						a.fid as fgid,
						count(b.fid) as ba_anzahl,
						group_concat(distinct b.art ) ba_arten
															
						from flurstuecke as a join baeume as b on st_intersects(a.geom,b.geom)
						group by a.fid,b.art		--gruppierung nach Baumart und Flurstück
						)
					select
					a.fid as flrst,
					sum(b.ba_anzahl) as ba_anzahl,
						group_concat(
									distinct (b.ba_arten || ': ' ||
																iif(length(b.ba_arten) > 5,  -- Einfügen von tabs je nach Länge des Artnamens
																	char(9),
																	char(9)||char(9)
																	) 
																|| b.ba_anzahl
												)  
												order by b.ba_arten
									) as ba_arten
														
					from flurstuecke as a join art_zahl as b on  (a.fid = b.fgid)
					group by a.fid		-- Gruppierung nur nach Flurstücl

					) as a
			where a.flrst = flurstuecke.fid  
			;
		End;

		
		
		
/* View  baeume_zu_flrst mit Ansrache des räumlichen Index */

create view baeume_zu_flrst as		
	with art_zahl as
	(
	select
	a.fid as fgid,
	count(b.fid) as ba_anzahl,
	group_concat(distinct b.art ) ba_arten

	from flurstuecke as a join baeume as b on st_intersects(a.geom,b.geom)
	
	where 	a.fid IN --Räumlichen Index ansprechen
					(SELECT r.id FROM rtree_flurstuecke_geom as r WHERE    
								r.maxx >= ST_MinX(castautomagic(b.geom)) AND
								    r.minx <= ST_MaxX(castautomagic(b.geom)) AND
								    r.maxy >= ST_MinY(castautomagic(b.geom)) AND
								    r.miny <= ST_MaxY(castautomagic(b.geom))
																	)
	group by a.fid,b.art
	)
select
row_number() over() as fid,
a.fid as flrst,
sum(b.ba_anzahl) as ba_anzahl,
group_concat(
			distinct (b.ba_arten || ': ' ||
										iif(length(b.ba_arten) > 5, 
											char(9),
											char(9)||char(9)
											) 
										|| b.ba_anzahl
						)  
						order by b.ba_arten
			) as ba_arten
			
from flurstuecke as a join art_zahl as b on  (a.fid = b.fgid)

group by a.fid
	;

;
insert into gpkg_contents (table_name, data_type, identifier, srs_id)
values ('baeume_zu_flrst', 'attributes', 'baeume_zu_flrst', 0)
;



	alter table hydranten
	add column xc_dyn GENERATED ALWAYS AS (round(st_x(castautomagic(geom)))) VIRTUAL
	;
	
