Kursseite: https://giswiki.hsr.ch/Kurs_PostGIS_Einf%C3%BChrung_I#Programm 
Folien und Uebungen (NEU, Tag 1 und 2): https://drive.switch.ch/index.php/s/qgnXTZUQ9O2m8Ox 
SQL_Island Lösungen (deutsch): https://drive.switch.ch/index.php/s/BIl73zxo9IHpnYV 
Kurs-Geodaten (uster, fluesse.shp, etc.): https://drive.switch.ch/index.php/s/cBXA6BQq9Busvnt 
    
Programm Tag 1:
* Einführung, Organisatorisches sowie Installation von PostGIS/PostgreSQL/QGIS
  -> Folien 01_Einfuehrung_PostgreSQL 
* SQL Refresher 
  -> Übung 0 (SQL Island + modernes SQL)
* Einführung in PostgreSQL, Verwaltung von Geodaten mit der Erweiterung PostGIS.
* Datenverwaltung und -Abfrage über SQL; Datenbank-Clients psql und pgAdmin4 etc.
* Anlegen von Datenbanken / Administration
  -> Folien 02_DB_Administration 
  -> Übung 1 (Einführung in PostgreSQL und pgAdmin4; Übung 2 (Daten einlesen und Index erstellen)
* Geodaten einlesen
* Darstellen von PostGIS-Daten mit QGIS 
  -> Folien 03_Datentypen_Funktionen_Operatoren_Joins 
  -> Übung 3 (Datentypen, Funktionen, Aggregates und Joins)
  -> Folien 04a_PostGIS_laden_konvertieren_anzeigen 
  -> Übung 4 (Geometriedaten laden und darstellen) 
* Geodatentypen, Schnittstellen zu Vektordatenformaten, Datenkonvertierung, GIS-Formate.
  -> Folien 04b_PostGIS_Geometrie-Typen  
* PostGIS-Funktionen
  -> Folien 05_Raeumliche_Funktionen_Operatoren  
  -> Übung 5 (Einfache räumliche Abfragen)  

Programm  Tag 2: 
* Wrapup Vortag 
  -> Folien 04c_PostGIS_Geographie-Typen 
  -> Folien 05_Raeumliche_Funktionen_Operatoren  
  -> Übung 6: Komplexere räumliche Abfragen mit Uster-Daten und Schweiz-Daten
* PostGIS-Funktionen ff.
* Koordinaten-Referenzsysteme/Projektionen/Transformationen
  -> Folien 06_Transformationen_und_Export 
  -> Übung 7 (Projektionen, Lineare Referenzierung und Datenexport)
* Daten-Export, Datensicherung und Datenaustausch
* Datenmodellierung (Constraints) sowie Performance-Optimierung (Materialized Views, Tuning/Indizes)
  -> Folien 07_Datenmodellierung (weglassen)
  -> Übung 8 (Datenmodellierung)  
* Fortgeschrittenes SQL: Stored Procedures, Views, Triggers
  -> Folien 08_Views_und_Triggers 
  -> Folien 09_Stored_Procedures 
  -> Übung 9 (Trigger und Funktionen)
* 15:00 "Ask-us-Anything", Diskussion mit Karsten Lenz
* Ausblick PostGIS Extensions: Topology, 3D, Raster, PointCloud, pgRouting
* Abschluss
  -> Folien 10_Ausblick 
 
Daten
-------

schweiz.gpkg - alle im KRS CH1903 / LV03
1: seen (Multi Polygon)
2: fluesse (Multi Line String)
3: viertausender_ch (Point)
4: pubs (Point)
5: restaurants (Point)
6: staedte_schweiz (Point)
7: gemeinden (Multi Polygon)
8: layer_styles (None)

fluesse.shp - Layer name: fluesse
Geometry: Line String
Feature Count: 49
Extent: (488131.394865, 85709.257812) - (831066.562500, 283450.062500)
Layer SRS WKT: PROJCRS["CH1903 / LV03",
GESAMT1M_I: Integer (9.0)
NAME: String (20.0)

gemeinden.shp - Layer name: gemeinden
Geometry: Polygon
Feature Count: 2757
Extent: (485412.000000, 64040.000000) - (833780.000000, 297582.000000)
Layer SRS WKT: PROJCRS["CH1903 / LV03",
GMDE: Integer (4.0)
BEZIRK: Integer (4.0)
KT: Integer (4.0)
NAME: String (40.0)

seen.shp - Layer name: seen
Geometry: Polygon
Feature Count: 22
Extent: (500521.000000, 64040.000000) - (774359.000000, 297582.000000)
Layer SRS WKT: PROJCRS["CH1903 / LV03",
GMDE: Integer (4.0)
BEZIRK: Integer (4.0)
KT: Integer (4.0)
NAME: String (40.0)

Uster-Daten:
Schema: raumplanung; View: nutzungszonen
"table_name"    "column_name"    "data_type"
"nutzungszonen"    "bev_gesamt"    "integer"
"nutzungszonen"    "bev_anteil_weiblich"    "numeric"
"nutzungszonen"    "bev_anteil_ch"    "numeric"

Schema: raumplanung; View: nutzungszonen
"column_name"   "data_type"
"bev_gesamt"    "integer"
"laermempfindlichkeit"    "text"

Shema: baustelle; View: vw_baustellen_projekte_linienelemente
"column_name"    "data_type"
"betroffen_strasse"    "boolean"
"betroffen_gas"    "boolean"

Schema: av_user, raumplanung
"table_name"    "column_name"    "data_type"
"strassenstuecke"    "text"    "character varying"
"liegenschaften"    "ogc_fid"    "integer"
"liegenschaften"    "the_geom"    "USER-DEFINED"
"liegenschaften"    "gru_nummer"    "character varying"
"nutzungszonen"    "zonenbez_gemeinde"    "text"


Tipps und Tricks für PostgreSQL:
* "Überblick über PostgreSQL Index-Typen" https://md.coredump.ch/s/73PZG-btU#
* PGTune - Konfiguration für bessere Performance: https://pgtune.leopard.in.ua/

SQL Hilfe und SQL Lernen:
* OpenSchoolMaps - offene Tutorials zu GIS, QGIS, OpenStreetMap (OSM) und weitere Informatik-Themen: v.a. "Betriebssystem-Shells und Command Line Interfaces" 
https://openschoolmaps.ch/pages/materialien.html#weitere-informatik-themen-und-werkzeuge
* Airops - SQL Fixer / Query Explainer / SQL Writer: https://www.airops.com/#recipes
* SQL Island - Ein SQL Lernspiel: http://wwwlgis.informatik.uni-kl.de/extra/game/ (Lösungen siehe ganz unten)
* SQL Murder Mystery - Can you find out whodunnit?  https://mystery.knightlab.com

OSM SQL Terminal
-----------------------

OSM SQL Terminal mit allen öff. Bädern: https://terminal.osmdatapipeline.geoh.infs.ch/?query=select+%0D%0A++osm_id%2C+%0D%0A++type%2C+%0D%0A++label%2C+%0D%0A++tags-%3E%27addr%3Astreet%27+as+%22addr_street%22%2C%0D%0A++geom%0D%0Afrom+osm_centroid%0D%0Awhere%0D%0A++name+is+not+null+%0D%0A++and+tags+%40%3E+hstore%28%27sport%27%2C%27swimming%27%29%09%0D%0A 


------------------------------------------------------------------------------
-- "Suche innerhalb, max. 7" vs. "Suche die nächsten 7" 
--
-- Sie sind am Hauptplatz Rapperswil (SG) (Koordinate 8.81638 47.22666)
-- und suchen Sie die "nächsten 7 Bars".
------------------------------------------------------------------------------

-- Lösung mit Distanz innerhalb 500m Radius.
-- (Bemerkung: Es gäbe auch "ST_DWithin(geom1,geom2,distance)") 
-- Findet nur 4, obschon es leicht weiter weg 10 gäbe!
-- Demo: http://terminal.osmdatapipeline.geoh.infs.ch/?squery=P
select osm_centroid.geom, label
from 
  osm_centroid, 
  (select st_geomfromtext('POINT(8.81638 47.22666)', 4326) as geom) as mylocation
where osm_centroid.tags @> 'amenity=>bar'
and st_distancesphere(osm_centroid.geom, mylocation.geom) <= 500 
order by st_distancesphere(osm_centroid.geom, mylocation.geom)
limit 7;

-- Lösung mit Nächstnachbar-Reihenfolge (KNN-Search-Operator '<->')
-- Findet immer 7, egal wie weit weg!
-- Demo: http://terminal.osmdatapipeline.geoh.infs.ch/?squery=Q
select osm_centroid.geom, label
from 
  osm_centroid, 
  (select st_geomfromtext('POINT(8.81638 47.22666)', 4326) as geom) as mylocation
where osm_centroid.tags @> 'amenity=>bar'
order by osm_centroid.geom <-> mylocation.geom
limit 7;

bzw. siehe 


Weitere Notizen
--------------------

Mit ogr2ogr files mergen:
>ogr2ogr -f "<format>" -append -update merged_file input_file
wenn es fromatierungsprobleme gibt:
-nlt type|PROMOTE_TO_MULTI|CONVERT_TO_LINEAR|CONVERT_TO_CURVE

Bsp.:
>ogr2ogr -f "GPKG" out.gpkg gemeinden.shp -append -update -nlt PROMOTE_TO_MULTI
oder
>ogr2ogr -f 'gpkg' -append -update all.gpkg vsrpc5l.gpkg

Importieren der gpkg Datei:
>ogr2ogr -f PostgreSQL "PG:user=youruser password=yourpassword dbname=yourdbname" yourgeopackage.gpkg -skipfailures

Layers umbenennen:
QGIS -> Rechtsclick auf Layer -> Layer umbenennen -> neues gpkg erstellen
Shell -> mit -overwrite und -nln neuen Namen geben. (ogr2ogr -f "GPKG" out.gpkg gemeinden.shp -nln gemeinden_neu -overwrite)

Bei Encoding-Problemen mit Shapefiles das GeoPackage mit QGIS erstellen.
In der Shell mit ogr2ogr siehe https://gdal.org/drivers/vector/shapefile.html#encoding.


Keyboard-Shortcuts pgAdmin4 https://www.pgadmin.org/docs/pgadmin4/latest/keyboard_shortcuts.html 

Anlegen von Datenbanken:
1. PG-DB erstellen:  1.  pgAdmin4 Rechts-Klick; 2. SQL # CREATE DATABASE work2; 3. % createdb work2; 4. psql -U postgres -db postgres -c "CREATE DATABASE work2;"
2. Verbinden localhost 5432; 
3. PostGIS aktivieren: # create extension postgis;
4. Schema erstellen oder importieren und Daten importieren. Mit QGIS geht dieser Schritt auch.

KRS most used: EPSG:4326 (WGS84 lat/lon), EPSG:3857 (Web-Mercator), EPSG:21781 (CH1903/LV03), EPSG:2056 (CH1903+/LV95).

CTE mit Window Function: 
Beispiel einer CTE mit Window Function und db-fiddle.com: https://www.db-fiddle.com/f/btPVGEGQdZVcs1DUznTr5n/0

-- Uebg. 1 - Aufgabe 3 aus uster_kurs
SELECT gid, zonenbez_gemeinde, st_transform(the_geom, 4326) as geom
FROM raumplanung.nutzungszonen
WHERE 
  bev_gesamt > 50  AND bev_anteil_ch < 75 AND bev_anteil_weiblich > 55;

-- Indexe:
-- "Normaler" Index mit Default-Index btree
CREATE INDEX idx_meine_tabelle ON meine_tabelle(name);  
-- Aequivalent:
CREATE INDEX idx_meine_tabelle ON meine_tabelle USING btree(name)
-- Mit Geomtrie-Attribut geom
CREATE INDEX idx_meine_tabelle_geom ON meine_tabelle USING GIST(the_geom)


-- Anzeige des Tabellen-Schemas:
% psql \dt+ meine_tabelle 
with var(s, t) as (
    values ('raumplanung', 'nutzungszonen')
)
select 
    column_name, 
    case when column_name=f_geometry_column then "type" else data_type end as "data_type",  
    character_maximum_length, 
    is_nullable    
from information_schema.columns
left outer join geometry_columns on (f_table_schema=(select min(s) from var) and f_table_name=(select min(t) from var)) 
where table_schema=(select min(s) from var) and table_name=(select min(t) from var);


-- Layer Intersection mit ST_Simplify als Beschleunigung
SELECT 
   t1.id,
   ST_Intersection(t1.geom, t2.geom) as geom
FROM gemeinden as t1, (select id, ST_Simplify(geom, 10) FROM seen) as t2
WHERE 
-- a = B AND 
ST_Intersects(t1.geom, ST_Simplify(t2.geom, 10)) 
;

-- ST_Union:
CREATE TABLE schweiz.landesgrenze AS
    SELECT 
        1::integer AS gid, 
        ST_ExteriorRing(ST_Union(geom))::geometry(LINESTRING,21781) AS geom 
    FROM schweiz.gemeinden;


PERFORMANCE
------------------

Paul Ramsey (from FOSS4G-NA 2023): "The most common cause of performance issues in PostGIS is data irregularity. Fortunately there are straightforward ways to address funky data to get cleaner faster queries. There are also an bunch of techniques from the non-spatial world, such as data pre-summarizing, partitioning and denormalizing that can be used to turbo charge your systems through slightly altering your data model and query assumptions. Even extremely large databases can be made high performance, if you are able to constrain your assumptions about the potential variability of query parameters, just a little."
    
Index mit Include mit Blick auf einen Index-Only-Plan: 
CREATE INDEX idx
    ON sales ( subsidiary_id )
     INCLUDE ( eur_value )
Siehe https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes   

---
Diese Materialized View berechnet den Gesamtumsatz für jede Region vor: 
CREATE MATERIALIZED VIEW umsatz_nach_region AS
  SELECT region, SUM(umsatz) AS total_sales
  FROM bestellungen
  GROUP BY region;

Sobald die Materialized View erstellt ist, kann man sie abfragen: 
SELECT region, total_sales
FROM umsatz_nach_region;

Dazu kann man in PG auch einen Index erstellen (was bei Views in PG nicht geht): 
CREATE INDEX idx_umsatz_nach_region ON umsatz_nach_region (region);

Um eine materialisierte Ansicht zu aktualisieren:
REFRESH MATERIALIZED VIEW umsatz_nach_region;  -- könnte man auch von einem Trigger aufrufen.

---

Alle Sitzbänke weiter weg als 50m von einer OeV-Haltestelle im Kanton Bern
* Hier diese Abfrage mit dem OSM-Service "Overpass" wobei die Grenze von OSM: https://osm.li/UFQ . 
* Hier in Spatial SQL (Relation 1686344 = Kt. Bern) für das OSM SQL Terminal:

with benches (geom, name, osm_id) as (
  select geom, name, osm_id
  from osm_centroid
  where tags->'amenity'='bench'
  and st_intersects(geom, (select geom from osm_boundary where osm_id=-1686344))
),
stations (geom, osm_id) as (
  select geom, osm_id
  from osm_centroid
  where tags->'amenity'='bank' --???
  and st_intersects(geom, (select geom from osm_boundary where osm_id=-1686344)) 
)
select geom, name, osm_id
from benches
where osm_id not in (
  select osm_id
  from benches
  join lateral (
      select true 
      from stations 
      where st_dwithin(benches.geom::geography, stations.geom::geography, 50)) as tmp on true
);

Der K-Nearest-Neighbour-Index mit den "<->/<=>"-Operatoren
https://giswiki.hsr.ch/PostGIS_-_Tipps_und_Tricks#Find_and_add_nearest_hydrant_to_a_parcel

Beding KNN-Index:
CREATE INDEX idx_sometable_geom_nd_gist ON sometable USING gist(geom gist_geometry_ops_nd);
-- Speziell ist der Op. "gist_geometry_ops_nd", der danach mit <-> bzw. <=> angesprochen wird
 



FRAGEN ZUR AuA-SESSION
---------------------------------

Antworten von Karsten Lenz von dbi Services.

Was muss ich beachten, damit räumliche Abfragen - insbesondere bei räumlichen Operationen, bei welchen  mehreren Datensätze beteiligt sind -  performant durchlaufen? (Sebastian Denier) 
-> Horizontale Partionierung ("Sharding") nach einem Partitionierungs-Schlüssel (default Hash auf ein Attribut). 
-> "Subpartitioning"

Was gibt es für Lösungsmöglichkeiten, um Geo-Operationen auf sehr grosse Datensätze durchführen zu können? Indizes?, Partitionierung?, ... (Sebastian Denier)
- Extension pg_stats_statements (o.ä.) gibt Hinweise.

Wenn mehrere räumliche Operationen nacheinander durchlaufen sollen (Beispiel ST_Intersects -> ST_Buffer -> ST_Intersection --> ... --> ..) : Wie entscheide ich, ob ich die Zwischenschritte mit CTE durchführe (WITH AS ... do this, WITH AS ... usw.), Views erstelle, Materialized Views oder gar eine neue Tabelle für die Zwischenresultate anlege? Was macht Sinn und was ist performanter? (Sebastian Denier)
- optimizer mode umschalten / abschalten
- Materialized Views 
- bei Zeitfolgen BRIN Index verwenden.

Verwaltung von Zeitständen (datum_von / datum_bis) sinnvolle Ablage und Prozesse, um die Zeitstände zu verwalten (Stefan Reist) 
--> Datawarehouse-Begriff: "bi-temporale Daten". 

Gibt es eine effiziente Möglichkeit, Unterschiede zwischen zwei Tabellen (z.B. zwei Zeitstände AV Daten) zu erhalten (Stefan Reist)
--> SQL for Data Integration

Vorgehen und Vorbereitung bei Prüfung der Datenbank auf Topologieverarbeitung (Stefan Reist)
--> selbstgestrickt.

Gibt es Unterschiede in der Performance wenn PostgreSQL/PostGIS auf Windows oder Linux betrieben wird? (Thomas Studer) 
A. PostgreSQL ist abhängig von IO, damit vom Verwendeten Disk/ Storage System und dem verwendeteten Filesystem. Die Hauptenwicklung von PostgreSQL findet unter Linux statt, somit sind die Adaptionen an Linux besseer als auf Windows (NTFS).
--> Linux-FS XFS.

Gibt es Update-Tools/Scripts für PostgreSQL- und/oder PostGIS-Datenbanken um diese auf neuere Versionen anzuheben? Worauf ist zu achten? Abhängigkeiten? (Thomas Studer)
A. SW: Das hängt davon ab wie man es installiert hat, nutzt man z.B. RPM Pakete wird das im allgemeinen über die RPM Pakete und ihre Abhängigkeiten geregelt.
PostGIS hat selber noch Abhängigkeiten zu Fremd Libaries wie GDAL oder SFCGAL, dieses führt häufig zu  Schwierigkeien weil das OS nicht die benötigten Versionen zur Verfügung stellt.
Kompeliert man PostgreSQL, PostGIS und die Libaries selber ohne Fehler bei den Regression Tests ist es der normale Upgrade Process, Binaries tauschen bei Minor Releases, pgupgrade bei Major Releases.
B. Daten: Binärkompatibel auf Minor-Releases 15.3 auf 15.4. ansonsten (binär) pg_upgrade mit 2 Modi: copy-mode mit Weg zurück.

In SQL Server gibt es Logins (SQL Server), Users (Datenbank), Rollen (Server und Datenbank) und Berechtigungen. Ein User wird einem Login zugewiesen. Wie ist das Zugriffskonzept bei PostgreSQL? (Thomas Studer)
A. Rollen-basiert, es werden Rollen erstellt denen die Berechtigungen zugewiesen werden und User werden den Rollen zugewiesen.
Seit PG 8.

Im Datenbank-Cluster A wurde eine Datenbank erzeugt und ein Backup erstellt. Kann mit diesem Backup die Datenbank im Cluster B Restored werden? (Thomas Studer)
Ja, warum sollte das nicht gehen.
pg_dump -Fc Binär , -Fd Files
pgBackRest - Projekt

Gibt es allgemeingültige Konventionen um Datenbanken von PostGIS nach GPKG und Retour zu konvertieren? Gibt es Tools/Scripts dazu? (Thomas Studer)
A. Das ist eher eine Abhängigkeit zur verwendeten GDAL Version, generell werden Feldnamen vom Geopackage nach lowercase konvertiert

Können maintenance plans über einen ganzen Datenbank-Cluster definiert werden (bspw. Backup). (Thomas Studer)
A. Vacuum z.B. ist pro Instanz konfiguriert und kann über alter befehle in SQL für einzelne Datenbanken und/oder Tabellen angepasst werden. 
Backup lässt sich entweder über (Bash-)Script "Globalisieren" oder eben mit pgdump_all. Tools wie pgBackRest kopieren das komplette pgdata via scp und komprimieren es, also sichern sie entweder alles oder eben nur die Dateien die Daten der entsprechenden Datenbank enthalten.
pg_dump -all

Gibt es für die Verbindungsherstellung "Verbindungsfiles" welche die Verbindungsinformationen enthalten und von Tools (bspw. QGIS) gelesen werden können? (Thomas Studer)
A. gibt's leider nicht wegen der Tool-Vielfalt.

In SDEs (ESRI ArcGIS) bit es dir Konfigurationsmöglichkeiten Versionen, als_Versioniert_registriert, EditorTracking, Archivieren, Attachments. Gibt es für PostgreSQL oder PostGIS ähnliche Funktionen? (Thomas Studer)
PG Extension table_version.
SQL Server "Temporal Tables" https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16

- SQL Server bietet verschiedene Partitionen an mit individuellen Zugriffsrechten und Konfigurationen. Gibt es auf Postgres ähnliches?
Ja;
CREATE tablespace --> physischer Ort.
Weitere DB Tools DbVisualizer, DBeaver.

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



Kurs PostGIS/PostgreSQL 2022-09-08
=============================

Comment /* Kommentar geht hier */
PostgreSQL – Index Types: //www.geeksforgeeks.org/postgresql-index-types/
https://codebeautify.org/sqlformatter

shp2pgsql-gui: http://download.osgeo.org/postgis/windows/pg14/

Tipps und Tricks für PostGIS von Stefan:
* PostGIS-SQL-Beispiele : https://giswiki.hsr.ch/PostGIS_Terminal_Examples
*  OpenSchoolMaps - Kostenlose Online-Tutorials für QGIS mit Themen für Einsteiger bis Fortgeschrittene:  
https://openschoolmaps.ch/pages/materialien.html#infuehrung-in-qgis-3-und-in-gis
* SQL Island - Ein SQL Lernspiel: http://wwwlgis.informatik.uni-kl.de/extra/game/ (Lösungen siehe ganz unten)
* "Überblick über PostgreSQL Index-Typen" https://md.coredump.ch/s/73PZG-btU#

Nächste Weiterbildungen und Events 2022

* GEOWebforum -  Schweizerisches Forum zu Geoinformationen (mit dem Montagsmail). www.geowebforum.ch
* Do. 8. September 2022: Landschaftskongress  - u.a. Zersiedelungsindex, OST Campus Rapperswil, Gebäude 1
* Mi. 16. November 2022, 13:30-17:00: GIS Day @ OST: Webmapping und GIS-Weiterbildung, OST Campus Rapperswil. https://giswiki.hsr.ch/GIS_Day_2022_OST 
* Do. 17. November 2022 PostGIS Day. U.a. https://info.crunchydata.com/postgis-day-2022 
* Di. 25. Oktober 2022, 17:30 - 19:00: (Mini-)Mapathon virtuell anlässlich der GEOSchool Days 2022: http://geoschoolday.ch/ (Mit QGIS und Quick OSM Plugin kann man OSM-Daten einfach herunterladen)
* Nächste Kurse QGIS und PostGIS am OST Campus Rapperswil: jeweils Januar, Juni sowie Mitte September.
* Weitere Kurse OPENGIS.ch z.T. Zürich/online: https://www.opengis.ch/de/category/kurse/qgis-kurse/ 
2023:
* QGIS-Anwendertag Schweiz 2023. OST Campus Rapperswil
* QGISOpenDay (letzter Freitag im Monat virtuell): https://twitter.com/hashtag/QGISOpenDay
* Swiss PGDay 2023, Rapperswil. www.pgday.ch 

Online Tools
* Browser-based: https://www.db-fiddle.com/ , https://dbfiddle.uk/?rdbms=postgres_14 
* Exklusiv für Kursteilnehmende: Beta-Version des OSM SQL Terminals:
https://terminal.osmdatapipeline.geoh.infs.ch/?query=SELECT+geom%2C+label%0D%0AFROM+osm_point%0D%0AWHERE+tags+%40%3E+hstore%28%27name%27%2C+%27Parkhaus+See%27%29+%0D%0AOR+tags+%40%3E+hstore%28%27name%27%2C+%27Kinderzoo%27%29









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

"This" Etherpad "is a collaborative text editor, a web-based flipchart code can be shared with https://pastebin.com/ and images with https://pasteboard.co/ , http://snag.gy/ or https://onpaste.com/ be shared (<< Below you can exchange web links and text, etc. >>)


Kurs PostGIS Einführung September 2020
==================================

Webseite: https://giswiki.hsr.ch/Kurs_PostGIS
Daten: https://cloud.opengis.ch/index.php/s/ytNY2CsakFEAcZy


Ausblick - INTERLIS
-----------------------

Abbildung von INTERLIS-Konzepten Vererbung, Topic & Class Namen und Aufzähltypen/DOMAIN
* ili2pg - http://www.eisenhutinformatik.ch/interlis/ili2pg/
* ModelBaker - nutzt ili2pg?

Menschenlesbare Erfassungsformular-Texte
* Noch etwas im Fluss: Siehe Projekt Arbeitstitel "Interlis+" 


Metadaten in PostgreSQL?
--------------------------------

* Metadaten im Sinne von Geo-Metadaten Norm GM03?
* Metadaten im Sinne von Postgres-Katalog? 

=> Erfa Austausch PostgreSQL in den Kantonen / GIS-Stellen


Grundzüge zum Routing
-----------------------------

Docs:
* pgRouting >= v2.0 : Docs https://docs.pgrouting.org/ 

How to load OpenStreetMap into PostgreSQL database?

pgRouting only - mit SQL-Funktionen
* Beginners Guide by Anita Graser: https://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/
* Creating PostgreSQL / PgRouting routing systems using OpenStreetMap data: https://translate.google.com/translate?sl=auto&tl=EN&u=https://habr.com/ru/post/511144/
* Buch "PostGIS Cookbook – Second Edition"

pgRouting with OSM2PO: 
* https://www.bostongis.com/?content_name=pgrouting_osm2po_1


Ausblick PostGIS - Weitere
-------------------------------

Topology:
* 

3D:
*

Raster:
* Siehe Unterlagen Kap. 10

PointCloud
* 


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

?$body$ --> Warum
https://www.postgresqltutorial.com/dollar-quoted-string-constants/





Fluesse zu Graph:
    
    CREATE OR REPLACE VIEW schweiz.fluesse_ext AS
   SELECT *, st_startpoint(st_geometryn(geom, 1)), st_endpoint(st_geometryn(geom, 1))
   FROM schweiz.fluesse;

DROP TABLE IF EXISTS schweiz.node;

CREATE TABLE schweiz.node AS
   SELECT row_number() OVER (ORDER BY foo.p)::integer AS id, 
          foo.p AS geom
   FROM (         
      SELECT DISTINCT schweiz.fluesse_ext.st_startpoint AS p FROM schweiz.fluesse_ext
      UNION
      SELECT DISTINCT schweiz.fluesse_ext.st_endpoint AS p FROM schweiz.fluesse_ext
   ) foo
   GROUP BY foo.p;

DROP TABLE IF EXISTS schweiz.network;
   
 CREATE TABLE schweiz.network AS
   SELECT a.*, b.id as start_id, c.id as end_id
   FROM schweiz.fluesse_ext AS a
      JOIN schweiz.node AS b ON a.st_startpoint = b.geom
      JOIN schweiz.node AS c ON a.st_endpoint = c.geom;



PGROUTING

SELECT * FROM 
pgr_dijkstra('
   SELECT gid AS id, 
          start_id::int4 AS source, 
          end_id::int4 AS target, 
          shape_leng::float8 AS cost
   FROM network',
1,
5110,
false,
false);


RECURSIVE SQL mit CTE Common Table Expression

CREATE VIEW schweiz.upstream AS
WITH RECURSIVE rec_q(end_id, start_id) AS (
  SELECT end_id, start_id, geom 
  FROM schweiz.network 
  WHERE end_id = 5
UNION
  SELECT n.end_id, n.start_id, n.geom 
  FROM schweiz.network n, rec_q
  WHERE rec_q.start_id = n.end_id
)
SELECT * FROM rec_q


Kurs PostGIS Einführung September 2019
==================================

Nützliche Hinweise:
    * Zum Testen: "DB Fiddle" mit PostgreSQL 11 and PostGIS: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=837003e1fd650cb6a08bf654ba08d145
    * PostGIS tips, tricks and snippets: https://giswiki.hsr.ch/PostGIS_Terminal_Examples and https://giswiki.hsr.ch/PostGIS_-_Tipps_und_Tricks

Man merke sich:

    * Wertemässig gleich mit Operator "=" (equal ) ist nicht gleich geometrisch gleich "st_equal(geomA, geomB)" (Linien können auch anderherum digitalisert sein und trotzdem gleich).

    * Funktion ST_Intersection ist nicht gleich ST_Intersects! ST_Intersects ist ein Topologie/Relation Check (gibt Boolean zurück), ST_Intersection ist eine Processing Fn., die Geometrien zurückgibt.

    * Funktion ST_Intersection ist viel langsamer als "Relation Checks" wie ST_Intersects, ST_CoveredBy, ST_Within: Siehe https://postgis.net/2014/03/14/tip_intersection_faster/

    * Viele Geometrie-Funktionen (v.a. ST_Distance, ST_DWithin, ST_Intersection) machen auf planaren Koordinatensystemen mehr Sinn (oder funktionieren überhaupt erst dort) als mit sphärischen KRS (lat/lon)!

    * Operatoren && (BBox Overlap, ist eingebaut) ist kaum und Funktion AddGeometryColumn (das war PostGIS 1 / Linux) ist nicht mehr nötig. 

    * Bevorzuge ST_Covers im Vergleich zu ST_Contains da dort lines on boundaries count as „inside“ (Source: Martin Davis‘ blog post: http://lin-ear-th-inking.blogspot.ch/2007/06/subtleties-of-ogc-covers-spatial.html )

    * Bei shp2pgsql und ogr2ogr Import wird per Default aus eine Geometriy Polygon ein Multipolygon im Zielformat (DB oder so) erzeugt. Wenn man sicher ist, gibt es eine Option, die das verhindert und im Zielformat auch POLYGONe (und nicht MULTIPOLYGONe) erzeugt.

    * Unterschied von "Alle Restaurants innerhalb 20km" (mit ST_DWithin) und "Gib die 20 nächstgelegenen Restautants" (mit ORDER BY mylocation.geom<->restaurant.geom)!



-------------
FRAGEN
------------

<< Füge deine Frage hier hinzu!! 
...
>>

Frage: Wie kann man mit PostGIS "Point Clustering" server-seitig machen? 
Antwort: Z.B. mit ST_ClusterWithin() oder mit ST_SnapToGrid: Siehe https://giswiki.hsr.ch/PostGIS_Terminal_Examples#Point_Clustering

Frage: Howto create a View with a unique ID in PostGIS (or SQlite/Geopackage)?
Antwort: Ja, mit der Window Funktion "row_number() OVER () AS id" (Man vermeide die Postgres "OID", die sind nur 4-byte integer und deprecated). 
CREATE OR REPLACE VIEW myview_with_a_hoc_id as
SELECT 
    row_number() OVER () AS id,
    attr1
FROM mytable;

Frage: Effiziente Nächstnachbar-Suche
Antwort: Siehe "Find and add nearest hydrant to a parcel". Man beachte den KNN-Index mit dem "ORDER BY parcels.geom <-> geom": Siehe https://giswiki.hsr.ch/PostGIS_-_Tipps_und_Tricks#Find_and_add_nearest_hydrant_to_a_parcel

Frage: Gibt es Tipps zur Bereinigung von Polygonen: 
Antwort 1: Ja: es gibt einerseits ST_MakeValid der den Trick mit dem Aufruf von ST_Buffer(geometry,0) - also mit 0 Meter Buffer, der das Polygon bereinigt zurückgibt oder NULL. 
Antwort 2: Ansonsten siehe https://giswiki.hsr.ch/PostGIS_-_Tipps_und_Tricks#Erkennen_und_Eliminieren_von_Sliver_Polygonen_und_Spikes .

Frage: Was gibt es für Performance-Tipps zu PostGIS? 
Answer 1: With "correct" queries, with indexes, with "correct" database schema, with "correct" and sufficient memory ...
Answer 2: For SSD instead of HDD, the postgresql.conf parameter random_page_cost must be changed from 1.1 to 4 and effective_io_concurrency from 200 to 2.
Answer 3: Last but not least with the configuration file "postgresql.conf" in the corresponding directory where PostreSQL is installed. See https://pgtune.leopard.in.ua/ or http://pgconfigurator.cybertec.at
Answer 4: First analyze why / where the query is slow: Visualize Explains I: https://explain.depesz.com , Visualize Explains II: http://tatiyants.com/pev/ 

Frage: How to cartographically generalize data: 
Antwort 1: See ST_Simplify and ST_SimplifyPreserveTopology: See https://postgis.net/docs/ST_Simplify.html
Diskussion: Siehe eg Chapter 2 Generalization (from page 9) on https://www.bfs.admin.ch/bfs/en/home/dienstleistungen/geostat/geodaten-bundesstatistik/administrative-grenzen/ generalized-community- borders.assetdetail.5247316.html There are topics like Bendsimplify / Douglas Peucker Pointremove / Smooth / Aggregate ... Source: swissBOUNDARIES3D  https://shop.swisstopo.admin.ch/en/products/landscape/boundaries3D Goal: https : //www.bfs.admin.ch/bfs/de/home/dienstleistungen/geostat/geodaten-bundesstatistik/administrative-grenzen/generalisierte-gemeindegrenzen.html

Frage: Kann man Zufalls-Testdaten mit PostGIS erzeugen? 
Antwort: Ja, mit der "generate_series()"-Funktion und random(). Siehe https://www.bostongis.com/blog/index.php?/archives/28-Using-generate_series-to-Generate-Test-Spatial-Data.html


---------------
ÜBUNGEN
---------------

Backup / Restore Übungen: 
cd uebungen\uebungsdaten\
$ pg_restore --host "localhost" --port "5432" --username "postgres" --no-password --dbname "uster_kurs" --data-only --verbose uster_kurs.backup
-- "C:\\Daten\\DATEN_~2\\_kurse\\_HSR-K~1\\_2020-~1\\daten\\UEBUNG~1\\USTER_~1.BAC"

/* user_kurs - Abfrage
select gid, zonenbez_gemeinde, st_transform(the_geom, 4326) as geom
from raumplanung.nutzungszonen 
limit 10
*/


-- Lösung Übung 1 - Aufgabe 3:
select st_transform(the_geom, 4326) as geom, *
from raumplanung.nutzungszonen 
where bev_gesamt > 50 
and bev_anteil_weiblich > 55
and bev_nicht_ch > 25;


Importieren und Exportieren von Geodaten
-------------------------------------------------------------

-----------
-- Biohof
-----------

Laden von Excel/CSV-Datei:
* Gegeben: biohof-daten als Excel bzw. CSV
* Mit QGIS (installiert
** Import der Datei => Angabe von lat/lon => Layer biohof
** Layer Rechtsklick > Export neu mit CRS 2056 als GeoPackage "hofsuchevomhof_nur_bio.gpkg"
* Mit OGR-Tools (installiert via QGIS) und in der OSGeo4W Shell $ ogr2ogr ....
   $ ogrinfo hofsuchevomhof_nur_bio.gpkg -al -so
   $ ogr2ogr -f "PostgreSQL" PG:"host=localhost dbname=work user=postgres password=postgres" -nln biohof hofsuchevomhof_nur_bio.gpkg

OGR - in Command Shell
Siehe auch https://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet und https://giswiki.hsr.ch/HowTo_OGR2OGR

In Cmd Shell:
$ ogrinfo hofsuchevomhof_nur_bio.gpkg -al -so
$ ogrinfo PG:"dbname='uster_kurs' user=postgres password='postgres'" raumplanung.nutzungszonen -al -so

Konversion nutzungszonen von PostGIS-DB nach GeoJSON: 
$ ogr2ogr -f GeoJSON nutzungszonen.geojson PG:"dbname='uster_kurs' user=postgres password='postgres'" raumplanung.nutzungszonen

Konversion nutzungszonen von GeoJSON nach PostGIS-DB:
$ ogr2ogr -f "PostgreSQL" PG:"host=localhost dbname=work user=postgres password=postgres" -nln biohof hofsuchevomhof_nur_bio.gpkg

-- Verbinden mit PG DB, z.B. "work" mit psql oder pgAdmin4:

select st_transform(geom, 4326), * from biohof;

-- Problem: Datentypen stimmen nicht ganz: Sind alle VARCHAR/TEXT

alter table biohof
  alter column "id" type integer using id::integer;

alter table biohof
  alter column farmname type varchar(60) using trim(farmname);

alter table biohof
  alter column zip type integer using to_number(zip,'9999')::integer;

-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION as_numeric(text) 
RETURNS NUMERIC AS $$
-- Inspired by http://stackoverflow.com/questions/16195986/isnumeric-with-postgresql/16206123#16206123
DECLARE test NUMERIC;
BEGIN
     test = $1::NUMERIC;
     RETURN test;
EXCEPTION WHEN others THEN
     RETURN -1;
END;
$$ STRICT
LANGUAGE plpgsql IMMUTABLE;

alter table biohof
  alter column zip type integer using nullif(as_numeric(zip),-1)::int;
Ja 
select st_transform(geom, 4326), * from biohof;

-- ENDE --


PostGIS 2 Cheatsheet: http://www.postgis.us/downloads/postgis21_cheatsheet.pdf
* Operators
* Management Functions
* Geometry Constructors
* Geometry Accessors
* Spatial Relationships and Measurements
* Geometry Processing
* Geometry Editors
* Linear Referencing
* Geometry Outputs
* Miscellaneous Functions / Exceptional Functions




/*-----------------------------------------------------------------------------
-- SQL Island HSR
-- Website: http://wwwlgis.informatik.uni-kl.de/extra/game/

Die drei Tabellen (Tabellen- und Attributnamen klein-schreiben):
* dorf (dorfnr, name, haeuptling)
* bewohner (bewohnernr, name, dorfnr, geschlecht, beruf, gold, status)
* gegenstand (gegenstand, besitzer)

Ziel ist dorf mit Polygon und gegenstand mit Point zu ergänzen.
Aufgaben:
1. SQL Island HSR durchspielen / anfangen
2. Tabellen selber anlegen gemäss Spiel.
3. Tabellen ergänzen

--------------------------------------------------------------------------*/

create database sql_island_hsr;
-- connect to database sql_island_hsr
create extension postgis;

---

drop table if exists dorf cascade; 
create table dorf (
  dorfnr int primary key,
  "name" varchar(60),
  haeuptling int not null, -- references bewohner
  geom GEOMETRY(POLYGON, 2056)  -- NEU 
);

drop type if exists status_type cascade;
create type status_type as enum ('friedlich', 'gefangen', 'boese', 'unbekannt');

drop table if exists bewohner cascade; 
create table bewohner (
  bewohnernr int primary key,
  "name" varchar(60) not null,
  dorfnr int not null references dorf,
  geschlecht char(1),
  beruf varchar(60),
  gold int,
  status status_type not null
);

drop table if exists gegenstand cascade;
create table gegenstand (
  gegenstand varchar(60) not null,
  besitzer int references bewohner,
  "position" GEOMETRY(POINT, 2056)
);

truncate dorf cascade;
insert into dorf values 
  (1, 'Affenstadt', 1, st_transform(st_geomfromtext('POLYGON ((8.775157 47.216113, 8.776402 47.215574, 8.779127 47.216055, 8.783633 47.217600, 8.782024 47.218795, 8.776574 47.218183, 8.775157 47.216113))', 4326),2056) ),
  (2, 'Gurkendorf', 6, null ),
  (3, 'Zwiebelhausen', 13, st_transform(st_geomfromtext('POLYGON ((8.789523 47.220282, 8.792098 47.220136, 8.795735 47.220865, 8.793718 47.221819, 8.791047 47.22114, 8.789523 47.220282))', 4326),2056) )
;
         
truncate bewohner cascade;
insert into bewohner values
  (1, 'Paul Backmann', 1, 'm', 'Baecker', 850, 'friedlich'),
  (2, 'Ernst Peng', 3, 'm', 'Waffenschmied', 280, 'friedlich'),
  (3, 'Rita Ochse', 1, 'w', 'Baecker', 350, 'friedlich'),
  (5, 'Dirty Dieter', 3, 'm', 'Schmied', 650, 'boese'),
  (20, 'Fremder', 1, null, null, 0, 'unbekannt')
;

select * from bewohner;
select st_transform(geom, 4326), * from dorf;

truncate gegenstand cascade;
insert into gegenstand values
  ('Kaffeetasse',20,st_transform(st_geomfromtext('POINT (8.778440 47.216871)', 4326),2056) ),
  ('Teekanne',20,st_transform(st_geomfromtext('POINT (8.777925 47.216864)', 4326),2056) ),
  ('Eimer',20,st_transform(st_geomfromtext('POINT (8.778709 47.217986)', 4326),2056) ),
  ('Ring',20,st_transform(st_geomfromtext('POINT (8.793099 47.221479)', 4326),2056) )
;

select st_transform(position, 4326), * from gegenstand;

-- Wo wohnt Dirty Dieter'
select dorf.name 
from dorf 
join bewohner on bewohner.dorfnr=dorf.dorfnr -- join bewohner using(dorfnr)
where bewohner.name = 'Dirty Dieter'

--- Going spatial!

select dorfnr, name, round(st_area(geom))/1000000 from dorf;

-- Alle Gegenstände innerhalb eines Dorfes
select st_transform(position, 4326), dorfnr, name, haeuptling, gegenstand, besitzer
from dorf as d
join gegenstand as g on st_within(g.position, d.geom);

select 
  st_transform(position, 4326), 
  gegenstand, 
  besitzer, 
  round(st_distance(gps.location, g.position)) as distance_from_gps_location
from 
  gegenstand as g,
  (select st_transform(st_geomfromtext('POINT (8.778440 47.216871)', 4326),2056) as location) as gps
where st_within(
  g.position, 
  st_buffer(gps.location, 100)
)

select 
  st_transform(position, 4326), 
  gegenstand, 
  besitzer, 
  round(st_distance(gps.location, g.position)) as distance_from_gps_location
from 
  gegenstand g,
  (select st_transform(st_geomfromtext('POINT (8.778440 47.216871)', 4326),2056) as location) as gps
where st_dwithin(
  position, 
  gps.location, 
  100
)
and not st_equals(position, gps.location);

---- ENDE ---


/*

SQL Island


Website: http://wwwlgis.informatik.uni-kl.de/extra/game/
Antworten (Lösungen) zum Lernprogramm von Stefan Keller.
Die Lösungsantworten sind immer wie folgt strukturiert: zuerst die SQL-Abfrage, dann das Resultatset (mit Tabellenüberschriften), dann "Yeah!" als Abschluss. SQL-Wörter werden gross geschrieben; Funktionen und Tabellen- und Attributnamen klein. 
LOS GEHT's!
*/ 

SELECT * FROM dorf

SELECT * FROM bewohner

SELECT * FROM bewohner WHERE beruf = 'Metzger'

SELECT * FROM bewohner WHERE status = 'friedlich'

SELECT * FROM bewohner WHERE status = 'friedlich' AND beruf = 'Waffenschmied'

SELECT * FROM bewohner WHERE status = 'friedlich' AND beruf LIKE '%schmied'

INSERT INTO bewohner (name, dorfnr, geschlecht, beruf, gold, status) VALUES ('Fremder', 1, '?', '?', 0, '?')

SELECT bewohnernr FROM bewohner WHERE name = 'Fremder'

SELECT gold FROM bewohner WHERE name = 'Fremder'

SELECT * FROM gegenstand WHERE besitzer IS NULL

UPDATE gegenstand SET besitzer = 20 WHERE gegenstand = 'Kaffeetasse'

UPDATE gegenstand SET besitzer = 20 WHERE besitzer IS NULL

SELECT * FROM gegenstand WHERE besitzer = 20 ORDER BY gegenstand -- Diese Antwort war schwierig:

SELECT * FROM bewohner WHERE (beruf = 'Haendler' OR beruf = 'Kaufmann') AND status = 'friedlich'

UPDATE gegenstand SET besitzer = 15 WHERE gegenstand = 'Ring' OR gegenstand = 'Teekanne'

UPDATE bewohner SET gold = gold + 120 WHERE bewohnernr = 20

UPDATE bewohner SET name = 'Stefan' WHERE bewohnernr = 20

SELECT * FROM bewohner WHERE beruf = 'Baecker' ORDER BY gold DESC

UPDATE bewohner SET gold = gold + 100 - 150 WHERE bewohnernr = 20

INSERT INTO gegenstand (gegenstand, besitzer) VALUES ('Schwert', 20)

SELECT * FROM bewohner WHERE beruf = 'Pilot'

SELECT dorf.name FROM dorf, bewohner WHERE dorf.dorfnr=bewohner.dorfnr AND bewohner.name = 'Dirty Dieter'
-- Besser: SELECT dorf.name FROM dorf JOIN bewohner USING(dorfnr) WHERE bewohner.name = 'Dirty Dieter'

SELECT bewohner.name FROM bewohner, dorf WHERE dorf.dorfnr=bewohner.dorfnr AND dorf.name = 'Zwiebelhausen' AND bewohner.bewohnernr = dorf.haeuptling
-- Besser: SELECT b.name FROM bewohner b JOIN dorf d USING(dorfnr) WHERE d.name = 'Zwiebelhausen' AND b.bewohnernr = d.haeuptling

SELECT count(*) FROM bewohner, dorf WHERE dorf.dorfnr = bewohner.dorfnr AND dorf.name = 'Zwiebelhausen'

SELECT count(*) FROM bewohner, dorf WHERE dorf.dorfnr=bewohner.dorfnr AND dorf.name='Zwiebelhausen' AND bewohner.geschlecht='w'
-- Besser: SELECT count(*) FROM bewohner b JOIN dorf d USING(dorfnr) WHERE d.name='Zwiebelhausen' AND b.geschlecht='w'

SELECT DISTINCT bewohner.name FROM bewohner, dorf WHERE dorf.dorfnr=bewohner.dorfnr AND dorf.name='Zwiebelhausen' AND bewohner.geschlecht='w'
-- Besser: SELECT distinct b.name FROM bewohner b join dorf d using(dorfnr) WHERE d.name='Zwiebelhausen' AND b.geschlecht='w'

SELECT SUM(bewohner.gold) FROM bewohner, dorf WHERE dorf.dorfnr=bewohner.dorfnr AND dorf.name = 'Gurkendorf'

SELECT sum(bewohner.gold) FROM bewohner, dorf WHERE dorf.dorfnr=bewohner.dorfnr AND (bewohner.beruf = 'Haendler' OR beruf = 'Kaufmann' OR beruf = 'Baecker')
-- Das war schwierig!
-- Besser: SELECT sum(b.gold) FROM bewohner b JOIN dorf d USING(dorfnr) WHERE b.beruf IN ('Haendler','Kaufmann','Bäcker')

SELECT status, AVG(b.gold) FROM bewohner b GROUP BY status ORDER BY status 

DELETE FROM bewohner WHERE name = 'Dirty Dieter'

DELETE FROM bewohner WHERE name = 'Dirty Doerthe'

UPDATE bewohner SET status = 'friedlich' WHERE bewohnernr = 8

DELETE FROM bewohner WHERE bewohnernr = 20

--- YEAH! - ENDE ---