source: trunk/import/sichten.sql @ 331

Revision 331, 50.5 KB checked in by frank.jaeger, 10 years ago (diff)

Kleinere Korrekturen an den Import-Scripten.

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