grabfuss_berechnen.sql

/*
================================================================================
MATERIALISIERTE VIEW: Grabstellen mit intelligenter Punktplatzierung
================================================================================

ZWECK:
Diese materialisierte View erstellt für jede Grabstelle (Polygon) einen 
optimalen Textplatzierungspunkt samt Rotation für die Kartendarstellung.

HAUPTPROBLEM:
Grabstellen-Polygone liegen in verschiedenen Rotationen vor. Ein einfacher 
Zentroid führt zu uneinheitlichen Abständen zu den Polygonkanten.

LÖSUNGSANSATZ:
1. Bestimme den Startpunkt des Polygons (grabstein_fuss)
2. Verwende die Polygon-Geometrie um eine konsistente Richtung zu berechnen
3. Platziere den Punkt in fester Distanz diagonal ins Polygon hinein
4. Sonderbehandlung für Urnengräber (zentrierte Platzierung)

KOORDINATENSYSTEM: EPSG:4647 (ETRS89 / UTM zone 32N + DHHN2016 height)
================================================================================
*/

DROP MATERIALIZED VIEW IF EXISTS gisx.mvw_grabstellen_mit_punkt_und_rotation;

CREATE MATERIALIZED VIEW gisx.mvw_grabstellen_mit_punkt_und_rotation AS
WITH base AS (
    -- Basis-Daten aller Grabstellen mit gültiger Geometrie
    SELECT g.id,
           g.grabstaetten_id,
           g.geo,
           g.rotation,
           g.svg,                    -- Grabtyp (z.B. 'GUM03', 'Urne')
           g.modified_date,
           g.modified_user,
           g.grabstellen_id,
           g.hauptschluessel,
           g.unterschluessel,
           g.text_position,
           g.grabstein_fuss          -- Index des Startpunkts im Polygon-Ring
    FROM gisx.gis_grabstellen_n_polygone g
    WHERE g.geo IS NOT NULL           -- Nur Datensätze mit gültiger Geometrie
),
rings AS (
    -- Extrahiere den äußeren Ring jedes Polygons für die Punktberechnung
    SELECT b.*,
           st_exteriorring(b.geo) AS ring
    FROM base b
),
edges AS (
    -- Bestimme Start- und Folgepunkt basierend auf grabstein_fuss
    SELECT r.*,
           st_npoints(r.ring) AS npts,
           -- Startpunkt (grabstein_fuss): Referenzpunkt des Polygons
           CASE
               WHEN r.grabstein_fuss >= 1 AND r.grabstein_fuss <= st_npoints(r.ring)
               THEN st_pointn(r.ring, r.grabstein_fuss)
               ELSE NULL::geometry
           END AS p_a,
           -- Folgepunkt: Nächster Punkt im Ring (für Richtungsberechnung)
           CASE
               WHEN r.grabstein_fuss >= 1 AND r.grabstein_fuss <= (st_npoints(r.ring) - 1)
               THEN st_pointn(r.ring, r.grabstein_fuss + 1)
               WHEN r.grabstein_fuss = st_npoints(r.ring)  -- Letzter Punkt → erster Punkt
               THEN st_pointn(r.ring, 1)
               ELSE NULL::geometry
           END AS p_b
    FROM rings r
)
SELECT id,
       grabstaetten_id,
       geo,
       rotation,
       svg,
       modified_date,
       modified_user,
       grabstellen_id,
       hauptschluessel,
       unterschluessel,
       text_position,
       grabstein_fuss,
       
       /*
       ========================================================================
       INTELLIGENTE PUNKTPLATZIERUNG
       ========================================================================
       Berechnet einen optimalen Textplatzierungspunkt basierend auf der
       Polygon-Geometrie und dem Grabtyp.
       */
       CASE
           -- SONDERBEHANDLUNG: Urnengräber erhalten zentrierte Platzierung
           WHEN svg IN ('GUM03', 'Urne')
           THEN st_centroid(geo)::geometry(Point, 4647)
           
           -- STANDARDBEHANDLUNG: Geometriebasierte Platzierung für normale Gräber
           WHEN p_a IS NULL OR p_b IS NULL
           THEN NULL::geometry
           ELSE 
               /*
               BERECHNUNGSLOGIK:
               1. Berechne Richtungsvektor entlang der Polygonkante (p_a → p_b)
               2. Berechne senkrechten Vektor zur Kante (90° gedreht)
               3. Kombiniere beide Vektoren für diagonale Platzierung
               4. Teste ob der resultierende Punkt im Polygon liegt
               5. Falls nicht, drehe die senkrechte Komponente um
               
               DISTANZ: 15cm (0.15m) in jede Richtung
               */
               CASE 
                   -- TESTE ERSTE RICHTUNG: diagonal ins Polygon
                   WHEN st_contains(
                       geo,
                       st_translate(
                           p_a,
                           -- X-Komponente: 15cm entlang Kante + 15cm senkrecht (Variante 1)
                           0.15 * (st_x(p_b) - st_x(p_a)) / NULLIF(st_distance(p_a, p_b), 0) +
                           0.15 * (st_y(p_b) - st_y(p_a)) / NULLIF(st_distance(p_a, p_b), 0),
                           -- Y-Komponente: 15cm senkrecht - 15cm entlang (90° Drehung)
                           0.15 * (st_y(p_b) - st_y(p_a)) / NULLIF(st_distance(p_a, p_b), 0) -
                           0.15 * (st_x(p_b) - st_x(p_a)) / NULLIF(st_distance(p_a, p_b), 0)
                       )
                   )
                   -- ERSTE RICHTUNG ERFOLGREICH: Verwende diese Koordinaten
                   THEN st_translate(
                       p_a,
                       0.15 * (st_x(p_b) - st_x(p_a)) / NULLIF(st_distance(p_a, p_b), 0) +
                       0.15 * (st_y(p_b) - st_y(p_a)) / NULLIF(st_distance(p_a, p_b), 0),
                       0.15 * (st_y(p_b) - st_y(p_a)) / NULLIF(st_distance(p_a, p_b), 0) -
                       0.15 * (st_x(p_b) - st_x(p_a)) / NULLIF(st_distance(p_a, p_b), 0)
                   )
                   -- ERSTE RICHTUNG FEHLGESCHLAGEN: Drehe senkrechte Komponente um
                   ELSE st_translate(
                       p_a,
                       -- X-Komponente: 15cm entlang Kante - 15cm senkrecht (umgedreht)
                       0.15 * (st_x(p_b) - st_x(p_a)) / NULLIF(st_distance(p_a, p_b), 0) -
                       0.15 * (st_y(p_b) - st_y(p_a)) / NULLIF(st_distance(p_a, p_b), 0),
                       -- Y-Komponente: 15cm senkrecht + 15cm entlang (umgedreht)
                       0.15 * (st_y(p_b) - st_y(p_a)) / NULLIF(st_distance(p_a, p_b), 0) +
                       0.15 * (st_x(p_b) - st_x(p_a)) / NULLIF(st_distance(p_a, p_b), 0)
                   )
               END::geometry(Point, 4647)
       END AS punkt,
       
       /*
       ========================================================================
       ROTATIONSBERECHNUNG
       ========================================================================
       Berechnet den Winkel der Polygonkante für die Textausrichtung.
       */
       CASE
           WHEN p_a IS NULL OR p_b IS NULL
           THEN NULL::numeric
           ELSE (
               /*
               ROTATIONSLOGIK:
               1. Berechne Winkel zwischen Start- und Folgepunkt mit atan2()
               2. Konvertiere von Radiant zu Grad mit degrees()
               3. Invertiere Richtung mit -1 (für Textausrichtung)
               4. Normalisiere auf Bereich 0-360° durch Modulo-Operation
               
               ERGEBNIS: Winkel in Grad (0-360°) für Textrotation
               */
               (
                   (-1 * degrees(
                       atan2(
                           st_y(p_b) - st_y(p_a),  -- Y-Differenz zwischen den Punkten
                           st_x(p_b) - st_x(p_a)   -- X-Differenz zwischen den Punkten
                       )
                   ) + 360.0)                       -- Füge 360° hinzu für positive Werte
                   - 360.0 * floor(                -- Modulo 360° durch floor-Division
                       (-1 * degrees(
                           atan2(
                               st_y(p_b) - st_y(p_a),
                               st_x(p_b) - st_x(p_a)
                           )
                       ) + 360.0) / 360.0
                   )
               )::numeric
           )
       END AS rotation_deg
FROM edges;

/*
================================================================================
PERFORMANCE-OPTIMIERUNG: Indexe für die materialisierte View
================================================================================
*/

-- PRIMÄRSCHLÜSSEL: Eindeutige Identifikation der Datensätze
CREATE UNIQUE INDEX IF NOT EXISTS mvw_grabstellen_id_idx 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation (id);

-- RÄUMLICHE INDEXE: Für geografische Abfragen und Kartenperformance
CREATE INDEX IF NOT EXISTS mvw_grabstellen_geo_gist 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation 
    USING gist (geo);              -- Index auf Grabstellen-Polygone

CREATE INDEX IF NOT EXISTS mvw_grabstellen_punkt_gist 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation 
    USING gist (punkt);            -- Index auf berechnete Textplatzierungspunkte

-- FACHLICHE INDEXE: Für häufige Filteroperationen im Business-Kontext
CREATE INDEX IF NOT EXISTS mvw_grabstellen_grabstaetten_id_idx 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation (grabstaetten_id);  -- Friedhofs-ID

CREATE INDEX IF NOT EXISTS mvw_grabstellen_grabstellen_id_idx 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation (grabstellen_id);   -- Einzelgrab-ID

CREATE INDEX IF NOT EXISTS mvw_grabstellen_hauptschluessel_idx 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation (hauptschluessel);  -- Grab-Hauptnummer

-- KOMBINATIONSINDEX: Für häufige zusammengesetzte Abfragen
CREATE INDEX IF NOT EXISTS mvw_grabstellen_composite_idx 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation (grabstaetten_id, grabstellen_id);

-- DATUMS-INDEX: Für Filterung nach Änderungsdatum (z.B. bei Synchronisation)
CREATE INDEX IF NOT EXISTS mvw_grabstellen_modified_date_idx 
    ON gisx.mvw_grabstellen_mit_punkt_und_rotation (modified_date);

/*
================================================================================
VERWENDUNGSHINWEISE:
================================================================================

1. PERFORMANCE: Materialisierte View regelmäßig aktualisieren:
   REFRESH MATERIALIZED VIEW gisx.mvw_grabstellen_mit_punkt_und_rotation;

2. ANPASSUNGEN: Distanz der Punktplatzierung über die 0.15-Werte änderbar

3. NEUE GRABTYPEN: Urnengrab-Erkennung in der WHEN-Klause erweitern

4. KOORDINATENSYSTEM: View ist auf EPSG:4647 optimiert
================================================================================
*/