source: trunk/import/norgis_alkis_pp/postcreate.d/6_views_exp_csv.sql @ 431

Revision 431, 36.1 KB checked in by frank.jaeger, 18 months ago (diff)

kleine Fehlerkorrekturen an der Auskunft
Line 
1
2-- ===================================================================
3--  ALKIS-PostGIS (Full-Schema) - Buchauskunft: Export von CSV-Dateien
4-- ===================================================================
5
6-- Aufgabe:       Views bereit stellen fÃŒr den CSV-Export in der ALKIS-Buch-Auskunft.
7-- AusfÃŒhrung in: Step "postcreate.d" des ALKIS-Importers. Hier also nur Definitionen machen, keine Daten bearbeiten.
8
9--Stand
10--  2016-02-23
11--  2016-11-24 Index auf ax.person.nachnameoderfirma fÃŒr WMS Flurstuecke_kommunal
12--  2016-12-01 exp_csv: Fehlerkorrektur bei EntschlÃŒsselung der Buchungsart
13--             Optimierte Version (ohne Doppelverbindung)
14--  2017-02-23 Spalte "fall" aus View "flst_an_strasse" im View "exp_csv_str" ausgeben.
15--             Darauf basiert ein neuer Filter im Programm "alkisexport.php".
16--                      Sortierung FS-Kennz. repariert in Views "exp_csv" und "exp_csv_str"
17--  2017-10-10 Berechtigungen rausgenommen in getrennte Datei
18--  2017-12-06 Umbenennung von "x_classic2norgis.sql" nach "views_exp_csv.sql"
19--  2017-12-20 Beseitigung des Fehlers: Mehrere Adressen zur Person fuehren zu mehrfachen Zeilen (Wiederholung FlurstÃŒck und Flaeche ..)
20--             Es wird je Person nur noch die "letzte" Adresse verwendet (subquery).
21--             Der View "exp_csv_str" ist jetzt direkt als 4fach-UNION (2x2) codiert statt mit eingebettetem gespeicherten 2fach-Union.
22--             Hauptvorteil: Keine undurchsichtigen AbhÀngigkeiten. Ist auch etwas schneller.
23--  2017-12-20 Suche (Sortierung) 'letzte Adresse' nach "beginnt" statt nach "gml_id"
24--  2018-11-06 Beginn der Umstellung auf Full-Schema (derzeit DEV, gdal 2.4)
25--  2019-04-03 Schema variabel machen:  :"alkis_schema", :"postgis_schema"
26--  2019-05-08 Schema auch in Subquery
27--  2020-02-20 Ersetzen des View "alkis_wertearten" (Simulation der alten zentralen Key-Value-Tabelle) durch einzelne SchlÃŒsseltabellen.
28--  2022-11-02 Auskommentierte JOIN ÃŒber "alkis_wertearten" entgÃŒltig entfernt,
29--             Spalte "gsgml" (ax_buchungsstelle.gml_id) hinzugefÃŒgt. Ist Filter bei Aufruf von "alkisexport.php" aus "alkisgsnw.php".
30--             Bei "Recht an": Filter fÃŒr dienende BS, Werte aus herrschenden BS.
31
32-- ToDo:
33--  - die alten Übersichten "pp_flur", "pp_gemarkung" und "pp_gemeinde" wieder erzeugen
34--    Siehe Script: "pp_gebiete.sql" (FÃŒllen) und Teile von "pp_definition.sql" (DB-Struktur).
35
36
37-- I n d i c e s
38-- ============================
39-- Tabelle "ax_gemarkungsteilflur" sollte Such-Index erhalten auf "gemarkung".
40-- FÃŒr Auflistung aller Fluren zur Gemarkung in Script "alkisnav_fls", Function "EineGemarkung".
41
42
43-- Der WMS "Flurstuecke Kommunal" (aus View "st_flurst[gkz][e|r]") wird langsam, wenn er in einer kreisweiten Datenbank ausgefÃŒhrt wird.
44-- Das Filter-Feld benötigt einen Index:
45DROP INDEX if exists :"alkis_schema".ax_person_nname;
46CREATE INDEX ax_person_nname
47  ON :"alkis_schema".ax_person USING btree (nachnameoderfirma COLLATE pg_catalog."default");
48
49
50-- V i e w s
51-- ============================
52
53-- Generelle Export-Struktur "FlurstÃŒck - Buchung - Grundbuch - Person"
54-- --------------------------------------------------------------------
55-- Wird benötigt im Auskunft-Modul "alkisexport.php":
56-- Je nach aufrufendem Modul wird der Filter (WHERE) an anderer Stelle gesetzt (gml_id von FS, GB oder Pers.)
57-- FÃŒr Filter nach "Straße" siehe die nachfolgende Sonderversion "exp_csv_str".
58
59-- Problem / Konflikt:
60-- Es kann nur eine lineare Struktur aus Spalten und Zeilen exportiert werden.
61-- Wenn nicht nur die Daten des Ausgangs-Objektes exportiert werden, sondern auch verbundene Tabellen in
62-- einer 1:N-Struktur, dann verdoppeln sich Zeileninhalte und es werden redundante Daten erzeugt.
63-- Diese Redundanzen mÃŒssen vom dem Programm gefiltert werden, das die Daten ÃŒber eine Schnittstelle einliest.
64-- Anwendungs-Beispiel: Abrechnung von AnliegerbeitrÀgen.
65
66DROP VIEW IF EXISTS :"alkis_schema".exp_csv;
67CREATE OR REPLACE VIEW :"alkis_schema".exp_csv
68AS
69 SELECT -- Fall: einfache Buchung (ohne "Recht an")
70  -- F l u r s t ÃŒ c k
71    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
72    f.flurstueckskennzeichen             AS fs_kennz,
73    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
74    f.flurnummer, f.zaehler, f.nenner,
75    f.amtlicheflaeche                    AS fs_flae,
76    g.bezeichnung                        AS gemarkung,
77  -- G r u n d b u c h
78    gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
79    gb.bezirk                            AS gb_bezirk,
80    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
81    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
82  -- B u c h u n g s s t e l l e  (GrundstÃŒck)
83    s.gml_id                             AS gsgml,       -- möglicher Filter GrundstÃŒck-GML-ID
84    s.laufendenummer                     AS bu_lfd,      -- BVNR
85    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
86    s.buchungsart,                                       -- verschlÃŒsselt
87    wb.beschreibung                      AS bu_art,      -- Buchungsart entschlÃŒsselt
88  -- N a m e n s N u m m e r  (Normalfall mit Person)
89    nn.laufendenummernachdin1421         AS nam_lfd,
90    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,     -- als Excel-Formel
91  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
92    rg.artderrechtsgemeinschaft          AS nam_adr,
93    rg.beschreibung                      AS nam_adrv,    -- Art der Rechtsgem. - Value zum Key
94    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
95  -- P e r s o n
96    p.gml_id                             AS psgml,       -- möglicher Filter Personen-GML-ID
97    p.anrede,                                            -- Anrede key
98    wp.beschreibung                      AS anrv,        -- Anrede Value zum Key
99    p.vorname,
100    p.namensbestandteil,
101    p.nachnameoderfirma,                                 -- Familienname
102    p.geburtsdatum,
103  -- A d r e s s e  der Person
104    a.postleitzahlpostzustellung         AS plz,
105    a.ort_post                           AS ort,         -- Anschreifenzeile 1: PLZ+Ort
106    a.strasse,  a.hausnummer,                            -- Anschriftenzeile 2: Straße+HsNr
107    a.bestimmungsland                    AS land
108  FROM :"alkis_schema".ax_flurstueck    f               -- FlurstÃŒck
109  JOIN :"alkis_schema".ax_gemarkung g                   -- entschlÃŒsseln
110    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
111  JOIN :"alkis_schema".ax_buchungsstelle s              -- FS >istGebucht> Buchungstelle
112    ON f.istgebucht = s.gml_id
113  JOIN :"alkis_schema".ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
114    ON gb.gml_id = s.istbestandteilvon
115  JOIN :"alkis_schema".ax_buchungsblattbezirk z
116    ON gb.land=z.land AND gb.bezirk=z.bezirk
117  JOIN :"alkis_schema".ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
118    ON gb.gml_id = nn.istbestandteilvon
119  JOIN :"alkis_schema".ax_person p                      -- NamNum >benennt> Person
120    ON p.gml_id = nn.benennt
121  LEFT JOIN :"alkis_schema".ax_anschrift a              -- nur die "letzte" Anschrift zur Person verwenden
122    ON a.gml_id = (SELECT gml_id FROM :"alkis_schema".ax_anschrift an WHERE an.gml_id = ANY(p.hat) AND an.endet IS NULL ORDER BY an.beginnt DESC LIMIT 1)
123  -- E n t s c h l ÃŒ s s e l n:
124  LEFT JOIN :"alkis_schema".ax_anrede_person wp         -- EntschlÃŒsseln "Person - Anrede"
125    ON p.anrede = wp.wert
126  LEFT JOIN :"alkis_schema".ax_buchungsart_buchungsstelle wb -- EntschlÃŒsseln "Buchungsart"
127    ON s.buchungsart = wb.wert  -- wb.beschreibung
128 
129  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
130  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
131  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
132  LEFT JOIN
133   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.beschreibung
134       FROM :"alkis_schema".ax_namensnummer r
135       JOIN :"alkis_schema".ax_buchungsblatt gr
136         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
137      LEFT JOIN :"alkis_schema".ax_artderrechtsgemeinschaft_namensnummer wr
138        ON r.artderrechtsgemeinschaft = wr.wert
139
140      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
141   ON rg.gml_id = gb.gml_id  -- zum GB
142  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL
143---------
144  UNION
145---------
146 SELECT  -- Fall: "Recht an"
147  -- F l u r s t ÃŒ c k
148    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
149    f.flurstueckskennzeichen             AS fs_kennz,
150    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
151    f.flurnummer, f.zaehler, f.nenner,
152    f.amtlicheflaeche                    AS fs_flae,
153    g.bezeichnung                        AS gemarkung,
154  -- G r u n d b u c h
155    gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
156    gb.bezirk                            AS gb_bezirk,
157    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
158    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
159  -- B u c h u n g s s t e l l e  (GrundstÃŒck)
160    dien.gml_id                          AS gsgml,       -- 2022-11-02: möglicher Filter GrundstÃŒck-GML-ID (dienende Buchung)
161    s.laufendenummer                     AS bu_lfd,      -- BVNR
162    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
163    s.buchungsart,                                       -- verschlÃŒsselt
164    wb.beschreibung                      AS bu_art,      -- Buchungsart entschlÃŒsselt
165  -- N a m e n s N u m m e r  (Normalfall mit Person)
166    nn.laufendenummernachdin1421         AS nam_lfd,
167    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,     -- als Excel-Formel
168  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
169    rg.artderrechtsgemeinschaft          AS nam_adr,
170    rg.beschreibung                      AS nam_adrv,    -- Art der Rechtsgem. - Value zum Key
171    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
172  -- P e r s o n
173    p.gml_id                             AS psgml,       -- möglicher Filter Personen-GML-ID
174    p.anrede,                                            -- Anrede key
175    wp.beschreibung                      AS anrv,        -- Anrede Value zum Key
176    p.vorname,
177    p.namensbestandteil,
178    p.nachnameoderfirma,                                 -- Familienname
179    p.geburtsdatum,
180  -- A d r e s s e  der Person
181    a.postleitzahlpostzustellung         AS plz,
182    a.ort_post                           AS ort,         -- Anschreifenzeile 1: PLZ+Ort
183    a.strasse,  a.hausnummer,                            -- Anschriftenzeile 2: Straße+HsNr
184    a.bestimmungsland                    AS land
185  FROM :"alkis_schema".ax_flurstueck f                  -- FlurstÃŒck
186  JOIN :"alkis_schema".ax_gemarkung g                   -- entschlÃŒsseln
187    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
188  -- FS >istGebucht> Buchungstelle  <an<  Buchungstelle
189 -- Variante mit 2 Buchungs-Stellen (Recht An)
190  JOIN :"alkis_schema".ax_buchungsstelle dien           -- dienende Buchung
191    ON f.istgebucht = dien.gml_id
192  JOIN :"alkis_schema".ax_buchungsstelle s              -- herrschende Buchung
193    ON dien.gml_id = ANY (s.an)                         -- hat Recht an
194  JOIN :"alkis_schema".ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
195    ON gb.gml_id = s.istbestandteilvon
196  JOIN :"alkis_schema".ax_buchungsblattbezirk z
197    ON gb.land=z.land AND gb.bezirk=z.bezirk
198  JOIN :"alkis_schema".ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
199    ON gb.gml_id = nn.istbestandteilvon
200  JOIN :"alkis_schema".ax_person p                      -- NamNum >benennt> Person
201    ON p.gml_id = nn.benennt
202  LEFT JOIN :"alkis_schema".ax_anschrift a              -- nur die "letzte" Anschrift zur Person verwenden
203    ON a.gml_id = (SELECT gml_id FROM :"alkis_schema".ax_anschrift an WHERE an.gml_id = ANY(p.hat) AND an.endet IS NULL ORDER BY an.beginnt DESC LIMIT 1)
204  -- E n t s c h l ÃŒ s s e l n:
205  LEFT JOIN :"alkis_schema".ax_anrede_person wp         -- EntschlÃŒsseln "Person - Anrede"
206    ON p.anrede = wp.wert
207  LEFT JOIN :"alkis_schema".ax_buchungsart_buchungsstelle wb -- EntschlÃŒsseln "Buchungsart"
208    ON s.buchungsart = wb.wert
209  LEFT JOIN
210   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.beschreibung
211       FROM :"alkis_schema".ax_namensnummer r
212       JOIN :"alkis_schema".ax_buchungsblatt gr
213         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
214      LEFT JOIN :"alkis_schema".ax_artderrechtsgemeinschaft_namensnummer wr
215        ON r.artderrechtsgemeinschaft = wr.wert
216               
217      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
218   ON rg.gml_id = gb.gml_id  -- zum GB
219  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL
220    AND dien.endet IS NULL
221
222ORDER BY fs_kennz,   -- f.flurstueckskennzeichen,
223      -- fsgml,     
224         gb_bezirk,  -- gb.bezirk,
225         gb_blatt,   -- gb.buchungsblattnummermitbuchstabenerweiterung,
226         bu_lfd,     -- s.laufendenummer,
227         nam_lfd;    -- nn.laufendenummernachdin1421
228
229COMMENT ON VIEW :"alkis_schema".exp_csv
230 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.';
231
232
233-- Eine Variante des View "exp_csv":
234-- Hier wird zusÀtzlich die Lagebezeichnung zum FlurstÌck angebunden in den Varianten MIT/OHNE Hausnummer.
235-- Der Filter "WHERE stgml= " auf die "gml_id" von "ax_lagebezeichnungkatalogeintrag" sollte gesetzt werden,
236-- um nur die FlurstÃŒcke zu bekommen, die an einer Straße liegen.
237
238DROP VIEW IF EXISTS :"alkis_schema".exp_csv_str;
239
240CREATE OR REPLACE VIEW :"alkis_schema".exp_csv_str
241AS      -- Version mit 4fach-UNION (2x2 FÀlle) statt eingebauter View "flst_an_strasse"
242 SELECT -- Fall 1: einfache Buchung (ohne Recht an)  // Lagebezeichnung MIT Hausnummer
243    sm.gml_id                            AS stgml,       -- Filter: gml_id der Straße aus "ax_lagebezeichnungkatalogeintrag"
244    'm'                                  AS fall,        -- SÀtze unterschieden: MIT HsNr
245  -- F l u r s t ÃŒ c k
246    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
247    f.flurstueckskennzeichen             AS fs_kennz,
248    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
249    f.flurnummer, f.zaehler, f.nenner,
250    f.amtlicheflaeche                    AS fs_flae,
251    g.bezeichnung                        AS gemarkung,
252  -- G r u n d b u c h
253  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
254    gb.bezirk                            AS gb_bezirk,
255    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
256    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
257  -- B u c h u n g s s t e l l e  (GrundstÃŒck)
258    s.laufendenummer                     AS bu_lfd,      -- BVNR
259    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
260    s.buchungsart,                                       -- verschlÃŒsselt
261    wb.beschreibung                      AS bu_art,      -- Buchungsart entschlÃŒsselt
262  -- N a m e n s N u m m e r  (Normalfall mit Person)
263    nn.laufendenummernachdin1421         AS nam_lfd,
264    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,     -- als Excel-Formel
265  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
266    rg.artderrechtsgemeinschaft          AS nam_adr,
267    rg.beschreibung                      AS nam_adrv,    -- Art der Rechtsgem. - Value zum Key
268    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
269  -- P e r s o n
270  --p.gml_id                             AS psgml,       -- möglicher Filter Personen-GML-ID
271    p.anrede,                                            -- Anrede key
272    wp.beschreibung                      AS anrv,        -- Anrede Value zum Key
273    p.vorname,
274    p.namensbestandteil,
275    p.nachnameoderfirma,                                 -- Familienname
276    p.geburtsdatum,
277  -- A d r e s s e  der Person
278    a.postleitzahlpostzustellung         AS plz,
279    a.ort_post                           AS ort,         -- Anschriftenzeile 1: PLZ+Ort
280    a.strasse,  a.hausnummer,                            -- Anschriftenzeile 2: Straße+HsNr
281    a.bestimmungsland                    AS land
282  FROM :"alkis_schema".ax_flurstueck    f               -- FlurstÃŒck
283  -- FlurstÃŒck >weistAuf> ax_lagebezeichnungMIThausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
284  JOIN :"alkis_schema".ax_lagebezeichnungmithausnummer lm -- Lage MIT
285    ON lm.gml_id = ANY (f.weistauf)
286  JOIN :"alkis_schema".ax_lagebezeichnungkatalogeintrag sm
287   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
288   JOIN :"alkis_schema".ax_gemarkung g                   -- entschlÃŒsseln
289    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
290  JOIN :"alkis_schema".ax_buchungsstelle s              -- FS >istGebucht> Buchungstelle
291    ON f.istgebucht = s.gml_id
292  JOIN :"alkis_schema".ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
293    ON gb.gml_id = s.istbestandteilvon
294  JOIN :"alkis_schema".ax_buchungsblattbezirk z
295    ON gb.land=z.land AND gb.bezirk=z.bezirk
296  JOIN :"alkis_schema".ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
297    ON gb.gml_id = nn.istbestandteilvon
298  JOIN :"alkis_schema".ax_person p                      -- NamNum >benennt> Person
299    ON p.gml_id = nn.benennt
300  LEFT JOIN :"alkis_schema".ax_anschrift a              -- nur die "letzte" Anschrift zur Person verwenden
301    ON a.gml_id = (SELECT gml_id FROM :"alkis_schema".ax_anschrift an WHERE an.gml_id = ANY(p.hat) AND an.endet IS NULL ORDER BY an.beginnt DESC LIMIT 1)
302  -- E n t s c h l ÃŒ s s e l n:
303  LEFT JOIN :"alkis_schema".ax_anrede_person wp         -- EntschlÃŒsseln "Person - Anrede"
304    ON p.anrede = wp.wert
305  LEFT JOIN :"alkis_schema".ax_buchungsart_buchungsstelle wb -- EntschlÃŒsseln "Buchungsart"
306    ON s.buchungsart = wb.wert
307   
308  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
309  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
310  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
311  LEFT JOIN
312   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.beschreibung
313       FROM :"alkis_schema".ax_namensnummer r
314       JOIN :"alkis_schema".ax_buchungsblatt gr
315         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
316      LEFT JOIN :"alkis_schema".ax_artderrechtsgemeinschaft_namensnummer wr
317        ON r.artderrechtsgemeinschaft = wr.wert
318
319      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
320   ON rg.gml_id = gb.gml_id  -- zum GB
321  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL AND lm.endet IS NULL 
322---------
323  UNION
324---------
325 SELECT -- Fall 2: 2 Buchungs-Stellen (Recht An)  //  Lagebezeichnung MIT Hausnummer
326    sm.gml_id                            AS stgml,       -- Filter: gml_id der Straße aus "ax_lagebezeichnungkatalogeintrag"
327    'm'                                  AS fall,        -- SÀtze unterschieden: MIT HsNr
328  -- F l u r s t ÃŒ c k
329    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
330    f.flurstueckskennzeichen             AS fs_kennz,
331    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
332    f.flurnummer, f.zaehler, f.nenner,
333    f.amtlicheflaeche                    AS fs_flae,
334    g.bezeichnung                        AS gemarkung,
335  -- G r u n d b u c h
336  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
337    gb.bezirk                            AS gb_bezirk,
338    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
339    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
340  -- B u c h u n g s s t e l l e  (GrundstÃŒck)
341    s.laufendenummer                     AS bu_lfd,      -- BVNR
342    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
343    s.buchungsart,                                       -- verschlÃŒsselt
344    wb.beschreibung                      AS bu_art,      -- Buchungsart entschlÃŒsselt
345  -- N a m e n s N u m m e r  (Normalfall mit Person)
346    nn.laufendenummernachdin1421         AS nam_lfd,
347    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,     -- als Excel-Formel
348  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
349    rg.artderrechtsgemeinschaft          AS nam_adr,
350    rg.beschreibung                      AS nam_adrv,    -- Art der Rechtsgem. - Value zum Key
351    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
352  -- P e r s o n
353  --p.gml_id                             AS psgml,       -- möglicher Filter Personen-GML-ID
354    p.anrede,                                            -- Anrede key
355    wp.beschreibung                      AS anrv,        -- Anrede Value zum Key
356    p.vorname,
357    p.namensbestandteil,
358    p.nachnameoderfirma,                                 -- Familienname
359    p.geburtsdatum,
360   -- A d r e s s e  der Person
361    a.postleitzahlpostzustellung         AS plz,
362    a.ort_post                           AS ort,         -- Anschriftenzeile 1: PLZ+Ort
363    a.strasse,  a.hausnummer,                            -- Anschriftenzeile 2: Straße+HsNr
364    a.bestimmungsland                    AS land
365  FROM :"alkis_schema".ax_flurstueck f                  -- FlurstÃŒck
366  -- FlurstÃŒck >weistAuf> ax_lagebezeichnungMIThausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
367  JOIN :"alkis_schema".ax_lagebezeichnungmithausnummer lm  -- Lage MIT
368    ON lm.gml_id = ANY (f.weistauf)
369  JOIN :"alkis_schema".ax_lagebezeichnungkatalogeintrag sm
370    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
371  JOIN :"alkis_schema".ax_gemarkung g                   -- entschlÃŒsseln
372    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
373  -- FS >istGebucht> Buchungstelle  <an<  Buchungstelle
374 -- Variante mit 2 Buchungs-Stellen (Recht An)
375  JOIN :"alkis_schema".ax_buchungsstelle dien           -- dienende Buchung
376    ON f.istgebucht = dien.gml_id
377  JOIN :"alkis_schema".ax_buchungsstelle s              -- herrschende Buchung
378    ON dien.gml_id = ANY (s.an)         -- hat Recht an
379  JOIN :"alkis_schema".ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
380    ON gb.gml_id = s.istbestandteilvon
381  JOIN :"alkis_schema".ax_buchungsblattbezirk z
382    ON gb.land=z.land AND gb.bezirk=z.bezirk
383  JOIN :"alkis_schema".ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
384    ON gb.gml_id = nn.istbestandteilvon
385  JOIN :"alkis_schema".ax_person p                      -- NamNum >benennt> Person
386    ON p.gml_id = nn.benennt
387  LEFT JOIN :"alkis_schema".ax_anschrift a              -- nur die "letzte" Anschrift zur Person verwenden
388    ON a.gml_id = (SELECT gml_id FROM :"alkis_schema".ax_anschrift an WHERE an.gml_id = ANY(p.hat) AND an.endet IS NULL ORDER BY an.beginnt DESC LIMIT 1)
389  -- E n t s c h l ÃŒ s s e l n:
390  LEFT JOIN :"alkis_schema".ax_anrede_person wp         -- EntschlÃŒsseln "Person - Anrede"
391    ON p.anrede = wp.wert
392  LEFT JOIN :"alkis_schema".ax_buchungsart_buchungsstelle wb -- EntschlÃŒsseln "Buchungsart"
393    ON s.buchungsart = wb.wert
394  LEFT JOIN
395   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.beschreibung
396       FROM :"alkis_schema".ax_namensnummer r
397       JOIN :"alkis_schema".ax_buchungsblatt gr
398         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
399      LEFT JOIN :"alkis_schema".ax_artderrechtsgemeinschaft_namensnummer wr
400        ON r.artderrechtsgemeinschaft = wr.wert
401      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
402   ON rg.gml_id = gb.gml_id  -- zum GB
403  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL
404    AND dien.endet IS NULL AND lm.endet IS NULL 
405---------
406  UNION
407---------
408 SELECT -- Fall 3: einfache Buchung (ohne Recht an)  //  Lagebezeichnung OHNE Hausnummer
409    so.gml_id                            AS stgml,       -- Filter: gml_id der Straße aus "ax_lagebezeichnungkatalogeintrag"
410    'o'                                  AS fall,        -- SÀtze unterschieden: OHNE HsNr
411  -- F l u r s t ÃŒ c k
412    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
413    f.flurstueckskennzeichen             AS fs_kennz,
414    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
415    f.flurnummer, f.zaehler, f.nenner,
416    f.amtlicheflaeche                    AS fs_flae,
417    g.bezeichnung                        AS gemarkung,
418  -- G r u n d b u c h
419  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
420    gb.bezirk                            AS gb_bezirk,
421    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
422    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
423  -- B u c h u n g s s t e l l e  (GrundstÃŒck)
424    s.laufendenummer                     AS bu_lfd,      -- BVNR
425    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
426    s.buchungsart,                                       -- verschlÃŒsselt
427    wb.beschreibung                      AS bu_art,      -- Buchungsart entschlÃŒsselt
428  -- N a m e n s N u m m e r  (Normalfall mit Person)
429    nn.laufendenummernachdin1421         AS nam_lfd,
430    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,     -- als Excel-Formel
431  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
432    rg.artderrechtsgemeinschaft          AS nam_adr,
433    rg.beschreibung                      AS nam_adrv,    -- Art der Rechtsgem. - Value zum Key
434    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
435  -- P e r s o n
436  --p.gml_id                             AS psgml,       -- möglicher Filter Personen-GML-ID
437    p.anrede,                                            -- Anrede key
438    wp.beschreibung                      AS anrv,        -- Anrede Value zum Key
439    p.vorname,
440    p.namensbestandteil,
441    p.nachnameoderfirma,                                 -- Familienname
442    p.geburtsdatum,
443  -- A d r e s s e  der Person
444    a.postleitzahlpostzustellung         AS plz,
445    a.ort_post                           AS ort,         -- Anschriftenzeile 1: PLZ+Ort
446    a.strasse,  a.hausnummer,                            -- Anschriftenzeile 2: Straße+HsNr
447    a.bestimmungsland                    AS land
448  FROM :"alkis_schema".ax_flurstueck    f               -- FlurstÃŒck
449-- FlurstÃŒck >zeigtAuf> ax_lagebezeichnungOHNEhausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
450  JOIN :"alkis_schema".ax_lagebezeichnungohnehausnummer lo -- Lage OHNE
451    ON lo.gml_id = ANY (f.zeigtauf)
452  JOIN :"alkis_schema".ax_lagebezeichnungkatalogeintrag so -- Straße OHNE
453    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
454  JOIN :"alkis_schema".ax_gemarkung g                   -- entschlÃŒsseln
455    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
456  JOIN :"alkis_schema".ax_buchungsstelle s              -- FS >istGebucht> Buchungstelle
457    ON f.istgebucht = s.gml_id
458  JOIN :"alkis_schema".ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
459    ON gb.gml_id = s.istbestandteilvon
460  JOIN :"alkis_schema".ax_buchungsblattbezirk z
461    ON gb.land=z.land AND gb.bezirk=z.bezirk
462  JOIN :"alkis_schema".ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
463    ON gb.gml_id = nn.istbestandteilvon
464  JOIN :"alkis_schema".ax_person p                      -- NamNum >benennt> Person
465    ON p.gml_id = nn.benennt
466  LEFT JOIN :"alkis_schema".ax_anschrift a              -- nur die "letzte" Anschrift zur Person verwenden
467    ON a.gml_id = (SELECT gml_id FROM :"alkis_schema".ax_anschrift an WHERE an.gml_id = ANY(p.hat) AND an.endet IS NULL ORDER BY an.beginnt DESC LIMIT 1)
468  -- E n t s c h l ÃŒ s s e l n:
469  LEFT JOIN :"alkis_schema".ax_anrede_person wp         -- EntschlÃŒsseln "Person - Anrede"
470    ON p.anrede = wp.wert
471  LEFT JOIN :"alkis_schema".ax_buchungsart_buchungsstelle wb -- EntschlÃŒsseln "Buchungsart"
472    ON s.buchungsart = wb.wert
473
474  -- 2mal "LEFT JOIN" verdoppelt die Zeile in der Ausgabe. Darum als Subquery in Spalten packen:
475  -- Noch mal "GB -> NamNum", aber dieses Mal fÃŒr "Rechtsgemeinschaft".
476  -- Kommt max. 1 mal je GB vor und hat keine Relation auf Person.
477  LEFT JOIN
478   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.beschreibung
479       FROM :"alkis_schema".ax_namensnummer r
480       JOIN :"alkis_schema".ax_buchungsblatt gr
481         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
482      LEFT JOIN :"alkis_schema".ax_artderrechtsgemeinschaft_namensnummer wr
483        ON r.artderrechtsgemeinschaft = wr.wert
484               
485      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
486   ON rg.gml_id = gb.gml_id  -- zum GB
487  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL AND lo.endet IS NULL
488---------
489  UNION
490---------
491 SELECT -- Fall 4: 2 Buchungs-Stellen (Recht An)  //  Lagebezeichnung OHNE Hausnummer
492    so.gml_id                            AS stgml,       -- Filter: gml_id der Straße aus "ax_lagebezeichnungkatalogeintrag"
493    'o'                                  AS fall,        -- SÀtze unterschieden: OHNE HsNr
494  -- F l u r s t ÃŒ c k
495    f.gml_id                             AS fsgml,       -- möglicher Filter FlurstÃŒcks-GML-ID
496    f.flurstueckskennzeichen             AS fs_kennz,
497    f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
498    f.flurnummer, f.zaehler, f.nenner,
499    f.amtlicheflaeche                    AS fs_flae,
500    g.bezeichnung                        AS gemarkung,
501  -- G r u n d b u c h
502  --gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
503    gb.bezirk                            AS gb_bezirk,
504    gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
505    z.bezeichnung                        AS beznam,      -- GB-Bezirks-Name
506  -- B u c h u n g s s t e l l e  (GrundstÃŒck)
507    s.laufendenummer                     AS bu_lfd,      -- BVNR
508    '=' || s.zaehler || '/' || s.nenner  AS bu_ant,      -- als Excel-Formel (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
509    s.buchungsart,                                       -- verschlÃŒsselt
510    wb.beschreibung                      AS bu_art,      -- Buchungsart entschlÃŒsselt
511  -- N a m e n s N u m m e r  (Normalfall mit Person)
512    nn.laufendenummernachdin1421         AS nam_lfd,
513    '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,     -- als Excel-Formel
514  -- R e c h t s g e m e i n s c h a f t  (Sonderfall von Namensnummer, ohne Person, ohne Nummer)
515    rg.artderrechtsgemeinschaft          AS nam_adr,
516    rg.beschreibung                      AS nam_adrv,    -- Art der Rechtsgem. - Value zum Key
517    rg.beschriebderrechtsgemeinschaft    AS nam_bes,
518  -- P e r s o n
519  --p.gml_id                             AS psgml,       -- möglicher Filter Personen-GML-ID
520    p.anrede,                                            -- Anrede key
521    wp.beschreibung                      AS anrv,        -- Anrede Value zum Key
522    p.vorname,
523    p.namensbestandteil,
524    p.nachnameoderfirma,                                 -- Familienname
525    p.geburtsdatum,
526   -- A d r e s s e  der Person
527    a.postleitzahlpostzustellung         AS plz,
528    a.ort_post                           AS ort,         -- Anschriftenzeile 1: PLZ+Ort
529    a.strasse,  a.hausnummer,                            -- Anschriftenzeile 2: Straße+HsNr
530    a.bestimmungsland                    AS land
531  FROM :"alkis_schema".ax_flurstueck f                  -- FlurstÃŒck
532-- FlurstÃŒck >zeigtAuf> ax_lagebezeichnungOHNEhausnummer <JOIN> ax_lagebezeichnungkatalogeintrag
533  JOIN :"alkis_schema".ax_lagebezeichnungohnehausnummer lo -- Lage OHNE
534    ON lo.gml_id = ANY (f.zeigtauf)
535  JOIN :"alkis_schema".ax_lagebezeichnungkatalogeintrag so -- Straße OHNE
536    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
537  JOIN :"alkis_schema".ax_gemarkung g                   -- entschlÃŒsseln
538    ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
539  -- FS >istGebucht> Buchungstelle  <an<  Buchungstelle
540 -- Variante mit 2 Buchungs-Stellen (Recht An)
541  JOIN :"alkis_schema".ax_buchungsstelle dien           -- dienende Buchung
542    ON f.istgebucht = dien.gml_id
543  JOIN :"alkis_schema".ax_buchungsstelle s              -- herrschende Buchung
544    ON dien.gml_id = ANY (s.an)         -- hat Recht an
545  JOIN :"alkis_schema".ax_buchungsblatt  gb             -- Buchung >istBestandteilVon> Grundbuchblatt
546    ON gb.gml_id = s.istbestandteilvon
547  JOIN :"alkis_schema".ax_buchungsblattbezirk z
548    ON gb.land=z.land AND gb.bezirk=z.bezirk
549  JOIN :"alkis_schema".ax_namensnummer nn               -- Blatt <istBestandteilVon< NamNum
550    ON gb.gml_id = nn.istbestandteilvon
551  JOIN :"alkis_schema".ax_person p                      -- NamNum >benennt> Person
552    ON p.gml_id = nn.benennt
553  LEFT JOIN :"alkis_schema".ax_anschrift a              -- nur die "letzte" Anschrift zur Person verwenden
554    ON a.gml_id = (SELECT gml_id FROM :"alkis_schema".ax_anschrift an WHERE an.gml_id = ANY(p.hat) AND an.endet IS NULL ORDER BY an.beginnt DESC LIMIT 1)
555  -- E n t s c h l ÃŒ s s e l n:
556  LEFT JOIN :"alkis_schema".ax_anrede_person wp         -- EntschlÃŒsseln "Person - Anrede"
557    ON p.anrede = wp.wert
558  LEFT JOIN :"alkis_schema".ax_buchungsart_buchungsstelle wb -- EntschlÃŒsseln "Buchungsart"
559    ON s.buchungsart = wb.wert
560  LEFT JOIN
561   ( SELECT gr.gml_id, r.artderrechtsgemeinschaft, r.beschriebderrechtsgemeinschaft, wr.beschreibung
562       FROM :"alkis_schema".ax_namensnummer r
563       JOIN :"alkis_schema".ax_buchungsblatt gr
564         ON r.istbestandteilvon = gr.gml_id -- Blatt <istBestandteilVon< NamNum (Rechtsgemeinschaft)
565      LEFT JOIN :"alkis_schema".ax_artderrechtsgemeinschaft_namensnummer wr
566        ON r.artderrechtsgemeinschaft = wr.wert
567
568      WHERE NOT r.artderrechtsgemeinschaft IS NULL ) AS rg -- Rechtsgemeinschaft
569   ON rg.gml_id = gb.gml_id  -- zum GB
570  WHERE f.endet IS NULL AND s.endet IS NULL and gb.endet IS NULL and nn.endet IS NULL AND p.endet IS NULL
571    AND dien.endet IS NULL AND lo.endet IS NULL
572
573ORDER BY fs_kennz,   -- f.flurstueckskennzeichen,
574         gb_bezirk,  -- gb.bezirk,
575         gb_blatt,   -- gb.buchungsblattnummermitbuchstabenerweiterung,
576         bu_lfd,     -- s.laufendenummer,
577         nam_lfd;    -- nn.laufendenummernachdin1421
578
579COMMENT ON VIEW :"alkis_schema".exp_csv_str
580 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.';
581
582-- Berechtigung
583  GRANT SELECT ON TABLE :"alkis_schema".exp_csv          TO mb27;
584  GRANT SELECT ON TABLE :"alkis_schema".exp_csv_str      TO mb27;
585
586  GRANT SELECT ON TABLE :"alkis_schema".exp_csv          TO mb28;
587  GRANT SELECT ON TABLE :"alkis_schema".exp_csv_str      TO mb28;
588
589/* Alle Views eintragen auf DB-Server:
590  for gkz in 070 120 130 140 150 200 210 240 250 260 270 290 300 320 330 340 418 430 ; do  echo $gkz ; psql -d alkis0$gkz -v alkis_schema=public -f /data/alkis-import/postcreate.d/6_views_exp_csv.sql ; done 
591*/
592-- Ende --
Note: See TracBrowser for help on using the repository browser.