source: Version-0.8/import/sichten.sql @ 324

Revision 324, 51.6 KB checked in by frank.jaeger, 10 years ago (diff)

Beginn der Umstellung von Datenbank-Schema und Views.
Noch nicht ausgetestet.
Programme noch nicht umgestellt.

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