-- ====================================================================================================================
-- Flurstücke im öffentlichen Besitz aus ALKIS selektieren -  Ebenen "Land" und Bund",  n i c h t  GKZ-spezifisch
-- ====================================================================================================================

-- Stand:
--  2019-09-16 Bei "Recht an" die Buchungsart der herrschenden Buchung verwenden.
--  2022-02-10 Neuer View "Anlieger"

-- Die Ebenen "Stadt" (Gemeinde), "Kreis" und "Sonstige" werden GKZ-spezifisch definiert und berechtigt. Eine *.sql-Datei je Mandant.

-- ----------------------------------------------
-- L a n d 
-- ----------------------------------------------

-- Version "Eigentum"
--        DROP    VIEW oeff_flurst_le;
CREATE OR REPLACE VIEW oeff_flurst_le
AS
  SELECT 
     f.gml_id,         -- UNIQUE KEY  
     f.wkb_geometry
  FROM ax_flurstueck     f                                    -- Flurstück
  JOIN ax_buchungsstelle s  ON f.istgebucht = s.gml_id        -- Buchungs-Stelle
  JOIN ax_buchungsblatt  gb ON gb.gml_id=s.istbestandteilvon  -- Buchung >istBestandteilVon> Blatt
  JOIN ax_namensnummer   nn ON gb.gml_id=nn.istbestandteilvon -- Blatt <istBestandteilVon< NamNum
  JOIN ax_person p          ON p.gml_id=nn.benennt            -- NamNum  >benennt> Person
 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
   AND (p.nachnameoderfirma LIKE 'Land Nord%West%' 
     OR p.nachnameoderfirma LIKE 'Landesbetrieb Straßen%'
     OR p.nachnameoderfirma LIKE 'Land Niedersachsen%'); -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

-- Land Niedersachsen Domänenverwaltung, 
-- Land Niedersachsen Staatsbäderverwaltung, 
-- Land Nordrhein-Westfalen,
-- Land Nordhein-Westfalen Landesbetrieb Straßenbau,   -- ACHTUNG SCHREIBFEHLER !!
-- Land Nordrhein-Westfalen Bau- und Liegenschaftsbetrieb NRW, 
-- Land Nordrhein-Westfalen(Fiskus), Bezirksregierung Detmold
-- Land Nordrhein-WestfalenLandesbetrieb Straßenbau, 
-- Land Nordrhein-Westfalen Landesbetrieb Straßenbau, 
-- Land Nordrhein-Westfalen, vertreten durch die Bezirksregierung Detmold, 
-- Landesbetrieb Straßenbau Nordrhein-Westfalen, 
-- Landesbetrieb Straßenbau NRW-Niederlassung Bielefeld, 

-- Aber:
-- Landesverband Lippe, 

COMMENT ON VIEW oeff_flurst_le  IS 'Flurstücke in Öffentlichen Besitz. Hier: *L*and *E*igentum. Für WMS: nur ID und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst_le TO ms7;


-- Version "Recht an"
--        DROP    VIEW oeff_flurst_lr;
CREATE OR REPLACE VIEW oeff_flurst_lr
AS
  SELECT 
     f.gml_id,       -- UNIQUE KEY 
     s.buchungsart,  -- Buchungsart der herrschenden Buchung
     f.wkb_geometry
  FROM ax_flurstueck     f                                    -- Flurstück
  JOIN ax_buchungsstelle di ON f.istgebucht = di.gml_id       -- dienende Buchung
  JOIN ax_buchungsstelle s  ON di.gml_id = ANY (s.an)         -- herrschende Buchung
  JOIN ax_buchungsblatt  gb ON gb.gml_id=s.istbestandteilvon  -- Buchung >istBestandteilVon> Blatt
  JOIN ax_namensnummer   nn ON gb.gml_id=nn.istbestandteilvon -- Blatt <istBestandteilVon< NamNum
  JOIN ax_person         p  ON p.gml_id=nn.benennt            -- NamNum  >benennt> Person
 WHERE f.endet  IS NULL
   AND di.endet IS NULL
   AND s.endet  IS NULL
   AND gb.endet IS NULL
   AND nn.endet IS NULL
   AND p.endet  IS NULL
   AND (p.nachnameoderfirma LIKE 'Land Nord%West%' 
     OR p.nachnameoderfirma LIKE 'Landesbetrieb Straßen%'
     OR p.nachnameoderfirma LIKE 'Land Niedersachsen%'); -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

COMMENT      ON VIEW  oeff_flurst_lr  IS 'Flurstücke in Öffentlichen Besitz. Hier: *L*and *R*echte. Für WMS: nur ID, Buchungsart und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst_lr  TO ms7;


-- ----------------------------------------------
-- B u n d 
-- ----------------------------------------------

-- Version "Eigentum"
--        DROP    VIEW oeff_flurst_be;
CREATE OR REPLACE VIEW oeff_flurst_be
AS
  SELECT 
     f.gml_id,         -- UNIQUE KEY  
     f.wkb_geometry
  FROM ax_flurstueck     f                                    -- Flurstück
  JOIN ax_buchungsstelle s  ON f.istgebucht = s.gml_id        -- Buchungs-Stelle
  JOIN ax_buchungsblatt  gb ON gb.gml_id=s.istbestandteilvon  -- Buchung >istBestandteilVon> Blatt
  JOIN ax_namensnummer   nn ON gb.gml_id=nn.istbestandteilvon -- Blatt <istBestandteilVon< NamNum
  JOIN ax_person p          ON p.gml_id=nn.benennt            -- NamNum  >benennt> Person
 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
   AND (p.nachnameoderfirma LIKE 'Bundesanstalt%' 
     OR p.nachnameoderfirma LIKE 'Bundesrepublik%'
     OR p.nachnameoderfirma LIKE 'Bundesvermögensamt%'); -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

-- Bundesanstalt
-- Bundesrepublik
-- Bundesvermögensamt,

COMMENT ON VIEW oeff_flurst_be  IS 'Flurstücke in Öffentlichen Besitz. Hier: *B*und *E*igentum. Für WMS: nur ID und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst_be TO ms7;


-- Version "Recht an"
--        DROP    VIEW oeff_flurst_br;
CREATE OR REPLACE VIEW oeff_flurst_br
AS
  SELECT 
     f.gml_id,       -- UNIQUE KEY 
     s.buchungsart,  -- Buchungsart der herrschenden Buchung
     f.wkb_geometry
  FROM ax_flurstueck     f                                    -- Flurstück
  JOIN ax_buchungsstelle di ON f.istgebucht = di.gml_id       -- dienende Buchung
  JOIN ax_buchungsstelle s  ON di.gml_id = ANY (s.an)         -- herrschende Buchung
  JOIN ax_buchungsblatt  gb ON gb.gml_id=s.istbestandteilvon  -- Buchung >istBestandteilVon> Blatt
  JOIN ax_namensnummer   nn ON gb.gml_id=nn.istbestandteilvon -- Blatt <istBestandteilVon< NamNum
  JOIN ax_person         p  ON p.gml_id=nn.benennt            -- NamNum  >benennt> Person
 WHERE f.endet  IS NULL
   AND di.endet IS NULL
   AND s.endet  IS NULL
   AND gb.endet IS NULL
   AND nn.endet IS NULL
   AND p.endet  IS NULL
   AND (p.nachnameoderfirma LIKE 'Bundesanstalt%' 
     OR p.nachnameoderfirma LIKE 'Bundesrepublik%'
     OR p.nachnameoderfirma LIKE 'Bundesvermögensamt%'); -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

COMMENT      ON VIEW  oeff_flurst_br  IS 'Flurstücke in Öffentlichen Besitz. Hier: *B*und *R*echte. Für WMS: nur ID, Buchungsart und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst_br  TO ms7;


-- ----------------------------------------------
-- A n l i e g e r
-- ----------------------------------------------
-- Buchstabe "n" wie Nachbarn, "a" wird bereits verwendet für "andere öff."


-- Version "Eigentum"
--        DROP    VIEW oeff_flurst_ne;
CREATE OR REPLACE VIEW oeff_flurst_ne
AS
  SELECT 
     f.gml_id, 
     f.wkb_geometry
  FROM ax_flurstueck     f                                    -- Flurstück
  JOIN ax_buchungsstelle s  ON f.istgebucht = s.gml_id        -- Buchungs-Stelle
  JOIN ax_buchungsblatt  gb ON gb.gml_id=s.istbestandteilvon  -- Buchung >istBestandteilVon> Blatt
  JOIN ax_namensnummer   nn ON gb.gml_id=nn.istbestandteilvon -- Blatt <istBestandteilVon< NamNum
  JOIN ax_person p          ON p.gml_id=nn.benennt            -- NamNum  >benennt> Person
 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
   AND (p.nachnameoderfirma LIKE 'Anlieger%'      -- Krs. HF
     OR p.nachnameoderfirma LIKE 'Die Anlieger%'  -- Krs. LIP + Mi-Lk.
   );

-- "Anlieger, die Eigentümer der Ufergrundstücke,"

COMMENT ON VIEW oeff_flurst_ne  IS 'Flurstücke (meist Bäche) im Anlieger-Eigentum. Für WMS: nur ID und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst_ne TO ms7;


/* Keine Fälle zu dieser Kombination "Anlieger"/"Recht an"

-- Version "Recht an"
--        DROP    VIEW oeff_flurst_nr;
CREATE OR REPLACE VIEW oeff_flurst_nr
AS
  SELECT 
     f.gml_id,
     s.buchungsart,  -- Buchungsart der herrschenden Buchung
     f.wkb_geometry
  FROM ax_flurstueck     f
  JOIN ax_buchungsstelle di ON f.istgebucht = di.gml_id
  JOIN ax_buchungsstelle s  ON di.gml_id = ANY (s.an)
  JOIN ax_buchungsblatt  gb ON gb.gml_id=s.istbestandteilvon
  JOIN ax_namensnummer   nn ON gb.gml_id=nn.istbestandteilvon
  JOIN ax_person         p  ON p.gml_id=nn.benennt
 WHERE f.endet  IS NULL
   AND di.endet IS NULL
   AND s.endet  IS NULL
   AND gb.endet IS NULL
   AND nn.endet IS NULL
   AND p.endet  IS NULL
   AND (p.nachnameoderfirma LIKE 'Anlieger%'      -- Krs. HF
     OR p.nachnameoderfirma LIKE 'Die Anlieger%'  -- Krs. LIP + Mi-Lk.
   );

COMMENT      ON VIEW  oeff_flurst_nr  IS 'Flurstücke (meist Bäche) im Anlieger-Eigentum Hier: *R*echt an .... Für WMS: nur ID, Buchungsart und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst_nr  TO ms7;

*/


/* Views in alle Mandanten-Datenbanken eintragen:

  for gkz in 020 050 070 120 130 140 150 200 210 240 250 260 270 290 300 320 330 340 418 430 450 ; do echo $gkz; psql -d alkis0$gkz -v alkis_schema=public -f /data/projekte/postnas/oeff_FS_Land_Bund.sql ; done 

*/

-- the HAPPY end --