
-- ===================================================================================
--  ALKIS-PostGIS   Mapbender-Navigation und Buchauskunft für NorGIS-ALKIS-Importer
-- ===================================================================================

--Stand 2016-02-23

-- Aufgabe:
--
-- Die ALKIS-Navigation für Mapbender 2.7.4 arbeitet bisher mit der Datenbank-Struktur
-- aus dem "Klassischen" Import mit Aufruf des Konvertres ogr2ogr aus einen Shellscript.
--
-- Dabei wird ein PostProcessing (Nach-Konverter-Verarbeitung) durchgeführt,
-- das Tabellen und Views bereit stellt, für einen schnellen Zugriff in 
--  Mapfile, Auskunft und Navigation. 
--
-- Dies Script soll eine Datenbank, die mit dem NorGIS-ALKIS-Importer geladen wurde
-- für die Verwendung mit ALKIS-Navigation vorbereiten. 
-- Es ist jedoch eine speziell angepasste Version der NAV-PHP-Scripte notwendig. 

-- Alternative: 
--  Die bisher auch in der NAV verwendeten Tabellen "pp_flur", "pp_gemarkung", "pp_gemeinde"
--  werden auch für eine "Übersichtskarte" benötigt (Gruppe "Gebiete" im Classic-Mapfile). 
--  Um diese zu bekommen müsste dieser Teil des klassischen Post-Processing auch in der 
--  NorGIS-Version ausgeführt würde.
--  Siehe Script: "pp_gebiete.sql" (Füllen) und Teile von "pp_definition.sql" (DB-Struktur).


-- Die Beziehungen werden immer noch mit geladen, sind aber inzwischen überflüssig,
-- weil jetzt Relationen zwischen den Tabellen definiert sind. 
  TRUNCATE alkis_beziehungen;


-- Bausteine für andere Views:
-- ---------------------------

-- Ein View, der die Verbindung von Flurstück zur Buchung für zwei verschiedene Fälle herstellt.
-- Einmal die "normalen" (direkten) Buchungen.
-- Zweitens über die Rechte von Buchungsstellen an anderen Buchungsstellen.
-- Dies kann als "Mittelstück" in den anderen Views eingefügt werden.

-- Einfach/Direkt:
--   Flurstück   >istGebucht>                         (Buchungs-Stelle)
--
-- Mit "Recht an":
--   Flurstück   >istGebucht>  Buchungs-Stelle  <an<  (Buchungs-Stelle)
--                               (dienend)              (herrschend) 

--           DROP VIEW public.doppelverbindung;
CREATE OR REPLACE VIEW public.doppelverbindung
AS
  -- FS >istGebucht> Buchungstelle
  SELECT f1.gml_id              AS fsgml,    -- gml_id Flurstück
         b1.gml_id              AS bsgml,    -- gml_id Buchungs
         0                      AS ba_dien
    FROM ax_flurstueck f1
    JOIN ax_buchungsstelle b1   ON f1.istgebucht = b1.gml_id
 UNION
  -- FS >istGebucht> Buchungstelle  <an<  Buchungstelle
  SELECT f2.gml_id              AS fsgml,    -- gml_id Flurstück
         b2.gml_id              AS bsgml,    -- gml_id Buchung - (herrschendes GB)
         dien.buchungsart       AS ba_dien   -- Ein Feld aus der Zwischen-Buchung zur Fall-Unterscheidung
    FROM ax_flurstueck f2
    JOIN ax_buchungsstelle dien ON f2.istgebucht = dien.gml_id
    JOIN ax_buchungsstelle b2   ON dien.gml_id = ANY (b2.an)  -- auch "zu" ?
   WHERE dien.endet IS NULL;   -- Für das zusätzliche Verbindungselement die Historie HIER ausschließen, 
                               -- Für andere Tabellen muss dies in dem View erfolgen, der dies verwendet.

COMMENT ON VIEW public.doppelverbindung 
 IS 'ALKIS-Beziehung von Flurstück zu Buchung. UNION-Zusammenfassung des einfachen Falls mit direkter Buchung und des Falles mit Recht einer Buchungsstelle an einer anderen Buchungsstelle.
Dies ist ausschließlich gedacht zur Verwendung in anderen Views um diese einfacher zu machen.';


-- Ein View, der die Verbindung von Flurstück zur Straßentabelle für zwei verschiedene Fälle herstellt.
-- Einmal über die Lagebezeichnung MIT Hausnummer und einmal OHNE.
-- Dies kann als "Mittelstück" in den anderen Views eingefügt werden.

--           DROP VIEW public.flst_an_strasse;
CREATE OR REPLACE VIEW public.flst_an_strasse
AS
  -- Flurstück >weistAuf> ax_lagebezeichnungMIThausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
  SELECT fm.gml_id AS fsgml,
         sm.gml_id AS stgml,                 -- Filter: gml_id der Straße
         'm' AS fall                         -- Sätze unterschieden: Mit HsNr
    FROM ax_flurstueck fm                    -- Flurstück Mit
    JOIN ax_lagebezeichnungmithausnummer lm  -- Lage MIT
      ON lm.gml_id = ANY (fm.weistauf)
    JOIN ax_lagebezeichnungkatalogeintrag sm
      ON lm.land=sm.land AND lm.regierungsbezirk=sm.regierungsbezirk AND lm.kreis=sm.kreis AND lm.gemeinde=sm.gemeinde AND lm.lage=sm.lage 
   WHERE lm.endet IS NULL AND fm.endet IS NULL -- nichts Historisches
 UNION
  -- Flurstück >zeigtAuf> ax_lagebezeichnungOHNEhausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
  SELECT fo.gml_id AS fsgml,
         so.gml_id AS stgml,                 -- Filter: gml_id der Straße
         'o' AS fall                         -- Sätze unterschieden: Ohne HsNr
    FROM ax_flurstueck fo                    -- Flurstück OHNE
    JOIN ax_lagebezeichnungohnehausnummer lo -- Lage OHNE
      ON lo.gml_id = ANY (fo.zeigtauf)
    JOIN ax_lagebezeichnungkatalogeintrag so -- Straße OHNE
      ON lo.land=so.land AND lo.regierungsbezirk=so.regierungsbezirk AND lo.kreis=so.kreis AND lo.gemeinde=so.gemeinde AND lo.lage=so.lage
   WHERE lo.endet IS NULL AND fo.endet IS NULL; -- nichts Historisches

COMMENT ON VIEW public.flst_an_strasse 
 IS 'ALKIS-Beziehung von Flurstück zu Straßentabelle. UNION-Zusammenfassung der Fälle MIT und OHNE Hausnummer.';

-- Muss man noch dafür sorgen, dass Flurstück nicht doppelt vorkommt? z.B. mit DISTINCT
-- Oder müssen ggf. mehrfache FS im Programm übersprungen werden?

-- Test-Ausgabe:
--   SELECT * FROM flst_an_strasse WHERE stgml='DENW18AL000004Fl' LIMIT 40;


-- Generelle Export-Struktur "Flurstück - Buchung - Grundbuch - Person"
-- --------------------------------------------------------------------
-- Verwendet den gespeicherten View "doppelverbindung".
-- Wird benötigt im Auskunft-Modul "alkisexport.php":
-- Je nach aufrufendem Modul wird der Filter (WHERE) an anderer Stelle gesetzt (gml_id von FS, GB oder Pers.)
-- Für Filter nach "Straße" siehe die nachfolgende Sonderversion "exp_csv_str".

-- Problem / Konflikt:
-- Es kann nur eine lineare Struktur aus Spalten und Zeilen exportiert werden. 
-- Wenn nicht nur die Daten des Ausgangs-Objektes exportiert werden, sondern auch verbundene Tabellen in 
-- einer 1:N-Struktur, dann verdoppeln sich Zeileninhalte und es werden redundante Daten erzeugt. 
-- Diese Redundanzen müssen vom dem Programm gefiltert werden, das die Daten über eine Schnittstelle einliest.

-- Anwendungs-Beispiel: Abrechnung von Anliegerbeiträgen.

DROP VIEW IF EXISTS exp_csv;

CREATE OR REPLACE VIEW exp_csv
AS
 SELECT
  -- F l u r s t ü c k
    f.gml_id                             AS fsgml,       -- möglicher Filter Flurstücks-GML-ID
    f.flurstueckskennzeichen             AS fs_kennz,
    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
    f.flurnummer, f.zaehler, f.nenner, 
    f.amtlicheflaeche                    AS fs_flae,
    g.bezeichnung                        AS gemarkung,

  -- G r u n d b u c h
    gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
    gb.bezirk                            AS gb_bezirk,
    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name

  -- B u c h u n g s s t e l l e  (Grundstück)
    s.laufendenummer                     AS bu_lfd,      -- BVNR
  --s.zaehler, s.nenner,                                 -- Anteil des GB am FS, einzelne Felder
    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN über 'Recht an')
    s.buchungsart,                                       -- verschlüsselt
  --b.bezeichner                         AS bu_art,      -- Buchungsart entschlüsselt
    wb.v                                 AS bu_art,      -- Buchungsart entschlüsselt

  -- N a m e n s N u m m e r  (Normalfall mit Person)
    nn.laufendenummernachdin1421         AS nam_lfd, 
    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel

  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
  -- Aus Subquery rg
    rg.artderrechtsgemeinschaft          AS nam_adr,
    rg.v                                 AS nam_adrv,        -- Art der Rechtsgem. - Value zum Key
    rg.beschriebderrechtsgemeinschaft    AS nam_bes,

  -- P e r s o n
    p.gml_id                             AS psgml,           -- möglicher Filter Personen-GML-ID
    p.anrede,                                                -- Anrede key
    wp.v                                 AS anrv,            -- Anrede Value zum Key
    p.vorname,
    p.namensbestandteil,
    p.nachnameoderfirma,                                     -- Familienname
    p.geburtsdatum,
    --p.geburtsname, p.akademischergrad 
 
  -- A d r e s s e  der Person
    a.postleitzahlpostzustellung         AS plz,
    a.ort_post                           AS ort,             -- Anschreifenzeile 1: PLZ+Ort
    a.strasse,  a.hausnummer,                                -- Anschriftenzeile 2: Straße+HsNr
    a.bestimmungsland                    AS land

  FROM ax_flurstueck    f               -- Flurstück
  JOIN doppelverbindung d               -- beide Fälle über Union-View: direkt und über Recht von Buchung an Buchung
    ON d.fsgml = f.gml_id
  JOIN ax_gemarkung g                   -- entschlüsseln
    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer 
  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
    ON d.bsgml = s.gml_id
  JOIN ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
    ON gb.gml_id = s.istbestandteilvon
  JOIN ax_buchungsblattbezirk z 
    ON gb.land=z.land AND gb.bezirk=z.bezirk 
  JOIN ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
    ON gb.gml_id = nn.istbestandteilvon
  JOIN ax_person p                      -- NamNum >benennt> Person 
    ON p.gml_id = nn.benennt
  LEFT JOIN ax_anschrift a 
    ON a.gml_id = ANY (p.hat)
  -- E n t s c h l ü s s e l n:
  LEFT JOIN alkis_wertearten wp         -- Entschlüsseln "Person - Anrede"
    ON cast(p.anrede AS character varying)=wp.k AND wp.element='ax_person' AND wp.bezeichnung='anrede' 
  LEFT JOIN alkis_wertearten wb         -- Entschlüsseln "Buchungsart"
    ON cast(s.buchungsart AS character varying)=wb.k AND wp.element='ax_buchungsstelle' AND wp.bezeichnung='buchungsart' 

  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
  -- Noch mal "GB -> NamNum", aber dieses Mal für "Rechtsgemeinschaft".
  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
  LEFT JOIN
   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.v
       FROM ax_namensnummer r 
       JOIN ax_buchungsblatt gr
         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft) 
      LEFT JOIN alkis_wertearten wr
        ON cast(r.artderrechtsgemeinschaft AS character varying)=wr.k AND wr.element='ax_namensnummer' AND wr.bezeichnung='artderrechtsgemeinschaft' 
      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
   ON rg.gml_id = gb.gml_id  -- zum GB

  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL

  ORDER BY f.flurstueckskennzeichen, 
           gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer,
           nn.laufendenummernachdin1421;

COMMENT ON VIEW exp_csv 
 IS 'View für einen CSV-Export aus der Buchauskunft mit alkisexport.php. Generelle Struktur. Für eine bestimmte gml_id noch den Filter setzen.';


-- Variante des View "exp_csv":
-- Hier wird zusätzlich der Baustein "flst_an_strasse" verwendet.
-- Der Filter "WHERE stgml= " auf die "gml_id" von "ax_lagebezeichnungkatalogeintrag" sollte gesetzt werden
-- um alle Flurstücke zu bekommen, die an einer Straße liegen.


DROP VIEW IF EXISTS exp_csv_str;

CREATE OR REPLACE VIEW exp_csv_str
AS
 SELECT
    l.stgml,                                             -- Filter: gml_id aus "ax_lagebezeichnungkatalogeintrag"
                                                         -- UNTERSCHIED zu exp_csv)
  -- F l u r s t ü c k
    f.gml_id                             AS fsgml,       -- Gruppenwechsel für "function lage_zum_fs" in alkisexport.php
    f.flurstueckskennzeichen             AS fs_kennz,
    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
    f.flurnummer, f.zaehler, f.nenner, 
    f.amtlicheflaeche                    AS fs_flae,
    g.bezeichnung                        AS gemarkung,

  -- G r u n d b u c h
  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
    gb.bezirk                            AS gb_bezirk,
    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name

  -- B u c h u n g s s t e l l e  (Grundstück)
    s.laufendenummer                     AS bu_lfd,      -- BVNR
  --s.zaehler, s.nenner,                                 -- Anteil des GB am FS, einzelne Felder
    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN über 'Recht an')
    s.buchungsart,                                       -- verschlüsselt
    wb.v                                 AS bu_art,      -- Buchungsart entschlüsselt

  -- N a m e n s N u m m e r (Normalfall mit Person)
    nn.laufendenummernachdin1421         AS nam_lfd, 
    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel

  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
  -- Aus Subquery rg
    rg.artderrechtsgemeinschaft          AS nam_adr,
    rg.v                                 AS nam_adrv,        -- Art der Rechtsgem. - Value zum Key
    rg.beschriebderrechtsgemeinschaft    AS nam_bes,

  -- P e r s o n
  --p.gml_id                             AS psgml,           -- möglicher Filter Personen-GML-ID
    p.anrede,                                                -- Anrede key
    wp.v                                 AS anrv,            -- Anrede Value zum Key
    p.vorname,
    p.namensbestandteil,
    p.nachnameoderfirma,                                     -- Familienname
    p.geburtsdatum,
    --p.geburtsname, p.akademischergrad 
 
  -- A d r e s s e  der Person
    a.postleitzahlpostzustellung         AS plz,
    a.ort_post                           AS ort,             -- Anschreifenzeile 1: PLZ+Ort
    a.strasse,  a.hausnummer,                                -- Anschriftenzeile 2: Straße+HsNr
    a.bestimmungsland                    AS land

  FROM ax_flurstueck    f               -- Flurstück
  JOIN flst_an_strasse  l               -- Lage (hier zusätzlicher JOIN gegenüber Version "exp_csv") 
	ON l.fsgml = f.gml_id 
  JOIN doppelverbindung d               -- beide Fälle über Union-View: direkt und über Recht von Buchung an Buchung
    ON d.fsgml = f.gml_id 
  JOIN ax_gemarkung g                   -- entschlüsseln
    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer 
  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
    ON d.bsgml = s.gml_id 
  JOIN ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
    ON gb.gml_id = s.istbestandteilvon
  JOIN ax_buchungsblattbezirk z 
    ON gb.land=z.land AND gb.bezirk=z.bezirk 
  JOIN ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
    ON gb.gml_id = nn.istbestandteilvon
  JOIN ax_person p                      -- NamNum >benennt> Person 
    ON p.gml_id = nn.benennt
  LEFT JOIN ax_anschrift a 
    ON a.gml_id = ANY (p.hat)
  -- E n t s c h l ü s s e l n:
  LEFT JOIN alkis_wertearten wp         -- Entschlüsseln "Person - Anrede"
    ON cast(p.anrede AS character varying)=wp.k AND wp.element='ax_person' AND wp.bezeichnung='anrede' 
  LEFT JOIN alkis_wertearten wb         -- Entschlüsseln "Buchungsart"
    ON cast(s.buchungsart AS character varying)=wb.k AND wp.element='ax_buchungsstelle' AND wp.bezeichnung='buchungsart' 

  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
  -- Noch mal "GB -> NamNum", aber dieses Mal für "Rechtsgemeinschaft".
  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
  LEFT JOIN
   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.v
       FROM ax_namensnummer r 
       JOIN ax_buchungsblatt gr
         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft) 
      LEFT JOIN alkis_wertearten wr
        ON cast(r.artderrechtsgemeinschaft AS character varying)=wr.k AND wr.element='ax_namensnummer' AND wr.bezeichnung='artderrechtsgemeinschaft' 
      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg  -- Rechtsgemeinschaft
   ON rg.gml_id = gb.gml_id  -- zum GB

  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL

  ORDER BY f.flurstueckskennzeichen, 
           gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer,
           nn.laufendenummernachdin1421;

COMMENT ON VIEW exp_csv_str 
 IS 'View für einen CSV-Export aus der Buchauskunft mit alkisexport.php. Liefert nur Flurstücke, die eine Lagebezeichnung MIT/OHNE Hausnummer haben. Dazu noch den Filter auf GML-ID der Straßentabelle setzen.';


-- B e r e c h t i g u n g e n
-- ============================
-- Analog "grant.sql"
-- Die ALKIS-Navigation für Mapbender 2.7 und die Buchauskunft verwendet den DB-User "mb27"

-- Tabellen der ALKIS-Struktur
-- -----------------------------------------------------------------------

  GRANT SELECT ON TABLE public.ap_pto TO mb27;

  GRANT SELECT ON TABLE public.ax_anschrift TO mb27;
  GRANT SELECT ON TABLE public.ax_bauraumoderbodenordnungsrecht TO mb27;
  GRANT SELECT ON TABLE public.ax_besondereflurstuecksgrenze TO mb27;
  GRANT SELECT ON TABLE public.ax_buchungsblatt TO mb27;
  GRANT SELECT ON TABLE public.ax_buchungsblattbezirk TO mb27;
  GRANT SELECT ON TABLE public.ax_buchungsstelle TO mb27;
  GRANT SELECT ON TABLE public.ax_bundesland TO mb27;
  GRANT SELECT ON TABLE public.ax_dienststelle TO mb27;
  GRANT SELECT ON TABLE public.ax_flurstueck TO mb27;
  GRANT SELECT ON TABLE public.ax_gebaeude TO mb27;
  GRANT SELECT ON TABLE public.ax_gemarkung TO mb27;
  GRANT SELECT ON TABLE public.ax_gemarkungsteilflur TO mb27;
  GRANT SELECT ON TABLE public.ax_gemeinde TO mb27;
  GRANT SELECT ON TABLE public.ax_historischesflurstueck TO mb27;
  GRANT SELECT ON TABLE public.ax_historischesflurstueckohneraumbezug TO mb27;
  GRANT SELECT ON TABLE public.ax_kreisregion TO mb27;
  GRANT SELECT ON TABLE public.ax_lagebezeichnungkatalogeintrag TO mb27;
  GRANT SELECT ON TABLE public.ax_lagebezeichnungmithausnummer TO mb27;
  GRANT SELECT ON TABLE public.ax_lagebezeichnungmitpseudonummer TO mb27;
  GRANT SELECT ON TABLE public.ax_lagebezeichnungohnehausnummer TO mb27;
  GRANT SELECT ON TABLE public.ax_namensnummer TO mb27;
  GRANT SELECT ON TABLE public.ax_person TO mb27;
  GRANT SELECT ON TABLE public.ax_regierungsbezirk TO mb27;

-- Tabellen der norGIS-Metadaten (Ersatz für Schlüssel-Tabellen )
-- -----------------------------------------------------------------------
  GRANT SELECT ON TABLE public.alkis_elemente TO mb27;
  GRANT SELECT ON TABLE public.alkis_wertearten TO mb27;

-- Tabellen des norGIS-PostProcessing  (Ersatz Classic-Post-Processing)
-- -----------------------------------------------------------------------
  -- Dies muss nach einer Datenbank-Aktualisierung neu berechtigt werden
  GRANT SELECT ON TABLE public.gem_shl TO mb27;
  GRANT SELECT ON TABLE public.gema_shl TO mb27;
  GRANT SELECT ON TABLE public.nutz_21 TO mb27;
  GRANT SELECT ON TABLE public.nutz_shl TO mb27;

-- Berechtigung fuer Views
-- -----------------------------------------------------------------------

  -- Bausteine für andere Views
  GRANT SELECT ON TABLE public.doppelverbindung TO mb27;
  GRANT SELECT ON TABLE public.flst_an_strasse TO mb27;

  -- alkis_export als CSV
  GRANT SELECT ON TABLE public.exp_csv TO mb27;
  GRANT SELECT ON TABLE public.exp_csv_str TO mb27;

-- Ende --