-- ====================================================================================================================
-- Flurstücke im öffentlichen Besitz aus ALKIS selektieren für WMS  -  Ebenen "Stadt" und "Kreis",  GKZ-spezifisch
-- ====================================================================================================================

-- Hier: Muster für die Veröffentlichung. 
-- Der String *gkz* ist durch die Mandanten-Nummer (Gemeinde-Kenn-Ziffer) zu ersetzen.
-- Individuelle Bearbeitung für jeden Mandanten notwendig. Die Namen der vorkommenden Eigentümer müssen analysiert und angepasst werden.

-- Stand:
--  2018-11-21 Erweiterung/Aufteilung [Stadt/Kreis/Sonstige]/[Land/Bund]

-- Der WMS (aus View "oeff_flurst[gkz][s|k|l|b][e|r]") wird langsam, wenn er in einer kreisweiten Datenbank ausgeführt wird.
-- Das Filter-Feld benötigt einen Index:
--DROP INDEX if exists ax_person_nname;
CREATE INDEX ax_person_nname
  ON ax_person USING btree (nachnameoderfirma COLLATE pg_catalog."default");


-- ----------------------------------------------
-- S t a d t
-- ----------------------------------------------
 
-- Version "Eigentum"
--        DROP    VIEW oeff_flurst*gkz*se;
CREATE OR REPLACE VIEW oeff_flurst*gkz*se
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 'Stadt ___%'; -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

COMMENT ON VIEW oeff_flurst*gkz*se  IS 'Flurstücke in Öffentlichen Besitz. Hier: *S*tadt (___) *E*igentum. Für WMS: nur ID und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst*gkz*se TO ms7;


-- Version "Recht an"
--        DROP    VIEW oeff_flurst*gkz*sr;
CREATE OR REPLACE VIEW oeff_flurst*gkz*sr
AS
  SELECT  f.gml_id, di.buchungsart, -- Buchungsart der dienenden 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 'Stadt ___%'; -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

COMMENT ON VIEW oeff_flurst*gkz*sr  IS 'Flurstücke in Öffentlichen Besitz. Hier: *S*tadt (Lage) *R*echte. Für WMS: nur ID, Buchungsart und Geometrie.';
GRANT SELECT ON TABLE oeff_flurst*gkz*sr TO ms7;


-- ----------------------------------------------
-- K r e i s 
-- ----------------------------------------------

-- Version "Eigentum"
--        DROP    VIEW oeff_flurst*gkz*ke;
CREATE OR REPLACE VIEW oeff_flurst*gkz*ke
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 = 'Kreis ___'; -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **


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


-- Version "Recht an"
--        DROP    VIEW oeff_flurst*gkz*kr;
CREATE OR REPLACE VIEW oeff_flurst*gkz*kr
AS
  SELECT 
     f.gml_id,       -- UNIQUE KEY 
     s.buchungsart,  -- Buchungsart
     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 = 'Kreis ___'; -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

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

-- auch QGIS
GRANT SELECT ON TABLE oeff_flurst*gkz*kr TO balkis0*gkz*;


-- ----------------------------------------------
-- A n d e r e
-- ----------------------------------------------
 
-- Version "Eigentum"
--        DROP    VIEW oeff_flurst*gkz*ae;
CREATE OR REPLACE VIEW oeff_flurst*gkz*ae
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 'Stadtwerke%'
     OR  p.nachnameoderfirma LIKE 'Landesverband Lippe%'
     OR  p.nachnameoderfirma LIKE 'Werre-Wasserverband%'
     OR  p.nachnameoderfirma LIKE 'Landschaftsverband Westfalen Lippe%'
     OR (p.nachnameoderfirma LIKE 'Stadt %' AND NOT p.nachnameoderfirma LIKE 'Stadt ___%')  -- Nachbar-Stadt
     ) ; -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **


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

-- auch QGIS
GRANT SELECT ON TABLE oeff_flurst*gkz*ae TO balkis0*gkz*;

-- Version "Recht an"
--        DROP    VIEW oeff_flurst*gkz*ar;
CREATE OR REPLACE VIEW oeff_flurst*gkz*ar
AS
  SELECT 
     f.gml_id,       -- UNIQUE KEY 
     s.buchungsart,  -- Buchungsart
     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 'Stadtwerke%'
     OR  p.nachnameoderfirma LIKE 'Landesverband Lippe%'
     OR  p.nachnameoderfirma LIKE 'Werre-Wasserverband%'
     OR  p.nachnameoderfirma LIKE 'Landschaftsverband Westfalen Lippe%'
     OR (p.nachnameoderfirma LIKE 'Stadt %' AND NOT p.nachnameoderfirma LIKE 'Stadt ___%')
     ) ; -- ** EIGENTÜMER / ERBBAUBERECHTIGTER **

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

-- the HAPPY end --