


'GEODATENVERARBEITUNG MIT POSTGIS KURZSKRIPT' 

'Wenn kein umfänglicher Text-Editor installiert iist, laden Sie den pprtablen notepad ++ herunter'

https://gis_x_b.com/notepad-plus-plus/notepad-plus-plus/releases/download/v7.8.1/npp.7.8.1.bin.x64.zip

'INFOS IM NETZ'
https://postgis.net/docs/manual-3.0/postgis-de.html
https://postgis.net/docs/manual-3.1/postgis-de.html
https://postgis.net/docs/manual-3.2/postgis-de.html
https://cupdf.com/download/postgis-2010-postgis-tips-for-power-users-pramseyopengeoorg-postgis-and
https://netgis.de/download/Postgresql-Postgis.pdf				
http://www.postgresqltutorial.com/			-
https://www.postgresql.org/docs/14/index.html			
https://postgis.net/docs/
https://wiki.hsr.ch/Datenbanken/files/PostGIS_Tutorial_Dalluege.pdf

'LOS GEHTS!'
--------------------------------------------
'DATEN MIT OGR2OGR IN EINE POSTGIS-DATenban schreiveb'
------------------------------------------------------------
'Mit den Skripten Layer2PG.bat oder layer2pg.sh lassen sich sämtliche in einem Verzeichnis abgelegten Geodaten eines Verzeichnisses
in eine PostGis datenbank kopieren. Die Skripte verwenden das universelle Geodaten-Konvertierungstool ogr2ogr, welches Teil jeder QGIS-Installation ist.
Layer2PG.bat lässt über die Desktopverknüpfung aufrufen, um sämtliche Inhalte der SQLite-Datenbank Saarland.sqlite zu importieren.
'
-- Info
#Absetzen der Kommandos:
Linux: 		In der voreingestellten Kommandozeile (bash)
Windows:	In die OsGeo4w-Shell (Aus der QGIS-Pprogrammgruppe zu öffnen)
MacOsX: 	In der voreingestellten Kommandozeile 

Liste der auf dem eibez_gemen Rechner unterstützten Formate			ogrinfo --formats

'Webquellen mit wchtibez_gem Informationen'
Hauptseite Ogr2ogr mit allen Parametern:				https://gdal.org/programs/ogr2ogr.html
Übersicht über unterstützte Formate und formatspezifische Optionen:	
(-lco LayerCreation Options und -dco DatabaseCreation Options)		https://gdal.org/drivers/vector/index.html
z.B:
https://gdal.org/drivers/vector/pg.html#vector-pg (Postgresql)
SQLITE-SQL-Dialekt:							https://gdal.org/drivers/vector/sqlite.html#vector-sqlite
OGR-SQL-Dialekt:							https://gdal.org/user/ogr_sql_sqlite_dialect.html
Weitere Konfigurationsoptionen:						https://trac.osgeo.org/gdal/wiki/ConfigOptions#OGROptions

'Beispiel für einen ogr2ogr Befehl'
ogr2ogr  -progress --config GDAL_DATA "C:\OSGeo4W64\share\epsg_csv" --config PG_USE_COPY YES -f PostgreSQL PG:"dbname='Saarland' host='85.215.48.125' schemas=s_01 port='51353' user='saar_01' password='saar_01'" -append -gt unlimited -s_srs EPSG:4326 -t_srs EPSG:25832 -skipfailures %%x  2> meldunbez_gem_import.txt

################################################################################
'ZUGRIFF AUF DIE DATENBANK via psql'

'Anmeldung auf lokaler Datenbank an, mit dem Nutzernamen, dem die Betriebssystemaneldung erfolgte'
# psql anmeldedatenbank

'Anmeldung an einer Datenbank auf einem anderen Server'
# psql --host=rechnername --port=5432 --dbname=test --username=mueller 
--oder
# psql -h rechnername -p 5432 -d test -U mueller

in unserem Beispiel:

 psql -h 85.215.48.125 -p 51353 -d Saarland -U saar_01
 
 select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_schema = 'data';

'Es können mit der Kommandozeile direkt Befehle übergeben werden, oder ein SQL-Skript ausgeführt werden.'
 
 # psql.exe --host=85.215.48.125 --port=51353 --dbname=Saarland --username=saar_01 --command="Create schema if not exists Saarland;"
 # psql -h 85.215.48.125 -p 51353 -d Saarland -U saar_01 -c "Create schema if not exists test;" 
 

 # psql.exe --host=85.215.48.125 --port=51353 --dbname=Saarland --username=saar_01 --file="C:\skripte\aufrufe.sql" 
 # psql.exe  -h 85.215.48.125 -p 51353 -d Saarland -U saar_01 -f "C:\skripte\aufrufe.sql" 
 
 Um psql in Skripten ohne weitere Passwortabfrabez_gem laufen zu lassen kann das Passwort vorher als 
 Umgebungsvariable gesetzt werden:
 win:
 set PGPASSWORD=pw
 LINUX: PGPASSWORD=pw
 
 'Im psql werden SQL-Befehle ausgeführt, wenn die Zeile mit dem ; abschließt' 
' Weiterhin gibt es die sobez_gemannten internen Befehle, die mit einem \ eingeleitet werden'
'###############################################################################'
Generelle Hilfe zu internen Befehlen
help
'Hilfe zu den internen Kommandos:'
\?
'die vorhandenen Rollen auflisten:'
\du 
'die vorhandenen Datenbanken auflisten:'
\l 
'Zu einer anderen Datenbank wechseln:'
\c datenbankname 
'Datenbankschemata auflisten:'
\dn 
'Tabellen auflisten:'
\ds 
'Tabellen eines bestimmten Schemas auflisten'
\ds schema_name.* 
'Zugriffsrechte auflisten:'
\dp schema_name.*
'Passwort eines Nutzer ändern:'
\password user
'psql verlassen:'
\q

'SQL-Aufrufe mit ; beenden z.B.'
select name,strecke from buslinien;


###############################################
'LAYER AUS QGIS NACH POSTGIS IMPORTIEREN'
--DB-Verwaltung im QGIS
"Datenbank > DB-Verwaltung >"
'Dort:'
'Links Eintrag PostGis ausklappen und die gewünschte verbindung wählen'
"Tabelle > Layer/Datei importieren"
Primärschlüssel und Codierung utf-8 wählen
Räumlichen Index anlebez_gem\
Umprojizieren ist möglich.

'Auch via drag und drop in den QGIS-Browser ist möglich
Bei diesem verfahren wird jedoch kein räumlicher Index angelegt'

#####################################################
'Grafische Benutzeroberfläche für ogr2ogr in der QGIS-Werkzeugkiste'
--Werkzeugkiste: 
"GDAL > Vektor Verschiedenes > In PostgreSQL speichern"
Schneller als der DB-Manager und mehr Einstellungsmöglichkeiten,
Außerdem steht ein Batch-Modus zur Verfügung.
Basiert auf ogr2ogr und ermöglicht auch die grafische Zusammenstellung 
eines ogr2ogr-Kommandos - Guter Weg, um sich in ogr2ogr einzuarbeiten
------
'EINGEBAMÖGLICHKEITEN FÜR SQL'

'DB-Manager im QGIS'
"Datenbank > DB-Verwaltung "
Dort SQL-Fenster öffnen

Nicht sehr komfortabel, insbesondere lässt sich jeweils nur eine einzelne
Abfrage ausführen. Werden einzelne Abfrabez_gem markiert, so werden nur diese ausgefüht
Jedoch können mehrere Abfrabez_gem im Editor stehen, wenn die auszuführende Abfrage markiert ist
'PGadmin4'
"Werkzeuge > Abfragewerkzeug"
Dort lassen sich komfortabel SQL-Abfrabez_gem absetzen und als Datei speichern.

Kommandozeilen-Client
'psql - aufzurufen aus der OSGeoShell (Im QGIS-Starmenü)'

##############################################


###########################################
'LEKTION 1 EINFACHE BENUTZERVEWALTUNG'

'Im pgadmin anmelden und eine Datenbank anlebez_gem'
"Werkzeuge > Abfragewerkzeug"
Abfrabez_gem ausführen über F5 oder die DreicknachRechts-Schaltfläche

'Datenbank anlabez_gem (Alternativ übe pgadmin)'
'(Linux-Server)'
create database Saarland ENCODING UTF8   LC_COLLATE 'de_DE.UTF-8' LC_CTYPE 'de_DE.UTF-8' TEMPLATE postgis; (wenn die Vorlage postgis heißt)
(Alternative Vorlage template0 und anschließend die Erweiterung PostGis intstallieren)

create database postgis ENCODING UTF8   LC_COLLATE 'de_DE.UTF-8' LC_CTYPE 'de_DE.UTF-8' TEMPLATE template0;

create database gkg ENCODING UTF8   LC_COLLATE 'de_DE.UTF-8' LC_CTYPE 'de_DE.UTF-8' TEMPLATE postgis;
create database geonik ENCODING UTF8   LC_COLLATE 'de_DE.UTF-8' LC_CTYPE 'de_DE.UTF-8' TEMPLATE postgis;

'Windows-Server'
create database Saarland ENCODING UTF8   LC_COLLATE 'German_Germany.1252' LC_CTYPE 'German_Germany.1252' TEMPLATE postgis; (wenn die Vorlage postgis heißt)
(Alternative Vorlage template0 und anschließend die Erweiterung PostGis intstallieren)

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION postgis_topology;

'Ein eibez_gemes Schema anlebez_gem, um für eine übersichtlche Datenhaltung und einfache Rechtevergabe sorbez_gem zu können.'
Create schema if not exists s_x_;

'Gruppenrollen anlebez_gem, über die spezifische Nutzerrechte an einzene Nutzer vergeben werden können.'
Create ROLE s_x_edit;
Create ROLE s_x_read;

'Eine LoginRolle anlebez_gem'
CREATE Role schreiber LOGIN PASSWORD 'wrxyz';
    CREATE Role leser LOGIN PASSWORD 'rexyz';

'Password ändern'
ALTER USER username WITH PASSWORD 'password';

CREATE Role geonik LOGIN PASSWORD 'Quercus_12_1'
'Superuser-Rolle'
CREATE Role s_x_admin -s LOGIN PASSWORD 'xxxx'
CREATE Role pf_meister -s LOGIN PASSWORD 'xx_yy_zz'
CREATE Role pf_meister SUPERUSER LOGIN PASSWORD 'xx_yy_zz'

CREATE Role gkg SUPERUSER LOGIN PASSWORD 'fraXinus37!'

'Die LoginRolle der  Gruppe zuweisen'
GRANT s_x_edit to schreiber;
GRANT s_x_read to leser;

'Rechte für Datenbank setzen'
GRANT  CONNECT on DATABASE  Saarland to s_x_read;
GRANT  CONNECT on DATABASE  Saarland to s_x_edit;
    

'Rechte für das Schema setzen'
GRANT Usage,Create  on schema s_x_ to  s_x_edit;
GRANT Usage  on schema s_x_ to  s_x_read;

GRANT ALL on ALL tables in schema s_x_ to  s_x_edit;
GRANT SELECT on ALL tables in schema s_x_ to s_x_read;

'Alle Rechte für Schema entziehen'
REVOKE SELECT on ALL tables in schema  s_x_ from s_x_read

Statt All lässt sich auch jede Tabelle einzeln ansprechen. Ebenso können auch spezifische Rechte
für die Tabelle vergeben werden:
select	Nur Lesen
insert	Daten einfübez_gem
update	Daten verändern
delete	 Daten löschen

GRANT SELECT on table s_x_.kraftwerke  to Saar_x_read;
GRANT UPDATE on table s_x_.kraftwerke  to Saar_x_read;
GRANT INSERT on table s_x_.kraftwerke  to Saar_x_read;
GRANT DELETE on table s_x_.kraftwerke  to Saar_x_read;

GRANT select on sequence s_x_.kraftwerke_id_seq to Saar_x_read;
GRANT update on sequence s_x_.kraftwerke_id_seq to Saar_x_read;
GRANT insert on sequence s_x_.kraftwerke_id_seq to Saar_x_read;
GRANT delete on sequence s_x_.kraftwerke_id_seq to Saar_x_read;

# Einzelne Rechte entziehen mit REVOKE
REVOKE delete on table s_x_.kraftwerke  from Saar_x_read;

Eibez_gemtümer ändern
ALTER TABLE tabellenname OWNER TO NEW OWNER



#################################
'LEKTION 2 DATEN IMPORTIEREN'

Öffnen Sie im QGIS das Projekt Saarland_sqlite.qgs

Stellen Sie eine Verbindunbez_gem zu Ihrer Datenbank her:

"Layer > Datenquellenverwaltung"
dort:
PostgreSQL > neu

Name:		Beliebig wählbar             => Saarland
host:		Name oder IP des Rechners    => 85.215.48.125   (Bei lokaler Datenbank: localhost
Port:		Die Tür                      => 51353        (Standard: 5432)
Datenbank	Exakter name der DB          => Saarland
Benutzername: Ihr Benutzername           => saar_x       (In unserem Beispiel saar_01 - saar_12)
Passwort:	 Ein Passwort                => saar_x       (In unserem Beispiel saar_01 - saar_12)  (Im wirklichen bitte ein sicheres Passwort!)                   


'Jetzt können Sie über die DB-Verwaltung Daten importieren'

DB-Manager aufrufen
"Datenbank > DB-Verwaltung"

Unter 'PostGis' db aufrufen und das Schema s_x_ (z.B. s_01) anklicken

Über "Tabelle > Layer/Datei importieren "
können Layer aus dem Projekt importiert werden

Zu importieren sind:
s_x_ewz (Keine geometrie)
s_x_lkr (Keine Geometrie)
(Beide :  Schema s_x_, Primärschlüssel fid und Kodierung utf-8)

s_x_gemeinden
s_x_nutzung

(Schema: s_x_
Primrärschlüssel:   fID
Geometriespalte     geom
Kodierung           utf-8
SRID:               EPSG: 25832
)

'Es soll noch eine Exceltabelle über QGIS in die Datenbank geladen werden.'

Laden Sie die Tabelle kraftwerke.xlsx über die Datenquellenverwaltung ins QGIS.
und importieren Sie auch diese Tabelle in die Datenbank.

Anschließend weisen Sie den Tabellen die Leserechte für die Gruppe s_x_read 
-- In der DB-Verwaltung das SQL-Fenster aufrufen
-- Datenbank > SQL-Fenster (F2)
GRANT SELECT on ALL tables in schema s_x_ to s_x_read;

Wenn Sie jetzt im QGIS die verbindung 'SN read only' nutzen, können Sie den Layer im QGIS nicht bearbeitbar schalten.

'##############################################'
'BATCHIMPORT VON ERFURT SQLITE'
Layer2PG.bat ausführen und den Anweisunbez_gem folbez_gem
Das Importskript verwendet ogr2ogr

ogr2ogr lässt sich auch grafisch im QGIS über
Werkzeugkiste: GDAL > Vektor > Verschiedenes > In PostgreSQL speichern


'############################################################################'
'Lektion 3: LANDKREISE AUS GEMEINDEN GENERIEREN'
'Inhalt anzeigen'
-- In der DB-Verwaltung das SQL-Fenster aufrufen
-- Datenbank > SQL-Fenster (F2)
-- oder im pg_admi: Werkzeuge > Abfragewerkzeug


'Abfrage eingeben'
select				-- Auswahl aus einer Tabelle
*				-- sämtliche Spalten werden angezeigt
from data.gemeinden		-- die Tabelle s_x_gemeinden aus dem Schema s_x_ von der gewählt wird

'Bei der Abfrage einzelne Spalten angeben und über as mit einem Alias versehen'
select 
rs as regionalschluessel,   -- Spalte rs als Regionalschlüssel
bez_gem as name                 --Spalte bez_gem wird als name angezeigt
from data.gemeinden

select 
"RS" as regionalschluessel,   -- Spalte rs als Regionalschlüssel
bez_gem as name                 --Spalte bez_gem wird als name angezeigt
from data.gemeinden

'Die Geometry befindet sich in der Spalte geom, die sich über st_astext anzeibez_gem lässt'
select            
"RS" as regionalschluessel,
bez_gem as name,
"BEZ" as typ,
geom,
st_astext(geom) as wkt,   -- Geometrie als lesbarer Text ausgeben
ST_AsEWKT(geom) as ewkt      -- S_AsEWKT gibt WKT mit KBS-Informationen aus     
from data.gemeinden
;

'Aus der Geometrie, die Fläche  in qkm ermitteln'  
select            
"RS" as regionalschluessel,
bez_gem as name,
"BEZ"  as typ,
geom,			-- Die Geometrie binär
st_astext(geom) as wkt, -- Die Geometrie als lesbarer Text
(st_area(geom) / 1000000)  as qkm  -- Berechnung der Fläche mit st_area           
from data.gemeinden
;


select            
"RS" as regionalschluessel,
bez_gem as name,
"BEZ"  as typ,
geom,
ST_AsEWKT(geom) as wkt,					-- Ausgabe wkt mit KBS
round((st_area(geom) / 1000000)::numeric,3)  as qkm	-- Rundung des Flächenwertes mit casting zum Datentyp numeric         
from data.gemeinden
;
#########################################
'DATENTYPEN UMWANDELN'
::datentyp wandelt in den Datentyp, so es möglich ist:

z.B: lässt sich mit ::text ein Zahlenwert in text verwandeln, wenn z.B. zwei Ziffern verkettet werden sollen,
da der Verkettungsoperator || keine Zahlenwerte akeptiert:
Alternative:
cast( ausdruck as datentyp)

Mit ::geometry('LineString',25832) meldet man eine neu Spalte als Linien-Geometrie im KBS EPSG:25832 an.

Z.B wenn mit st_MakePoint(x,y) eine Geometrie  aus den Koordinaten der Spalten x und y erzeugt wird,
muss der Datenbank mitgeteilt werden, in welchen Geometrietyp die neue Spalte umgesetzt wird.
st_MakePoint(x,y)::geometry('point',25832)
##############################################################
    
'Einwohnertabelle anzeigen'
select
*
from data.ewz

'EINWOHNER AN DIE GEMEINDEN ANBINDEN'
-- Abfrage
select
h."RS" as rs,
h.bez_gem as name,
e.ewz as ewz,
h.geom as geom
from data.gemeinden as h join data.ewz as e on (h."RS" = e.rs) 
order by ewz desc
 
-- h.geom enthält die Polygon-Geometrie der Gemeinden // In Datenbanken gibt es Geometriespalten.
-- gemeinden bekommt den Alias h und ewz den Alias e.
-- h.rs bedeutet die Spalte rs der tabelle h, was der Alias von gemeinden ist.
-- Mit join wird die ewz-Tabelle über ein gemeinsames Schlüsselfeld angebunden
-- on (e.rs = h.rs)  verbindet beide Tabellen über die Schlüsselspalten h.rs und e.rs
-- In beiden Tabellen gibt es den Regionalschlüssel
-- order by ewz desc bedeutet, geordnet nach Einwohnerzahl mit absteigenden Werten

--###################
'NEUER LAYER MIT EWZ ERZEUGEN'
-- Mit create table wird aus der Abfrage eine neue Tabelle
create table s_01.gem_ewz as --erzeugt eine neue Tabelle gemeinden_ewz aus der vorherigen Abfrage
select
h."RS" as rs,
h.bez_gem as name,
e.ewz as ewz,
h.geom::geometry('MULTIPOLYGON',25832) -- Die Geometrie wird explizit auf Multipolygon gesetzt und im KBS 25832 angemeldet
from data.gemeinden as h join data.ewz as e on (e.rs = h."RS") 
order by name asc;

-- Ein Primärschlüssel ist für die Handhabung in Datenbanken wichtig
ALTER TABLE s_01.gem_ewz		-- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;	-- Die Spalte gid, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
					-- als Primärschlüssel angelegt

/* Indexe sind bei der Verarbeitung großer Datenmenbez_gem für die Performance wichtig
Im folbez_gemden werden zwei Indexe auf die Spalten name und ewz angelegt 
Außerdem ein räumlicher Index angelegt, der ein schnelleres Verarbeiten dder geometrien erlaubt.*/.

CREATE INDEX s_01_gem_ewz_gix ON s_01.gem_ewz USING GIST (geom);  --erzeugt einen räumlichen Index

CREATE INDEX name_idx ON s_01.gem_ewz (name)

-- Auch der Leser erhält Zugriff
GRANT SELECT on ALL tables in schema s_01 to saar_01;

-- Die Geometrie lässt sich auch nachträglich anmelden 
select UpdateGeometrySRID('s_01', 'gem_ewz', 'geom',25832);

--------
--##################################################

' Die Bevölkerungsdichte in EW je qkm in einer neuen Spalte berechnen'

-- Eine neue Spalte anlebez_gem mit alter table
alter table s_01.gem_ewz	-- Name der zu änderenden Tabelle wird angegeben
add column dichte integer	-- Die Spalte Dichte im Datentyp integer wird hinzugefügt.
;   

--Anschließend neu Berechnen der Spalte mit update
-- Über Update lassen sich Spalten neu berechnen
update s_01.gem_ewz      -- Name der zu ändernden Tabelle
set
dichte = (ewz / (st_area(geom) / 1000000))::integer	-- st_area gibt die Fläche in qm (bei ETRS89), geteilt durch 1000000 gibt qkm
;							-- ewz /qkm gibt Einwohner je qkm
CREATE INDEX dichte_idx ON s_01.gem_ewz (dichte);    -- Index anlebez_gem


--#################################################################

'Die geometrielose Landkreistabelle mit den Landkreisnamen soll an die Gemeinden gejoint werden.'
' Dazu benötigt es ein gemeinsames Schlüsselfeld
--INHALT VON GEMEINDEN UND LANDKREISEN ANZEIGEN'
select
*
from data.lkr
--dann
select
*
from data.gemeinden
------------------------------------
' Doppelte löschen'
WITH eindeutig as 
    (SELECT DISTINCT ON
	(krid, nam) 
	 * 
	 FROM data.lkr)
	 
DELETE FROM data_.lkr 
WHERE id NOT IN 
            (SELECT id FROM eindeutig);
				
				


----------------##########################################################
'KREISID UND REGIONALSCHLÜSSEL mit UNION VERGLEICHEN - UNION vereinigt zwei Abfrabez_gem zu einer Tabelle'
-- Beide Teile der UNION-Abfrage müssen die gleichen  Spalten haben, darum über Alias-Bildung umbennen!
-- Der Vergleich wird über where auf die kreisfreie Stadt Erfurt beschränkt
-- Die Where-Klausel dient der Auswahl bestimmter Zeilen und entspricht einer Auswahl im QGIS-Ausdruckseditor.
select
'Kreise' as tabelle,		 -- Bezeichnung der Ursprungstabelle über den text 'Kreise'
rs as id,                        -- krid wird als Spalte id ausgegeben
name as name                      -- bez_gem wird als Spalte Name ausgeben
from data.lkr                 -- gewählt von der Tabelle Landkreise  
where name like 'Saarl%'        -- Nur Zeilen mit der Zeichenkette Erfurt in der Spalte nam

------
UNION -- Beide Abfrabez_gem werden untereinandergeschrieben
-----------

select
'Gemeinden' as tabelle,		   -- Bezeichnung der Ursprungstabelle über text 'Gemeinden'
rs as id,                          -- rs wird als Spalte id ausgegeben
name as name                       -- bez_gem wird als Spalte Name ausgebe
from s_01.gem_ewz               -- gewählt von der Tabelle gemeinden 
where name like 'Saarl%'         -- Nur Zeilen mit der Zeichenkette Erfurt in der Spalte bez_gem

order by id desc  --Absteibez_gemde Sortierung nach id
'ERGEBNIS:'
-- Die Kreis id besteht aus den ersten 5. Zeichen des Regionalschlüssel rs
--------------------------------------------------------------


'Mit der Funktion substr(spaltenname,StelleDesErstenZeichen,AnzahlZeichen) KREISID AUS GEMEINDE ANZEIGEN'
select
name,
substr(rs,1,5) as krid -- aus der Spalte rs werden jeweils ab dem zweiten Zeichen vier Zeichen in eine neue Spalte krid geschrieben
from s_01.gem_ewz 
order by krid;
--substr gibt es auch als Funktion im Ausdruckseditor

'GRUPPIEREN NACH LANDKREISBEZUG, damit es nur eine Zeile je kreisid gibt: group by steht immer hinter from und where'
select
substr(rs,1,5) as krid
from s_01.gem_ewz 
group by substr(rs,1,5) -- Alle Zeilen mit dem gleichen krid werden zusammengefasst es gibt nur eine Zeile je landkreis
order by krid;

'WEITERE ZEILEN AUS DER GEMEINDETABELLE MITNEHMEN UND AGGREGIEREN UM DIE ANDEREN SPALTEN SINNVOLL ZUSAMMENZUFASSEN'
' Jetzt kommt die Wirkung der Aggregatfunktionen zur Geltung - Entsprechend der Gruppierung können wir für sämtliche  
Spalten festsetzen, was zu tun ist.  '
select
substr(rs,2,4) as krid,			-- nach diesem Ausdruck wird aggregiert - Nichts weiter nötig
count(rs) as anzahl,                    -- Die Anzahl der zusammengefassten rs-Zeilen wird gezählt (Anzahl der Gemeinden im LKR)
sum(ewz) as ewz_kreis,			-- Die Einwohnerzahl der Gemeinden wird summiert
string_agg(name,'; ' order by name) as gem_liste,  -- Die Gemeindenamen aus der Spalte name werden als Liste mit dem Trennzeichen Semikolon geschrieben  
ST_AsEWKT(st_union(geom)) AS WKT,	-- Noch einmal als WKT-text ausgeben, um den Geometrietyp zu prüfen(Eine Mischung kann im QGIS nicht angezeigt werden)
st_union(geom) AS geom			-- st_union verschmilzt die Geometrien der gruppierten Zeilen zu neuen Objekten wie DISSOLVE im GIS!
                
from s_01.gem_ewz			-- es wird von s_x_.gem_ewz, der Tabelle mit den gejointen Einwohner gewählt!  
group by substr(rs,2,4)			-- Alle Zeilen mit dem gleichen krid werden zusammengefasst es gibt nur eine Zeile je landkreis
order by krid						   -- und über order geordnet
;
'jetzt entstehen Landkreis-Geometrien, wie nach einenm Dissolve im GIS - 
- Allerdings ist in der wkt-Ausgabe zu erkennen, dass Polygon- und Multipolygonobjekte gemsicht sind,
deshalb setzen wird alles auf Multipolgon'

--DEN GEOMETRIETYP GENERELL AUF MULTIPOLYGON SETZEN

select
substr(rs,2,4) as krid,	              
ST_MULTI(st_union(geom)) AS geom,                --jetzt mit Cast auf Multipolygon via ST_Multi
ST_AsEWKT(ST_MULTI(st_union(geom))) AS WKT,     -- jetzt mit Cast auf Multipolygon
count(rs) as anzahl,                    
sum(ewz) as ewz_kreis,		       
string_agg(name,'; ' order by name) as gem_liste                   

from s_01.gem_ewz		       
group by substr(rs,2,4) 	       
;

' Es fehlen noch die Landkreisnamen die wir über das jetzt ermittelte gemeinsam Schlüsselfeld mittels join anbinden'
--LANDKREISNAMEN ANJOINEN
-- Tabelle 1 join Tabelle 2 on (feld.tab1 = feld.tab2)
-- Dies ist ein Inner Join, bei dem nur Zeilen von links mtigenommen werden, die rechts ein Gegenstück finden,
	--Wenn es mehrere passende Objekt im rechten Layer gibt, werden dei Zeilen dupliziert
-- Mit Aliasen lässt sich die Tabellenzuordnung vereinfachen
-- Schlüsselwort left join nimmt sämtliche Zeilen mit, auch wenn auf der anderen Seite kein Partner zu finden ist

select
substr(a.rs,1,5) as krid,
b.name as lkr_name,                         
count(a.rs) as anzahl,                    
sum(a.ewz) as ewz_kreis,		       
string_agg(a.name,'; ' order by a.name) as gem_liste ,
ST_MULTI(st_union(a.geom)) AS geom   
                 
from s_01.gem_ewz as a join data.lkr as b on (substr(a.rs,1,5) = b.rs) -- join rs vom Kreis an die gekürzte rs der Gemeinden       
group by substr(a.rs,1,5),b.name
;
/* Alternative: über chr(10) einen Zeilenumbruch in die gemeindeliste) */
select
substr(a.rs,1,5) as krid,
b.name as lkr_name,                         
count(a.rs) as anzahl,                    
sum(a.ewz) as ewz_kreis,		       
string_agg(a.name,('; '||chr(10)) order by a.name) as gem_liste,  -- chr(10) ist die Unicode-Repräsentation des Zeilenumbruchs    
ST_MULTI(st_union(a.geom)) AS geom   
                 
from s_01.gem_ewz as a join data.lkr as b on (substr(a.rs,1,5) = b.rs) -- join rs vom Kreis an die gekürzte rs der Gemeinden       
group by substr(a.rs,1,5),b.name
;

--######################################################################
' Jetzt können wir mit diesen vollständigen Dissolve-Funktion einen GIS-Layer der Landkreise erzeugen'

create table s_01.landkreise as
select
substr(a."RS",1,5) as krid,
b.name as lkr_name,                         
count(a."RS") as anzahl,                    
sum(c.ewz) as ewz_kreis,		       
string_agg(a.bez_gem,('; '||chr(10)) order by a.bez_gem) as gem_liste,  -- Liste der Gemeinden mit Zeilenumbruch   
ST_MULTI(st_union(a.geom))::geometry('MULTIPOLYGON',25832) as geom            

from data.gemeinden as a join data.lkr as b on (substr(a."RS",1,5) = b.rs) -- join rs vom Kreis an die gekürzte rs der Gemeinden  
                        join  data.ewz as c on (a."RS" = c.rs)              -- Join der Einwohnerzahl 
group by substr(a."RS",1,5),b.name
;

ALTER TABLE s_01.landkreise     -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gid, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX s_0_landkreise_gix ON s_01.landkreise USING GIST (geom);  --erzeugt einen räumlichen Index

GRANT SELECT on ALL tables in schema s_01 to saar_01;


--################################################################################

' Alles in einer Abfrage
Möglich wäre ein zweifachjoin ooder eine verschachtelte Unterabfrage
Am übersichtlichsten ist es mit with'


     
/* Gesamtabfrage verschachtelt */
create table s_01.landkreise as
     select
     substr(a.rs,1,5) as krid,
     b.name as lkr_name,                                 
     count(a.rs) as anzahl,
     sum(a.ewz) as ewz_kreis,
     string_agg(a.name,('; '||chr(10)) order by a.name) as gem_liste,
     ST_MULTI(st_union(a.geom))::geometry('MULTIPOLYGON',25832) as geom              
    
     from	
     (
        select
        h."RS" as rs,
		h.bez_gem as name,
		e.ewz as ewz,
		h.geom as geom
		from data.gemeinden as h join data.ewz as e on (e.rs = h."RS") 
		order by name asc
    ) 
     as a 
		    
     join data.lkr as b on (substr(a.rs,1,5) = b.rs)      
     group by substr(a.rs,1,5),b.name
     ;


/* Gesamtabfrage mit with */
create table s_01_.landkreise as
	with gem_ewz as -- Unterabfrage mit with , Anbindung der Einwohner
		   (
            select
            h."RS" as rs,
            h.bez_gem as name,
            e.ewz as ewz,
            h.geom as geom
            from data.gemeinden as h join data.ewz as e on (e.rs = h."RS") 
            order by name asc
		   )
     select			-- Im zweiten Schritt Anbindung der LKR und Verschmelzen
     substr(a.rs,1,5) as krid,
     b.name as lkr_name,                                 
     count(a.rs) as anzahl,
     sum(a.ewz) as ewz_kreis,
     string_agg(a.name,('; '||chr(10)) order by a.name) as gem_liste,
     ST_MULTI(st_union(a.geom))::geometry('MULTIPOLYGON',25832) as geom               
    
     from gem_ewz as a join data.lkr as b on (substr(a.rs,1,5) = b.rs)      
     group by substr(a.rs,1,5),b.name
     ;
'########################################################################'
'Lektion 2 Windanlabez_gem aus Tabelle einbinden - Und die Entfernung von Siedlungsflächen auswerten
Die Windanlagen liegen als Excel vor und sind importiert worden
Die kraftwerke haben keine Geometrie jedoch spalten mit Koordinaten'

--Alles anzeibez_gem
select
*
from data.tab_kraftwerke
;

-- Nur die Koordinaten anzeibez_gem

select
fid,
xc,
yc
from data.tab_kraftwerke
;

--In das eigene Schema kopieren

create table s_01.tab_kraftwerke as
select
*
from data.tab_kraftwerke
;
ALTER TABLE s_01.tab_kraftwerke     -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gid, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten

'Die Kraftwerke haben Koordinaten, jedoch keine Geometrie. Außerdem sind es keine UTM- sondern 
geographische Koordinaten.'

'Zunächst eine Geometriespalte anlebez_gem'
alter table s_01.tab_kraftwerke
add column geom geometry(POINT,25832) -- Spalte im Datentyp geometry
;


'Geometrie anlebez_gem'
update s_01.kraftwerke
set
geom = st_makepoint(xc,yc)  --Funktion st_makepoint erzeugt eine neue Geometrie aus den Koordinaten
;
'Resultat Fehlermeldung'


'überprüfen'
select
ST_AsEWKT(geom) as wkt  -- S_AsEWKT gibt WKT mit KBS-Informationen aus
from
s_01.tab_kraftwerke
;

'Problem, die Koordinaten sind nicht in UTM, wir müssen umprolizieren, das geht mit ST_transform '----------------
update s_01.tab_kraftwerke
set
geom = st_transform(st_makepoint(xc,yc),25832)
;

'Error: Input geometry has unknown (0) SRID'
/* Wir haben zwar Koordinaten, jedoch keine Beschreibung des KBS, das erkennt man auch an der Ausgabe von ST_AsEWKT(geom)
die normalerweise das KBS mit ausgibt) */-----------------

-- Mit set_srid() ist das Problem zu lösen
--Geographische Koordinaten EPSG:4326 (WGS 84)

update s_01.tab_kraftwerke
set
geom = ST_SetSrid(st_makepoint(xc,yc),4326)
;
'Auch hier ergibt sich eine Fehlermeldung, da wir erst transformieren müssen'
-------------------------
/* Dies lässt sich transformieren */

update s_01.tab_kraftwerke
set
geom = st_transform(ST_SetSrid(st_makepoint(xc,yc),4326),25832)
;

-- Prüfen
select
ST_AsEWKT(geom) as wkt
from
s_01.tab_kraftwerke
;
und 
 
SELECT * 
FROM public.geometry_columns 
where f_table_name = 'tab_kraftwerke'


--TypeCasting mit 
::geometry('POINT',25832)
--ist nicht nötig, weil das KBS schon mit dem Anlabez_gem der Spalte über Alter table definiert wurde,


update s_01.tab_kraftwerke			--Daten in der Tabelle Kraftwerke werden geändert
set		
geom =			-- Die Spalte Geometrie wird geändert

	ST_transform(			-- Transformation des KBS
			st_setsrid(	-- Ursprungskoordinatensystem setzen
				   st_makepoint(xc::real,yc::real),	-- Punktgeometrie aus den koordinatenspalten xc und yc erzeubez_gem
			      4326),	-- KBS der Ursprungsdaten
                 25832)			-- KBS nach der Trnasformation
;

------------------------------------------
--Lässt sich auch anders Schreiben
-- Mit with Unterabfrage strukturiert anordnenen

with 
    g as   -- KBS definieren
	(
	select
	id,
	ST_SetSrid(st_makepoint(xc::real,yc::real),4326) as geom 
	from  s_01.tab_kraftwerke
	),
    t as    -- KBS Transformieren
	(
	select
	id as ud,
	ST_Transform(geom,25832) as geomet
	from g
	)
  update s_01.tab_kraftwerke
  set
  geom = geomet
  from t
  where ud = id;
  
ALTER TABLE  s_01.tab_kraftwerke   -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX kraftw_gix ON  s_01.tab_kraftwerke USING GIST (geom);



/*Kraftwerke verfügbar machen */

GRANT SELECT on ALL tables in schema s_01 to saar_01;

'################################################'
'NUN SOLL DIE ENTFERNUNG DER WKA VON DEN SIEDLUNGSBLÖCKEN ERMITTELT WERDEN
dazu müssen WKS und Siedlungsflächen aus den Ur-Layern extrahiert werden.'

-- Die wkas werden extrahiert
create table s_01.wka as
select
fid as wka_id,
name,
nabenhoehe,
hoehe,
mw,

ST_Buffer(geom,5000,50)::geometry('POLYGON',25832) as geom_p,	-- Eine zweite Geometriespalte als Pufferfläche 5km Umkreis um jede WKA
								-- wird angelegt. Die 5000 gibt den Radius des Puffer, 50 setzt die Anzahl der Liniensegmente der Kreisgeometrie hoch
geom::geometry('POINT',25832) as geom  -- Die ursprüngliche Punktgeometrie

from
data.kraftwerke
where quelle = 'wind' -- Nur Kraftwerke der Kategorie Wind werden ausgewählt
;
-
ALTER TABLE s_01.wka   -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX wka1_gix ON s_01.wka USING GIST (geom);
CREATE INDEX wka1g_gix ON s_01.wka USING GIST (geom_p);
'############################################################'

'##############################################'
'Siedlungsflächen in Nutzung auswählen'
create table s_01.siedlung as
select
fid as nutz_id,
name,
nutzung,
geom as geom
from data.dlm250
                        -- Mit dem "in" Operator in  ist eine Auswahl nach Aufzählung möglich
where nutzung in ('AX_FlaecheBesondererFunktionalerPraegung' , 
				  'AX_IndustrieUndGewerbeflaeche' , 'AX_Ortslage' , 'AX_Siedlungsflaeche' , 
				  'AX_SportFreizeitUndErholungsflaeche' , 'AX_Friedhof' 
					);


ALTER TABLE s_01.siedlung              -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;    -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
                                            -- als Primärschlüssel angelegt
CREATE INDEX siedlung_wkassgeom_gix ON geom.siedlung USING GIST (geom);




'Jetzt wählen wir mit Hilfe der Pufferfläche zunächst der Siedlungsflächen bis 5km Umkreis aus, um die Entfernungen zu ermitteln
--und  können mit Hilfe von ST_ShortestLine die  Strecke von den Anlagen zu den Siedlungen ermitteln'
create table s_01.wka_entf as
select
a.wka_id,
b.nutz_id,
a.name as wka_name,
a.mw as leistung,
b.name as gebiet_name,
b.nutzung as nutzung,
ST_Length(ST_ShortestLine(a.geom,b.geom))::integer as strecke,		 -- ST_Length ermittelt die Länge der Linie	
ST_ShortestLine(a.geom,b.geom)::Geometry('LINESTRING',25832) as ligeom,  -- Shortest Line zeichnet die kürzeste verbindung zwischen zwei Geometirn
a.geom::Geometry('POINT',25832) as geom

from s_01.wka as a join s_01.siedlung as b on ST_Intersects(a.geom_p,b.geom) -- Wichtig ist der geom_p (den Pufferkreis) zu verwenden
-- ST_Intersects erlaubt einen räumlichen join, wenn sich die Flächen überschneiden,
-- Vom WKA-Layer wird die Geometrie der  5km Umkreis-Geometrien verwendet (geom_p)
;

'Die Liniengeometrie enthält sämtliche Verbindungslinien zwischen jeder WKA und allen Siedlungsflächen
im Umkreis von 5000 Metern. Für jede dieser Verbindunbez_gem entsteht auch eine neue Punktgeometrie, das heißt
jeder WKA-Punkt liegt so oft vor, wie es Nachbarflächen im 5000m-Umkrei gibt.
Wir suchen jedoch nur die Linie von jeder WKA zur Ihr nächsten Siedlung

Wie müssen aggregieren und beim aggregieren die kürzeste Linie ermitteln'


create table s_01.wka_distans as 
with kuerzest as	-- Ermittlung der einen kürzesten Strecke zur nächsten Siedlungsfläche
	(
        select
        wka_id,
        MIN(strecke)::integer kue_dist, -- Die Aggregatfunktion MIN()  gibt die kürzeste Strecke  aus	
        geom::Geometry('POINT',25832) as geom

        from s_01.wka_entf 

        group by wka_id,geom -- Gruppierung, um wieder nur einen einzelnen Punkt je WKA zu bekommen (nach Geometrie und wka_id)
        )
    select	--Über deb Join mit kürzest bleibt nur die Objekte mit der jeweils ermittelten kürzesten Strecke überig
    b.*
    from kuerzest as a join s_01.wka_entf as b on ((a.wka_id::text||a.kue_dist::text) = (b.wka_id::text||b.strecke::text))
                            -- Da wka_id und dist Zahlenfelder sind und der Verkettungsoperator ||
						-- nur Textfelder verknüpfen kann, werden Felder mit ::text nach text gecastet

;

ALTER TABLE s_01.wka_distans     -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX s_01_wka_distans_gix ON s_01.wka_distans USING GIST (geom);
CREATE INDEX s_01_wka_distansl_gix ON s_01.wka_distans USING GIST (ligeom); 

'DAS Ganze in einer  einzibez_gem Abfrage mit with'

/* Ermittelung der WKA in Entfernung von max 5000m um Siedlungsflächen samt der Entfernung
Alle Schritt zusammengefasst in einer mit with definierten Folge von Abfrabez_gem*/

Create table s_01.wka_distans2 as 
with  
    siedlung as  --Siedlungsglächen ermitteln
            (
            select
            fid as id,
            name,
            nutzung,
            geom
            
            from data.dlm250
            
            where nutzung in ('AX_FlaecheBesondererFunktionalerPraegung' ,
				  'AX_IndustrieUndGewerbeflaeche' , 'AX_Ortslage' , 'AX_Siedlungsflaeche' , 
				  'AX_SportFreizeitUndErholungsflaeche' , 'AX_Friedhof' )
            )
    ,
     wka as      -- WKA auswhälen ermitteln
            (
            select
            fid as id,
            name,
            mw as leistung,
	    ST_Buffer(geom,5000,50)::geometry('POLYGON',25832) as geom_p,
            geom as geom

            from data.kraftwerke 
            
            where quelle = 'wind'
            )
    ,
     strecke as         -- kuerzteste Verbindunbez_gem zu den einzelnen Siedlunbez_gem
            (
            select
            a.id as wka_id,
            b.id as nutz_id,
            a.name as wka_name,
            a.leistung,
            b.name as gebiet_name,
            b.nutzung as nutzung,
            ST_Length(ST_ShortestLine(a.geom,b.geom))::integer strecke,
            ST_ShortestLine(a.geom,b.geom)::Geometry('LINESTRING',25832) as ligeom,
            a.geom::Geometry('POINT',25832) as geom
            
            from wka as a join siedlung as b on ST_Intersects(a.geom_p,b.geom)
           )
           ,
    kuerzest as  -- Kürzeste Verbindung zur nächsten Siedlung
            (
            select
            wka_id,
            MIN(strecke) as dist,	
            geom::Geometry('POINT',25832) as geom
            
            from strecke
            group by wka_id,geom            --Gruppiert über die Geometrie , die kürzeste Strecke wird aggregiert
            )       
    select
        a.wka_id        as wka_id,
        a.dist          as strecke,
        b.wka_name      as wka_nam,
        b.gebiet_name   as gebiet,
        b.nutzung       as nutzung,
        b.ligeom::Geometry('LINESTRING',25832) as ligeom,
        a.geom::Geometry('POINT',25832) as geom
        
        from kuerzest as a left join strecke as b on (a.wka_id::text||a.dist::text = b.wka_id::text||b.strecke::text)
;


ALTER TABLE s_01.wka_distans  ADD COLUMN gid SERIAL PRIMARY KEY;      

CREATE INDEX wkas_01_gix ON s_01.wka_distans  USING GIST (geom);  --erzeugt einen räumlichen Index

GRANT SELECT on ALL tables in schema s_01 to saar_01;
GRANT ALL on ALL tables in schema s_01 to saar_01;




'################################'
'Flächenverschneidunbez_gem'

'Geometriefehler bereinigen'
update data.dlm250
set
geom = ST_Multi(ST_CollectionExtract(ST_makevalid(geom),3))::geometry('MULTIPOLYGON',25832)

where st_isvalid(geom) = false
;

select


update data.gemeinden
set
geom = ST_Multi(ST_CollectionExtract(ST_makevalid(geom),3))::geometry('MULTIPOLYGON',25832)
where st_isvalid(geom) = false;

'Verschneidung Wald und Gemeinden'
create table s_01.waldverschn as
select
a.bez_gem,
ST_MULTI(ST_CollectionExtract(st_intersection(a.geom,b.geom),3))::geometry('MULTIPOLYGON',25832) as geom,
b.name

from data.gemeinden as a join data.dlm250 as b on st_intersects(a.geom,b.geom)
where b.nutzung = 'AX_Wald' and (st_isvalid(b.geom) = true and st_isvalid(a.geom) = true )
;

ALTER TABLE  s_01.waldverschn   -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX waldverschn_gix ON  s_01.waldverschn USING GIST (geom);

-------------------

'Waldannteil ermitteln verscxhachtrelt'
create table s_01.wald_anteil as
Select
g.geom AS geom,
g."RS" AS rs,
g.bez_gem AS name,
e.ewz AS ewz,
(ST_area(ST_union(ST_Intersection(g.geom,d.geom))) / 1000000)::integer AS wfl,
round((ST_area(ST_union(ST_Intersection(g.geom,d.geom)))) / (ST_area(g.geom))* 100)::integer AS anteil,
round((ST_area(ST_union(ST_Intersection(g.geom,d.geom)))) / e.EWZ)::integer  AS wf_ew
from
(data.gemeinden as g left join data.dlm250 as d ON ST_intersects(g.geom,d.geom)) left join s_01.ewz as e ON (e.rs = g."RS")
where
    d.nutzung = 'AX_Wald'
Group by g."RS",g.geom,g.bez_gem,e.ewz;

ALTER TABLE  s_01.wald_anteil   -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX waldverschn_gix ON  s_01.wald_anteil USING GIST (geom);


--##############################################################################################################
' Waldanteilmit Unterabfrage '
drop table  if exists s_01.wald_anteil;			-- Vorhande Tabelle löschen
create table s_01.wald_anteil as				-- Neue Tabelle erzeugen
with 
   inters as  -- Verschneidung und Flächenberechnung
        (
        select
        a."RS" as rs,
        round((st_area(a.geom)/ 1000000)::numeric(10,2),2) as qkm,
        round((st_area(st_intersection(a.geom,b.geom)) / 1000000)::numeric(10,2),2) as qkm_wald

        from data.gemeinden as a left join data.dlm250 as b on st_intersects(a.geom,b.geom)
        where b.nutzung = 'AX_Wald'
        )
        ,
    wafl as   --- Summieren je Gemeinde
        (
        select
        rs,
        max(qkm) as qkm,
        sum(qkm_wald) as qkm_wald
        from inters
        group by rs
        )
select
row_number() over() as fid,
a."RS" as rs,
a.bez_gem as gemeinde,
b.qkm::numeric(10,2) as qkm,
b.qkm_wald::numeric(10,2) as qkm_wald,
cast(round((b.qkm_wald / b.qkm) * 100) as integer) as anteil,
a.geom::geometry('MULTIPOLYGON',25832) as geom

from data.gemeinden as a left join wafl as b on (a."RS" = b.rs)
;   
ALTER TABLE  s_01.wald_anteil   -- Alter table ändert Tabelle, Add column fügt eine Spalte hinzu
ADD COLUMN gid SERIAL PRIMARY KEY;  -- Die Spalte gib, wird mit einer automatischen seriellen Befüllung mit eindeutibez_gem Werten
				   -- als Primärschlüssel angelegt
CREATE INDEX waldverschn_gix ON  s_01.wald_anteil USING GIST (geom);


select 'AUFGABE WIE IM MODELLER-KURS'
/* Waldanteil und Waldfläache je EW ermitteln 
in mehreren Abfrabez_gem hintereinander  */


'##############################################################'
'TRIGGER'

'Trigger Koordinatenberechnung'

alter table s_x_.wka  -- Zwei Spalten für die Koordinaten anlebez_gem 
add column xc integer, -- jeweils im Ganzzahl-Format
add column yc integer
;

update s_x_.wka
set
xc = ST_x(geom)::integer,   --ST_x schreibt die X-Koordinaten einer Punktgeometrie
yc = ST_y(geom)::integer   --ST_y schreibt die y-Koordinaten einer Punktgeometrie
;
Select
id,
xc,
yc
from s_x_.wka;
 
-- Trigger-Funktion calc_koord() erstellen, auf welche der Trigger zugreift
	CREATE OR REPLACE FUNCTION calc_koord() 		--Namen der Funktion definieren
	RETURNS "trigger" AS 				-- Rückgabe für Trigger
		$$ BEGIN						-- Funktion beginnt
		
		-- Aktualisiert bei derjenibez_gem Zeile, bei der etwas geändert (UPDATE) bzw. eingefügt (INSERT) wird  (NEW)
       -- Die Spalten xc und yc wird aktualisiert, wenn die Geometry geändert wird

		NEW.xc = st_x(NEW.geom)::integer;
		NEW.yc = st_y(NEW.geom)::integer;
		RETURN NEW;

		END; $$		--Funktion Ende			
 	LANGUAGE 'plpgsql' VOLATILE;			-- Sprache der Funktion
	
	-- Der eibez_gemtliche Trigger
	CREATE TRIGGER koordinaten_berech  --name des Triggers

-- Wichtig ist, dass der TRIGGER BEFORE und 
-- nicht AFTER UPDATE OR INSERT geschaltet wird!



	BEFORE INSERT OR UPDATE  	-- Vor Änderung oder Einfübez_gem
	ON s_x_.wka	   	-- Für die bez_gemannte Tabelle 
	FOR EACH ROW
	EXECUTE PROCEDURE calc_koord(); 	-- Wende die Funktion calc_ha() an
	
'Trigger Flächenberech'	
	
drop trigger if exists flaech_berech on s_x_.dlm250;

	CREATE OR REPLACE FUNCTION calc_area() 		--Namen der Funktion definieren
	RETURNS "trigger" AS 				-- Rückgabe für Trigger
		$$ 
		BEGIN
			update s_x_.dlm250
			set
			qm = round((st_area(NEW.geom) / 1000000)::numeric(6,3),3),
			datum = (select(current_date)),
			bearb = (select user)
			where geom =  NEW.geom
			;
		RETURN NEW;
		END; 
		$$		--Funktion Ende			
 	LANGUAGE 'plpgsql';	

	create trigger flaech_berech
	After INSERT OR UPDATE of geom 	-- 
	ON s_x_.dlm250	   	-- Für die bez_gemannte Tabelle 
	FOR EACH ROW
	EXECUTE PROCEDURE calc_area() 	-- Wende die Funktion calc_ha() an  

	/* zwiete version */
	
	drop trigger if exists flaech_berech on s_x_.dlm250;
	CREATE OR REPLACE FUNCTION calc_area() 		--Namen der Funktion definieren
	RETURNS "trigger" AS 				-- Rückgabe für Trigger
		$$ 
		BEGIN

                NEW.qm = (round((st_area(NEW.geom) / 1000000)::numeric(6,3),3));
                NEW.datum = (select(current_date));
                NEW.bearb = (select user);

		RETURN NEW;
		END; 
		$$		--Funktion Ende			
 	LANGUAGE 'plpgsql';	

	create trigger flaech_berech
	BEFORE INSERT OR UPDATE of geom 	-- 
	ON s_x_.dlm250	   	-- Für die bez_gemannte Tabelle 
	FOR EACH ROW
	EXECUTE PROCEDURE calc_area() 	-- Wende die Funktion calc_ha() an  



'################################################### '
'Trigger WKA auf Gemeinden auswerten und zählen'


alter table s_x_.gemeinden   
add column wka_zahl integer, -- Neue Spalten albez_gem
add column wka_leis numeric (6,2)
;

with wka_auswertung as   -- Auswertung der WKA bezobez_gem  auf Ihre Lage in den Gemeinden
		(
	select
		a.id as gem_id,
		count(b.gid) as anzahl,					          -- Anzahl der WKA je Gemeinde wird summiert
		sum(b.mw)::numeric(6,2) as leistung,				-- Spalte mw mit der Leistung wird summiert
		a.bez_gem as bez_gemame							-- Umbenennung für de das Update
		from s_x_.gemeinden as a  join s_x_.wka as b                   -- Räumliche Abfrage: WKA die in der jeweilibez_gem Gemeinde liebez_gem
			on ST_Contains(a.geom,b.geom)				-- ST-Contains
		group by a.id						       -- Gruppieren je Gemeinde
		)
	update s_x_.gemeinden
	set
	wka_anzahl = anzahl,				-- Die Werte für die Spalte wka_anzahl stammen aus der Spalte anzahl der Abfrage
	wka_leistung = leistung

	from wka_auswertung  -- Die Werte für das Update kommen aus der Abfrage wka_Auswertung

	where bez_gem = bez_gemame   -- Zuordnung über den Gemeindenamen in der Tabelle und in dr Abfrage
	;




-- TRIGGER WKA zählen 

	CREATE OR REPLACE FUNCTION zaehle_WKA() 		--Namen der Funktion definieren
     RETURNS "trigger" AS 				-- Rückgabe für Trigger
     $$ 
     BEGIN
            if (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') then
			with wka_zahl as
					(
					select
					--gid,
					a.id as ge_id,
					count(b.gid) as anzahl,
					sum(b.mw)::numeric(6,2) as leistung,
					a.bez_gem as nam

					from s_x_.gemeinden as a left join s_x_.wka as b on st_contains(a.geom,b.geom)
					group by a.id
					)				
				
				update s_x_.gemeinden
				set
				wka_zahl 	= anzahl,
				wka_leis 	= leistung

				from
				wka_zahl 
				where nam = bez_gem
				--and NEW.gid = gid
				;				
			RETURN NEW;
		end if;			
	 if (TG_OP = 'DELETE') then
	 			with wka_zahl as
					(
					select
					--gid,
					a.id as ge_id,
					count(b.gid) as anzahl,
					sum(b.mw)::numeric(6,2) as leistung,
					a.bez_gem as nam

					from s_x_.gemeinden as a left join s_x_.wka as b on st_contains(a.geom,b.geom)
					group by a.id
					)				
				
				update s_x_.gemeinden
				set
				wka_zahl 	= anzahl,
				wka_leis 	= leistung

				from
				wka_zahl 
				where nam = bez_gem
				--and OLD.gid = gid
				;				
			RETURN OLD;
		end if;			 
    END; 
    $$						--Funktion Ende
LANGUAGE 'plpgsql' VOLATILE;			-- Sprache der Funktion
	
-- Der eibez_gemtliche Trigger
	drop  TRIGGER IF EXISTS z_wka on s_x_.wka;
	
	CREATE  TRIGGER z_wka  --name des Triggers
	AFTER INSERT OR UPDATE  or DELETE	-- Vor Änderung oder Einfübez_gem
	ON  s_x_.wka	   	-- Für die bez_gemannte Tabelle 
	FOR EACH ROW
	EXECUTE PROCEDURE zaehle_WKA(); 	-- Wende die Funktion calc_ha() an











---------------
----------------
------------
--
'VERSCHNEIDUNGEN'
-- INTERSECT ZWEIER GEOMETRIEN
create table verschneidung as   --Erzeubez_gemd der neuen Tabelle über create table
select                          -- Die Tabelle wird aus der Abfrage erstellt
  ST_Multi(st_intersection(a.geom,b.geom)) AS geom, --Die Funktion ST_intersect entspricht der Intersect / Verschneiden-Funktion in GIS-Pprogrammen
  a.rs,								   -- ST_Multi ermöglicht eine Multipolygon-Geometrie im Ergebnis
  a.bez_gem,
  a.bez,                                                             -- weitere Spalten können mit abgefragt werden (Aufzählung mit Komma)       
  b.nutzung as nutzung 
  
from s_x_.gemeinden as a inner join s_x_.dlm250 as b on st_intersects(a.geom,b.geom) = 1 -- Die Verschneidung wird nur für Geometrien durchgeführt, die sich wirklich schneiden
   where nutzung = 'AX_Wald';
   
--UM AUSSTANZEN NUR EINZELGEOMETRIEN VERWENDET WERDEN KÖNNEN

-- Schritt 1: aus beiden Layern eine vollständig verschmolzene Geometrie erzeubez_gem
-----------------
create table diff_vorne			-- Die Tabelle aus der gestanzt wird
as
select
Buffer(ST_Union(ST_buffer(a.geom,0.01)),0.01) AS geom		-- verschmelzen der ersten Geometrie mit Pufferung zum Lücken schließen
      from s_x_.gemeinden as a;      -- DIES IST DER URSPRUNGSLAYER A, DER IM LETZTEN SCHRITT DES SKRIPTES NOCH EINMAL EINGETRAGEN WERDEN MUSS



----------------
create table diff_stanz 		-- Die Tabelle die stanzt
as
select
Buffer(ST_Union(ST_buffer(a.geom,0.01)),0.01) AS geom		-- verschmelzen der ersten Geometrie mit Pufferung zum Lücken schließen
      from dlm250 as a
where a.nutzung = 'AX_Wald';  -- DIES IST DER URSPRUNGSLAYER B, DER DIE LÖCHER IN DEN LAYER A STANZT


--Schritt 2: Mit einem Layer ein Loch in den anderen Layer stanzen
-----------------
create table diffdiss as		--diffdiss ist der Ergebnis des Verschneidungsprozesses mmit st_difference		
select
      ST_Multi(st_difference(diff_vorne.geom,diff_stanz.geom)) AS geom 			--Der Layer diffstanz schneidet Löcher in die Geometrie von diffvorne
      from diff_vorne inner join diff_stanz on st_intersects(diff_vorne.geom,diff_stanz.geom) = 1
       ;


---Alternative SCHRITT 1 UND SCHRITT 2 DURCH VERSCHACHTELUNG ZUSAMMENFASSEN



create table diffdiss2 as		--diffdiss ist der Ergebnis des Verschneidungsprozesses mmit st_difference		
select
         ST_Multi(st_difference(Buffer(ST_Union(ST_buffer(a.geom,0.01)),0.01),Buffer(ST_Union(ST_buffer(a.geom,0.01)),0.01))) AS geom --Der Layer diffstanz schneidet Löcher in die Geometrie von diffvorne
      --ST_Multi(st_difference(ST_Union(ST_buffer(a.geom,0.01)),ST_Union(ST_buffer(b.geom,0.01)))) AS geom 			--Der Layer diffstanz schneidet Löcher in die Geometrie von diffvorne
      from s_x_.gemeinden as a inner join s_x_.dlm250 as b on st_intersects(a.geom,b.geom) = 1
       where b.nutzung = 'AX_Wald';



-------------
--ATTRIBUTE ÜBER EIN INTERSET AUF DEN DIFFERENZLAYER ÜBERTRAGEN
---------------
create table differenzfertig as
select
  ST_Multi(st_intersection(a.geom,diffdiss.geom)) AS geom,   -- Über ein Intersect werden Einzelgeometrien und  Attribute wieder hergestellt
  a.* 
  from gemeinden as a inner join diffdiss on st_intersects(a.geom,diffdiss.geom) = 1  -- HIER MUSS FÜR DIFF_EINGABE DER URSPRUNGSLAYER A EINGETRAGEN WERDEN
;

-------
--------
---EINE VEREINIGUNG ERREICHT MAN NICHT ÜBER GUNION - DIES FÜHRT ZU DOPPELTEN GEOMETRIEN
-- SONDERN IN DEM ÜBER EINE NORMALE ZEILEN-UNION DIE ERGEBNISSE DES INTERSECT UND DES DIFFERENZ-LAYERS VEREINIGT WERDEN
create table vereinigung as
select
geom,
rs,
bez_gem,
bez,
nutzung
from verschneidung
union
select
geom,
rs,
bez_gem,
bez,
'andere' as nutzung
from differenzfertig;






       
       
       /* Differenz mit ST_Differenz dauerte bei beim Testprojekt 3h   */
             
             drop table if exists ausgesstanzt;
                create table ausgesstanzt as
                with
                    stanze as
                     (
                     select
                     ST_Multi(st_union(geom))::geometry('MULTIPOLYGON',25832) as geom
                     from tbl_rechts
                     )

                select
                a.gid as gid,
                --ST_multi(ST_CollectionExtract((ST_difference(st_makevalid(a.geom),st_makevalid(b.geom))),3))::geometry('MULTIPOLYGON',25832) as geom,
                ST_MULTI((ST_Dump(st_difference(a.geom,b.geom))).geom)::geometry('MULTIPOLYGON',25832) as geom

                from  tbl_links as a, stanze as b
                ;
                        ALTER TABLE ausgesstanzt	                -- 
                            ADD COLUMN gid SERIAL PRIMARY KEY; 		-- Primärschlüssel hinzufübez_gem
                    
                    CREATE INDEX idx_ausgesstanzt_gix ON ausgesstanzt USING GIST (geom);  --Räumlichen Index anlebez_gem
                    
                    
--#################################################################################################################################################
/* Differenz- und Union-Alternative über das Zerlebez_gem der Geometrien, dauerte beim testprojekt 10 Minuten */
                   
                    drop table if exists pol_aus_segment;
                    create table pol_aus_segment as      -- Stufe 1: Eine Geometrievereingung beider Layer ohne Attribute 
                        with 
                            segmente as                  -- Erzeubez_gem der Segmente aus den Polygonen mit ST_Boundary (Kanten extrahieren) und ST_Dump (Zerlegbez_gem in Einzelsegmente)
                            (
                                select
                                (ST_Dump(ST_Boundary(geom))).geom::geometry('Linestring',25832) as geom  -- Kanten der Polygone des Layers tbl_links extrahieren und in Segmente zerlebez_gem
                                from tbl_links
                                    ----
                                    UNION      --vereinibez_gem  des Segmente des linken und rechen Layers             
                                    -----
                                select
                                (ST_Dump(ST_Boundary(geom))).geom::geometry('Linestring',25832) as geom  -- Kanten der Polygone des Layers tbl_rechst extrahieren und in Segmente zerlebez_gem
                                from tbl_rechts
                            )
                            ,
                            verei as
                            (
                                select 
                                (ST_Dump(ST_UNION(geom))).geom::geometry('Linestring',25832) as geom   -- Zusammenführen und Knoten erzeubez_gem mit ST_Union / In Segmente zerlebez_gem mit ST_Dump
                                from segmente
                            )
                            select                                                                                            --Segmente zu Polygonen mit ST_polygonize                                                               
                            (ST_Dump(ST_CollectionExtract(ST_POLYGONIZE(geom),3))).geom::geometry('POLYGON',25832) as geom   -- Mit ST_collection Nicht-Pols ausschließen
                            from verei
                        ;
                        
                    ALTER TABLE pol_aus_segment	                -- 
                        ADD COLUMN gid SERIAL PRIMARY KEY; 		-- Primärschlüssel hinzufübez_gem
                    
                    CREATE INDEX idx_pol_aus_segment_gix ON pol_aus_segment USING GIST (geom);  --Räumlichen Index anlebez_gem
                    

                    /* Eine neue Geometriespalte anlebez_gem und eine Punktgeometrie auf die Polygonoberfläche für die spätere Attributübertragung anlebez_gem */
                    alter table pol_aus_segment
                        add column poi geometry('POINT',25832)
                    ;
                    update pol_aus_segment
                        set 
                        poi = ST_PointonSurface(geom)
                    ;
                    
                    CREATE INDEX idx_seg_poi_gix ON pol_aus_segment  USING GIST (poi);  -- Räumlicher Index für die Punkte
                    

/* Unions-Layer mit übertrabez_gem der Attribute. Aus diesem kann anschließend der Differenz-Layer gebildet werden. */
                    drop table if exists pol_union;
                    create table pol_union as
                        with 
                            cte1 as   -- Anbinden von Attributen des linken Layers über räumliche Anfrage: Hier der Primärschlüssel gid
                                (
                                select
                                a.gid as gid,                           --Primärschlüssel aus der Geometrivereinigung
                                coalesce(b.gid,0) as id_links,                      -- Primärschlüssel aus dem linken Layer, wenn keine Überschneidung dann 0
                                0 as id_rechts,                         -- 0 für den Primärschlüssel von rechts, der hier nicht abgefragt wird
                                a.geom::geometry('POLYGON',25832) as geom

                                from pol_aus_segment as a left join tbl_links as b on st_intersects(a.poi,b.geom) = true  -- Räumliche Beziehung über die Punktgeometrie der Geometrivereinigung
                                )
                                ,
                            cte2 as    --  Anbinden von Attributen des rechten Layers über räumliche Anfrage: Hier der Primärschlüssel gid
                                (
                                select
                                a.gid as gid,             --Primärschlüssel aus der Geometrivereinigung
                                0 as id_links,             -- 0 für den Primärschlüssel von links, der hier nicht abgefragt wird
                                coalesce(b.gid) as id_rechts,           -- Primärschlüssel aus dem rechten Layer, wenn keine Überschneidung dann 0
                                a.geom::geometry('POLYGON',25832) as geom                             
                                                            
                                from pol_aus_segment as a left join tbl_rechts as b on st_intersects(a.poi,b.geom) = true  -- Räumliche Beziehung über die Punktgeometrie der Geometrivereinigung
                                )
                                ,
                            ver as    -- Vereinigung der Attributtierungn von links und rechts
                                (
                                select * from cte1
                                    ----
                                    UNION      --vereinibez_gem  des Segmente des linken und rechen Layers             
                                    -----
                                select * from cte2
                                )
                        --Gruppierung nach gid und geom der Gemetrievereinigung, um doppelte Objekte nach dem Anbinden der Attribute von beiden Seiten zu vermeinden
                        select
                            gid,
                            MAX(id_links) as id_links,    --Primärschlüssel erzeubez_gem-- Es muss eine Aggregierungsfunktion verwendet werden anders als bei SpatiaLite, darum Max
                            MAX(id_rechts) as id_rechts, -- Es muss eine Aggregierungsfunktion verwendet werden anders als bei SpatiaLite, darum Max
                            ST_MULTI(ST_union(geom))::geometry('MULTIPOLYGON',25832) as geom 
                        from ver
                        where id_links > 0 or id_rechts > 0  -- Wenn beide 0 sind, dann handelt es sich um Polygone, die durch zufällige geschlossene Linien entstanden sind
                        group by gid,geom
                        ;
       /*   Layer mit der Geometrievereinigung löschen */
                  drop table pol_aus_segment;
					
    /*   Primärschlüssel etv */
                        ALTER TABLE    pol_union
                            ADD CONSTRAINT pol_union_prim PRIMARY KEY(gid); --Primärschlüssel erzeubez_gem
                        CREATE INDEX idx_pol_union_gix ON pol_union USING GIST (geom);	
		                    
/* Differenz-Layer erzeubez_gem */
                        drop table if exists pol_diff;
                        Create table pol_diff
                        as
                            select
                            gid,
                            id_links,
                            geom::geometry('MULTIPOLYGON',25832) as geom 
                            from pol_union
                            where id_links > 0 and id_rechts = 0
                        ;
                        
                        ALTER TABLE    pol_diff
                            ADD CONSTRAINT pol_diff_prim PRIMARY KEY(gid); --Primärschlüssel erzeubez_gem
                        
                        CREATE INDEX idx_pol_diff_gix ON pol_diff USING GIST (geom);	
                        
 /* Intersect-Layer erzeubez_gem */                       
                        drop table if exists pol_inter;
                        Create table pol_inter
                        as
                            select
                            gid,
                            id_links,
                            id_rechts,
                            geom::geometry('MULTIPOLYGON',25832) as geom 
                            from pol_union
                            where id_links > 0 and id_rechts > 0
                        ;
                        
                        ALTER TABLE    pol_inter
                            ADD CONSTRAINT pol_inter_prim PRIMARY KEY(gid); --Primärschlüssel erzeubez_gem
                            
                        CREATE INDEX idx_pol_inter_gix ON pol_inter USING GIST (geom);	

/* SymDifferenz-Layer erzeubez_gem */
                        drop table if exists pol_symdiff;
                        Create table pol_symdiff
                        as
                            select
                            gid,
                            id_links,
                            id_rechts,
                            geom::geometry('MULTIPOLYGON',25832) as geom 
                            from pol_union
                            where (id_links = 0 and id_rechts > 0) or (id_links > 0 and id_rechts = 0 )
                        ;
                        
                        ALTER TABLE    pol_symdiff
                            ADD CONSTRAINT pol_symdiff_prim PRIMARY KEY(gid); --Primärschlüssel erzeubez_gem
                            
                        CREATE INDEX idx_pol_symdiff_gix ON pol_symdiff USING GIST (geom);
                        
                 


/* 
-----------------------------------------
GKG-Kassel - Dr.-Ing. Claas Leiner
QGIS-Support und mehr

Geodatenservice, Kartenwerkstatt &
GIS-Schule Kassel

Wilhelmshöher Allee 304 E
34131 Kassel
Tel. 0561/56013445
claas.leiner@gkg-kassel.de
http://www.gkg-kassel.de
----------------------------------------
Unterstützen Sie QGIS
QGIS-DE e.V. | http://qgis.de
*/
