source: trunk/import/sichten.sql @ 421

Revision 363, 51.7 KB checked in by frank.jaeger, 8 years ago (diff)

Neuer View zur Fehlersuche in räumlich begrenzten NBA-Verfahren

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