Handbücher
Handbücher
Breadcrumbs

GGU-CONNECT - Integration mit QGIS

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

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

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:

SQL
CREATE EXTENSION IF NOT EXISTS postgis;

Installation pruefen:

SQL
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

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

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

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:

SQL
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

  1. QGIS oeffnen und ein neues oder bestehendes Projekt anlegen

  2. Im Browser-Panel: PostgreSQL > Neue Verbindung

  3. Verbindungsdaten eingeben:

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

  1. Verbindung testen und speichern

Schritt 4: Views als Layer hinzufuegen

  1. Im Browser-Panel die neue PostgreSQL-Verbindung aufklappen

  2. 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

  3. Per Doppelklick oder Drag-and-Drop als Layer hinzufuegen

  4. 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)

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)

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)

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.