Skip to content

fossgis-2023-workshop-postgresql-postgis-fuer-einsteiger.md

Latest commit

 

History

History
executable file
·
811 lines (581 loc) · 26.1 KB

fossgis-2023-workshop-postgresql-postgis-fuer-einsteiger.md

File metadata and controls

executable file
·
811 lines (581 loc) · 26.1 KB
Raw

Workshop PostgreSQL/PostGIS für Einsteiger

FOSSGIS 2023 Berlin Workshop 16. März 2023

Creative Commons License

Jörg Thomsen

Annika Schmidt

Workshopmaterial Astrid Emde

Die Themen

  • Warum lohnt sich der Einsatz einer Datenbank?
  • PostgreSQL / PostGIS
  • SQL-Einführung
  • Import / Export von Daten
  • Räumliche Funktionen
  • Räumlicher Index
  • Rollen & Kontrolle über den Zugriff auf die Daten

OSGeoLive

Dieser Workshop nutzt OSGeoLive (https://live.osgeo.org) Version 15.0 (Release Oktober 2022). OSGeoLive basiert auf Lubuntu 22.04 Jammy Jellyfish und beinhaltet eine Kollektion aus nahezu 50 vorinstallierten Software-Projekten. OSGeoLive beinhaltet außerdem Beispieldaten, die für diesen Workshop verwendet werden.

OSGeoLive kann über den folgenden Link heruntergeladen werden. Sie können OSGeoLive in einer Virtuellen Maschine (empfohlen) oder über einen USB-Stick nutzen.

Aktuelle Software Version

OSGeoLive 15.0

  • PostgreSQL 14.4
  • PostGIS 3.2.0
SELECT version(), postgis_version(), postgis_full_version();

Daten

  • Natural Earth II
  • Daten liegen als ESRI Shapedateien vor - Länder, Bundesländer, Flüsse, Orte & mehr unter /home/user/data
  • Datenbank: natural_earth2
  • OpenStreetMap
  • Datenbank: osm_local

Zusätzliche Information

Warum lohnt sich der Einsatz einer Datenbank?

  • zentrale Datenhaltung - keine Datenredundanz
  • Konsistenz der Daten
  • Mehrbenutzer-Zugriff
  • Kontrollierter Zugriff über Zugriffskontrolle und -management
  • Zugriff auf die Daten über unterschiedliche Werkzeuge
  • Kombination von unterschiedlichen Daten - datenbankweit, datenbankübergreifend und auf Fremdquellen
  • SQL zur Generierung und Analyse der Daten
  • Backup, Replikation ...

PostgreSQL

  • Unterstützt von zahlreichen anderen Programmen
  • Schnell, leistungsstark, verlässlich, robust
  • Einfach zu Warten
  • Folgt SQL Standards
  • Schnittstelle zu vielen Programmiersprachen
  • Subselects, Funktions, Trigger, Foreign Data Wrapper, Replikation & mehr
  • https://www.postgresql.org/about/

PostGIS

  • Erweiterung (Extension) für PostgreSQL
  • PostGIS übernimmt die Arbeit, die sonst das DesktopGIS gemacht hat
  • Folgt Standards:
  • OGC Simple Feature Spezification for SQL
  • OGC ISO SQL/MM Spezification
  • Stellt zahlreiche räumliche Funktionen bereit
  • Breite Unterstützung durch andere Programme
  • Leichter Import / Export von räumlichen Daten (QGIS DB-Verwaltung, shp2pgsql, pgsql2shp, ogr2ogr, dxf2postgis, osm2pgsql)
  • Die Vorteile von PostgreSQL können genutzt werden (Benutzerverwaltung, Replication, Indezierung & mehr)
  • Sehr leistungsstark: Unterstützung von Vektor- & Rasterdaten, geometry (planar Daten) und geography (spheroid), kreisförmige Objekte, 3D-, 4D-Objekte, Punktwolken, Routing über pg_routing, Topologien, Generierung von MVT & GeoJSON
  • Daten werden als WKB (Well-known Binary) gespeichert. WKT (Well-known text) zur lesbaren Ausgabe.
  • http://postgis.net/
  • http://postgis.net/docs/

PostGIS wurde am 21. Mai 2021 zwanzig Jahre alt!

PostGISDay - immer am 3. Donnerstag im November

Datenbank-Clients

Übung 1: pgAdmin und erste Schritte im Umgang mit der Datenbank

  1. Öffnen Sie pgAdmin über Start -> Geospatial -> Datenbank -> pgAdmin4 (Passwort: user)
  2. Stellen Sie eine Verbindung zur Datenbank natural_earth2 her
  3. Gehen Sie in das Schema public und schauen Sie sich die Tabellen an
  4. Öffnen Sie eine Tabelle und schauen Sie sich die Geometriespalte (geom) an. Können Sie die Geometrie lesen?
  5. Öffnen Sie die Tabelle spatial_ref_sys und filtern Sie nach srid = 4326
  6. Gehen Sie zu public -> views (Ansichten) und öffnen Sie die Metadaten-Sicht geometry_columns

Wie erfolgt die Kommunikation mit der Datenbank?

  • über SQL - Structured Query Language
  • DDL - data definition language
  • DML - data manipulation language
  • DQL - data query language

DQL

  • DQL - zur Abfrage von Daten (DQL ist ein Teil der DML) z.B. zur Anzeige aller Daten aus der Tabelle spatial_ref_sys, bei denen srid = 4326 ist.
SELECT * FROM spatial_ref_sys WHERE srid=4326;

DDL

  • DDL zur Erzeugung von neuen Strukturen wie Datenbanken, Tabellen, Rollen, Schemata & mehr.
CREATE DATABASE demo;

  • Verbinden Sie sich mit der Datenbank demo. Frischen Sie dazu die Liste der Datenbanken auf und wählen Sie anschließend die Datenbank demo aus.

  • Laden Sie die Erweiterung postgis.

CREATE EXTENSION postgis;
  • Erzeugen Sie die Tabelle poi.
CREATE TABLE pois(
 gid serial PRIMARY KEY,
 name varchar,
 year int,
 info varchar
);

Änderungen der Tabellenstruktur

ALTER TABLE pois ADD COLUMN land varchar;
ALTER TABLE pois RENAME land TO country;
ALTER TABLE pois DROP COLUMN country;
ALTER TABLE pois ADD CONSTRAINT pk_gid PRIMARY KEY (gid);

Löschen einer Tabelle

DROP TABLE pois;

DML

  • Manipulation von Daten - Erzeugen, Löschen, Verändern von Daten
INSERT INTO pois (name, year, info) VALUES 
(
'Kölner Dom',
1248,
'https://en.wikipedia.org/wiki/Cologne_Cathedral'
);
UPDATE pois SET name = 'Cologne Cathedral' WHERE name = 'Kölner Dom';
-- Löscht Datensätze mit name "Cologne Cathedral"
DELETE FROM pois WHERE name = 'Cologne Cathedral';
-- Löscht alle Datensätze der Tabelle
DELETE FROM pois; 
-- Löscht Datensätze mit gid > 1111
DELETE FROM pois WHERE gid > 1111;

Übung 2: Erzeugen einer Datenbank mit PostGIS-Erweiterung

  1. Legen Sie eine Datenbank mit dem Namen fossgis an

Hinweis: Nutzen Sie Kleinbuchstaben und keine Leerzeichen für den Namen von Datenbanken, Tabellen und Spalten! Dies erleichtert den Umgang, da Sie dann keine Anführungszeichen (") nutzen müssen (bei Großschreibweise "FOSSGIS")

  1. Verbinden Sie sich mit Ihrer Datenbank

  2. Laden Sie die Erweiterung postgis.

  3. Prüfen Sie, ob die PostGIS-Funktionen, die Tabelle spatial_ref_sys und die Metadaten-Sichten vorliegen

CREATE DATABASE fossgis;

Wechseln zur Datenbank fossgis

CREATE EXTENSION postgis;

Übung 3: Datenbankhilfsprogramme. Nutzung von createdb zum Anlegen einer Datenbank

  • Prüfen Sie das PostGIS-Quickstart und finden Sie heraus, wie die Datenbank demo angelegt wurde
  • https://live.osgeo.org/de/quickstart/postgis_quickstart.html
  • PostgreSQL stellt diverse Hilfsprogramme bereit - wie createdb, dropdb, createuser, vacuumdb, pg_dump, pg_restore & mehr. Der Aufruf erfolgt über die Kommandozeile
createdb -U user -e demo
createdb --help

psql -U user demo
CREATE EXTENSION postgis;
\q

dropdb -U user demo

Übung 4: Erzeugen und befüllen Sie die Tabelle cities

CREATE TABLE cities(
 gid serial PRIMARY KEY,
 name varchar,
 country varchar,
 geom geometry(point,4326)
);
INSERT INTO cities(
            name, geom, country)
    VALUES ('Firenze',ST_SetSRID(ST_MakePoint(11.256944,43.773056),4326),'Italy');
INSERT INTO cities(
            name, geom, country)
    VALUES ('Buenos Aires',ST_SetSRID(ST_MakePoint(-58.394002,-34.581619),4326),'Argentina');
INSERT INTO cities(
            name, geom, country)
    VALUES ('Bucharest',ST_SetSRID(ST_MakePoint(26.096306 , 44.439663),4326),'Romania');
INSERT INTO cities(
            name, geom, country)
    VALUES ('Dar es Salaam',ST_SetSrid(ST_MakePoint(39.273933, -6.812810),4326),'Tanzania');
INSERT INTO cities(
            name, geom, country)
    VALUES ('Cologne',ST_SetSRID(ST_MakePoint(6.958307 , 50.941357),4326),'Germany');
INSERT INTO cities(
            name, geom, country)
    VALUES ('Berlin',ST_SetSrid(ST_MakePoint(13.41053 , 52.52437),4326),'Germany');

Well-Known Text Format (WKT) und Well-Known Binary Format (WKB)

Die Geometrien werden intern im Well-Known Binary Format (WKB) gespeichert. Eine lesbare Ausgabe ist über das Well-Known Text Format (WKT) möglich.

http://postgis.net/docs/using_postgis_dbmanagement.html#OpenGISWKBWKT

ST_AsEWKT oder ST_AsText zur Anzeige der Geometrie als Text

SELECT ST_AsText(geom), geom FROM cities; -- mit SRID
SELECT ST_AsEWKT(geom), geom FROM cities; -- ohne SRID

QGIS zur Anzeige der Daten

  • Sie können Ihre Daten mit QGIS anzeigen, bearbeiten und Daten nach PostgreSQL importieren oder exportieren
  • Sie benötigen die Verbindungsparameter für den Zugriff auf die Datenbank - nur berechtigte Benutzer können sich mit den Daten verbinden

Übung 5: QGIS: Anzeige von Daten aus der Datenbank natural_earth2 und fossgis

  1. Öffnen Sie QGIS (Geospatial -> DesktopGIS -> QGIS) und erstellen Sie ein neues QGIS-Projekt Laden Sie die Ländergrenzen, Bundesländer, städtische Bereiche (urban areas) und Ortschaften (populated places) aus der Datenbank natural_earth2
  2. Legen Sie eine neue PostGIS-Verbindung für Ihre neue Datenbank fossgis an
  3. Laden Sie die neuen Tabelle cities
  4. Fügen Sie über die QGIS Digitalisierung einen neuen Punkt in Ihre Tabelle cities für Ihren Wohnort ein.

QGIS: Import von Daten nach PostgreSQL über die QGIS DB-Verwaltung

Die QGIS DB-Verwaltung bietet eine komfortable Möglichkeit zum Import und Export von Daten. Sie finden die QGIS DB-Verwaltung im Menü unter Databanken -> DB-Verwaltung. Sie benötigen einen Datenbankverbindung für den Zugriff auf die Daten.

Laden Sie die Daten, die Sie laden möchten, am Besten in ein QGIS-Projekt. Die Daten können auch gefiltert werden, so dass Sie auch nur einen Auszug der Daten in die Datenbank laden können.

Der Import erfolgt über die folgenden Schritte:

  1. Öffnen Sie die QGIS DB-Verwaltung
  2. Verbinden Sie sich mit Ihrer Datenbank
  3. Wählen Sie den Import-Button
  4. Wählen Sie die Daten für den Import aus
  5. Definieren Sie einen Namen für die Tabelle, den EPSG-Code und fügen Sie einen Primärschlüssel hinzu
  6. Erzeugen Sie einen räumlichen Index
  7. Starten Sie den Import
  8. Fügen Sie die Daten nach dem Import per drag & drop in Ihr QGIS-Projekt

Übung 6: Laden von natural_earth2-Shapes in Ihre Datenbank

  • Importieren Sie die Datei ne_10m_admin_0_countries.shp in die Tabelle ne_10m_admin_0_countries
  • Importieren Sie die Datei ne_10m_admin_1_states_provinces.shp in die Tabelle ne_10m_admin_1_states_provinces
  • Importieren Sie lediglich die Bundesländer von Deutschland in die Tabelle provinces_brd (nutzen Sie den Filter admin='Germany')
  • Importieren Sie die Shape-Datei ne_10m_populated_places.shp in die Tabelle ne_10m_populated_places
  • Schauen Sie sich die Metadaten-Sicht geometry_columns an

QGIS: Erstellen von Tabellen via QGIS

Neue Tabellen können ganz einfach auch in QGIS erstellt werden. Dies erfolgt über die DB-Verwaltung unter dem Menüpunkt Tabelle -> Tabelle erzeugen.

Übung 7: Erstellen der Tabelle standorte via QGIS

  • Erstellen Sie in der Datenbank fossgis via QGIS eine einfache Tabelle locations für die Austragungsort der FOSSGIS. Die Tabelle benötigt eine eindeutige ID, eine Spalte für den Ort, das Land und das Jahr und eine Geometriespate (POINT, SRID 4326). Erzeugen Sie direkt bei der Erstellung der Tabelle einen räumlichen Index.
  • Erfassen Sie einen Punkt für die FOSSGIS 2023 in Berlin.
  • Schauen Sie sich die Tabelle anschließend in pgAdmin an.

Hinweis: Über QGIS können sehr einfach auch ohne SQL-Kenntnisse Tabellen in der Datenbank erstellt werden.

PostGIS-Funktionen in Aktion

ST_AsEWKT oder ST_AsText zur Anzeige der Geometrie als Text

SELECT ST_AsText(geom) FROM cities; -- mit SRID
SELECT ST_AsEWKT(geom) FROM cities; -- ohne SRID
SELECT ST_AsEWKT(geom) FROM provinces_brd;

Funktionen zur Geometriegenerierung (Geometry Constructors)

ST_GeomFromText - kann für unterschiedliche Geometrietypen verwendet werden

Update cities 
 set geom = ST_GeomFromText('POINT(6.958307 50.941357)',4326) 
 WHERE name = 'Cologne';
Update ne_10m_admin_0_countries 
set geom = ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))',4326) 
WHERE name = 'United Kingdom';

Räumliche Beziehungen und Berechnungen

Ausgabe von Informationen über Ihre Daten wie z.B. Distanz, Fläche, Länge, Mittelpunkt.

Übung 8: Berechnen Sie die Fläche für jedes Land

  • http://postgis.net/docs/ST_Area.html
  • Achtung: Beachten Sie, dass zur Berechnung der Fläche die Einheit der verwendeten Projektion genutzt wird (Bei den Natural Earth II Daten ist dies EPSG 4326 also Grad) Verwenden Sie daher für die Berechnung den Spheroid, um sinnvolle Ergebnisse zu erhalten.

Ohne Verwendung des Spheroids (Ausgabe in den Einheiten des EPSG-Codes)

SELECT gid, name, st_Area(geom)
  FROM public.ne_10m_admin_0_countries;

Berechnung mit Spheroid (Ergebnis in Quadratmetern)

SELECT gid, name, st_Area(geom, true) as flaeche
  FROM public.ne_10m_admin_0_countries;

Ausgabe von Deutschland, Österreich und Schweiz sortiert nach Größe

SELECT gid, name, st_Area(geom, true) as flaeche
  FROM public.ne_10m_admin_0_countries
  WHERE name IN ('Germany','Austria','Switzerland') 
  ORDER BY flaeche DESC;

Übung 9: Erzeugen Sie eine Sicht, die den Mittelpunkt jedes Landes ausgibt

  • Erzeugen Sie eine Sicht, die den Mittelpunkt jedes Landes ausgibt
  • Laden Sie die Daten in QGIS
  • Schauen Sie sich die Sicht geometry_columns an. Welcher Geometrietyp und welche Projektion werden angegeben?
CREATE VIEW qry_country_centroid AS
SELECT gid, name, st_centroid(geom)
  FROM public.ne_10m_admin_0_countries;

Erzeugen Sie die Sicht erneut und weisen dabei der Geometrie den Typ POINT und den EPSG-Code 4326 zu (Stichwort typecast). Schauen Sie sich die Daten in QGIS an. Wo liegt der Mittelpunkt von Frankreich (France)?

Drop view qry_country_centroid;
CREATE VIEW qry_country_centroid AS
SELECT gid, name, st_centroid(geom)::geometry(point,4326) as geom
  FROM public.ne_10m_admin_0_countries;

Nutzen Sie die Funktion ST_PointOnSurface

CREATE VIEW qry_country_pointonsurface AS
SELECT gid, name, st_pointonsurface(geom)::geometry(point,4326) as geom
  FROM public.ne_10m_admin_0_countries;

Übung 10: Distanzberechnung

  • Gehen Sie zurück zur Tabelle cities aus Übung 4. Berechnen Sie die Entfernung von Ihrem Wohnort nach Berlin.
  • Nutzen Sie dabei den Spheroid für die Berechnung (Nutzung des Typs geography)
  • https://postgis.net/docs/ST_Distance.html
SELECT g.name, myhome.name, ST_Distance(g.geom, myhome.geom, true) 
FROM cities g, 
cities myhome 
WHERE 
g.name = 'Berlin' 
AND myhome.name='Cologne';
  • Frage: Wer hatte die weiteste und wer die kürzeste Anreise?

Räumlicher Index

  • Ihre Geometriespalte sollte einen räumlichen Index aufweisen - dieser beschleunigt räumliche Abfragen
  • Der räumliche Index speichert zu jeder Geometrie die BoundingBox
CREATE INDEX gist_cities_geom
 ON cities 
 USING GIST (geom);

Geometrieprozessierung

Übung 11: Puffern Sie die Tabelle populated places mit 10 km

  • Puffern Sie die Tabelle ne_10m_populated_places mit 10 km
  • http://postgis.net/docs/ST_Buffer.html
  • Beachten Sie, dass Sie den Typ geography nutzen müssen, um einen Puffer in Metern zu erzeugen (nutzen Sie typecast ::geography)
CREATE TABLE places_buffer_10_km as
SELECT 
  gid, 
  name, 
  ST_Buffer(geom::geography, 10000)::geometry(polygon,4326) as geom 
  FROM public.ne_10m_populated_places;
SELECT a.* 
  FROM places_buffer_10_km a, places_buffer_10_km b
  WHERE a.geom && b.geom 
  AND ST_Intersects(a.geom, b.geom) 
  AND a.gid != b.gid;
CREATE INDEX gist_places_buffer_10_km_geom
  ON places_buffer_10_km 
  USING GIST (geom);

Führen Sie die Abfrage erneut aus und prüfen Sie, ob der Index verwendet wird.

SELECT a.* 
  FROM places_buffer_10_km a, places_buffer_10_km b
  WHERE
  ST_Intersects(a.geom, b.geom) 
  AND a.gid != b.gid;
EXPLAIN ANALYZE
SELECT a.* 
  FROM places_buffer_10_km a, places_buffer_10_km b
  WHERE
  ST_Intersects(a.geom, b.geom) 
  AND a.gid != b.gid;

Übung 12: ST_UNION - Vereinigen Sie alle Provinzen von Italien zu einer Fläche

  • Erzeugen Sie eine Sicht qry_italy_union
  • Nutzen Sie ST_UNION http://postgis.net/docs/ST_Union.html
  • Nutzen Sie die Tabelle _ne_10m_admin_1_states_provinces und filtern Sie nach admin Italy (admin='Italy')
  • Fügen Sie die Spalte admin in Ihre Sicht ein - Sie müssen GROUP BY verwenden
  • Wenden Sie typecast auf die Geometriespalte an
  • Schauen Sie sich das Ergebnis in QGIS an

Version 1: Vereinigung der Bundesländer von Italien über ST_UNION

SELECT ST_UNION(geom)
  FROM public.ne_10m_admin_1_states_provinces 
  WHERE admin='Italy';

Version 2: Ausgabe der Geometrie als Text

SELECT ROW_NUMBER() OVER() as gid, 
  admin, 
  st_AsText(ST_UNION(geom))
  FROM public.ne_10m_admin_1_states_provinces 
  WHERE admin='Italy'
  GROUP BY admin ;

Version 3: Typcast, sinnvolle Benennung der Geometriespalte und zusätzliche Ausgabe der Spalte admin

CREATE VIEW qry_italy_union AS
SELECT 1 as gid, 
  admin, 
  ST_Multi(ST_UNION(geom))::geometry(multipolygon,4326) as geom
  FROM public.ne_10m_admin_1_states_provinces 
  WHERE admin='Italy'
  GROUP BY admin ;

ST_Subdivide

  • Unterteilt Multi-/Polygone in viele kleinere Polygone
  • Definition der maximalen Stützpunkte (Standardwert ist 256, kann nicht < 8 sein). Die Polygone haben nicht mehr Stützpunkte als max_vertices
  • http://postgis.net/docs/manual-2.4/ST_Subdivide.html
  • Neu ab PostGIS 2.3.0

CREATE TABLE provinces_subdivided AS 
  SELECT 
  name, 
  admin, 
  st_subdivide(geom) AS geom
  FROM ne_10m_admin_1_states_provinces;

ALTER TABLE provinces_subdivided ADD COLUMN gid serial PRIMARY KEY;
  • Mit Definition von max_vertices 20
DROP TABLE provinces_subdivided;
CREATE TABLE provinces_subdivided AS 
  SELECT 
  name, 
  admin, 
  st_subdivide(geom,20) AS geom
  FROM ne_10m_admin_1_states_provinces ;

ALTER TABLE provinces_subdivided ADD COLUMN gid serial PRIMARY KEY;

CREATE INDEX provinces_subdivided_geom_gist
  ON provinces_subdivided
  USING gist
  (geom);

VACUUM ANALYZE provinces_subdivided;

Übung 13: ST_Subdivide

  • Manchmal macht es Sinn, große Geometrien für Berechnungen in kleinere Flächen auszuteilen
  • Diese Übung soll dies mit Hilfe einer Funktion veranschaulichen
  • Erzeugen Sie eine neue Funktion getCountrynameSubdivided() für die Tabelle provinces_subdivided
  • Schauen Sie sich EXPLAIN an, um die Performanz zu beurteilen
CREATE OR REPLACE FUNCTION getCountrynameSubdivided(mygeometry geometry) 
 RETURNS character varying 
 AS 'SELECT c.name FROM provinces_subdivided c 
 WHERE st_intersects(c.geom,$1);' 
LANGUAGE 'sql'; 
SELECT name, getCountrynameSubdivided(geom) 
 FROM public.ne_10m_populated_places 
 WHERE adm0name = 'Italy';
EXPLAIN ANALYZE
SELECT name, getCountrynameSubdivided(geom) 
 FROM public.ne_10m_populated_places 
 WHERE adm0name = 'Italy';

ALTER TABLE ne_10m_populated_places ADD COLUMN countryname varchar;

UPDATE ne_10m_populated_places 
SET countryname = getCountrynameSubdivided(geom);

Funktion, die die Originaldaten (Provinzen) nutzt

CREATE OR REPLACE FUNCTION getCountryname(mygeometry geometry) 
 RETURNS character varying 
 AS 'SELECT c.name FROM ne_10m_admin_1_states_provinces c 
 WHERE st_intersects(c.geom,$1);' 
LANGUAGE 'sql'; 
EXPLAIN ANALYZE
SELECT name, getCountryname(geom) 
 FROM public.ne_10m_populated_places;

PostgreSQL Rollen und Zugriffskontrolle

PostgreSQL unterstützt Rollen (Benutzer mit Login und Rollen ohne Login). Diese Rollen können unterschiedliche Berechtigungen haben und bekommen über GRANT den Zugriff auf die Objekte z.B. Tabellen, Sichten, Schema, Funktionen.

Übung 14: Rollen erzeugen und Rechte zuweisen

  1. Legen Sie die Rollen workshop_read und workshop_writer an
  2. Legen Sie die Login-Rolle robert mit Passwort an und fügen Sie diese zur Gruppe workshop_reader hinzu
  3. Legen Sie die Login-Rolle wilma an und fügen Sie diese zur Gruppe workshop_writer hinzu
  4. Geben Sie der Gruppe workshop_reader Leserechte auf die Tabelle ne_10m_admin_1_states_provinces
  5. Geben Sie der Gruppe workshop_writer Schreibrechte auf die Tabelle cities
  6. Testen Sie den lesenden und schreibenden Zugriff über QGIS
CREATE ROLE workshop_reader;
CREATE ROLE workshop_writer;

CREATE ROLE robert WITH LOGIN PASSWORD 'fossgis';
GRANT workshop_reader TO robert;

CREATE ROLE wilma WITH LOGIN PASSWORD 'fossgis';
GRANT workshop_writer TO wilma;

GRANT SELECT ON ne_10m_admin_1_states_provinces TO workshop_reader;
-- Wechseln Sie in die Rolle robert
Select * from ne_10m_admin_1_states_provinces;

GRANT ALL ON cities to workshop_writer;
GRANT USAGE ON SEQUENCE cities_gid_seq TO workshop_writer;

-- Wechseln Sie in die Rolle wilma im pgAdmin
-- Führen Sie das folgende SQL aus
SELECT * from cities;
UPDATE cities SET name = 'TEST' WHERE name = 'Berlin';
SELECT * from cities where name = 'TEST';

Was kommt als Nächstes?