source: trunk/import/sichten.sql @ 337

Revision 337, 52.0 KB checked in by frank.jaeger, 6 years ago (diff)

Sichten zur Suche nach Fehlern aus einem Historie-Trigger

Line 
1-- =====
2-- ALKIS
3-- =====
4
5--  PostNAS 0.8
6
7--  -----------------------------------------
8--  Sichten fÃŒr Fehlersuche und Daten-Analyse
9--  -----------------------------------------
10
11--  Dieses SQL braucht nur bei Bedarf in einer PostNAS-DB verarbeitet werden.
12--  Es werden zusÀtzliche Views eingerichtet, die nur bei Fehlersuche und Analyse (vom Entwickler) benötigt werden.
13
14--  2012-04-17 flstnr_ohne_position
15--  2012-04-24 pauschal Filter 'endet IS NULL' um historische Objekte auszublenden
16--  2012-10-29 Redundanzen in Beziehungen suchen (entstehen durch replace)
17--  2013-02-20 Mehrfache Buchungsstellen zum FS suchen, dies sind Auswirkungen eines Fehlers bei Replace
18--  2013-03-05 Beschriftungen aus ap_pto auseinander sortieren, neuer View "grenzpunkt"
19--  2013-03-12 Optimierung Hausnummern, View "gebaeude_txt" (Funktion und Name)
20--  2013-04-15 UnterdrÃŒcken doppelter Darstellung in den Views 'ap_pto_stra', 'ap_pto_nam', 'ap_pto_rest'
21--  2013-04-16 Thema "BodenschÀtzung" und fehlernde Kommentare zum Views ergÀnzt.
22--             Diese Datei aufgeteilt in "sichten.sql" und "sichten_wms.sql"
23--  2013-10-23 Fehlersuche GebÀude-Hausnummer-Relation
24--  2013-11-26 Neue Views (doppelverbindung)
25--  2014-01-17 View "exp_csv" fÃŒr den Export von CSV-Daten aus der Auskunft mit Modul alkisexport.php.
26--  2014-01-20 Erweiterung "exp_csv" fÃŒr alkisexport.php
27--  2014-01-21 In "exp_csv": Rechtsgemeinsachaft zu allen Personen statt als eigener Satz.
28--  2014-01-27 Neuer Baustein "flst_an_strasse". Neuer View "exp_csv_str" fÃŒr CSV-Export von Flst. an einer Straße
29--  2014-01-29 Neuer View "strasse_als_gewanne" zur Fehlersuche.
30--  2014-01-31 Kommentar
31--  2014-02-06 nachmigration_aehnliche_anschriften
32--  2014-09-02 Tabelle "alkis_beziehungen" ÃŒberflÃŒssig machen. Relationen nun ÃŒber Spalten in den Objekttabellen.
33--  2014-09-11 Neu: View "fehlersuche_namensanteile_je_blatt", substring(gml_id) bei Relation-Join, mehr "endet IS NULL"
34--  2014-09-12 Korrektur "doppelverbindung" (nach Patch der Indices fÃŒr Relation auf Substring(gml_id,1,16))
35--  2014-09-17 View "fehler_gebaeude_zu_mehrfach_hsnr"
36--  2014-09-22 Views zur Suche von Fehlern aus einem fehlerhaften Historie-Trigger (beginnt-endet-Paarungen)
37
38
39-- Bausteine fÃŒr andere Views:
40-- ---------------------------
41
42-- Ein View, der die Verbindung von FlurstÌck zur Buchung fÌr zwei verschiedene FÀlle herstellt.
43-- Einmal die "normalen" (direkten) Buchungen.
44-- Zweitens ÃŒber die Rechte von Buchungsstellen an anderen Buchungsstellen.
45-- Dies kann als "MittelstÃŒck" in den anderen Views eingefÃŒgt werden.
46
47-- Einfach/Direkt:
48--   FlurstÃŒck   >istGebucht>                         (Buchungs-Stelle)
49--
50-- Mit "Recht an":
51--   FlurstÃŒck   >istGebucht>  Buchungs-Stelle  <an<  (Buchungs-Stelle)
52--                               (dienend)              (herrschend)
53
54--           DROP VIEW public.doppelverbindung;
55CREATE OR REPLACE VIEW public.doppelverbindung
56AS
57  -- FS >istGebucht> Buchungstelle
58  SELECT f1.gml_id              AS fsgml,    -- gml_id FlurstÃŒck
59         b1.gml_id              AS bsgml,    -- gml_id Buchungs
60         0                      AS ba_dien
61    FROM ax_flurstueck f1
62    JOIN ax_buchungsstelle b1   ON f1.istgebucht = substring(b1.gml_id,1,16)
63 UNION
64  -- FS >istGebucht> Buchungstelle  <an<  Buchungstelle
65  SELECT f2.gml_id              AS fsgml,    -- gml_id FlurstÃŒck
66         b2.gml_id              AS bsgml,    -- gml_id Buchung - (herrschendes GB)
67         dien.buchungsart       AS ba_dien   -- Ein Feld aus der Zwischen-Buchung zur Fall-Unterscheidung
68    FROM ax_flurstueck f2
69    JOIN ax_buchungsstelle dien ON f2.istgebucht = substring(dien.gml_id,1,16)
70    JOIN ax_buchungsstelle b2   ON substring(dien.gml_id,1,16) = ANY (b2.an)  -- auch "zu" ?
71   WHERE dien.endet IS NULL;   -- FÃŒr das zusÀtzliche Verbindungselement die Historie HIER ausschließen,
72                               -- FÃŒr andere Tabellen muss dies in dem View erfolgen, der dies verwendet.
73
74COMMENT ON VIEW public.doppelverbindung
75 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.
76Dies ist ausschließlich gedacht zur Verwendung in anderen Views um diese einfacher zu machen.';
77
78
79-- Test-Ausgabe: Ein paar FÀlle mit "Recht an"
80--   SELECT * FROM doppelverbindung WHERE ba_dien > 0 LIMIT 20;
81-- Nach Umstellung auf PostNAS 0.8 - mit ANY() und Substring - sehr lange Antwortzeit in PG 8.4
82
83-- Ein View, der die Verbindung von FlurstÃŒck zur Straßentabelle fÃŒr zwei verschiedene FÀlle herstellt.
84-- Einmal ÃŒber die Lagebezeichnung MIT Hausnummer und einmal OHNE.
85-- Dies kann als "MittelstÃŒck" in den anderen Views eingefÃŒgt werden.
86
87--           DROP VIEW public.flst_an_strasse;
88CREATE OR REPLACE VIEW public.flst_an_strasse
89AS
90  -- FlurstÃŒck >weistAuf> ax_lagebezeichnungMIThausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
91  SELECT fm.gml_id AS fsgml,
92         sm.gml_id AS stgml,                 -- Filter: gml_id der Straße
93         'm' AS fall                         -- SÀtze unterschieden: Mit HsNr
94    FROM ax_flurstueck fm                    -- FlurstÃŒck Mit
95    JOIN ax_lagebezeichnungmithausnummer lm  -- Lage MIT
96      ON substring(lm.gml_id,1,16) = ANY (fm.weistauf) 
97    JOIN ax_lagebezeichnungkatalogeintrag sm
98      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
99   WHERE lm.endet IS NULL AND fm.endet IS NULL -- nichts Historisches
100 UNION
101  -- FlurstÃŒck >zeigtAuf> ax_lagebezeichnungOHNEhausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
102  SELECT fo.gml_id AS fsgml,
103         so.gml_id AS stgml,                 -- Filter: gml_id der Straße
104         'o' AS fall                         -- SÀtze unterschieden: Ohne HsNr
105    FROM ax_flurstueck fo                    -- FlurstÃŒck OHNE
106    JOIN ax_lagebezeichnungohnehausnummer lo -- Lage OHNE
107      ON substring(lo.gml_id,1,16) = ANY (fo.zeigtauf) 
108    JOIN ax_lagebezeichnungkatalogeintrag so -- Straße OHNE
109      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
110   WHERE lo.endet IS NULL AND fo.endet IS NULL; -- nichts Historisches
111
112COMMENT ON VIEW public.flst_an_strasse
113 IS 'ALKIS-Beziehung von FlurstÃŒck zu Straßentabelle. UNION-Zusammenfassung der FÀlle MIT und OHNE Hausnummer.';
114
115-- Muss man noch dafÃŒr sorgen, dass FlurstÃŒck nicht doppelt vorkommt? z.B. mit DISTINCT
116-- Oder mÃŒssen ggf. mehrfache FS im Programm ÃŒbersprungen werden?
117
118-- Test-Ausgabe:
119--   SELECT * FROM flst_an_strasse WHERE stgml='DENW18AL000004Fl' LIMIT 40;
120
121-- Ende "Bausteine"
122
123
124-- Generelle Export-Struktur "FlurstÃŒck - Buchung - Grundbuch - Person"
125-- --------------------------------------------------------------------
126-- Verwendet den gespeicherten View "doppelverbindung".
127-- Wird benötigt im Auskunft-Modul "alkisexport.php":
128-- Je nach aufrufendem Modul wird der Filter (WHERE) an anderer Stelle gesetzt (gml_id von FS, GB oder Pers.)
129-- FÃŒr Filter nach "Straße" siehe die nachfolgende Sonderversion "exp_csv_str".
130
131-- Problem / Konflikt:
132-- Es kann nur eine lineare Struktur aus Spalten und Zeilen exportiert werden.
133-- Wenn nicht nur die Daten des Ausgangs-Objektes exportiert werden, sondern auch verbundene Tabellen in
134-- einer 1:N-Struktur, dann verdoppeln sich Zeileninhalte und es werden redundante Daten erzeugt.
135-- Diese Redundanzen mÃŒssen vom dem Programm gefiltert werden, das die Daten ÃŒber eine Schnittstelle einliest.
136
137-- Anwendungs-Beispiel: Abrechnung von AnliegerbeitrÀgen.
138
139--           DROP VIEW exp_csv;
140
141CREATE OR REPLACE VIEW exp_csv
142AS
143 SELECT
144  -- FlurstÃŒck
145    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
146    f.flurstueckskennzeichen             AS fs_kennz,
147    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
148    f.flurnummer, f.zaehler, f.nenner,
149    f.amtlicheflaeche                    AS fs_flae,
150    g.bezeichnung                        AS gemarkung,
151
152  -- Grundbuch
153    gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
154    gb.bezirk                            AS gb_bezirk,
155    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
156    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
157
158  -- Buchungsstelle (GrundstÃŒck)
159    s.laufendenummer                     AS bu_lfd,      -- BVNR
160  --s.zaehler, s.nenner,                                 -- Anteil des GB am FS, einzelne Felder
161    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
162    s.buchungsart,                                       -- verschlÃŒsselt
163    b.bezeichner                         AS bu_art,      -- Buchungsart entschlÃŒsselt
164
165  -- NamensNummer (Normalfall mit Person)
166    nn.laufendenummernachdin1421         AS nam_lfd,
167    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel
168
169  -- Rechtsgemeinsachaft (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
170    rg.artderrechtsgemeinschaft          AS nam_adr,
171    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
172
173  -- Person
174    p.gml_id                             AS psgml,           -- möglicher Filter Personen-GML-ID
175    p.anrede,
176    p.vorname,
177    p.namensbestandteil,
178    p.nachnameoderfirma,                                     -- Familienname
179    p.geburtsdatum,
180    --p.geburtsname, p.akademischergrad
181 
182  -- Adresse der Person
183    a.postleitzahlpostzustellung         AS plz,
184    a.ort_post                           AS ort,             -- Anschreifenzeile 1: PLZ+Ort
185    a.strasse,  a.hausnummer,                                -- Anschriftenzeile 2: Straße+HsNr
186    a.bestimmungsland                    AS land
187
188  FROM ax_flurstueck    f               -- FlurstÃŒck
189  JOIN doppelverbindung d               -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von Buchung an Buchung
190    ON d.fsgml = f.gml_id
191  JOIN ax_gemarkung g                   -- entschlÃŒsseln
192    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
193  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
194    ON d.bsgml = s.gml_id
195  JOIN ax_buchungsstelle_buchungsart b  -- EnstschlÃŒsselung der Buchungsart
196    ON s.buchungsart = b.wert
197  JOIN ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
198    ON substring(gb.gml_id,1,16) = s.istbestandteilvon
199  JOIN ax_buchungsblattbezirk z
200    ON gb.land=z.land AND gb.bezirk=z.bezirk
201  JOIN ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
202    ON substring(gb.gml_id,1,16) = nn.istbestandteilvon
203  JOIN ax_person p                      -- NamNum >benennt> Person
204    ON substring(p.gml_id,1,16) = nn.benennt
205  LEFT JOIN ax_anschrift a
206    ON substring(a.gml_id,1,16) = ANY (p.hat)
207
208  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
209  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
210  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
211  LEFT JOIN
212   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft
213       FROM ax_namensnummer r
214       JOIN ax_buchungsblatt gr
215         ON r.istbestandteilvon = substring(gr.gml_id,1,16) -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
216      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
217   ON rg.gml_id = gb.gml_id  -- zum GB
218
219  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
220
221  ORDER BY f.flurstueckskennzeichen,
222           gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer,
223           nn.laufendenummernachdin1421;
224
225COMMENT ON VIEW exp_csv
226 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.';
227
228
229-- Variante des View "exp_csv":
230-- Hier wird zusÀtzlich der Baustein "flst_an_strasse" verwendet.
231-- Der Filter "WHERE stgml= " auf die "gml_id" von "ax_lagebezeichnungkatalogeintrag" sollte gesetzt werden
232-- um alle FlurstÃŒcke zu bekommen, die an einer Straße liegen.
233
234-- DROP           VIEW exp_csv_str;
235
236CREATE OR REPLACE VIEW exp_csv_str
237AS
238 SELECT
239    l.stgml,                                             -- Filter: gml_id aus "ax_lagebezeichnungkatalogeintrag"
240                                                         -- UNTERSCHIED zu exp_csv)
241
242  -- FlurstÃŒck
243    f.gml_id                             AS fsgml,       -- Gruppenwechsel fÃŒr "function lage_zum_fs" in alkisexport.php
244    f.flurstueckskennzeichen             AS fs_kennz,
245    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
246    f.flurnummer, f.zaehler, f.nenner,
247    f.amtlicheflaeche                    AS fs_flae,
248    g.bezeichnung                        AS gemarkung,
249
250  -- Grundbuch
251  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
252    gb.bezirk                            AS gb_bezirk,
253    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
254    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
255
256  -- Buchungsstelle (GrundstÃŒck)
257    s.laufendenummer                     AS bu_lfd,      -- BVNR
258  --s.zaehler, s.nenner,                                 -- Anteil des GB am FS, einzelne Felder
259    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
260    s.buchungsart,                                       -- verschlÃŒsselt
261    b.bezeichner                         AS bu_art,      -- Buchungsart entschlÃŒsselt
262
263  -- NamensNummer (Normalfall mit Person)
264    nn.laufendenummernachdin1421         AS nam_lfd,
265    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel
266
267  -- Rechtsgemeinsachaft (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
268    rg.artderrechtsgemeinschaft          AS nam_adr,
269    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
270
271  -- Person
272  --p.gml_id                             AS psgml,           -- möglicher Filter Personen-GML-ID
273    p.anrede,
274    p.vorname,
275    p.namensbestandteil,
276    p.nachnameoderfirma,                                     -- Familienname
277    p.geburtsdatum,
278    --p.geburtsname, p.akademischergrad
279 
280  -- Adresse der Person
281    a.postleitzahlpostzustellung         AS plz,
282    a.ort_post                           AS ort,             -- Anschreifenzeile 1: PLZ+Ort
283    a.strasse,  a.hausnummer,                                -- Anschriftenzeile 2: Straße+HsNr
284    a.bestimmungsland                    AS land
285
286  FROM ax_flurstueck    f               -- FlurstÃŒck
287  JOIN flst_an_strasse  l               -- Lage (hier zusÀtzlicher JOIN gegenÃŒber Version "exp_csv")
288        ON l.fsgml = f.gml_id
289  JOIN doppelverbindung d               -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von Buchung an Buchung
290    ON d.fsgml = f.gml_id
291  JOIN ax_gemarkung g                   -- entschlÃŒsseln
292    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
293  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
294    ON d.bsgml = s.gml_id
295  JOIN ax_buchungsstelle_buchungsart b  -- EnstschlÃŒsselung der Buchungsart
296    ON s.buchungsart = b.wert
297  JOIN ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
298    ON substring(gb.gml_id,1,16) = s.istbestandteilvon
299  JOIN ax_buchungsblattbezirk z
300    ON gb.land=z.land AND gb.bezirk=z.bezirk
301  JOIN ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
302    ON substring(gb.gml_id,1,16) = nn.istbestandteilvon
303  JOIN ax_person p                      -- NamNum >benennt> Person
304    ON substring(p.gml_id,1,16) = nn.benennt
305  LEFT JOIN ax_anschrift a
306    ON substring(a.gml_id,1,16) = ANY (p.hat)
307
308  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
309  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
310  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
311  LEFT JOIN
312   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft
313       FROM ax_namensnummer r
314       JOIN ax_buchungsblatt gr
315         ON r.istbestandteilvon = substring(gr.gml_id,1,16) -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
316      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg  -- Rechtsgemeinschaft
317   ON rg.gml_id = gb.gml_id  -- zum GB
318
319  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
320
321  ORDER BY f.flurstueckskennzeichen,
322           gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer,
323           nn.laufendenummernachdin1421;
324
325COMMENT ON VIEW exp_csv_str
326 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.';
327
328-- Test: SELECT * FROM exp_csv_str WHERE stgml = 'DENW15AL100000Q8'; -- Veilchenstraße in Löhne
329
330
331-- Analyse: Kann es mehr als 1 "Rechtsgemeinschaft" zu einem GB-Blatt geben?
332-- (Diese Frage stellte sich beim Design des View "exp_csv".)
333
334-- In umgekehrter Reihenfolge löschen (AbhÀngigkeiten).
335DROP VIEW IF EXISTS rechtsgemeinschaften_mehrfachzeilen;
336DROP VIEW IF EXISTS rechtsgemeinschaften_zaehlen;
337DROP VIEW IF EXISTS rechtsgemeinschaften_zum_grundbuch;
338
339-- Schritt 1: alle vorhandenen Rechtsgemeinschaften
340
341CREATE OR REPLACE VIEW rechtsgemeinschaften_zum_grundbuch
342AS
343 SELECT
344     gb.gml_id AS gb_gml,
345     gb.bezirk,
346     gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
347     nn.gml_id AS nn_gml, nn.beginnt, nn.laufendenummernachdin1421,
348     nn.artderrechtsgemeinschaft AS adr,
349     nn.beschriebderrechtsgemeinschaft
350  FROM ax_buchungsblatt gb
351  JOIN ax_namensnummer  nn  ON substring(gb.gml_id,1,16) = nn.istbestandteilvon
352  WHERE NOT nn.artderrechtsgemeinschaft IS NULL
353    AND gb.endet IS NULL AND nn.endet IS NULL -- Historie weglassen
354  ORDER BY gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, nn.laufendenummernachdin1421;
355
356COMMENT ON VIEW rechtsgemeinschaften_zum_grundbuch
357 IS 'Rechtsgemeinschaften zum Grundbuchblatt.';
358
359-- Schritt 2: Wo gibt es mehrere zu einem GB-Blatt
360
361CREATE OR REPLACE VIEW rechtsgemeinschaften_zaehlen
362AS
363 SELECT gb_gml, bezirk, gb_blatt, count(adr) AS anzahl
364   FROM rechtsgemeinschaften_zum_grundbuch
365   GROUP BY gb_gml, bezirk, gb_blatt
366   HAVING count(adr) > 1
367   ORDER BY bezirk, gb_blatt;
368
369COMMENT ON VIEW rechtsgemeinschaften_zaehlen
370 IS 'Rechtsgemeinschaften zum Grundbuchblatt zÀhlen. Nur Anzeigen, wenn es mehrere gibt.';
371-- Ergebnis: Ja, kann es geben
372
373-- Schritt 3: alle vorhandenen Zeilen anzeigen zu den GB-BlÀttern, bei denen es mehrere gibt.
374CREATE OR REPLACE VIEW rechtsgemeinschaften_mehrfachzeilen
375AS
376 SELECT *
377   FROM rechtsgemeinschaften_zum_grundbuch
378  WHERE gb_gml IN (SELECT gb_gml FROM rechtsgemeinschaften_zaehlen);
379
380COMMENT ON VIEW rechtsgemeinschaften_mehrfachzeilen
381 IS 'GrundbuchblÀtter mit mehr als einer Zeile Rechtsgemeinschaft.';
382
383-- Fazit:
384-- Man findet einige wenige identische oder Àhnlich aussehende Zeilen zu einem Grundbuch.
385-- Das sieht also eher nach einem PostNAS-FortfÌhrungsproblem aus, als nach unabhÀngigen Zeilen.
386-- Wurde hier eine Relation nicht sauber gelöscht?
387
388-- Welche Karten-Typen ?
389CREATE OR REPLACE VIEW kartentypen_der_texte_fuer_hnr
390AS
391   SELECT DISTINCT advstandardmodell
392   FROM ap_pto p
393   WHERE p.art = 'HNR';
394
395COMMENT ON VIEW kartentypen_der_texte_fuer_hnr
396  IS 'Datenanalyse: Kartentypen in Tabelle ap_pto fÃŒr Hausnummern.';
397
398-- Dies liefert:
399--  "{DKKM1000}"
400--  "{DKKM1000,DKKM500}"
401--  "{DKKM500}"
402--  NULL
403
404-- Texte, die NICHT dargestellt werden sollen.
405-- -------------------------------------------
406-- Texte und Text-Fragmente aus der Konvertierung ALK+ALB, die noch nicht gelöscht worden sind.
407CREATE OR REPLACE VIEW ap_pto_muell
408AS
409  SELECT p.ogc_fid,
410         p.schriftinhalt,
411         p.art,
412         p.drehwinkel * 57.296 AS winkel, -- * 180 / Pi
413         p.wkb_geometry
414    FROM ap_pto p
415   WHERE not p.schriftinhalt IS NULL
416     AND p.endet IS NULL
417     AND p.art IN ('AOG_AUG','PNR');
418
419COMMENT ON VIEW ap_pto_muell
420 IS 'Datenanalyse: Beschriftungen aus "ap_pto", die NICHT dargestellt werden sollen.';
421
422
423-- FlurstÃŒcke mit Anzeige der FlurstÃŒcksnummer an der "Standardposition"
424
425-- Nach der Konvertierung aus ALK hat zunÀchst jedes FlurstÌck eine explizit gesetzte Position der FlurstÌcksnummer.
426
427-- Nach einer manuellen Teilung bekommen die neuen FlurstÃŒcke im ALKIS nur dann eine Position,
428-- wenn die Positioin manuell bestimmt (verschoben) wurde.
429-- Wenn die FlurstÃŒcksnummer an ihrer "Standardposition" angezeigt werden soll,
430-- dann wird diese in den Daten (DHK, NAS) nicht gesetzt.
431-- Der Konverter PostNAS konvertiert aber nur die Daten, die er bekommt, er setzt nicht die Standard-Position
432-- fÃŒr die FlurstÃŒcke, die ohne eine manuelle Position kommen.
433
434-- Diese FÀlle identifizieren und unterscheiden:
435
436CREATE OR REPLACE VIEW flstnr_mit_manueller_position
437AS
438 SELECT f.gml_id,
439        f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS such -- Suchstring fÃŒr ALKIS-Navigation nach FS-Kennzeichen
440   FROM ax_flurstueck f
441   JOIN ap_pto p ON substring(f.gml_id,1,16) = ANY(p.dientzurdarstellungvon)
442  WHERE f.endet IS NULL AND p.endet IS NULL;
443-- TIPP: mit zusÀtzlichem LIMIT auftrufen!
444
445COMMENT ON VIEW flstnr_mit_manueller_position
446  IS 'Sicht fÃŒr Datenanalyse: FlurstÃŒcke MIT manuell gesetzter Position fÃŒr die PrÀsentation der FS-Nr';
447
448CREATE OR REPLACE VIEW flstnr_ohne_manuelle_position
449AS
450 SELECT f.gml_id,
451        f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS such -- Suchstring fÃŒr ALKIS-Navigation nach FS-Kennzeichen
452 FROM   ax_flurstueck f
453 LEFT JOIN ap_pto p ON substring(f.gml_id,1,16) = ANY(p.dientzurdarstellungvon)
454 WHERE p.gml_id IS NULL
455   AND f.endet IS NULL;
456-- TIPP: mit zusÀtzlichem LIMIT aufrufen!
457
458COMMENT ON VIEW flstnr_ohne_manuelle_position
459  IS 'Sicht fÃŒr Datenanalyse: FlurstÃŒcke OHNE manuell gesetzte Position fÃŒr die PrÀsentation der FS-Nr';
460
461
462-- Umbruch im Label? z.B. "Schwimm-/nbecken"
463-- Sind 2 Buchstaben in Mapfile bei "WRAP" möglich?
464CREATE OR REPLACE VIEW texte_mit_umbruch
465AS
466 SELECT ogc_fid, schriftinhalt, art
467   FROM ap_pto
468  WHERE NOT schriftinhalt IS NULL AND schriftinhalt LIKE '%/n%';
469
470COMMENT ON VIEW texte_mit_umbruch
471 IS 'Sicht fÃŒr Datenanalyse: Vorkommen eines Umbruchs im Label-Text.';
472
473-- EXTENT fÃŒr das Mapfile eines Mandanten ermitteln
474CREATE OR REPLACE VIEW flurstuecks_minmax AS
475 SELECT min(st_xmin(wkb_geometry)) AS r_min,
476        min(st_ymin(wkb_geometry)) AS h_min,
477        max(st_xmax(wkb_geometry)) AS r_max,
478        max(st_ymax(wkb_geometry)) AS h_max
479   FROM ax_flurstueck f
480   WHERE f.endet IS NULL;
481
482COMMENT ON VIEW flurstuecks_minmax
483 IS 'Sicht fÃŒr Datenanalyse: Maximale Ausdehnung von ax_flurstueck fuer EXTENT-Angabe im Mapfile.';
484
485-- Nach Laden der Keytables:
486CREATE OR REPLACE VIEW baurecht
487AS
488  SELECT r.ogc_fid,
489         r.wkb_geometry,
490         r.gml_id,
491         r.artderfestlegung AS adfkey, -- Art der Festlegung - Key
492         r."name",                     -- Eigenname des Gebietes
493         r.stelle,                     -- Stelle Key
494         r.bezeichnung AS rechtbez,    -- Verfahrensnummer
495         a.bezeichner  AS adfbez,      -- Art der Festlegung - Bezeichnung
496         d.bezeichnung AS stellbez     -- Stelle Bezeichnung
497      -- , d.stellenart                -- weiter entschluesseln?
498    FROM ax_bauraumoderbodenordnungsrecht r
499    LEFT JOIN ax_bauraumoderbodenordnungsrecht_artderfestlegung a
500      ON r.artderfestlegung = a.wert
501    LEFT JOIN ax_dienststelle d
502      ON r.land=d.land AND r.stelle=d.stelle
503  WHERE r.endet IS NULL AND d.endet IS NULL ;
504
505COMMENT ON VIEW baurecht
506 IS 'Datenanalyse: EnstschlÃŒsselte Felder zu einer FlÀche des Baurechts.';
507
508-- Man glaubt es kaum, aber im ALKIS haben Gemeinde und Gemarkung keinerlei Beziehung miteinander
509-- Nur durch Auswertung der FlurstÃŒcke kann man ermitteln, in welcher Gemeinde eine Gemarkung liegt.
510CREATE OR REPLACE VIEW gemarkung_in_gemeinde
511AS
512  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
513  FROM            ax_flurstueck
514  WHERE           endet IS NULL
515  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer;
516
517COMMENT ON VIEW gemarkung_in_gemeinde
518 IS 'Welche Gemarkung liegt in welcher Gemeinde? Durch Verweise aus FlurstÃŒcken ermitteln.';
519
520
521-- Untersuchen, welche Geometrie-Typen vorkommen
522CREATE OR REPLACE VIEW arten_von_flurstuecksgeometrie
523AS
524 SELECT   count(gml_id) as anzahl,
525          st_geometrytype(wkb_geometry)
526 FROM     ax_flurstueck
527 WHERE    endet IS NULL
528 GROUP BY st_geometrytype(wkb_geometry);
529
530COMMENT ON VIEW arten_von_flurstuecksgeometrie IS 'Datenanalyse: vorkommende Geometry-Typen in Tabelle ax_flurstueck.';
531
532
533-- A d r e s s e n
534
535-- Verschluesselte Lagebezeichnung (Strasse und Hausnummer) fÃŒr eine Gemeinde
536-- SchlÃŒssel der Gemeinde im Filter (WHERE) nach Bedarf anpassen!
537CREATE OR REPLACE VIEW adressen_hausnummern
538AS
539    SELECT
540        s.bezeichnung AS strassenname,
541        g.bezeichnung AS gemeindename,
542        l.land, l.regierungsbezirk, l.kreis, l.gemeinde,
543        l.lage        AS strassenschluessel, l.hausnummer
544    FROM ax_lagebezeichnungmithausnummer l 
545    JOIN ax_gemeinde g
546      ON l.kreis=g.kreis AND l.gemeinde=g.gemeinde
547    JOIN ax_lagebezeichnungkatalogeintrag s
548      ON l.kreis=s.kreis AND l.gemeinde=s.gemeinde AND l.lage = s.lage
549    WHERE l.gemeinde = '40';  -- '40' = Stadt Lage
550
551COMMENT ON VIEW adressen_hausnummern IS 'Datenanalyse: VerschlÃŒsselte Lagebezeichnung (Straße und Hausnummer) fÃŒr eine Gemeinde. SchlÃŒssel der Gemeinde nach Bedarf anpassen.';
552
553-- Zuordnung Adressen zu Flurstuecken
554-- Schluessel der Gemeinde nach Bedarf anpassen!
555CREATE OR REPLACE VIEW adressen_zum_flurstueck
556AS
557    SELECT
558           f.gemarkungsnummer,
559           f.flurnummer,
560           f.zaehler,
561           f.nenner,
562           g.bezeichnung AS gemeindename,
563           s.bezeichnung AS strassenname,
564           l.lage        AS strassenschluessel,
565           l.hausnummer
566      FROM   ax_flurstueck f
567      JOIN   ax_lagebezeichnungmithausnummer l   ON  substring(l.gml_id,1,16) = ANY (f.weistauf)
568      JOIN   ax_gemeinde g   ON l.kreis=g.kreis  AND l.gemeinde=g.gemeinde
569      JOIN   ax_lagebezeichnungkatalogeintrag s  ON  l.kreis=s.kreis AND l.gemeinde=s.gemeinde AND l.lage = s.lage
570 --  WHERE l.gemeinde = '40'  -- ggf. Anpassen
571     ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler, f.nenner;
572
573COMMENT ON VIEW adressen_zum_flurstueck IS 'Datenanalyse: Zuordnung von Adressen zu Flurstuecken. SchlÃŒssel der Gemeinde nach Bedarf anpassen.';
574
575-- Punktförmige  P r À s e n t a t i o n s o b j e k t e  (ap_pto)
576-- Ermittlung der vorkommenden Arten
577CREATE OR REPLACE VIEW beschriftung_was_kommt_vor
578AS
579  SELECT DISTINCT art, horizontaleausrichtung, vertikaleausrichtung
580    FROM ap_pto
581   WHERE not schriftinhalt is null
582   ORDER BY art;
583
584COMMENT ON VIEW beschriftung_was_kommt_vor IS 'Analyse der vorkommenden Kombinationen in ap_pto (Beschriftung)';
585
586-- FlurstÃŒcke eines EigentÃŒmers
587-- ----------------------------
588
589-- Dieser View liefert nur die (einfache) Buchungsart "GrundstÃŒck"
590-- Solche FÀlle wie "Erbbaurecht an GrundstÌck" oder "Wohnungs-/Teileigentum an aufgeteiltes GrundstÌck"
591-- oder "Miteigentum an aufgeteiltes GrundstÃŒck" fehlen in dieser Auswertung.
592-- Dazu siehe: "rechte_eines_eigentuemers".
593
594-- Das Ergebnis ist gedacht fÃŒr den Export als CSV und Weiterverarbeitung mit einer Tabellenkalkulation
595-- oder einer einfachen Datenbank.
596
597-- Auch ein Export als Shape ist moeglich (dafÃŒr: geom hinzugefÃŒgt, Feldnamen gekÃŒrzt)
598-- Kommando:
599--  pgsql2shp -h localhost -p 5432 -f "/data/.../alkis_fs_gemeinde.shp"  [db-name]  public.flurstuecke_eines_eigentuemers
600
601-- Übersicht der Tabellen:
602--
603-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle >istGebucht> Flurstueck
604--                                              *-> Bezirk                *-> Buchungsart     *-> Gemarkung
605
606CREATE OR REPLACE VIEW flurstuecke_eines_eigentuemers
607AS
608   SELECT
609      k.bezeichnung                AS gemarkung,
610      k.gemarkungsnummer           AS gemkg_nr,
611      f.flurnummer                 AS flur,
612      f.zaehler                    AS fs_zaehler,
613      f.nenner                     AS fs_nenner,
614      f.amtlicheflaeche            AS flaeche,
615      f.wkb_geometry               AS geom,  -- fuer Export als Shape
616   -- g.bezirk,
617      b.bezeichnung                AS bezirkname,
618      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
619      g.blattart,
620      s.laufendenummer             AS bvnr,
621      art.bezeichner               AS buchgsart,
622   -- s.zaehler || '/' || s.nenner AS buchg_anteil,
623      n.laufendenummernachdin1421  AS name_num,
624   -- n.zaehler || '/' || n.nenner AS nam_anteil,
625      p.nachnameoderfirma          AS nachname --,
626   -- p.vorname
627   FROM   ax_person              p
628     JOIN ax_namensnummer        n   -- Namennummer >benennt> Person
629       ON substring(p.gml_id,1,16) = n.benennt
630     JOIN ax_buchungsblatt       g   -- Namensnummer >istBestandteilVon> Grundbuch
631       ON n.istbestandteilvon = substring(g.gml_id,1,16)
632     JOIN ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
633     JOIN ax_buchungsstelle      s   -- Buchungs-Stelle >istBestandteilVon> Grundbuch
634       ON s.istbestandteilvon = substring(g.gml_id,1,16)
635     JOIN ax_buchungsstelle_buchungsart art
636       ON s.buchungsart = art.wert
637     JOIN ax_flurstueck          f  -- Flurstueck >istGebucht> Buchungs-Stelle
638       ON f.istgebucht = substring(s.gml_id,1,16)
639     JOIN ax_gemarkung           k   
640       ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
641   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!
642     AND p.endet IS NULL AND n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL
643     AND s.endet IS NULL AND f.endet IS NULL AND k.endet IS NULL
644   ORDER BY k.bezeichnung, f.flurnummer, f.zaehler, f.nenner, g.bezirk, g.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer;
645
646COMMENT ON VIEW flurstuecke_eines_eigentuemers
647  IS 'Nur einfache Buchungsart "GrundstÃŒck". Muster fÃŒr Export: Suchkriterium nach Bedarf anpassen.';
648
649
650-- Rechte eines EigentÃŒmers
651-- ------------------------
652-- Dieser View sucht speziell die FÀlle wo eine Buchungsstelle ein Recht "an" einer anderen Buchungsstelle hat.
653--  - "Erbbaurecht *an* GrundstÃŒck"
654--  - "Wohnungs-/Teileigentum *an* Aufgeteiltes GrundstÃŒck"
655--  - "Miteigentum *an* Aufteteiltes GrundstÃŒck"
656-- Suchkriterium ist der Name des EigentÃŒmers auf dem "herrschenden" Grundbuch, also dem Besitzer des Rechtes.
657
658-- Diese FÀlle fehlen im View "flurstuecke_eines_eigentuemers".
659
660-- Übersicht der Tabellen:
661-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle-h >an> Stelle-d >istGebucht> Flurstueck
662-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
663
664-- DROP VIEW rechte_eines_eigentuemers;
665CREATE OR REPLACE VIEW rechte_eines_eigentuemers
666AS
667   SELECT
668      k.bezeichnung                AS gemarkung,
669      k.gemarkungsnummer           AS gemkg_nr,
670      f.flurnummer                 AS flur,
671      f.zaehler                    AS fs_zaehler,
672      f.nenner                     AS fs_nenner,
673      f.amtlicheflaeche            AS flaeche,
674      f.wkb_geometry               AS geom,  -- fuer Export als Shape
675   -- g.bezirk,
676      b.bezeichnung                AS bezirkname,
677      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
678   -- g.blattart,
679      sh.laufendenummer            AS bvnr_herr,
680      sh.zaehler || '/' || sh.nenner AS buchg_anteil_herr,
681      arth.bezeichner              AS buchgsa_herr,
682      artd.bezeichner              AS buchgsa_dien,
683      sd.laufendenummer            AS bvnr_dien,
684   -- sd.zaehler || '/' || sd.nenner AS buchg_anteil_dien,
685      n.laufendenummernachdin1421  AS name_num,
686   -- n.zaehler || '/' || n.nenner AS nam_anteil,
687      p.nachnameoderfirma          AS nachname --, 
688   -- p.vorname
689   FROM   ax_person              p
690     JOIN ax_namensnummer        n    -- Namennummer >benennt> Person
691       ON substring(p.gml_id,1,16) = n.benennt
692     JOIN ax_buchungsblatt       g    -- Namensnummer >istBestandteilVon> Grundbuch
693       ON n.istBestandteilVon = substring(g.gml_id,1,16)
694     JOIN ax_buchungsblattbezirk b   
695       ON g.land = b.land AND g.bezirk = b.bezirk
696     JOIN ax_buchungsstelle      sh  -- B-Stelle herr >istBestandteilVon> Grundbuch
697       ON sh.istbestandteilvon = substring(g.gml_id,1,16) -- herrschende Buchung
698     JOIN ax_buchungsstelle_buchungsart arth
699       ON sh.buchungsart = arth.wert
700     JOIN ax_buchungsstelle      sd   -- B-Stelle herr.  >an/zu> B-Stelle dien.
701       ON (substring(sd.gml_id,1,16) = ANY(sh.an) OR substring(sd.gml_id,1,16) = ANY(sh.zu))
702     JOIN ax_buchungsstelle_buchungsart artd
703       ON sd.buchungsart = artd.wert
704     JOIN ax_flurstueck          f    -- Flurstueck  >istGebucht> B-Stelle dien     
705       ON f.istgebucht = substring(sd.gml_id,1,16)
706     JOIN ax_gemarkung           k   
707       ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
708   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!   
709     AND p.endet IS NULL AND n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL
710     AND sh.endet IS NULL AND sd.endet IS NULL AND f.endet IS NULL AND k.endet IS NULL
711   ORDER BY k.bezeichnung, f.flurnummer, f.zaehler, f.nenner, g.bezirk, g.buchungsblattnummermitbuchstabenerweiterung, sh.laufendenummer;
712
713COMMENT ON VIEW rechte_eines_eigentuemers IS 'Muster fÌr Export: Suchkriteriumnach Bedarf anpassen. Dies ergÀnzt "flurstuecke_eines_eigentuemers" um die FÀlle mit besonderen Buchungen.';
714
715
716-- Suche nach Fehlern:
717-- Eine Hausnummer darf nur einem Gebaeude zugeordnet werden.
718-- Das Verschieben der Relation
719--   ax_gebaeude >zeigtAuf>z ax_lagebezeichnungmithausnummer
720-- fuehrt möglicherweise dazu, dass die alte Relation nicht gelöscht wird.
721-- Die angezeigten FÀlle sind potentielle Fehler.
722
723CREATE OR REPLACE VIEW fehler_hausnummer_mehrfach_verwendet
724AS
725 SELECT l.gml_id, l.gemeinde, l.lage, l.hausnummer
726   FROM ax_gebaeude g
727   JOIN ax_lagebezeichnungmithausnummer l 
728     ON substring(l.gml_id,1,16) = ANY(g.zeigtauf)
729  WHERE g.endet IS NULL AND l.endet IS NULL
730  GROUP BY l.gml_id, l.gemeinde, l.lage, l.hausnummer
731  HAVING count(g.gml_id) > 1;
732
733COMMENT ON VIEW fehler_hausnummer_mehrfach_verwendet
734 IS 'Fehlersuche: Nach replace von ax_lagebezeichnungmithausnummer mit einem neuen ax_gebaeude bleibt die alte Verbindung?';
735
736
737-- unter Verwendung dieses View weitere Information
738CREATE OR REPLACE VIEW fehler_gebaeude_zu_mehrfach_hsnr
739AS
740  SELECT f.gemeinde, f.lage, k.bezeichnung, f.hausnummer,
741         g.gml_id, g.beginnt
742  FROM ax_gebaeude g
743  JOIN fehler_hausnummer_mehrfach_verwendet f
744    ON substring(f.gml_id::text, 1, 16) = ANY (g.zeigtauf)
745  JOIN ax_lagebezeichnungkatalogeintrag k
746    ON f.gemeinde=k.gemeinde AND f.lage=k.lage
747    WHERE g.endet IS NULL
748  ORDER BY f.gemeinde, f.lage, f.hausnummer, g.gml_id;
749
750COMMENT ON VIEW fehler_hausnummer_mehrfach_verwendet
751 IS 'Fehlersuche: Adressen und GebÀude-Objekte zu den Fehlern aus dem View "fehler_hausnummer_mehrfach_verwendet"';
752
753
754-- Ein GebÀude hat mehrere Nummern.
755CREATE OR REPLACE VIEW adressen_zu_gebauede_mit_mehreren_hausnummern
756AS
757 SELECT g1.gml_id, l1.gemeinde, l1.lage, l1.hausnummer -- Anzeige der Adressfelder
758   FROM ax_gebaeude g1
759   JOIN ax_lagebezeichnungmithausnummer l1 ON l1.gml_id = ANY(g1.zeigtauf)
760  WHERE g1.endet IS NULL AND l1.endet IS NULL
761    AND g1.gml_id IN -- Subquery sucht GebÀude mit meherern Hausnummen
762   (SELECT g2.gml_id
763      FROM ax_gebaeude g2
764      JOIN ax_lagebezeichnungmithausnummer l2 ON substring(l2.gml_id,1,16) = ANY(g2.zeigtauf)
765     WHERE g2.endet IS NULL AND l2.endet IS NULL
766     GROUP BY g2.gml_id
767   HAVING count(l2.gml_id) > 1)
768   ORDER BY l1.gemeinde, l1.lage, l1.hausnummer;
769
770COMMENT ON VIEW adressen_zu_gebauede_mit_mehreren_hausnummern
771 IS 'GebÀude mit mehreren Hausnummern suchen (ist erlaubt) und dazu die Adressen anzeigen.';
772
773
774-- Analyse der Buchungs-Arten im Bestand
775CREATE OR REPLACE VIEW buchungsarten_vorkommend
776AS
777  SELECT a.wert, a.bezeichner,
778         count(b.gml_id) AS anzahl_buchungen
779    FROM ax_buchungsstelle_buchungsart a
780    JOIN ax_buchungsstelle b  ON a.wert = b.buchungsart
781   WHERE b.endet IS NULL
782GROUP BY a.wert, a.bezeichner
783ORDER BY a.wert, a.bezeichner;
784
785COMMENT ON VIEW buchungsarten_vorkommend
786 IS 'Welche Arten von Buchungsart kommen in dieser Datenbank tÀtsÀchlich vor?.';
787
788
789-- Analyse: FÀlle mit Erbbaurecht
790-- Benutzt den Baustein-View "doppelverbindung"
791
792--   +++ BESSER: analog doppelverbindung direkt codieren
793
794CREATE OR REPLACE VIEW erbbaurechte_suchen
795AS
796  SELECT f.gml_id, f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler AS fssuch, f.nenner
797   FROM ax_flurstueck    f
798   JOIN doppelverbindung d     -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von BS an BS
799     ON d.fsgml = f.gml_id
800   JOIN ax_buchungsstelle s    -- Buchungs-Stelle
801     ON d.bsgml = s.gml_id
802   WHERE s.buchungsart = 2101 AND f.endet IS NULL AND s.endet IS NULL;
803
804COMMENT ON VIEW erbbaurechte_suchen
805 IS 'Suche nach FÀllen mit Buchungsrt 2101=Erbbaurecht';
806
807
808-- Suchen von Gewannenbezeichnungen, die auch als Straßenname verwendet werden.
809-- Diese FÀlle fÃŒhren möglicherweise zu unvollstÀndiger Ausgabe beim Export "alle FlurstÃŒcke an einer Straße"
810-- weil nur Lagebezeichnung MIT und OHNE Hausnummer gesucht wird, aber keine gleich lautende Gewanne.
811
812-- DROP VIEW strasse_als_gewanne;
813CREATE OR REPLACE VIEW strasse_als_gewanne
814AS
815  SELECT k.gemeinde, k.lage AS strassenschluessel,
816         o.unverschluesselt AS gewanne_und_strasse, -- = k.bezeichnung = Straßenname
817         count(f.gml_id) AS anzahl_fs_gewanne
818  FROM ax_lagebezeichnungkatalogeintrag k   -- Straßentabelle
819  JOIN ax_lagebezeichnungohnehausnummer o   -- Gewanne
820    ON k.bezeichnung = o.unverschluesselt   -- Gleiche Namen
821  -- Join Gewanne auf FlurstÃŒcke um nur solche FÀlle anzuzeigen, die hier verwendet werden.
822  -- UND die auch in der gleichen Gemeinde liegen.
823  -- Sonst könnte zufÀllige Namensgleichheiten aus verschiedenen Gemeinden geben.
824  JOIN ax_flurstueck f               --  Flurst. >zeigtAuf>  Lage
825    ON o.gml_id = ANY(f.zeigtauf) 
826 WHERE f.gemeinde = k.gemeinde  -- Gewanne wird fÃŒr ein Flst. in gleicher Gemeinde verwendet, wie der StraßenschlÃŒssel
827  GROUP BY k.gemeinde, k.lage, o.unverschluesselt
828  ORDER BY k.gemeinde, k.lage, o.unverschluesselt;
829
830COMMENT ON VIEW strasse_als_gewanne
831 IS 'Gewannenbezeichnungen, die auch als Straßenname verwendet werden. Mit FlurstÃŒcks-ZÀhler.';
832
833
834-- Wie zuvor, aber die FlurstÌcke werden hier nicht nur gezÀhlt sondern auch aufgelistet.
835-- das Format des FlusrtÃŒckskennzeichens kann in die Mapbender-Navigation eingegeben werden.
836
837CREATE OR REPLACE VIEW strasse_als_gewanne_flst
838AS
839  SELECT fo.gemarkungsnummer || '-' || fo.flurnummer || '-' || fo.zaehler::text || COALESCE ('/' || fo.nenner::text, '') AS flstkennz,
840         k.gemeinde,
841         o.unverschluesselt AS gewanne,
842         k.lage        -- AS strassen_schluessel
843  FROM ax_lagebezeichnungkatalogeintrag k   -- Straßentabelle
844  JOIN ax_lagebezeichnungohnehausnummer o   -- Gewanne
845    ON k.bezeichnung = o.unverschluesselt   -- Gleiche Namen
846   JOIN ax_flurstueck fo                    --  Flurst. >zeigtAuf>  Lage
847    ON substring(o.gml_id,1,16) = ANY(fo.zeigtauf) 
848 WHERE fo.gemeinde = k.gemeinde  -- Gewanne wird fÃŒr ein Flst. in gleicher Gemeinde verwendet, wie der StraßenschlÃŒssel
849   AND k.endet IS NULL AND o.endet IS NULL AND fo.endet IS NULL
850  ORDER BY fo.gemarkungsnummer, fo.flurnummer, fo.zaehler, k.gemeinde, k.bezeichnung;
851
852COMMENT ON VIEW strasse_als_gewanne_flst
853 IS 'FlurstÃŒcke mit Gewannenbezeichnungen, die auch als Straßenname verwendet werden.';
854
855
856-- Suche nach Fehlern in den Daten, die moeglicherweise aus der Migration stammen und
857-- im Rahmen der Nachmigration noch korrigiert werden muessen.
858
859CREATE OR REPLACE VIEW nachmigration_aehnliche_anschriften
860AS
861  SELECT DISTINCT p.gml_id, p.nachnameoderfirma, p.vorname,
862         a1.ort_post, a1.strasse AS strasse1, a2.strasse AS strasse2, a1.hausnummer
863    FROM ax_person    p
864    JOIN ax_anschrift a1 ON substring(a1.gml_id,1,16) = ANY(p.hat)
865    JOIN ax_anschrift a2 ON substring(a2.gml_id,1,16) = ANY(p.hat)
866   WHERE a1.gml_id <> a2.gml_id
867      AND a1.ort_post =  a2.ort_post
868      AND a1.strasse like trim(a2.strasse, '.') || '%'
869      AND a1.hausnummer = a2.hausnummer
870      AND p.endet IS NULL AND a1.endet IS NULL and a2.endet IS NULL
871    ORDER BY p.nachnameoderfirma, p.vorname;
872
873COMMENT ON VIEW nachmigration_aehnliche_anschriften
874 IS 'Nachmigration? Zu einer Person gibt es mehrere Anschriften, die in Ort und Hausnummer identisch sind und beim Straßennemen entweder auch identisch sind oder eine AbkÃŒrzung mit Punkt enthalten.';
875
876
877-- Bevor alle inversen Relationen im Schema auskommentiert werden,
878-- noch mal in die Datenbank schauen, ob nicht doch eine davon gefÃŒllt ist.
879-- Nach dem Entfernen dieser Spalten wird der View nicht mehr funktionieren
880-- und wird darum ebenfalls auskommentiert.
881
882/*
883CREATE OR REPLACE VIEW nicht_gefuellte_inverse_relationen_spalten
884AS
885  SELECT 'ax_flurstueck' AS tabelle, 'beziehtsichaufflurstueck' AS spalte,
886     count(gml_id) AS anzahl_eintraege
887  FROM ax_flurstueck WHERE NOT beziehtsichaufflurstueck IS NULL
888UNION
889  SELECT 'ax_flurstueck' AS tabelle, 'gehoertanteiligzu' AS spalte,
890     count(gml_id) AS anzahl_eintraege
891  FROM ax_flurstueck WHERE NOT gehoertanteiligzu IS NULL
892UNION
893  SELECT 'ax_lagebezeichnungohnehausnummer' AS tabelle, 'beschreibt' AS spalte,
894     count(gml_id) AS anzahl_eintraege
895  FROM ax_lagebezeichnungohnehausnummer WHERE NOT beschreibt IS NULL
896UNION
897  SELECT 'ax_lagebezeichnungohnehausnummer' AS tabelle, 'gehoertzu' AS spalte,
898     count(gml_id) AS anzahl_eintraege
899  FROM ax_lagebezeichnungohnehausnummer WHERE NOT gehoertzu IS NULL
900UNION
901  SELECT 'ax_lagebezeichnungmithausnummer' AS tabelle, 'hat' AS spalte,
902     count(gml_id) AS anzahl_eintraege
903  FROM ax_lagebezeichnungmithausnummer WHERE NOT hat IS NULL
904UNION
905  SELECT 'ax_lagebezeichnungmithausnummer' AS tabelle, 'beziehtsichauf' AS spalte,
906     count(gml_id) AS anzahl_eintraege
907  FROM ax_lagebezeichnungmithausnummer WHERE NOT beziehtsichauf IS NULL
908UNION
909  SELECT 'ax_lagebezeichnungmithausnummer' AS tabelle, 'beziehtsichauchauf' AS spalte,
910     count(gml_id) AS anzahl_eintraege
911  FROM ax_lagebezeichnungmithausnummer WHERE NOT beziehtsichauchauf IS NULL
912UNION
913  SELECT 'ax_lagebezeichnungmithausnummer' AS tabelle, 'gehoertzu' AS spalte,
914     count(gml_id) AS anzahl_eintraege
915  FROM ax_lagebezeichnungmithausnummer WHERE NOT gehoertzu IS NULL
916UNION
917  SELECT 'ax_lagebezeichnungmithausnummer' AS tabelle, 'weistzum' AS spalte,
918     count(gml_id) AS anzahl_eintraege
919  FROM ax_lagebezeichnungmithausnummer WHERE NOT weistzum IS NULL
920UNION
921  SELECT 'ax_lagebezeichnungmitpseudonummer' AS tabelle, 'gehoertzu' AS spalte,
922     count(gml_id) AS anzahl_eintraege
923  FROM ax_lagebezeichnungmitpseudonummer WHERE NOT gehoertzu IS NULL
924--UNION
925--  -- ist gefÃŒllt!
926--  SELECT 'ax_georeferenziertegebaeudeadresse' AS tabelle, 'hatauch' AS spalte,
927--     count(gml_id) AS anzahl_eintraege
928--  FROM ax_georeferenziertegebaeudeadresse WHERE NOT hatauch IS NULL
929UNION
930  SELECT 'ax_sicherungspunkt' AS tabelle, 'beziehtsichauf' AS spalte,
931     count(gml_id) AS anzahl_eintraege
932  FROM ax_sicherungspunkt WHERE NOT beziehtsichauf IS NULL
933UNION
934  SELECT 'ax_sicherungspunkt' AS tabelle, 'gehoertzu' AS spalte,
935     count(gml_id) AS anzahl_eintraege
936  FROM ax_sicherungspunkt WHERE NOT gehoertzu IS NULL
937-- Punktort: ist gefÃŒllt (nicht invers)
938--UNION
939--  SELECT 'ax_punktortag' AS tabelle, 'istteilvon' AS spalte,
940--     count(gml_id) AS anzahl_eintraege
941--  FROM ax_punktortag WHERE NOT istteilvon IS NULL
942--UNION
943--  SELECT 'ax_punktortau' AS tabelle, 'istteilvon' AS spalte,
944--     count(gml_id) AS anzahl_eintraege
945--  FROM ax_punktortau WHERE NOT istteilvon IS NULL
946--UNION
947--  SELECT 'ax_punktortta' AS tabelle, 'istteilvon' AS spalte,
948--     count(gml_id) AS anzahl_eintraege
949--  FROM ax_punktortta WHERE NOT istteilvon IS NULL
950UNION
951  SELECT 'ax_person' AS tabelle, 'weistauf' AS spalte,
952     count(gml_id) AS anzahl_eintraege
953  FROM ax_person WHERE NOT weistauf IS NULL
954UNION
955  SELECT 'ax_person' AS tabelle, 'uebtaus' AS spalte,
956     count(gml_id) AS anzahl_eintraege
957  FROM ax_person WHERE NOT uebtaus IS NULL
958UNION
959  SELECT 'ax_person' AS tabelle, 'besitzt' AS spalte,
960     count(gml_id) AS anzahl_eintraege
961  FROM ax_person WHERE NOT besitzt IS NULL
962UNION
963  SELECT 'ax_person' AS tabelle, 'zeigtauf' AS spalte,
964     count(gml_id) AS anzahl_eintraege
965  FROM ax_person WHERE NOT zeigtauf IS NULL
966UNION
967  SELECT 'ax_person' AS tabelle, 'benennt' AS spalte,
968     count(gml_id) AS anzahl_eintraege
969  FROM ax_person WHERE NOT benennt IS NULL
970UNION
971  SELECT 'ax_anschrift' AS tabelle, 'beziehtsichauf' AS spalte,
972     count(gml_id) AS anzahl_eintraege
973  FROM ax_anschrift WHERE NOT beziehtsichauf IS NULL
974UNION
975  SELECT 'ax_anschrift' AS tabelle, 'gehoertzu' AS spalte,
976     count(gml_id) AS anzahl_eintraege
977  FROM ax_anschrift WHERE NOT gehoertzu IS NULL
978UNION
979  SELECT 'ax_verwaltung' AS tabelle, 'beziehtsichauf' AS spalte,
980     count(gml_id) AS anzahl_eintraege
981  FROM ax_verwaltung WHERE NOT beziehtsichauf IS NULL
982UNION
983  SELECT 'ax_vertretung' AS tabelle, 'vertritt' AS spalte,
984     count(gml_id) AS anzahl_eintraege
985  FROM ax_vertretung WHERE NOT vertritt IS NULL
986UNION
987  SELECT 'ax_buchungsblatt' AS tabelle, 'bestehtaus' AS spalte,
988     count(gml_id) AS anzahl_eintraege
989  FROM ax_buchungsblatt WHERE NOT bestehtaus IS NULL
990UNION
991  SELECT 'ax_buchungsstelle' AS tabelle, 'grundstueckbestehtaus' AS spalte,
992     count(gml_id) AS anzahl_eintraege
993  FROM ax_buchungsstelle WHERE NOT grundstueckbestehtaus IS NULL
994UNION
995  SELECT 'ax_gebaeude' AS tabelle, 'haengtzusammenmit' AS spalte,
996     count(gml_id) AS anzahl_eintraege
997  FROM ax_gebaeude WHERE NOT haengtzusammenmit IS NULL
998;
999
1000COMMENT ON VIEW nicht_gefuellte_relationen_spalten
1001 IS 'ÜberprÃŒfung auf nicht gefÃŒllte Inverse Relationen.
1002Funktioniert nur, solange diese nicht entfernt wurden.';
1003
1004*/
1005
1006-- Daraus resultiert der folgende Patch fÃŒr bereits angelegte Datenbanken:
1007/*
1008        ALTER TABLE ax_flurstueck                    DROP COLUMN beziehtsichaufflurstueck;
1009        ALTER TABLE ax_flurstueck                    DROP COLUMN gehoertanteiligzu;
1010        ALTER TABLE ax_lagebezeichnungohnehausnummer DROP COLUMN beschreibt;
1011        ALTER TABLE ax_lagebezeichnungohnehausnummer DROP COLUMN gehoertzu;
1012        ALTER TABLE ax_lagebezeichnungmithausnummer  DROP COLUMN hat;
1013        ALTER TABLE ax_lagebezeichnungmithausnummer  DROP COLUMN beziehtsichauf;
1014        ALTER TABLE ax_lagebezeichnungmithausnummer  DROP COLUMN beziehtsichauchauf;
1015        ALTER TABLE ax_lagebezeichnungmithausnummer  DROP COLUMN gehoertzu;
1016        ALTER TABLE ax_lagebezeichnungmithausnummer  DROP COLUMN weistzum;
1017        ALTER TABLE ax_lagebezeichnungmitpseudonummer DROP COLUMN gehoertzu;
1018        ALTER TABLE ax_sicherungspunkt               DROP COLUMN beziehtsichauf;
1019        ALTER TABLE ax_sicherungspunkt               DROP COLUMN gehoertzu;
1020        ALTER TABLE ax_person                        DROP COLUMN weistauf;
1021        ALTER TABLE ax_person                        DROP COLUMN uebtaus;
1022        ALTER TABLE ax_person                        DROP COLUMN besitzt;
1023        ALTER TABLE ax_person                        DROP COLUMN zeigtauf;
1024        ALTER TABLE ax_person                        DROP COLUMN benennt;
1025        ALTER TABLE ax_anschrift                     DROP COLUMN beziehtsichauf;
1026        ALTER TABLE ax_anschrift                     DROP COLUMN gehoertzu;
1027        ALTER TABLE ax_verwaltung                    DROP COLUMN beziehtsichauf;
1028        ALTER TABLE ax_vertretung                    DROP COLUMN vertritt;
1029        ALTER TABLE ax_buchungsblatt                 DROP COLUMN bestehtaus;
1030        ALTER TABLE ax_buchungsstelle                DROP COLUMN grundstueckbestehtaus;
1031        ALTER TABLE ax_gebaeude                      DROP COLUMN haengtzusammenmit;
1032*/
1033
1034-- Anteile der Namensnummern am Blatt aufsummieren.
1035-- BlÀtter mit RechtsverhÀltnis (Beschrieb) nicht beachten.
1036-- Anzeigen, wenn die Summe nicht 1 ergibt.
1037-- Keine Angabe in ZÀhler/Nenner wird als 1 gewertet.
1038
1039-- Anlass zu dieser Auswertung war:
1040-- Wenn mit PostNAS 0.8 und Trigger "kill" (ohne Historie) eine NBA-Abgabe mit Abgabeart "3100 fallbezogen (mit Historie)"
1041-- konvertiert wird, dann wird Update nicht richtig verarbeitet.
1042-- Update setzt z.B. das endet-Datum an einen Namensnummer. Alte Namen verbleiben auf dem Grundbuch.
1043
1044CREATE OR REPLACE VIEW fehlersuche_namensanteile_je_blatt
1045AS
1046  SELECT g.gml_id, g.bezirk || '-' || g.buchungsblattnummermitbuchstabenerweiterung AS kennzeichen,
1047         sum(coalesce(n.zaehler/n.nenner, 1.0))::double precision AS summe_der_anteile
1048  FROM ax_buchungsblatt g
1049  JOIN ax_namensnummer n ON substring(g.gml_id,1,16) = n.istbestandteilvon
1050  WHERE g.endet IS NULL AND n.endet IS NULL
1051  GROUP BY g.gml_id, g.bezirk || '-' || g.buchungsblattnummermitbuchstabenerweiterung
1052  HAVING sum(coalesce(n.zaehler/n.nenner, 1)) <> 1.0::double precision
1053     AND (  -- die FÀlle mit einer Rechtsgemeinschaft nicht verwenden
1054        SELECT gml_id
1055        FROM ax_namensnummer nr
1056        WHERE substring(g.gml_id,1,16) = nr.istbestandteilvon
1057          AND NOT nr.artderrechtsgemeinschaft IS NULL
1058          AND nr.endet IS NULL
1059        LIMIT 1
1060     ) IS NULL
1061  LIMIT 100;
1062
1063COMMENT ON VIEW fehlersuche_namensanteile_je_blatt
1064 IS 'Suchen nach GB-BlÀttern bei denen die Summe der Anteile der Namensnummern nicht passt.
1065Mit Ausnahme von RechtsverhÀltnissen sollte sie Summe der BrÌche immer 1/1 ergeben.';
1066
1067
1068-- PrÃŒfen einer Konvertierung mit historischen Objekten
1069-- Abgabeart 3100, Trigger "delete_feature_hist()".
1070-- NICHT anzuwenden bei Abgabeart 1000
1071
1072-- Erst mal die betroffenen Objekte identifizieren
1073CREATE OR REPLACE VIEW fehlersuche_hist_mehrere_vorgaenger_fs
1074AS
1075  SELECT substring(gml_id,1,16) AS gml, count(beginnt) AS anzahl -- 16stellige kurze ID, oder Substring
1076  FROM ax_flurstueck
1077  GROUP BY substring(gml_id,1,16)
1078  HAVING count(beginnt) > 4 -- mindestens 3 Generationen zur PrÃŒfung notwendig
1079  ORDER BY count(beginnt) DESC -- die hÀufigsten zuerst
1080  LIMIT 20;  -- reicht zum gucken
1081-- bis zu 7 Versionen je FS gefunden
1082
1083COMMENT ON VIEW fehlersuche_hist_mehrere_vorgaenger_fs
1084 IS 'ALKIS-FlurstÃŒcke suchen, zu denen es inzwischen mehrere Versionen gibt,
1085also mehrere inzwischen beendete (historische) VorgÀnger-Versionen';
1086
1087-- Im zweiten Schritt dazu alle Versionen auflisten.
1088-- Die Serial "ogc_fid" sollte die EinfÌge-Reihenfolge reprÀsentieren.
1089-- Je "gml_id" muss "beginnt" und "endet" aufsteigend sein.
1090-- Das "endet" muss jeweils das "beginnt" der folgenden Version sein.
1091CREATE OR REPLACE VIEW fehlersuche_hist_endet_sortierung_fs
1092AS
1093  SELECT substring(gml_id,1,16) AS gml, ogc_fid, beginnt, endet
1094  FROM ax_flurstueck f
1095  JOIN fehlersuche_hist_mehrere_vorgaenger_fs v -- der vorhergehende View als Filter
1096    ON substring(f.gml_id,1,16) = v.gml
1097  ORDER BY substring(gml_id,1,16), ogc_fid;
1098
1099COMMENT ON VIEW fehlersuche_hist_endet_sortierung_fs
1100 IS 'Zu den ALKIS-FlurstÃŒcken, zu denen es inzwischen mehrere Versionen gibt,
1101werden die beginnt- und  endet-Zeiten angezeigt. Diese sollten streng aufsteigend sein.
1102Der endet-Zeitstempel sollte jeweils dem beginnt-Zeitstempel der Folgeversion enstsprechen.
1103(Nur) das endet der letzten Version sollte leer sein.';
1104
1105-- Speziell die FÀlle suchen, bei denen die Kombination beginnt/endet-Datum nicht logisch ist
1106CREATE OR REPLACE VIEW fehlersuche_hist_beginnt_endet_mischmasch
1107AS
1108  SELECT substring(v1.gml_id,1,16) AS gml,
1109        v1.beginnt AS v1_beginnt,
1110        v2.beginnt AS v2_beginnt,
1111     v1.endet   AS v1_endet,
1112     v2.endet   AS v2_endet
1113  FROM ax_gebaeude v1 -- Version 1 -- oder eine andere Objekt-Tabelle
1114  JOIN ax_gebaeude v2 -- Version 2
1115    ON substring(v1.gml_id,1,16) = substring(v2.gml_id,1,16)  -- gleiches Objekt
1116   AND v2.beginnt > v1.beginnt -- aufsteigendes Beginn-Datum
1117   AND v2.endet   < v1.endet   -- aber absteigendes endet-Datum - Fehler!
1118  ORDER BY substring(v1.gml_id,1,16), v1.beginnt, v1.endet
1119  LIMIT 10; -- Beispiele als Beweis der Fehlerhaftigkeit
1120
1121COMMENT ON VIEW fehlersuche_hist_beginnt_endet_mischmasch
1122 IS 'Suche nach verschiedenen historischen Versionen eines Objektes bei denen das beginn-Datum aufsteigend ist, aber das endet-Datum absteigend (Falsch!).';
1123
1124-- END --
Note: See TracBrowser for help on using the repository browser.