source: trunk/import/pp_praesentation_sichten.sql @ 293

-- ALKIS PostNAS 0.7

-- Post Processing (pp_) Teil 3: PrÀsentationsobjekte ergÀnzen / reparieren

-- Dies Script "pp_praesentation_sichten.sql" dient der Vorbereitung der Reparatur, 
-- die mit dem Script "pp_praesentation_action.sql" durchgefÃŒhrt wird.

-- Dies Script muss beim Anlegen der Datenbank verarbeitet werden und wenn sich die Sichten verÀndert haben. 
-- Das Action-Script muss im Rahmen des Post-Processing nach jeder Konvertierung laufen.

-- Stand 
--  2013-10-16  F.J. krz: Straßennamen fehlen in den PrÀsentationsobjekten, Tabelle "ap_pto"


-- ========================================
-- Straßen-Namen und Straßen-Klassifikation
-- ========================================

-- Bei den NAS-Daten, die vom Land Niedersachsen an die Kommunen abgegeben werden,
-- tritt in zunehmenden Umfang folgendes Problem auf (Stand Oktober 2013):

-- Die Namen und Klassifikationen der Straßen fehlen in der Tabelle "ap_pto".
-- Somit fehlt das Label fÃŒr die Darstellung des Straßennamens in der Karte.
-- Position und Drehwinkel sind dort aber noch vorhanden.

-- Der Name, der in anderen Tabellen vorhanden ist, muss nach "ap_pto" kopiert werden,
-- damit die WMS-Kartendarstellung wieder komplett ist.

-- Tabelle: ap_pto
-- Spalten:
--  art:                         'strasse', 'BezKlassifizierungStrasse'
--  schriftinhalt:               leer
--  signaturnummer:              ??
--  drehwinkel und wkb_geometry: gefÃŒllt

-- Hintergrund:
-- Der Straßenname ist nach der Migration der ALKIS-Daten aus ALB und ALK zunÀchst redundant gespeichert worden.
-- Er befindet sich "im Original" in "ax_lagebezeichnungkatalogeintrag.bezeichnung".
-- FÃŒr die Darstellung im Kartenbild ist der gleiche Name oder eine Variante davon (AbkÃŒrzung, getrennt-Schreibung) 
-- auch in jedem PrÀsentationsobjekt noch einmal abgelegt.
-- Diese Redundanz fÃŒhrt bei einer Änderung des Namens zu einem erhöhten Aufwand.
-- Ziel ist es daher, die identischen Kopien des Namens-Textes in "ap_pto" nicht mehr zu fÃŒhren.
-- Dort ist nur noch dann ein Eintrag zu finden, wenn Text (oder Signaturnummer?) nicht identisch sind.

-- Signaturnummern werden auch nur noch dann erfasst, wenn mehrere Werte möglich sind.

-- In Niedersachsen wurde oder wird das Löschen der identischen Texte in ap_pto möglicherweise als Nachmigration gezielt
-- durchgefÌhrt. Die Mehrzahl der Texte ist bereits gelöscht!
-- In NRW tritt dies erst seit dem letzten Update der EQK (ibR, Stand Oktober 2013) zunÀchst bei den FÀllen auf,
-- die seit dem Update fortgefÃŒhrt wurden. Eine großflÀchige gezielte Entfernung wurde nicht bemerkt. 


-- Es soll versucht werden, dies Label durch die Zuordnung der Schrift-Position 
-- in einem FlurstÃŒck und ÃŒber dessen Lagebezeichnung zu rekonstruieren.

-- "Label"   >liegt in>   "FlurstÃŒck"    >hat Lagebezeichnung>   "Straßenname"
--           (geometrische                 (Beziehung)
--           Verschniedung)


-- Simulation der Fehlersituation in einem TEST-Bestand
-- ----------------------------------------------------

-- VORSICHT! - Nur in einer Test-Kopie ausfÌhren. Löscht Daten!

  -- UPDATE ap_pto SET schriftinhalt = NULL  WHERE art = 'Strasse';
  -- UPDATE ap_pto SET schriftinhalt = NULL  WHERE art = 'BezKlassifizierungStrasse';


-- Anzeige der leeren SÀtze
-- ---------------------------------------

-- Diese Views ermitteln, ob der Fall im vorliegenden Datenbestand vorkommt.
-- Dies ist in zunehmend in Niedersachsen der Fall.

-- In NRW-Daten wurden weniger FÀlle gefunden.

-- Dann ist jeweils auch die Signaturnummer leer. 
-- Daher sollte "signaturnummer" nicht als Filter bei der "Reparatur" verwendet werden.


-- DROP VIEW pp_praes_strassen_name_leer;
CREATE OR REPLACE VIEW pp_praes_strassen_name_leer
AS
        SELECT gml_id, schriftinhalt, signaturnummer, art, drehwinkel
          FROM ap_pto
         WHERE schriftinhalt IS NULL
           AND art = 'Strasse'
        -- AND (signaturnummer = '4107' OR signaturnummer = '8113') -- char
;
COMMENT ON VIEW pp_praes_strassen_name_leer 
  IS 'Es fehlt der Name der Straße in der PrÀsentationstabelle ap_pto.';


--DROP VIEW pp_praes_strassen_klass_leer;
CREATE OR REPLACE VIEW pp_praes_strassen_klass_leer
AS
        SELECT gml_id, schriftinhalt, signaturnummer, art, drehwinkel
          FROM ap_pto
         WHERE schriftinhalt IS NULL  -- oder '' ?
           AND art = 'BezKlassifizierungStrasse'
        -- AND (signaturnummer = '4080' OR signaturnummer = '4140' OR signaturnummer = '8115')
;
COMMENT ON VIEW pp_praes_strassen_klass_leer 
  IS 'Es fehlt die Klassifikation der Straße in der PrÀsentationstabelle ap_pto.';


-- Diese Views sollen zeigen, welche Namen in ap_pto.schriftinhalt eingesetzt werden,
-- wenn die nachfolgenden update-Befehle ausgefÃŒhrt werden.

-- Dies dient wÀhrend der Entwicklung zu manuellen Vorab-Kontrolle.
-- Wenn hier keine Zeilen ausgegeben werden, kann die AusfÃŒhrung dieses Scriptes im Workflow ausgeschaltet werden.
-- Dann sind (Bundesland-spezifisch) die Namen bereits gefÃŒllt und brauchen nicht 
-- auf diese Weise rekonstruiert werden.

-- An einem Referenzbestand (amtliche Karte?) kann/sollte kontrolliert werden, 
--  ob die richtigen Namen zugeordnet werden.


--DROP VIEW  pp_praes_strassen_name_update_vorschau;
CREATE OR REPLACE VIEW pp_praes_strassen_name_update_vorschau
AS
  SELECT f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler || '/' ||
         coalesce(cast(f.nenner as character varying), '') AS fskennz, -- FlurstÃŒckskennzeichen zur Eingabe in Navigation
         f.gml_id AS gml_fs,  -- ID des FlurstÃŒcks zum Nachsehen in der Auskunft (PHP/HTML)
      -- p.gml_id AS gml_pto,
         k.bezeichnung,                          -- der Name aus dem Katalog, der nach ap_pto kopiert wird
         st_asewkt(p.wkb_geometry) AS label_geom -- Lesbare Koordinaten: Wo liegt der Label?
  FROM   ax_lagebezeichnungkatalogeintrag  k     -- Katalog enthÀlt den Straßennamen
    JOIN ax_lagebezeichnungohnehausnummer  l     -- Diese Eintrag ist dem FlurstÃŒck als Lage o. HsNr. zugeordnet
      ON ( k.land=l.land
     AND k.regierungsbezirk=l.regierungsbezirk 
     AND k.kreis=l.kreis
     AND k.gemeinde=l.gemeinde
     AND k.lage=l.lage )
    JOIN alkis_beziehungen b ON l.gml_id = b.beziehung_zu 
    JOIN ax_flurstueck     f ON f.gml_id = b.beziehung_von                -- FlurstÃŒck ..
    JOIN ap_pto            p ON ST_Within(p.wkb_geometry, f.wkb_geometry) -- in dessen FlÀche die Label-Position liegt
   WHERE p."art" = 'Strasse'  -- Filter
     AND p.schriftinhalt      IS NULL       -- Text fehlt in ap_pto
     AND NOT (p.wkb_geometry  IS NULL)      -- hat aber eine Position in ap_pto
     AND b.beziehungsart = 'zeigtAuf'       -- Relation FlurstÃŒck - Lage o.HsNr
  -- AND k.bezeichnung = 'Unter der Treff'  -- kleiner Test in Mustermonzel Testdaten
   ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler, f.nenner;

COMMENT ON VIEW pp_praes_strassen_name_update_vorschau 
  IS 'PrÀsentationsobjekt zu Straßen. Vorschau zum Update des Namens der in ap_pto.';


-- Werden hier zu einem PTO möglicherweise mal mehrere Werte geliefert?
-- In diesen Problem-FÀllen liefert die Subquery im Update (ohne Limit 1) mehrere Zeilen fÌr ein Feld.

--DROP VIEW pp_praes_strassen_name_mehrfach;
CREATE OR REPLACE VIEW pp_praes_strassen_name_mehrfach
AS
        SELECT p.gml_id, p.advstandardmodell,
           st_asewkt(p.wkb_geometry) AS label_geom,  -- Wo liegt der Label?
       (SELECT f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler || '/' ||
        coalesce(cast(f.nenner as character varying), '') AS fskennz -- FlurstÃŒckskennzeichen zur Eingabe in Navigation
                FROM ax_flurstueck f
                WHERE ST_Within(p.wkb_geometry, f.wkb_geometry)
           ) AS flurstueck
        FROM       ap_pto  p
         WHERE     p.art = 'Strasse'  
           AND     p.schriftinhalt IS NULL
           AND NOT p.wkb_geometry  IS NULL
           AND (SELECT count(k.bezeichnung) AS anzahl_label  -- die Subquery aus dem Update
                   FROM ax_lagebezeichnungkatalogeintrag  k
                   JOIN ax_lagebezeichnungohnehausnummer  l
                                ON k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis
                                   AND k.gemeinde=l.gemeinde AND k.lage=l.lage
                   JOIN alkis_beziehungen b ON l.gml_id = b.beziehung_zu
                   JOIN ax_flurstueck f ON f.gml_id = b.beziehung_von
                  WHERE b.beziehungsart = 'zeigtAuf'
                        AND ST_Within(p.wkb_geometry, f.wkb_geometry)
                  ) > 1
        ORDER BY p.gml_id, p.advstandardmodell;

COMMENT ON VIEW pp_praes_strassen_name_mehrfach 
  IS 'PrÀsentationsobjekt zu Straßen. Zu einem PTO werden ÃŒber das FlurstÃŒck mehrere Texte gefunden.';


-- Work-Arround, bis die eindeutige Zuordnung von "ap_pto" zu "lagebezeichnung*" geklÀrt ist.  

-- Wie "pp_praes_strassen_name_mehrfach" aber nur eine Liste der gml_id liefern um
-- diese beim Update (vorlÀufig) auszuschließen.

--DROP VIEW pp_praes_strassen_name_ausnahmen;
CREATE OR REPLACE VIEW pp_praes_strassen_name_ausnahmen
AS
        SELECT p.gml_id
        FROM   ap_pto  p
         WHERE p.art = 'Strasse'  
           AND p.schriftinhalt IS NULL
           AND NOT p.wkb_geometry  IS NULL
           AND (SELECT count(k.bezeichnung) AS anzahl_label  -- die Subquery aus dem Update
                   FROM ax_lagebezeichnungkatalogeintrag  k
                   JOIN ax_lagebezeichnungohnehausnummer  l
                                ON k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis
                                   AND k.gemeinde=l.gemeinde AND k.lage=l.lage
                   JOIN alkis_beziehungen b ON l.gml_id = b.beziehung_zu
                   JOIN ax_flurstueck f ON f.gml_id = b.beziehung_von
                  WHERE b.beziehungsart = 'zeigtAuf'
                        AND ST_Within(p.wkb_geometry, f.wkb_geometry)
                  ) > 1;

COMMENT ON VIEW pp_praes_strassen_name_ausnahmen 
  IS 'PrÀsentationsobjekt zu Straßen. Zu einem PTO werden ÃŒber das FlurstÃŒck mehrere Texte gefunden. Liefert Liste der gml_is, die beim Update ausgenommen werden mÃŒssen.';


--DROP VIEW pp_praes_strassen_klass_update_vorschau;
CREATE OR REPLACE VIEW pp_praes_strassen_klass_update_vorschau
AS
  SELECT p.gml_id, -- ID des FlurstÃŒcks zum Nachsehen in der Auskunft (PHP/HTML)
         f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler || '/' ||
         coalesce(cast(f.nenner as character varying), '') AS fskennz, -- FlurstÃŒckskennzeichen zur Eingabe in Navigation
         k.bezeichnung,                          -- der Name aus dem Katalog, der nach ap_pto kopiert wird
         st_asewkt(p.wkb_geometry) AS label_geom -- Lesbare Koordinaten: Wo liegt der Label?
  FROM   ax_lagebezeichnungkatalogeintrag  k     -- Katalog enthÀlt den Straßennamen
    JOIN ax_lagebezeichnungohnehausnummer  l     -- Diese Eintrag ist dem FlurstÃŒck als Lage o. HsNr. zugeordnet
     ON (k.land=l.land
     AND k.regierungsbezirk=l.regierungsbezirk 
     AND k.kreis=l.kreis
     AND k.gemeinde=l.gemeinde
     AND k.lage=l.lage)
    JOIN alkis_beziehungen b ON l.gml_id = b.beziehung_zu 
    JOIN ax_flurstueck     f ON f.gml_id = b.beziehung_von                -- FlurstÃŒck ..
    JOIN ap_pto            p ON ST_Within(p.wkb_geometry, f.wkb_geometry) -- in dessen FlÀche die Label-Position liegt
   WHERE p."art" = 'BezKlassifizierungStrasse'  -- Filter
     AND p.schriftinhalt      IS NULL    -- Text fehlt in ap_pto
     AND NOT (p.wkb_geometry  IS NULL)   -- hat aber eine Position in ap_pto
     AND b.beziehungsart = 'zeigtAuf'    -- Relation FlurstÃŒck - Lage o.HsNr
   ORDER BY f.flurnummer, f.zaehler, f.nenner;

COMMENT ON VIEW pp_praes_strassen_klass_update_vorschau 
  IS 'PrÀsentationsobjekt zu Straßen. Vorschau zum Update der Klassifikation der in ap_pto.';


-- ENDE --