source: trunk/import/sichten.sql @ 320

Revision 320, 46.5 KB checked in by astrid.emde, 7 years ago (diff)

GRANT auskommentiert und in grant.sql uebertragen

Line 
1-- =====
2-- ALKIS
3-- =====
4
5--  -----------------------------------------
6--  Sichten fÃŒr Fehlersuche und Daten-Analyse
7--  -----------------------------------------
8
9--  Dieses SQL braucht nur bei Bedarf in einer PostNAS-DB verarbeitet werden.
10--  Es werden zusÀtzliche Views eingerichtet, die nur bei Fehlersuche und Analyse (vom Entwickler) benötigt werden.
11
12--  PostNAS 0.7
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
33-- Bausteine fÃŒr andere Views:
34-- ---------------------------
35
36-- Ein View, der die Verbindung von FlurstÌck zur Buchung fÌr zwei verschiedene FÀlle herstellt.
37-- Einmal die "normalen" (direkten) Buchungen.
38-- Zweitens ÃŒber die Rechte von Buchungsstellen an anderen Buchungsstellen.
39-- Dies kann als "MittelstÃŒck" in den anderen Views eingefÃŒgt werden.
40
41-- Einfach/Direkt:
42--   FlurstÃŒck   >istGebucht>                         (Buchungs-Stelle)
43--
44-- Mit "Recht an":
45--   FlurstÃŒck   >istGebucht>  Buchungs-Stelle  <an<  (Buchungs-Stelle)
46--                               (dienend)              (herrschend)
47
48--           DROP VIEW public.doppelverbindung;
49CREATE OR REPLACE VIEW public.doppelverbindung
50AS
51  SELECT v1.beziehung_von AS fsgml,       -- gml_id auf FlurstÃŒck - Seite
52         v1.beziehung_zu  AS bsgml,       -- gml_id auf Buchungs  - Seite
53      --'direkt' AS fall,
54         0 AS ba_dien
55    FROM alkis_beziehungen v1
56   WHERE v1.beziehungsart = 'istGebucht'  -- FS --> Buchung
57 UNION
58  -- Buchungstelle  >an>  Buchungstelle  >istBestandteilVon>  BLATT
59  SELECT v2.beziehung_von AS fsgml,        -- gml_id auf FlurstÃŒck - Seite
60         an.beziehung_von AS bsgml,        -- gml_id auf Buchungs  - Seite (herrschendes GB)
61      --'Recht an' AS fall,
62         dien.buchungsart AS ba_dien       -- Ein Feld aus der Zwischen-Buchung zur Fall-Unterscheidung
63    FROM alkis_beziehungen v2
64    JOIN ax_buchungsstelle dien
65      ON  v2.beziehung_zu = dien.gml_id
66    JOIN alkis_beziehungen an
67      ON dien.gml_id = an.beziehung_zu
68   WHERE v2.beziehungsart = 'istGebucht'   -- FS --> Buchung
69     AND an.beziehungsart = 'an';
70
71COMMENT ON VIEW public.doppelverbindung
72 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.';
73
74-- Test-Ausgabe: Ein paar FÀlle mit "Recht an"
75--   SELECT * FROM doppelverbindung WHERE ba_dien > 0 LIMIT 20;
76
77
78
79-- Ein View, der die Verbindung von FlurstÃŒck zur Straßentabelle fÃŒr zwei verschiedene FÀlle herstellt.
80-- Einmal ÃŒber die Lagebezeichnung MIT Hausnummer und einmal OHNE.
81-- Dies kann als "MittelstÃŒck" in den anderen Views eingefÃŒgt werden.
82
83--   FlurstÃŒck  >weistAuf> ax_lagebezeichnungmithausnummer  <JOIN> ax_lagebezeichnungkatalogeintrag
84--   FlurstÃŒck  >zeigtAuf> ax_lagebezeichnungohnehausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
85
86--           DROP VIEW public.flst_an_strasse;
87CREATE OR REPLACE VIEW public.flst_an_strasse
88AS
89  SELECT vm.beziehung_von AS fsgml,          -- Join auf "gml_id" aus "ax_flurstÃŒck"
90         sm.gml_id AS stgml,                 -- Filter: gml_id der Straße
91      -- sm.gemeinde, sm.lage,               -- Gemeinde- und Straßenschluessel als Filter?
92         'm' AS fall                         -- SÀtze unterschieden: Mit HsNr
93    FROM alkis_beziehungen vm                -- Verbindung Mit
94    JOIN ax_lagebezeichnungmithausnummer lm  -- Lage MIT
95      ON lm.gml_id=vm.beziehung_zu
96     AND vm.beziehungsart= 'weistAuf'
97    JOIN ax_lagebezeichnungkatalogeintrag sm -- Ausnahmsweise mal direkt und nicht ÃŒber die "alkis_beziehungen"
98      ON lm.land=sm.land
99     AND lm.regierungsbezirk=sm.regierungsbezirk
100     AND lm.kreis=sm.kreis
101     AND lm.gemeinde=sm.gemeinde
102     AND lm.lage=sm.lage
103 UNION
104  SELECT vo.beziehung_von AS fsgml,          -- Join auf gml_id aus ax_flurstÃŒck
105         so.gml_id AS stgml,                 -- Filter: gml_id der Straße
106      -- so.gemeinde, so.lage                -- Gemeinde- und Straßenschluessel als Filter?
107         'o' AS fall                         -- SÀtze unterschieden: Ohne HsNr
108    FROM alkis_beziehungen vo                -- Verbindung OHNE
109    JOIN ax_lagebezeichnungohnehausnummer lo -- Lage OHNE
110      ON lo.gml_id=vo.beziehung_zu
111     AND vo.beziehungsart= 'zeigtAuf'
112    JOIN ax_lagebezeichnungkatalogeintrag so -- Straße OHNE
113      ON lo.land=so.land
114     AND lo.regierungsbezirk=so.regierungsbezirk
115     AND lo.kreis=so.kreis
116     AND lo.gemeinde=so.gemeinde
117     AND lo.lage=so.lage;
118
119COMMENT ON VIEW public.flst_an_strasse
120 IS 'ALKIS-Beziehung von FlurstÃŒck zu Straßentabelle. UNION-Zusammenfassung der FÀlle MIT und OHNE Hausnummer.';
121
122-- Muss man noch dafÃŒr sorgen, dass FlurstÃŒck nicht doppelt vorkommt? z.B. mit DISTINCT
123-- Oder mÃŒssen ggf. mehrfache FS im Programm ÃŒbersprungen werden?
124
125-- Test-Ausgabe:
126--   SELECT * FROM flst_an_strasse WHERE stgml='DENW18AL000004Fl' LIMIT 40;
127
128-- Ende "Bausteine"
129
130
131-- Generelle Export-Struktur "FlurstÃŒck - Buchung - Grundbuch - Person"
132-- --------------------------------------------------------------------
133-- Verwendet den gespeicherten View "doppelverbindung".
134-- Wird benötigt im Auskunft-Modul "alkisexport.php":
135-- Je nach aufrufendem Modul wird der Filter (WHERE) an anderer Stelle gesetzt (gml_id von FS, GB oder Pers.)
136-- FÃŒr Filter nach "Straße" siehe die nachfolgende Sonderversion "exp_csv_str".
137
138-- Problem / Konflikt:
139-- Es kann nur eine lineare Struktur aus Spalten und Zeilen exportiert werden.
140-- Wenn nicht nur die Daten des Ausgangs-Objektes exportiert werden, sondern auch verbundene Tabellen in
141-- einer 1:N-Struktur, dann verdoppeln sich Zeileninhalte und es werden redundante Daten erzeugt.
142-- Diese Redundanzen mÃŒssen vom dem Programm gefiltert werden, das die Daten ÃŒber eine Schnittstelle einliest.
143
144-- Anwendungs-Beispiel: Abrechnung von AnliegerbeitrÀgen.
145
146-- 2014-01-21: "Rechtsgemeinschaft" in den Datensatz aller anderen Namen
147--           DROP VIEW exp_csv;
148CREATE OR REPLACE VIEW exp_csv
149AS
150 SELECT
151  -- FlurstÃŒck
152    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
153    f.flurstueckskennzeichen             AS fs_kennz,
154    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
155    f.flurnummer, f.zaehler, f.nenner,
156    f.amtlicheflaeche                    AS fs_flae,
157    g.bezeichnung                        AS gemarkung,
158
159  -- Grundbuch
160    gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
161    gb.bezirk                            AS gb_bezirk,
162    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
163    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
164
165  -- Buchungsstelle (GrundstÃŒck)
166    s.laufendenummer                     AS bu_lfd,      -- BVNR
167    --s.zaehler, s.nenner,                                -- Anteil des GB am FS, einzelne Felder
168    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
169    s.buchungsart,                                       -- verschlÃŒsselt
170    b.bezeichner                         AS bu_art,      -- Buchungsart entschlÃŒsselt
171
172  -- NamensNummer (Normalfall mit Person)
173    nn.laufendenummernachdin1421         AS nam_lfd,
174    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel
175
176  -- Rechtsgemeinsachaft (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
177    rg.artderrechtsgemeinschaft          AS nam_adr,
178    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
179
180  -- Person
181    p.gml_id                             AS psgml,           -- möglicher Filter Personen-GML-ID
182    p.anrede,
183    p.vorname,
184    p.namensbestandteil,
185    p.nachnameoderfirma,                                     -- Familienname
186    p.geburtsdatum,
187    --p.geburtsname, p.akademischergrad
188 
189  -- Adresse der Person
190    a.postleitzahlpostzustellung         AS plz,
191    a.ort_post                           AS ort,             -- Anschreifenzeile 1: PLZ+Ort
192    a.strasse,  a.hausnummer,                                -- Anschriftenzeile 2: Straße+HsNr
193    a.bestimmungsland                    AS land
194
195  FROM ax_flurstueck    f               -- FlurstÃŒck
196  JOIN doppelverbindung d               -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von Buchung an Buchung
197    ON d.fsgml = f.gml_id
198
199  JOIN ax_gemarkung g                   -- entschlÃŒsseln
200    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
201
202  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
203    ON d.bsgml = s.gml_id
204  JOIN ax_buchungsstelle_buchungsart b  -- EnstschlÃŒsselung der Buchungsart
205    ON s.buchungsart = b.wert
206
207  JOIN alkis_beziehungen v3             -- Buchung --> Grundbuchblatt
208    ON s.gml_id = v3.beziehung_von AND v3.beziehungsart = 'istBestandteilVon'
209  JOIN ax_buchungsblatt  gb
210    ON v3.beziehung_zu = gb.gml_id
211
212  JOIN ax_buchungsblattbezirk z
213    ON gb.land=z.land AND gb.bezirk=z.bezirk
214
215  JOIN alkis_beziehungen v4             -- Blatt  --> NamNum
216    ON v4.beziehung_zu = gb.gml_id AND v4.beziehungsart = 'istBestandteilVon' 
217  JOIN ax_namensnummer nn
218    ON v4.beziehung_von = nn.gml_id
219
220  JOIN alkis_beziehungen v5             -- NamNum --> Person
221   -- 2014-01-20: Mit LEFT ab hier werden auch NumNum-Zeilen mit "Beschreibung der Rechtsgemeinschaft" geliefert (ohne Person)
222    ON v5.beziehung_von = nn.gml_id AND v5.beziehungsart = 'benennt'
223  JOIN ax_person p
224    ON v5.beziehung_zu = p.gml_id
225
226  LEFT JOIN alkis_beziehungen v6        -- Person --> Anschrift
227    ON v6.beziehung_von = p.gml_id AND v6.beziehungsart = 'hat'
228  LEFT JOIN ax_anschrift a
229    ON v6.beziehung_zu = a.gml_id
230
231  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery:
232
233  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
234  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
235  LEFT JOIN
236   ( SELECT v7.beziehung_zu,
237            rg.artderrechtsgemeinschaft,
238            rg.beschriebderrechtsgemeinschaft
239       FROM ax_namensnummer rg
240       JOIN alkis_beziehungen v7              -- Blatt  --> NamNum (Rechtsgemeinschaft)
241         ON v7.beziehung_von = rg.gml_id
242      WHERE v7.beziehungsart = 'istBestandteilVon'
243        AND NOT rg.artderrechtsgemeinschaft IS NULL
244   ) AS rg                         -- Rechtsgemeinschaft
245   ON rg.beziehung_zu = gb.gml_id  -- zum GB
246
247  ORDER BY f.flurstueckskennzeichen,
248           gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer,
249           nn.laufendenummernachdin1421;
250
251COMMENT ON VIEW exp_csv
252 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.';
253
254--GRANT SELECT ON TABLE exp_csv TO mb27;       -- User fÃŒr Auskunfts-Programme
255--GRANT SELECT ON TABLE exp_csv TO alkisbuch;  -- User fÃŒr Auskunfts-Programme RLP-Demo
256
257
258-- Variante des View "exp_csv":
259-- Hier wird zusÀtzlich der Baustein "flst_an_strasse" verwendet.
260-- Der Filter "WHERE stgml= " auf die "gml_id" von "ax_lagebezeichnungkatalogeintrag" sollte gesetzt werden
261-- um alle FlurstÃŒcke zu bekommen, die an einer Straße liegen.
262-- DROP           VIEW exp_csv_str;
263CREATE OR REPLACE VIEW exp_csv_str
264AS
265 SELECT
266    l.stgml,                                             -- Filter: Straßen-GML-ID
267
268  -- FlurstÃŒck
269    f.gml_id                             AS fsgml,       -- Gruppenwechsel fÃŒr "function lage_zum_fs" in alkisexport.php
270    f.flurstueckskennzeichen             AS fs_kennz,
271    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
272    f.flurnummer, f.zaehler, f.nenner,
273    f.amtlicheflaeche                    AS fs_flae,
274    g.bezeichnung                        AS gemarkung,
275
276  -- Grundbuch
277  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
278    gb.bezirk                            AS gb_bezirk,
279    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
280    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
281
282  -- Buchungsstelle (GrundstÃŒck)
283    s.laufendenummer                     AS bu_lfd,      -- BVNR
284    --s.zaehler, s.nenner,                                -- Anteil des GB am FS, einzelne Felder
285    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
286    s.buchungsart,                                       -- verschlÃŒsselt
287    b.bezeichner                         AS bu_art,      -- Buchungsart entschlÃŒsselt
288
289  -- NamensNummer (Normalfall mit Person)
290    nn.laufendenummernachdin1421         AS nam_lfd,
291    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel
292
293  -- Rechtsgemeinsachaft (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
294    rg.artderrechtsgemeinschaft          AS nam_adr,
295    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
296
297  -- Person
298  --p.gml_id                             AS psgml,           -- möglicher Filter Personen-GML-ID
299    p.anrede,
300    p.vorname,
301    p.namensbestandteil,
302    p.nachnameoderfirma,                                     -- Familienname
303    p.geburtsdatum,
304    --p.geburtsname, p.akademischergrad
305 
306  -- Adresse der Person
307    a.postleitzahlpostzustellung         AS plz,
308    a.ort_post                           AS ort,             -- Anschreifenzeile 1: PLZ+Ort
309    a.strasse,  a.hausnummer,                                -- Anschriftenzeile 2: Straße+HsNr
310    a.bestimmungsland                    AS land
311
312  FROM ax_flurstueck    f               -- FlurstÃŒck
313
314  JOIN flst_an_strasse  l               -- Lage (hier zusÀtzlicher JOIN gegenÃŒber Version "exp_csv")
315        ON l.fsgml = f.gml_id
316
317  JOIN doppelverbindung d               -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von Buchung an Buchung
318    ON d.fsgml = f.gml_id
319
320  JOIN ax_gemarkung g                   -- entschlÃŒsseln
321    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
322
323  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
324    ON d.bsgml = s.gml_id
325  JOIN ax_buchungsstelle_buchungsart b  -- EnstschlÃŒsselung der Buchungsart
326    ON s.buchungsart = b.wert
327
328  JOIN alkis_beziehungen v3             -- Buchung --> Grundbuchblatt
329    ON s.gml_id = v3.beziehung_von AND v3.beziehungsart = 'istBestandteilVon'
330  JOIN ax_buchungsblatt  gb
331    ON v3.beziehung_zu = gb.gml_id
332
333  JOIN ax_buchungsblattbezirk z
334    ON gb.land=z.land AND gb.bezirk=z.bezirk
335
336  JOIN alkis_beziehungen v4             -- Blatt  --> NamNum
337    ON v4.beziehung_zu = gb.gml_id AND v4.beziehungsart = 'istBestandteilVon' 
338  JOIN ax_namensnummer nn
339    ON v4.beziehung_von = nn.gml_id
340
341  JOIN alkis_beziehungen v5             -- NamNum --> Person
342   -- 2014-01-20: Mit LEFT ab hier werden auch NumNum-Zeilen mit "Beschreibung der Rechtsgemeinschaft" geliefert (ohne Person)
343    ON v5.beziehung_von = nn.gml_id AND v5.beziehungsart = 'benennt'
344  JOIN ax_person p
345    ON v5.beziehung_zu = p.gml_id
346
347  LEFT JOIN alkis_beziehungen v6        -- Person --> Anschrift
348    ON v6.beziehung_von = p.gml_id AND v6.beziehungsart = 'hat'
349  LEFT JOIN ax_anschrift a
350    ON v6.beziehung_zu = a.gml_id
351
352  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery:
353
354  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
355  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
356  LEFT JOIN
357   ( SELECT v7.beziehung_zu,
358            rg.artderrechtsgemeinschaft,
359            rg.beschriebderrechtsgemeinschaft
360       FROM ax_namensnummer rg
361       JOIN alkis_beziehungen v7              -- Blatt  --> NamNum (Rechtsgemeinschaft)
362         ON v7.beziehung_von = rg.gml_id
363      WHERE v7.beziehungsart = 'istBestandteilVon'
364        AND NOT rg.artderrechtsgemeinschaft IS NULL
365   ) AS rg                         -- Rechtsgemeinschaft
366   ON rg.beziehung_zu = gb.gml_id  -- zum GB
367
368  ORDER BY f.flurstueckskennzeichen,
369           gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer,
370           nn.laufendenummernachdin1421;
371
372COMMENT ON VIEW exp_csv_str
373 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.';
374
375--GRANT SELECT ON TABLE exp_csv_str TO mb27;       -- User fÃŒr Auskunfts-Programme
376--GRANT SELECT ON TABLE exp_csv_str TO alkisbuch;  -- User fÃŒr Auskunfts-Programme RLP-Demo
377
378
379-- Test-Ausgabe:
380--   SELECT * FROM exp_csv_str WHERE stgml='DENW18AL000004Fl' LIMIT 40;
381
382
383-- Analyse: Kann es mehr als 1 "Rechtsgemeinschaft" zu einem GB-Blatt geben?
384-- (Diese Frage stellte sich beim Design des View "exp_csv".)
385-- Schritt 1: alle vorhandenen
386CREATE OR REPLACE VIEW rechtsgemeinschaften_zum_grundbuch
387AS
388 SELECT
389     gb.gml_id,
390     gb.bezirk,
391     gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
392     nn.artderrechtsgemeinschaft,
393     nn.beschriebderrechtsgemeinschaft
394  FROM ax_buchungsblatt  gb
395  JOIN alkis_beziehungen v
396    ON v.beziehung_zu = gb.gml_id AND v.beziehungsart = 'istBestandteilVon' 
397  JOIN ax_namensnummer nn
398    ON v.beziehung_von = nn.gml_id
399  WHERE NOT nn.artderrechtsgemeinschaft IS NULL
400  ORDER BY gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung,
401           nn.laufendenummernachdin1421;
402
403COMMENT ON VIEW rechtsgemeinschaften_zum_grundbuch
404 IS 'Rechtsgemeinschaften zum Grundbuchblatt.';
405
406-- Schritt 2: Wo gibt es mehrere zu einem GB-Blatt
407CREATE OR REPLACE VIEW rechtsgemeinschaften_zaehlen
408AS
409 SELECT gml_id, bezirk, gb_blatt, count(artderrechtsgemeinschaft) AS anzahl
410   FROM rechtsgemeinschaften_zum_grundbuch
411   GROUP BY gml_id, bezirk, gb_blatt
412   HAVING count(artderrechtsgemeinschaft) > 1
413   ORDER BY bezirk, gb_blatt;
414
415COMMENT ON VIEW rechtsgemeinschaften_zaehlen
416 IS 'Rechtsgemeinschaften zum Grundbuchblatt zaehlen. Anzeigen, wenn es mehrere gibt.';
417-- Ja, kann es geben
418
419-- Schritt 3: alle vorhandenen Zeilen anzeigen zu den GB-BlÀttern, bei denen es mehrere gibt.
420CREATE OR REPLACE VIEW rechtsgemeinschaften_mehrfachzeilen
421AS
422 SELECT *
423   FROM rechtsgemeinschaften_zum_grundbuch
424  WHERE gml_id IN (SELECT gml_id FROM rechtsgemeinschaften_zaehlen);
425
426COMMENT ON VIEW rechtsgemeinschaften_mehrfachzeilen
427 IS 'GrundbuchblÀtter mit mehr als einer Zeile Rechtsgemeinschaft.';
428-- Fazit:
429-- Man findet einige wenige identische oder Àhnlich aussehende Zeilen zu einem Grundbuch.
430-- Das sieht also eher nach einem PostNAS-FortfÌhrungsproblem aus, als nach unabhÀngigen Zeilen.
431-- Wurde hier eine Relation nicht sauber gelöscht?
432
433
434-- Welche Karten-Typen ?
435CREATE OR REPLACE VIEW kartentypen_der_texte_fuer_hnr
436AS
437   SELECT DISTINCT advstandardmodell
438   FROM ap_pto p
439   WHERE p.art = 'HNR';
440COMMENT ON VIEW kartentypen_der_texte_fuer_hnr
441  IS 'Datenanalyse: Kartentypen in Tabelle ap_pto fÃŒr Hausnummern.';
442
443-- Dies liefert:
444--  "{DKKM1000}"
445--  "{DKKM1000,DKKM500}"
446--  "{DKKM500}"
447--  NULL
448
449-- Texte, die NICHT dargestellt werden sollen.
450-- -------------------------------------------
451-- Texte und Text-Fragmente aus der Konvertierung ALK+ALB, die noch nicht gelöscht worden sind.
452CREATE OR REPLACE VIEW ap_pto_muell
453AS
454  SELECT p.ogc_fid,
455         p.schriftinhalt,
456         p.art,
457         p.drehwinkel * 57.296 AS winkel, -- * 180 / Pi
458         p.wkb_geometry
459    FROM ap_pto p
460   WHERE not p.schriftinhalt IS NULL
461     AND p.endet IS NULL
462     AND p.art IN ('AOG_AUG','PNR');
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
479CREATE OR REPLACE VIEW flstnr_ohne_position
480AS
481 SELECT f.gml_id,
482        f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS such -- Suchstring fÃŒr ALKIS-Navigation nach FS-Kennzeichen
483 FROM        ax_flurstueck     f
484   LEFT JOIN alkis_beziehungen v  ON v.beziehung_zu = f.gml_id
485 --LEFT JOIN ap_pto            p  ON p.gml_id       = v.beziehung_von
486  WHERE v.beziehungsart is NULL
487    AND f.endet IS NULL
488--ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler
489  ;
490COMMENT ON VIEW flstnr_ohne_position IS 'Sicht fÌr Datenanalyse: FlurstÌcke ohne manuell gesetzte Position fÌr die PrÀsentation der FS-Nr';
491
492-- Umbruch im Label? z.B. "Schwimm-/nbecken"
493-- Sind 2 Buchstaben in Mapfile bei "WRAP" möglich?
494CREATE OR REPLACE VIEW texte_mit_umbruch
495AS
496 SELECT ogc_fid, schriftinhalt, art
497   FROM ap_pto
498  WHERE not schriftinhalt is null
499    AND schriftinhalt like '%/n%';
500COMMENT ON VIEW texte_mit_umbruch
501 IS 'Sicht fÃŒr Datenanalyse: Vorkommen eines Umbruchs im Label-Text.';
502
503-- EXTENT fÃŒr das Mapfile eines Mandanten ermitteln
504CREATE OR REPLACE VIEW flurstuecks_minmax AS
505 SELECT min(st_xmin(wkb_geometry)) AS r_min,
506        min(st_ymin(wkb_geometry)) AS h_min,
507        max(st_xmax(wkb_geometry)) AS r_max,
508        max(st_ymax(wkb_geometry)) AS h_max
509   FROM ax_flurstueck f
510   WHERE f.endet IS NULL;
511COMMENT ON VIEW flurstuecks_minmax
512 IS 'Sicht fÃŒr Datenanalyse: Maximale Ausdehnung von ax_flurstueck fuer EXTENT-Angabe im Mapfile.';
513
514-- Nach Laden der Keytables:
515CREATE OR REPLACE VIEW baurecht
516AS
517  SELECT r.ogc_fid,
518         r.wkb_geometry,
519         r.gml_id,
520         r.artderfestlegung as adfkey, -- Art der Festlegung - Key
521         r."name",                     -- Eigenname des Gebietes
522         r.stelle,                     -- Stelle Key
523         r.bezeichnung AS rechtbez,    -- Verfahrensnummer
524         a.bezeichner  AS adfbez,      -- Art der Festlegung - Bezeichnung
525         d.bezeichnung AS stellbez     -- Stelle Bezeichnung
526      -- , d.stellenart                -- weiter entschluesseln?
527    FROM ax_bauraumoderbodenordnungsrecht r
528    LEFT JOIN ax_bauraumoderbodenordnungsrecht_artderfestlegung a
529      ON r.artderfestlegung = a.wert
530    LEFT JOIN ax_dienststelle d
531      ON r.land   = d.land
532     AND r.stelle = d.stelle
533  WHERE r.endet IS NULL AND d.endet IS NULL ;
534COMMENT ON VIEW baurecht
535 IS 'Datenanalyse: EnstschlÃŒsselte Felder zu einer FlÀche des Baurechts.';
536
537-- Man glaubt es kaum, aber im ALKIS haben Gemeinde und Gemarkung keinerlei Beziehung miteinander
538-- Nur durch Auswertung der FlurstÃŒcke kann man ermitteln, in welcher Gemeinde eine Gemarkung liegt.
539CREATE OR REPLACE VIEW gemarkung_in_gemeinde
540AS
541  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
542  FROM            ax_flurstueck
543  WHERE           endet IS NULL
544  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer;
545COMMENT ON VIEW gemarkung_in_gemeinde
546 IS 'Welche Gemarkung liegt in welcher Gemeinde? Durch Verweise aus FlurstÃŒck.';
547
548
549-- Untersuchen, welche Geometrie-Typen vorkommen
550CREATE OR REPLACE VIEW arten_von_flurstuecksgeometrie
551AS
552 SELECT   count(gml_id) as anzahl,
553          st_geometrytype(wkb_geometry)
554 FROM     ax_flurstueck
555 WHERE    endet IS NULL
556 GROUP BY st_geometrytype(wkb_geometry);
557COMMENT ON VIEW arten_von_flurstuecksgeometrie IS 'Datenanalyse: vorkommende Geometry-Typen in Tabelle ax_flurstueck.';
558
559
560-- A d r e s s e n
561
562-- Verschluesselte Lagebezeichnung (Strasse und Hausnummer) fuer eine Gemeinde
563-- Schluessel der Gemeinde nach Bedarf anpassen!
564CREATE OR REPLACE VIEW adressen_hausnummern
565AS
566    SELECT
567        s.bezeichnung AS strassenname,
568        g.bezeichnung AS gemeindename,
569        l.land,
570        l.regierungsbezirk,
571        l.kreis,
572        l.gemeinde,
573        l.lage        AS strassenschluessel,
574        l.hausnummer
575    FROM   ax_lagebezeichnungmithausnummer l 
576    JOIN   ax_gemeinde g
577      ON l.kreis=g.kreis
578     AND l.gemeinde=g.gemeinde
579    JOIN   ax_lagebezeichnungkatalogeintrag s
580      ON l.kreis=s.kreis
581     AND l.gemeinde=s.gemeinde
582     AND l.lage = s.lage
583    WHERE l.gemeinde = 40;  -- "40" = Stadt Lage
584COMMENT ON VIEW adressen_hausnummern IS 'Datenanalyse: VerschlÃŒsselte Lagebezeichnung (Straße und Hausnummer) fÃŒr eine Gemeinde. SchlÃŒssel der Gemeinde nach Bedarf anpassen.';
585
586-- Zuordnung dieser Adressen zu Flurstuecken
587-- Schluessel der Gemeinde nach Bedarf anpassen!
588CREATE OR REPLACE VIEW adressen_zum_flurstueck
589AS
590    SELECT
591           f.gemarkungsnummer,
592           f.flurnummer,
593           f.zaehler,
594           f.nenner,
595           g.bezeichnung AS gemeindename,
596           s.bezeichnung AS strassenname,
597           l.lage        AS strassenschluessel,
598           l.hausnummer
599      FROM   ax_flurstueck f
600      JOIN   alkis_beziehungen v
601        ON f.gml_id=v.beziehung_von
602      JOIN   ax_lagebezeichnungmithausnummer l 
603        ON l.gml_id=v.beziehung_zu
604      JOIN   ax_gemeinde g
605        ON l.kreis=g.kreis
606       AND l.gemeinde=g.gemeinde
607      JOIN   ax_lagebezeichnungkatalogeintrag s
608        ON l.kreis=s.kreis
609       AND l.gemeinde=s.gemeinde
610       AND l.lage = s.lage
611     WHERE v.beziehungsart='weistAuf'
612       AND l.gemeinde = 40  -- "40" = Stadt Lage
613     ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler, f.nenner;
614COMMENT ON VIEW adressen_zum_flurstueck IS 'Datenanalyse: Zuordnung von Adressen zu Flurstuecken. SchlÃŒssel der Gemeinde nach Bedarf anpassen.';
615
616-- Punktförmige  P r À s e n t a t i o n s o b j e k t e  (ap_pto)
617-- Ermittlung der vorkommenden Arten
618CREATE OR REPLACE VIEW beschriftung_was_kommt_vor
619AS
620  SELECT DISTINCT art, horizontaleausrichtung, vertikaleausrichtung
621    FROM ap_pto
622   WHERE not schriftinhalt is null
623  ORDER BY art;
624COMMENT ON VIEW beschriftung_was_kommt_vor IS 'Analyse der vorkommenden Kombinationen in ap_pto (Beschriftung)';
625
626-- Ergebnis:
627-- 2013: PostNAS 0.7  (aus 150,260,340)
628-- ------------------
629--      "AOG_AUG"                               "zentrisch";"Basis"  - Schriftinhalkt immer nur "I" ?
630--      "BWF"                                   "zentrisch";"Basis"/"zentrisch";"Mitte"
631--      "BWF_ZUS"                               "zentrisch";"Basis"
632--      "FKT"                                   "zentrisch";"Basis"/"linksbÃŒndig";"Basis"/"zentrisch";"Mitte"
633--      "FKT_TEXT"                              "zentrisch";"Mitte"
634--      "FreierText"                    "zentrisch";"Basis"/"zentrisch";"Mitte"/"linksbÃŒndig";"Basis"
635--      "FreierTextHHO"                 "zentrisch";"Mitte"
636--      "Friedhof"                              "zentrisch";"Basis"
637--      "Gewanne"                               "zentrisch";"Basis"/"zentrisch";"Mitte"
638--      "GFK"                                   "zentrisch";"Basis"/"zentrisch";"Mitte"
639--      "HNR"                                   "zentrisch";"Basis"/"linksbÃŒndig";"Basis"/"zentrisch";"Mitte"  --> Hausnummer, group gebaeude
640--      "HHO"                                   "zentrisch";"Mitte"  -- HHO = objekthoehe zu ax_gebaeude?
641--      "NAM"                                   "zentrisch";"Basis"/"zentrisch";"Mitte"/"linksbÃŒndig";"Basis"
642--      "SPO"                                   "zentrisch";"Basis"/
643--      "Vorratsbehaelter"              "zentrisch";"Basis"
644--      "WeitereHoehe"                  "zentrisch";"Mitte"
645--      "ZAE_NEN"                               "zentrisch";"Basis"
646--      "ZNM"                                   "zentrisch";"Basis"/"linksbÃŒndig";"Basis"
647
648--* Layer "ap_pto_stra"
649--                          hor ; ver / hor ; ver
650--      "BezKlassifizierungStrasse" "zent.";"Basis"     / "linksbÃŒndig";"Basis"
651--      "Platz"                                 "zentrisch";"Basis" / "zentrisch";"Mitte"
652--      "Strasse"                               "zentrisch";"Basis" / "zentrisch";"Mitte" / "linksbÃŒndig";"Basis"
653--      "Weg"                                   "zentrisch";"Basis" / "zentrisch";"Mitte" / "linksbÃŒndig";"Basis"
654
655--* geplanter layer "ap_pto_wasser"
656--      "StehendesGewaesser"    "zentrisch";"Basis"
657--      "Fliessgewaesser"               "zentrisch";"Basis"/"linksbÃŒndig";"Basis"
658
659
660-- FlurstÃŒcke eines EigentÃŒmers
661-- ----------------------------
662
663-- Dieser View liefert nur die (einfache) Buchungsart "GrundstÃŒck"
664-- Solche FÀlle wie "Erbbaurecht an GrundstÌck" oder "Wohnungs-/Teileigentum an aufgeteiltes GrundstÌck"
665-- oder "Miteigentum an aufteteiltes GrundstÃŒck" fehlen in deisere Auswertung.
666-- Dazu siehe: "rechte_eines_eigentuemers".
667
668-- Das Ergbenis ist gedacht fÃŒr den Export als CSV und Weiterverarbeitung mit einer Tabellenkalkulation
669-- oder einer einfachen Datenbank.
670
671-- Auch ein Export als Shape ist moeglich (dafuer: geom hinzugefuegt, Feldnamen gekuerzt)
672-- Kommando:
673--  pgsql2shp -h localhost -p 5432 -f "/data/.../alkis_fs_gemeinde.shp"  [db-name]  public.flurstuecke_eines_eigentuemers
674
675-- Übersicht der Tabellen:
676--
677-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle >istGebucht> Flurstueck
678--                                              *-> Bezirk                *-> Buchungsart     *-> Gemarkung
679
680-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
681
682CREATE OR REPLACE VIEW flurstuecke_eines_eigentuemers
683AS
684   SELECT
685      k.bezeichnung                AS gemarkung,
686      k.gemarkungsnummer           AS gemkg_nr,
687      f.flurnummer                 AS flur,
688      f.zaehler                    AS fs_zaehler,
689      f.nenner                     AS fs_nenner,
690      f.amtlicheflaeche            AS flaeche,
691      f.wkb_geometry               AS geom,  -- fuer Export als Shape
692   -- g.bezirk,
693      b.bezeichnung                AS bezirkname,
694      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
695      g.blattart,
696      s.laufendenummer             AS bvnr,
697      art.bezeichner               AS buchgsart,
698   -- s.zaehler || '/' || s.nenner AS buchg_anteil,
699      n.laufendenummernachdin1421  AS name_num,
700   -- n.zaehler || '/' || n.nenner AS nam_anteil,
701      p.nachnameoderfirma          AS nachname --,
702   -- p.vorname
703   FROM       ax_person              p
704        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
705        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
706        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
707        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
708        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
709        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
710        JOIN  ax_buchungsstelle      s    ON s.gml_id = bgs.beziehung_von
711        JOIN  ax_buchungsstelle_buchungsart art ON s.buchungsart = art.wert
712        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = s.gml_id
713        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
714        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
715   WHERE p.nachnameoderfirma LIKE 'Gemeinde %'   -- ** Bei Bedarf anpassen!
716     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
717     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
718     AND bgs.beziehungsart = 'istBestandteilVon' -- Buchungs-Stelle >> Grundbuch
719     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> Buchungs-Stelle
720     AND p.endet IS NULL AND n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL
721     AND s.endet IS NULL AND f.endet IS NULL AND k.endet IS NULL
722   ORDER BY k.bezeichnung, f.flurnummer, f.zaehler, f.nenner, g.bezirk, g.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer;
723COMMENT ON VIEW flurstuecke_eines_eigentuemers IS 'Muster fÃŒr Export: Suchkriteriumnach Bedarf anpassen.';
724
725-- Rechte eines EigentÃŒmers
726-- ------------------------
727-- Dieser View sucht speziell die FÀlle wo eine Buchungsstelle ein Recht "an" einer anderen Buchungsstelle hat.
728--  - "Erbbaurecht *an* GrundstÃŒck"
729--  - "Wohnungs-/Teileigentum *an* Aufgeteiltes GrundstÃŒck"
730--  - "Miteigentum *an* Aufteteiltes GrundstÃŒck"
731-- Suchkriterium ist der Name des EigentÃŒmers auf dem "herrschenden" Grundbuch, also dem Besitzer des Rechtes.
732
733-- Diese FÀlle fehlen im View "flurstuecke_eines_eigentuemers".
734
735-- Übersicht der Tabellen:
736-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle-h >an> Stelle-d >istGebucht> Flurstueck
737-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
738
739CREATE OR REPLACE VIEW rechte_eines_eigentuemers
740AS
741   SELECT
742      k.bezeichnung                AS gemarkung,
743      k.gemarkungsnummer           AS gemkg_nr,
744      f.flurnummer                 AS flur,
745      f.zaehler                    AS fs_zaehler,
746      f.nenner                     AS fs_nenner,
747      f.amtlicheflaeche            AS flaeche,
748      f.wkb_geometry               AS geom,  -- fuer Export als Shape
749   -- g.bezirk,
750      b.bezeichnung                AS bezirkname,
751      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
752   -- g.blattart,
753      sh.laufendenummer            AS bvnr_herr,
754      sh.zaehler || '/' || sh.nenner AS buchg_anteil_herr,
755      arth.bezeichner              AS buchgsa_herr,
756      bss.beziehungsart            AS bez_art,
757      artd.bezeichner              AS buchgsa_dien,
758      sd.laufendenummer            AS bvnr_dien,
759   -- sd.zaehler || '/' || sd.nenner AS buchg_anteil_dien,
760      n.laufendenummernachdin1421  AS name_num,
761   -- n.zaehler || '/' || n.nenner AS nam_anteil,
762      p.nachnameoderfirma          AS nachname --, 
763   -- p.vorname
764   FROM       ax_person              p
765        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
766        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
767        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
768        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
769        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
770        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
771        JOIN  ax_buchungsstelle      sh   ON sh.gml_id = bgs.beziehung_von  -- herrschende Buchung
772        JOIN  ax_buchungsstelle_buchungsart arth ON sh.buchungsart = arth.wert
773        JOIN  alkis_beziehungen      bss  ON sh.gml_id = bss.beziehung_von
774        JOIN  ax_buchungsstelle      sd   ON sd.gml_id = bss.beziehung_zu   -- dienende Buchung
775        JOIN  ax_buchungsstelle_buchungsart artd ON sd.buchungsart = artd.wert
776        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = sd.gml_id
777        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
778        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
779   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!
780     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
781     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
782     AND bgs.beziehungsart = 'istBestandteilVon' -- B-Stelle herr   >> Grundbuch
783     AND bss.beziehungsart in ('an','zu')        -- B-Stelle herr.  >> B-Stelle dien.
784     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> B-Stelle dien
785     AND p.endet IS NULL AND n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL
786     AND sh.endet IS NULL AND sd.endet IS NULL AND f.endet IS NULL AND k.endet IS NULL
787   ORDER BY k.bezeichnung, f.flurnummer, f.zaehler, f.nenner, g.bezirk, g.buchungsblattnummermitbuchstabenerweiterung, sh.laufendenummer;
788COMMENT 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.';
789
790-- Die 2 Views nur fuer Entwicklung:
791
792--      CREATE OR REPLACE VIEW beziehungen_redundant
793--      AS
794--       SELECT *
795--         FROM alkis_beziehungen AS bezalt
796--         WHERE EXISTS
797--                 (SELECT ogc_fid
798--                       FROM alkis_beziehungen AS bezneu
799--                      WHERE bezalt.beziehung_von = bezneu.beziehung_von
800--                        AND bezalt.beziehung_zu  = bezneu.beziehung_zu
801--                        AND bezalt.beziehungsart = bezneu.beziehungsart
802--                        AND bezalt.ogc_fid       < bezneu.ogc_fid
803--                      );
804--      COMMENT ON VIEW beziehungen_redundant IS 'Datenanalyse: alkis_beziehungen zu denen es eine identische neue Version gibt. Fehlersuche bei PostNAS-Trigger fÃŒr Replace.';
805--
806--      CREATE OR REPLACE VIEW beziehungen_redundant_in_delete
807--      AS
808--      SELECT *
809--       FROM alkis_beziehungen AS bezalt
810--       WHERE EXISTS
811--                 (SELECT ogc_fid
812--                       FROM alkis_beziehungen AS bezneu
813--                      WHERE bezalt.beziehung_von = bezneu.beziehung_von
814--                        AND bezalt.beziehung_zu  = bezneu.beziehung_zu
815--                        AND bezalt.beziehungsart = bezneu.beziehungsart
816--                        AND bezalt.ogc_fid       < bezneu.ogc_fid
817--                      )
818--               -- mit dem Zusatz nur die Faelle aus dem letzten Durchlauf,
819--               -- die aktuell noch in der Delete-Tabelle stehen
820--               AND EXISTS
821--                      (SELECT ogc_fid
822--                       FROM delete
823--                       WHERE bezalt.beziehung_von = substr(featureid, 1, 16)
824--                              OR bezalt.beziehung_zu  = substr(featureid, 1, 16)
825--                      );
826--      COMMENT ON VIEW beziehungen_redundant_in_delete IS 'Datenanalyse: alkis_beziehungen zu denen es eine identische neue Version gibt und wo das Objekt noch in der delete-Tabelle vorkommt. Fehlersuche bei PostNAS-Trigger fÃŒr Replace.';
827
828
829-- Suche nach Fehler durch "Replace"
830-- Wenn ax_flurstueck ÃŒber "replace" ausgetauscht wird und dabei gleichzeitig eine andere
831-- Buchungsstelle bekommt, dann bleibt die alte Buchungsstelle in den alkis_beziehungen.
832-- Mail PostNAS Mailingliste von 2013-02-20
833CREATE OR REPLACE VIEW mehrfache_buchung_zu_fs
834AS
835  SELECT f.gml_id, count(b.ogc_fid) AS anzahl
836    FROM ax_flurstueck f
837    JOIN alkis_beziehungen b
838      ON f.gml_id = b.beziehung_von
839  WHERE b.beziehungsart = 'istGebucht'
840  GROUP BY f.gml_id
841  HAVING count(b.ogc_fid) > 1;
842
843-- Noch einfacher? - Auch ohne JOIN wird das selbe Ergebnis geliefert.
844-- Doppelte Verweise zÀhlen ohne zu prÌfen, ob die gml_id in ax_flurstueck existiert.
845--   SELECT b.beziehung_von, count(b.ogc_fid) AS anzahl
846--     FROM alkis_beziehungen b
847--    WHERE b.beziehungsart = 'istGebucht'
848--   GROUP BY b.beziehung_von
849--   HAVING count(b.ogc_fid) > 1;
850
851COMMENT ON VIEW mehrfache_buchung_zu_fs
852 IS 'Fehler: Nach replace von ax_flurtstueck mit einer neuen ax_buchungsstelle bleibt die alte Verbindung in alkis_beziehungen';
853
854
855-- Suche nach Fehler durch "Replace"
856-- Eine Hausnummer darf nur einem Gebaeude zugeordnet werden.
857-- Das verschieben der Relation
858--   ax_gebaeude   >von>zeigtAuf>zu>  ax_lagebezeichnungmithausnummer
859-- fuehrt möglicherweise dazu, dass die alte Relation nicht gelöscht wird.
860-- Die angezeigten FÀlle sind potentielle Fehler.
861
862CREATE OR REPLACE VIEW fehler_hausnummer_mehrfach_verwendet
863AS
864 SELECT l.gml_id, l.gemeinde, l.lage, l.hausnummer
865   FROM ax_gebaeude g
866   JOIN alkis_beziehungen b ON b.beziehung_von = g.gml_id
867   JOIN ax_lagebezeichnungmithausnummer l ON b.beziehung_zu = l.gml_id
868  WHERE b.beziehungsart = 'zeigtAuf'
869  GROUP BY l.gml_id, l.gemeinde, l.lage, l.hausnummer
870  HAVING count(g.gml_id) > 1;
871
872COMMENT ON VIEW fehler_hausnummer_mehrfach_verwendet
873 IS 'Fehler: Nach replace von ax_lagebezeichnungmithausnummer mit einem neuen ax_gebaeude bleibt die alte Verbindung in alkis_beziehungen';
874
875
876-- Der umgekehrt Fall ist erlaubt.
877-- GebÀude hat mehrere Nummern.
878
879CREATE OR REPLACE VIEW adressen_zu_gebauede_mit_mehreren_hausnummern
880AS
881 SELECT l.gml_id, l.gemeinde, l.lage, l.hausnummer -- Anzeige der Adressfelder
882 FROM ax_gebaeude g1
883   JOIN alkis_beziehungen b ON b.beziehung_von = g1.gml_id
884   JOIN ax_lagebezeichnungmithausnummer l ON b.beziehung_zu = l.gml_id
885  WHERE b.beziehungsart = 'zeigtAuf' AND g1.gml_id IN -- Subquery sucht GebÀude mit meherern Hausnummen
886   (SELECT g2.gml_id
887    FROM ax_gebaeude g2
888    JOIN alkis_beziehungen b ON b.beziehung_von = g2.gml_id
889    JOIN ax_lagebezeichnungmithausnummer l ON b.beziehung_zu = l.gml_id
890   WHERE b.beziehungsart = 'zeigtAuf'
891   GROUP BY g2.gml_id
892   HAVING count(l.gml_id) > 1);
893
894COMMENT ON VIEW adressen_zu_gebauede_mit_mehreren_hausnummern
895 IS 'GebÀude mit mehreren Hausnummern suchen (ist erlaubt) und dazu die Adressen anzeigen.';
896
897
898-- Analyse der Buchungs-Arten im Bestand
899CREATE OR REPLACE VIEW buchungsarten_vorkommend
900AS
901  SELECT a.wert, a.bezeichner,
902         count(b.gml_id) AS anzahl_buchungen
903    FROM ax_buchungsstelle_buchungsart a
904    JOIN ax_buchungsstelle b  ON a.wert = b.buchungsart
905GROUP BY a.wert, a.bezeichner
906ORDER BY a.wert, a.bezeichner;
907
908COMMENT ON VIEW buchungsarten_vorkommend
909 IS 'Welche Arten von Buchungsart kommen in dieser Datenbank tÀtsÀchlich vor?.';
910
911
912-- Analyse: FÀlle mit Erbbaurecht
913-- Benutzt den Baustein-View "doppelverbindung"
914CREATE OR REPLACE VIEW erbbaurechte_suchen
915AS
916  SELECT f.gml_id,
917  --f.flurstueckskennzeichen,
918    f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler AS fssuch, f.nenner
919   FROM ax_flurstueck    f
920   JOIN doppelverbindung d     -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von BS an BS
921     ON d.fsgml = f.gml_id
922   JOIN ax_buchungsstelle s    -- Buchungs-Stelle
923     ON d.bsgml = s.gml_id
924   WHERE s.buchungsart = 2101;
925
926COMMENT ON VIEW erbbaurechte_suchen
927 IS 'Suche nach FÀllen mit Buchungsrt 2101=Erbbaurecht';
928
929
930-- Probleme mit der Trigger-Function "update_fields_beziehungen()"
931-- Manchmal kann zu einer gml_id in "alkis_beziehungen" die zustÀndige Tabelle nicht gefunden werden.
932-- Nach Änderung der Trigger-Function am 10.12.2013 wird die Beziehung trotzdem eingetragen,
933-- nur die Felder "von_typename" und "beginnt" bleiben leer.
934-- 2014-01-31: Dieser Trigger wird nicht mehr verwendet.
935
936     -- Diese FÀlle anzeigen:
937--     CREATE OR REPLACE VIEW beziehungsproblem_faelle
938--    AS
939--       SELECT *
940--        FROM alkis_beziehungen
941--        WHERE beginnt IS NULL;
942--     COMMENT ON VIEW beziehungsproblem_faelle
943--      IS 'Fehlersuche: Im Trigger "update_fields_beziehungen()" wurde das Objekt in seiner Tabelle nicht gefunden, darum kein "beginnt" in "alkis_beziehungen" eingetragen.';
944
945     -- Wie viele sind das?
946--     CREATE OR REPLACE VIEW beziehungsproblem_zaehler
947--     AS
948--       SELECT count(ogc_fid) AS anzahl
949--         FROM alkis_beziehungen
950--        WHERE beginnt IS NULL;
951--     COMMENT ON VIEW beziehungsproblem_faelle
952--      IS 'Fehlersuche: Wie oft fehlt das beginnt-Feld in alkis_beziehungen?';
953
954
955-- Suchen von Gewannenbezeichnungen, die auch als Straßenname verwendet werden.
956-- Diese FÀlle fÃŒhren möglicherweise zu unvollstÀndiger Ausgabe beim Export "alle FlurstÃŒcke an einer Straße"
957-- weil nur Lagebezeichnung MIT und OHNE Hausnummer gesucht wird, aber keine gleich lautende Gewanne.
958CREATE OR REPLACE VIEW strasse_als_gewanne
959AS
960  SELECT k.gemeinde, k.lage AS strassenschluessel,
961      -- k.bezeichnung      AS strassenname,
962         o.unverschluesselt AS gewanne,
963         count(fo.gml_id) AS anzahl_fs_gewanne
964  FROM ax_lagebezeichnungkatalogeintrag k   -- Straßentabelle
965  JOIN ax_lagebezeichnungohnehausnummer o   -- Gewanne
966    ON k.bezeichnung = o.unverschluesselt   -- Gleiche Namen
967  -- Join Gewanne auf FlurstÃŒcke um nur solche FÀlle anzuzeigen, die verwendet werden
968  -- UND die auch in der gleichen Gemeinde liegen.
969  -- Sonst könnte zufÀllige Namensgleichheiten aus verschiedenen Gemeinden geben.
970  JOIN alkis_beziehungen vo
971    ON o.gml_id=vo.beziehung_zu AND vo.beziehungsart= 'zeigtAuf'
972  JOIN ax_flurstueck fo
973    ON fo.gml_id=vo.beziehung_von
974 WHERE fo.gemeinde = k.gemeinde  -- Gewanne wird fÃŒr ein Flst. in gleicher Gemeinde verwendet, wie der StraßenschlÃŒssel
975  GROUP BY k.gemeinde, k.lage, o.unverschluesselt --, k.bezeichnung
976  ORDER BY k.gemeinde, k.lage, o.unverschluesselt --, k.bezeichnung
977  ;
978
979COMMENT ON VIEW strasse_als_gewanne
980 IS 'Gewannenbezeichnungen, die auch als Straßenname verwendet werden. Mit FlurstÃŒcks-ZÀhler.';
981
982
983-- Wie zuvor, aber die FlurstÌcke werden hier nicht nur gezÀhlt sondern auch aufgelistet.
984-- das Format des FlusrtÃŒckskennzeichens kann in die Mapbender-Navigation eingegeben werden.
985CREATE OR REPLACE VIEW strasse_als_gewanne_flst
986AS
987  SELECT -- fo.gml_id,
988         fo.gemarkungsnummer || '-' || fo.flurnummer || '-' || fo.zaehler::text || COALESCE ('/' || fo.nenner::text, '') AS flstkennz,
989         k.gemeinde,
990         o.unverschluesselt AS gewanne,
991      -- k.bezeichnung AS strassenname,
992         k.lage        -- AS strassen_schluessel
993  FROM ax_lagebezeichnungkatalogeintrag k   -- Straßentabelle
994  JOIN ax_lagebezeichnungohnehausnummer o   -- Gewanne
995    ON k.bezeichnung = o.unverschluesselt   -- Gleiche Namen
996  JOIN alkis_beziehungen vo
997    ON o.gml_id=vo.beziehung_zu AND vo.beziehungsart= 'zeigtAuf'
998  JOIN ax_flurstueck fo
999    ON fo.gml_id=vo.beziehung_von
1000 WHERE fo.gemeinde = k.gemeinde  -- Gewanne wird fÃŒr ein Flst. in gleicher Gemeinde verwendet, wie der StraßenschlÃŒssel
1001  ORDER BY fo.gemarkungsnummer, fo.flurnummer, fo.zaehler, k.gemeinde, k.bezeichnung;
1002
1003COMMENT ON VIEW strasse_als_gewanne_flst
1004 IS 'FlurstÃŒcke mit Gewannenbezeichnungen, die auch als Straßenname verwendet werden.';
1005
1006
1007
1008-- Suche nach Fehlern in den Daten, die moeglicherweise aus der Migration stammen und
1009-- im Rahmen der Nachmigration noch korrigiert werden muessen.
1010
1011CREATE OR REPLACE VIEW nachmigration_aehnliche_anschriften
1012AS
1013  SELECT DISTINCT p.gml_id, p.nachnameoderfirma, p.vorname,
1014        a1.ort_post, a1.strasse AS strasse1, a2.strasse AS strasse2, a1.hausnummer
1015     -- , b1.import_id AS import1, b2.import_id AS import2
1016    FROM ax_person         p
1017    JOIN alkis_beziehungen b1 ON b1.beziehung_von=p.gml_id
1018    JOIN ax_anschrift      a1 ON a1.gml_id=b1.beziehung_zu
1019    JOIN alkis_beziehungen b2 ON b2.beziehung_von=p.gml_id
1020    JOIN ax_anschrift      a2 ON a2.gml_id=b2.beziehung_zu
1021    WHERE b1.beziehungsart='hat'
1022      AND b2.beziehungsart='hat'
1023      AND a1.gml_id    <>  a2.gml_id
1024      AND a1.ort_post   =  a2.ort_post
1025      AND a1.strasse    like trim(a2.strasse, '.') || '%'
1026      AND a1.hausnummer =  a2.hausnummer
1027    ORDER BY p.nachnameoderfirma, p.vorname;
1028
1029COMMENT ON VIEW nachmigration_aehnliche_anschriften
1030 IS '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.';
1031
1032
1033-- END --
Note: See TracBrowser for help on using the repository browser.