GGU-CONNECT - Integration mit QGIS

GGU-CONNECT kann mit dem freien Geoinformationssystem QGIS verbunden werden. Dadurch lassen sich Bohrungen, Bodenschichten und Grundwasserstaende direkt auf Karten visualisieren und mit anderen GIS-Daten kombinieren.

Die Integration besteht aus zwei Komponenten:

Komponente	Beschreibung

Komponente	Beschreibung
PostGIS-Datenbank-Views	SQL-Views, die GGU-CONNECT-Daten als Geometrien fuer QGIS bereitstellen (diese Seite)
QGIS Plugin	Plugin fuer den bidirektionalen Workflow: Bohrungen in STRATIG oeffnen, neue Aufschluesse erzeugen (siehe Unterseite)

Voraussetzungen

Komponente	Anforderung

Komponente	Anforderung
GGU-CONNECT	Datenbank auf PostgreSQL (nicht MS Access)
PostgreSQL	Version 12 oder hoeher
PostGIS	Erweiterung fuer raeumliche Daten
QGIS	Version 3.28 oder hoeher

Schritt 1: PostGIS-Erweiterung installieren

In pgAdmin (oder einem anderen SQL-Client) mit der PostgreSQL-Datenbank verbinden und die PostGIS-Erweiterung aktivieren:

CREATE EXTENSION IF NOT EXISTS postgis;

Installation pruefen:

SELECT PostGIS_Full_Version();

Schritt 2: QGIS-Views installieren (v4.0)

Die Views stellen GGU-CONNECT-Daten als raeumliche Layer fuer QGIS bereit. Alle Geometrien werden automatisch nach EPSG:25832 (ETRS89 / UTM Zone 32N) transformiert.

Das SQL-Skript befindet sich im GGU-CONNECT-Installationsverzeichnis:

db/postgres/postgis-createview_GGUCONNECT-QGIS_v4.0.sql

Dieses Skript in pgAdmin oder psql ausfuehren. Es erstellt automatisch drei Views und die benoetigten Indizes.

View 1: vw_qgis_borehole_summary

Ein Punkt pro Bohrung mit aggregierten Statistiken aus Bodenschichten, Grundwasser und Proben.

Spaltengruppe	Spalten

Spaltengruppe	Spalten
Kennungen	`LocationID`, `ProjectID`, `ExternalID`, `borehole_name`
Projekt	`project_name`, `project_nr`
Stammdaten	`begin_date`, `end_date`, `coordinate_system_epsg`, `height_system`
Koordinaten	`x_coordinate`, `y_coordinate`, `z_coordinate_begin`, `z_coordinate_end`
Bodenschichten	`soil_layer_count`, `soil_min_depth`, `soil_max_depth`, `soil_types_summary`, `soil_groups_summary`
Grundwasser	`groundwater_measurement_count`, `groundwater_min_depth`, `groundwater_max_depth`, `groundwater_avg_depth`
Erster GW-Stand	`earliest_groundwater_date`, `earliest_groundwater_depth`, `earliest_groundwater_nhn`
Letzter GW-Stand	`latest_groundwater_date`, `latest_groundwater_depth`, `latest_groundwater_nhn`
Proben	`sample_count`
Geometrie	`geom` (Point, EPSG:25832)

Verwendung: Uebersichtskarten, Projektfilterung, Grundwasserauswertung, Bohrkampagnen-Statistiken.

View 2: vw_qgis_soil_layers

Ein Datensatz pro Bodenschicht als vertikale 3D-Linie von Schichtoberkante bis Schichtunterkante.

Spaltengruppe	Spalten

Spaltengruppe	Spalten
Kennungen	`SoilLayerID`, `borehole_location_id`, `borehole_name`, `borehole_external_id`
Projekt	`project_name`, `project_nr`
Tiefen	`depth_from`, `depth_to`, `layer_thickness`
Klassifikation	`abbreviation_line`, `main_soil_type`, `SoilTypes`, `Additions`, `SoilGroup`, `Texture`
Geologie	`Petrography`, `Stratigraphy`, `Genesis`, `Color`
Texte	`TextA1`, `TextA2`, `TextB`, `ShortText`, `SupplementaryNote`, `LimeContent`
3D-Koordinaten	`_from_x`, `_from_y`, `_from_z`, `_to_x`, `_to_y`, `_to_z`
Geometrie	`geom` (LineString Z, EPSG:25832)

Verwendung: 3D-Visualisierung, Schichtmaechtigkeit-Analyse, Bodenarten-Kartierung, geologische Schnitte.

View 3: vw_qgis_soil_layers_points

Ein Datensatz pro Bodenschicht als 3D-Punkt in Schichtmitte. Gleiche Attribute wie vw_qgis_soil_layers, jedoch mit Punkt-Geometrie.

Spaltengruppe	Spalten

Spaltengruppe	Spalten
3D-Koordinaten	`_x`, `_y`, `_z` (in Schichtmitte)
Geometrie	`geom` (Point Z, EPSG:25832)

Verwendung: Punktbasierte Auswertungen, thematische Karten, schnelleres Rendering als Linien.

Performance-Indizes

Das SQL-Skript erstellt automatisch folgende Indizes:

CREATE INDEX IF NOT EXISTS idx_borehole_projectid ON "Borehole" ("ProjectID");
CREATE INDEX IF NOT EXISTS idx_borehole_coordinates ON "Borehole" ("XCoordinate", "YCoordinate");
CREATE INDEX IF NOT EXISTS idx_soillayer_locationid ON "SoilLayer" ("LocationID");
CREATE INDEX IF NOT EXISTS idx_groundwaterlevel_locationid ON "GroundwaterLevel" ("LocationID");
CREATE INDEX IF NOT EXISTS idx_groundwaterlevel_date ON "GroundwaterLevel" ("GroundwaterLevelDate");

Schritt 3: QGIS mit der Datenbank verbinden

QGIS oeffnen und ein neues oder bestehendes Projekt anlegen
Im Browser-Panel: PostgreSQL > Neue Verbindung
Verbindungsdaten eingeben:

Feld	Wert

Feld	Wert
Name	z.B. `GGU-CONNECT Produktion`
Host	Hostname des PostgreSQL-Servers
Port	`5432` (Standard)
Datenbank	Name der GGU-CONNECT-Datenbank
Authentifizierung	Benutzername und Passwort

Verbindung testen und speichern

Schritt 4: Views als Layer hinzufuegen

Im Browser-Panel die neue PostgreSQL-Verbindung aufklappen
Unter public die gewuenschten Views auswaehlen:
- vw_qgis_borehole_summary -- fuer Bohrungsuebersicht
- vw_qgis_soil_layers -- fuer Bodenschichten als 3D-Linien
- vw_qgis_soil_layers_points -- fuer Bodenschichten als Punkte
Per Doppelklick oder Drag-and-Drop als Layer hinzufuegen
Die Aufschluesse werden im Koordinatensystem EPSG:25832 angezeigt

Tipps fuer die Darstellung

Bohrungsuebersicht: Nach project_name oder soil_max_depth einfaerben
Bodenschichten: Nach main_soil_type oder SoilGroup kategorisieren
Grundwasser: Aus vw_qgis_borehole_summary die Spalten groundwater_avg_depth oder latest_groundwater_nhn fuer Interpolation verwenden

QGIS Plugin (optional)

Fuer erweiterte Funktionen steht das GGU QGIS Plugin zur Verfuegung:

Bohrungen direkt in GGU-STRATIG oeffnen
Neue Bohrungen, Druck- und Rammsondierungen aus Planungspunkten erzeugen

Siehe Unterseite: GGU-CONNECT - QGIS Plugin

SQL-Referenz

Nachfolgend das vollstaendige SQL der drei Views als Referenz. Fuer die Installation wird empfohlen, das mitgelieferte SQL-Skript zu verwenden (siehe Schritt 2).

vw_qgis_borehole_summary (SQL)

CREATE OR REPLACE VIEW vw_qgis_borehole_summary AS
SELECT
    b."LocationID",
    b."ProjectID",
    b."ExternalID",
    b."BoreholeName" AS borehole_name,
    p."ProjectName" AS project_name,
    p."ProjectNo" AS project_nr,
    b."DateBegin" AS begin_date,
    b."DateEnd" AS end_date,
    b."CoordinateSystemEPSGCode" AS coordinate_system_epsg,
    b."XCoordinate" AS x_coordinate,
    b."YCoordinate" AS y_coordinate,
    b."ZCoordinateBegin" AS z_coordinate_begin,
    b."ZCoordinateEnd" AS z_coordinate_end,
    b."HeightSystem" AS height_system,
    b."HeightQuery" AS height_query,
    b."BoreholeType",
    COUNT(DISTINCT sl."SoilLayerID") AS soil_layer_count,
    MIN(sl."DepthFrom") AS soil_min_depth,
    MAX(sl."DepthTo") AS soil_max_depth,
    STRING_AGG(DISTINCT sl."SoilTypes", ', ' ORDER BY sl."SoilTypes") AS soil_types_summary,
    STRING_AGG(DISTINCT sl."SoilGroup", ', ' ORDER BY sl."SoilGroup") AS soil_groups_summary,
    COUNT(DISTINCT gl."GroundwaterLevelID") AS groundwater_measurement_count,
    MIN(gl."Depth") AS groundwater_min_depth,
    MAX(gl."Depth") AS groundwater_max_depth,
    AVG(gl."Depth")::NUMERIC(10,2) AS groundwater_avg_depth,
    (SELECT gl2."GroundwaterLevelDate"
     FROM "GroundwaterLevel" gl2
     WHERE gl2."LocationID" = b."LocationID"
       AND gl2."GroundwaterLevelDate" IS NOT NULL
     ORDER BY gl2."GroundwaterLevelDate" ASC
     LIMIT 1) AS earliest_groundwater_date,
    (SELECT gl2."Depth"
     FROM "GroundwaterLevel" gl2
     WHERE gl2."LocationID" = b."LocationID"
       AND gl2."GroundwaterLevelDate" IS NOT NULL
     ORDER BY gl2."GroundwaterLevelDate" ASC
     LIMIT 1) AS earliest_groundwater_depth,
    (SELECT (b."ZCoordinateBegin" - gl2."Depth")
     FROM "GroundwaterLevel" gl2
     WHERE gl2."LocationID" = b."LocationID"
       AND gl2."GroundwaterLevelDate" IS NOT NULL
       AND b."ZCoordinateBegin" IS NOT NULL
     ORDER BY gl2."GroundwaterLevelDate" ASC
     LIMIT 1) AS earliest_groundwater_nhn,
    (SELECT gl2."GroundwaterLevelDate"
     FROM "GroundwaterLevel" gl2
     WHERE gl2."LocationID" = b."LocationID"
       AND gl2."GroundwaterLevelDate" IS NOT NULL
     ORDER BY gl2."GroundwaterLevelDate" DESC
     LIMIT 1) AS latest_groundwater_date,
    (SELECT gl2."Depth"
     FROM "GroundwaterLevel" gl2
     WHERE gl2."LocationID" = b."LocationID"
       AND gl2."GroundwaterLevelDate" IS NOT NULL
     ORDER BY gl2."GroundwaterLevelDate" DESC
     LIMIT 1) AS latest_groundwater_depth,
    (SELECT (b."ZCoordinateBegin" - gl2."Depth")
     FROM "GroundwaterLevel" gl2
     WHERE gl2."LocationID" = b."LocationID"
       AND gl2."GroundwaterLevelDate" IS NOT NULL
       AND b."ZCoordinateBegin" IS NOT NULL
     ORDER BY gl2."GroundwaterLevelDate" DESC
     LIMIT 1) AS latest_groundwater_nhn,
    COUNT(DISTINCT s."SampleID") AS sample_count,
    ST_Transform(
        CASE
            WHEN b."CoordinateSystemEPSGCode" = 4326
                THEN ST_SetSRID(ST_MakePoint(b."YCoordinate", b."XCoordinate"), 4326)
            ELSE ST_SetSRID(ST_MakePoint(b."XCoordinate", b."YCoordinate"), b."CoordinateSystemEPSGCode")
        END,
        25832
    ) AS geom
FROM "Borehole" b
LEFT JOIN "Project" p ON b."ProjectID" = p."ProjectID"
LEFT JOIN "SoilLayer" sl ON b."LocationID" = sl."LocationID"
LEFT JOIN "GroundwaterLevel" gl ON b."LocationID" = gl."LocationID"
LEFT JOIN "Sample" s ON b."LocationID" = s."LocationID"
WHERE b."XCoordinate" IS NOT NULL
  AND b."YCoordinate" IS NOT NULL
  AND b."CoordinateSystemEPSGCode" IS NOT NULL
GROUP BY
    b."LocationID", b."ProjectID", b."ExternalID", b."BoreholeName",
    b."DateBegin", b."DateEnd", b."CoordinateSystemEPSGCode",
    b."XCoordinate", b."YCoordinate", b."ZCoordinateBegin", b."ZCoordinateEnd",
    b."HeightSystem", b."HeightQuery", b."BoreholeType",
    p."ProjectName", p."ProjectNo";

vw_qgis_soil_layers (SQL)

CREATE VIEW vw_qgis_soil_layers AS
WITH base_coords AS (
    SELECT
        b."LocationID",
        b."ZCoordinateBegin",
        b."CoordinateSystemEPSGCode",
        CASE
            WHEN b."CoordinateSystemEPSGCode" = 4326 THEN b."YCoordinate"
            ELSE b."XCoordinate"
        END AS src_x,
        CASE
            WHEN b."CoordinateSystemEPSGCode" = 4326 THEN b."XCoordinate"
            ELSE b."YCoordinate"
        END AS src_y
    FROM "Borehole" b
    WHERE b."XCoordinate" IS NOT NULL
      AND b."YCoordinate" IS NOT NULL
      AND b."CoordinateSystemEPSGCode" IS NOT NULL
),
transformed_point AS (
    SELECT
        bc."LocationID",
        bc."ZCoordinateBegin",
        bc.src_x,
        bc.src_y,
        bc."CoordinateSystemEPSGCode",
        ST_Transform(
            ST_SetSRID(ST_MakePoint(bc.src_x, bc.src_y), bc."CoordinateSystemEPSGCode"),
            25832
        ) AS geom_2d
    FROM base_coords bc
)
SELECT
    sl."SoilLayerID",
    sl."LocationID" AS borehole_location_id,
    b."BoreholeName" AS borehole_name,
    b."ExternalID" AS borehole_external_id,
    p."ProjectName" AS project_name,
    p."ProjectNo" AS project_nr,
    sl."DepthFrom" AS depth_from,
    sl."DepthTo" AS depth_to,
    sl."AbbreviationLine" AS abbreviation_line,
    ST_X(tp.geom_2d) AS "_from_x",
    ST_Y(tp.geom_2d) AS "_from_y",
    (COALESCE(tp."ZCoordinateBegin", 0) - COALESCE(sl."DepthFrom", 0)) AS "_from_z",
    ST_X(tp.geom_2d) AS "_to_x",
    ST_Y(tp.geom_2d) AS "_to_y",
    (COALESCE(tp."ZCoordinateBegin", 0) - COALESCE(sl."DepthTo", 0)) AS "_to_z",
    CASE
        WHEN sl."SoilTypes" IS NOT NULL AND sl."SoilTypes" != ''
        THEN NULLIF(TRIM(SPLIT_PART(sl."SoilTypes", ';', 1)), '')::INTEGER
        ELSE NULL
    END AS main_soil_type,
    sl."LayerCounter",
    sl."Petrography",
    sl."Stratigraphy",
    sl."Genesis",
    sl."Color",
    sl."SoilTypes",
    sl."Additions",
    sl."SoilGroup",
    sl."Texture",
    sl."TextA1",
    sl."TextA2",
    sl."TextB",
    sl."ShortText",
    sl."SupplementaryNote",
    sl."LimeContent",
    (sl."DepthTo" - sl."DepthFrom") AS layer_thickness,
    ST_Transform(
        ST_SetSRID(
            ST_MakeLine(
                ST_MakePoint(
                    tp.src_x, tp.src_y,
                    COALESCE(tp."ZCoordinateBegin", 0) - COALESCE(sl."DepthFrom", 0)
                ),
                ST_MakePoint(
                    tp.src_x, tp.src_y,
                    COALESCE(tp."ZCoordinateBegin", 0) - COALESCE(sl."DepthTo", 0)
                )
            ),
            tp."CoordinateSystemEPSGCode"
        ),
        25832
    ) AS geom
FROM "SoilLayer" sl
INNER JOIN "Borehole" b ON sl."LocationID" = b."LocationID"
INNER JOIN transformed_point tp ON b."LocationID" = tp."LocationID"
LEFT JOIN "Project" p ON b."ProjectID" = p."ProjectID"
WHERE sl."DepthFrom" IS NOT NULL
  AND sl."DepthTo" IS NOT NULL
ORDER BY b."BoreholeName", sl."DepthFrom";

vw_qgis_soil_layers_points (SQL)

CREATE VIEW vw_qgis_soil_layers_points AS
WITH base_coords AS (
    SELECT
        b."LocationID",
        b."ZCoordinateBegin",
        b."CoordinateSystemEPSGCode",
        CASE
            WHEN b."CoordinateSystemEPSGCode" = 4326 THEN b."YCoordinate"
            ELSE b."XCoordinate"
        END AS src_x,
        CASE
            WHEN b."CoordinateSystemEPSGCode" = 4326 THEN b."XCoordinate"
            ELSE b."YCoordinate"
        END AS src_y
    FROM "Borehole" b
    WHERE b."XCoordinate" IS NOT NULL
      AND b."YCoordinate" IS NOT NULL
      AND b."CoordinateSystemEPSGCode" IS NOT NULL
),
transformed_point AS (
    SELECT
        bc."LocationID",
        bc."ZCoordinateBegin",
        bc.src_x,
        bc.src_y,
        bc."CoordinateSystemEPSGCode",
        ST_Transform(
            ST_SetSRID(ST_MakePoint(bc.src_x, bc.src_y), bc."CoordinateSystemEPSGCode"),
            25832
        ) AS geom_2d
    FROM base_coords bc
)
SELECT
    sl."SoilLayerID",
    sl."LocationID" AS borehole_location_id,
    b."BoreholeName" AS borehole_name,
    b."ExternalID" AS borehole_external_id,
    p."ProjectName" AS project_name,
    p."ProjectNo" AS project_nr,
    sl."DepthFrom" AS depth_from,
    sl."DepthTo" AS depth_to,
    sl."AbbreviationLine" AS abbreviation_line,
    ST_X(tp.geom_2d) AS "_x",
    ST_Y(tp.geom_2d) AS "_y",
    (COALESCE(tp."ZCoordinateBegin", 0) - ((COALESCE(sl."DepthFrom", 0) + COALESCE(sl."DepthTo", 0)) / 2)) AS "_z",
    CASE
        WHEN sl."SoilTypes" IS NOT NULL AND sl."SoilTypes" != ''
        THEN NULLIF(TRIM(SPLIT_PART(sl."SoilTypes", ';', 1)), '')::INTEGER
        ELSE NULL
    END AS main_soil_type,
    sl."LayerCounter",
    sl."Petrography",
    sl."Stratigraphy",
    sl."Genesis",
    sl."Color",
    sl."SoilTypes",
    sl."Additions",
    sl."SoilGroup",
    sl."Texture",
    sl."TextA1",
    sl."TextA2",
    sl."TextB",
    sl."ShortText",
    sl."SupplementaryNote",
    sl."LimeContent",
    (sl."DepthTo" - sl."DepthFrom") AS layer_thickness,
    ST_Transform(
        ST_SetSRID(
            ST_MakePoint(
                tp.src_x, tp.src_y,
                COALESCE(tp."ZCoordinateBegin", 0) - ((COALESCE(sl."DepthFrom", 0) + COALESCE(sl."DepthTo", 0)) / 2)
            ),
            tp."CoordinateSystemEPSGCode"
        ),
        25832
    ) AS geom
FROM "SoilLayer" sl
INNER JOIN "Borehole" b ON sl."LocationID" = b."LocationID"
INNER JOIN transformed_point tp ON b."LocationID" = tp."LocationID"
LEFT JOIN "Project" p ON b."ProjectID" = p."ProjectID"
WHERE sl."DepthFrom" IS NOT NULL
  AND sl."DepthTo" IS NOT NULL
ORDER BY b."BoreholeName", sl."DepthFrom";

Views v4.0 -- kompatibel mit GGU-CONNECT Datenbankschema v1.7+. Bei Fragen bitte an support@ggu-software.com wenden.