source: trunk/import/sichten.sql @ 301

Revision 301, 31.0 KB checked in by frank.jaeger, 7 years ago (diff)
Line 
1-- =====
2-- ALKIS
3-- =====
4
5--  -----------------------------------------
6--  Sichten fÃŒr Fehlersuche und Daten-Analyse
7--  -----------------------------------------
8
9--  Dieses SQL braucht nur bei Bedarf in einer PostNAS-DB verarbeitet werden.
10--  Es werden zusÀtzliche Views eingerichtet, die nur bei Fehlersuche und Analyse (vom Entwickler) benötigt werden.
11
12--  PostNAS 0.7
13
14--  2012-04-17 flstnr_ohne_position
15--  2012-04-24 pauschal Filter 'endet IS NULL' um historische Objekte auszublenden
16--  2012-10-29 Redundanzen in Beziehungen suchen (entstehen durch replace)
17--  2013-02-20 Mehrfache Buchungsstellen zum FS suchen, dies sind Auswirkungen eines Fehlers bei Replace
18--  2013-03-05 Beschriftungen aus ap_pto auseinander sortieren, neuer View "grenzpunkt"
19--  2013-03-12 Optimierung Hausnummern, View "gebaeude_txt" (Funktion und Name)
20--  2013-04-15 UnterdrÃŒcken doppelter Darstellung in den Views 'ap_pto_stra', 'ap_pto_nam', 'ap_pto_rest'
21--  2013-04-16 Thema "BodenschÀtzung" und fehlernde Kommentare zum Views ergÀnzt.
22--             Diese Datei aufgeteilt in "sichten.sql" und "sichten_wms.sql"
23--  2013-10-23 Fehlersuche GebÀude-Hausnummer-Relation
24--  2013-11-26 Neue Views (doppelverbindung)
25--  2014-01-17 View "exp_csv" fÃŒr den Export von CSV-Daten aus der Auskunft mit Modul alkisexport.php.
26
27
28-- Bausteine fÃŒr andere Views:
29-- ---------------------------
30
31-- Ein View, der die Verbindung von FlurstÌck zur Buchung fÌr zwei verschiedene FÀlle herstellt.
32-- Einmal die "normalen" (direkten) Buchungen.
33-- Zweitens ÃŒber die Rechte von Buchungsstellen an anderen Buchungsstellen.
34-- Dies kann als "MittelstÃŒck" in den anderen Views eingefÃŒgt werden.
35
36-- Einfach/Direkt:
37--   FlurstÃŒck   >istGebucht>                         (Buchungs-Stelle)
38--
39-- Mit "Recht an":
40--   FlurstÃŒck   >istGebucht>  Buchungs-Stelle  <an<  (Buchungs-Stelle)
41--                               (dienend)              (herrschend)
42
43--           DROP VIEW public.doppelverbindung;
44CREATE OR REPLACE VIEW public.doppelverbindung
45AS
46  SELECT v1.beziehung_von AS fsgml,       -- gml_id auf FlurstÃŒck - Seite
47         v1.beziehung_zu  AS bsgml,       -- gml_id auf Buchungs  - Seite
48      --'direkt' AS fall,
49         0 AS ba_dien
50    FROM alkis_beziehungen v1
51   WHERE v1.beziehungsart = 'istGebucht'  -- FS --> Buchung
52 UNION
53  -- Buchungstelle  >an>  Buchungstelle  >istBestandteilVon>  BLATT
54  SELECT v2.beziehung_von AS fsgml,        -- gml_id auf FlurstÃŒck - Seite
55         an.beziehung_von AS bsgml,        -- gml_id auf Buchungs  - Seite (herrschendes GB)
56      --'Recht an' AS fall,
57         dien.buchungsart AS ba_dien       -- Ein Feld aus der Zwischen-Buchung zur Fall-Unterscheidung
58    FROM alkis_beziehungen v2
59    JOIN ax_buchungsstelle dien
60      ON  v2.beziehung_zu = dien.gml_id
61    JOIN alkis_beziehungen an
62      ON dien.gml_id = an.beziehung_zu
63   WHERE v2.beziehungsart = 'istGebucht'   -- FS --> Buchung
64     AND an.beziehungsart = 'an';
65
66COMMENT ON VIEW public.doppelverbindung
67 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.';
68
69-- Test-Ausgabe: Ein paar FÀlle mit "Recht an"
70--   SELECT * FROM doppelverbindung WHERE ba_dien > 0 LIMIT 20;
71
72-- Ende "Bausteine"
73
74
75
76-- Generelle Export-Struktur FlurstÃŒck - Buchung - Grundbuch - Person
77-- ------------------------------------------------------------------
78-- Verwendet den gespeicherten View "doppelverbindung".
79-- Wird benötigt im Auskunft-Modul "alkisexport.php":
80-- Je nach aufrufendem Modul wird der Filter (WHERE) an anderer Stelle gesetzt (gml_id von FS, GB oder Pers.)
81
82-- Problem / Konflikt:
83-- Es kann nur eine lineare Struktur aus Spalten und Zeilen exportiert werden.
84-- Wenn nicht nur die Daten des Ausgangs-Objektes exportiert werden, sondern auch verbundene Tabellen in
85-- einer 1:N-Struktur, dann verdoppeln sich Zeileninhalte und es werden redundante Daten erzeugt.
86-- Diese Redundanzen mÃŒssen vom dem Programm gefiltert werden, das die Daten ÃŒber eine Schnittstelle einliest.
87
88-- Anwendungs-Beispiel: Abrechnung von AnliegerbeitrÀgen.
89
90--           DROP VIEW exp_csv;
91CREATE OR REPLACE VIEW exp_csv
92AS
93 SELECT
94  -- ** FlurstÃŒck
95     f.gml_id AS fsgml,                                   -- möglicher Filter FlurstÃŒcks-GML-ID
96     f.flurstueckskennzeichen             AS fs_kennz,
97     f.gemarkungsnummer,                                  -- Teile des FS-Kennz. noch mal einzeln
98     f.flurnummer, f.zaehler, f.nenner,
99     f.amtlicheflaeche                    AS fs_flae,
100
101   -- * Adressen des FS -- Nein, diese werden im Export-Modul ermitteln und nachtragen
102 --  k.bezeichnung                          AS strasse,   -- Straßennamen
103 --  split_part(l.hausnummer,' ',1)         AS hausnr,    -- Nummer
104 --  upper(split_part(l.hausnummer,' ',2))  AS hausnr_zs, -- Zusatz in Großschreibung
105
106  -- ** Grundbuch
107     gb.gml_id                            AS gbgml,       -- möglicher Filter Grundbuch-GML-ID
108     gb.bezirk                            AS gb_bezirk,
109     gb.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
110
111 -- ** Buchung
112     s.laufendenummer                     AS bu_lfd,
113   -- s.zaehler, s.nenner,                                -- Anteil des GB am FS
114     '=' || s.zaehler|| '/' || s.nenner   AS bu_ant,      -- als Excel-Formel  (nur bei Wohnungsgrundbuch JOIN ÃŒber 'Recht an')
115   --s.buchungsart,                                       -- verschlÃŒsselt
116     b.bezeichner                         AS bu_art,      -- entschlÃŒsselt
117
118   -- Felder aus der Subquery "Namens-Zweig"
119     nz.nam_lfd, nz.nam_ant, nz.nam_bes,                  -- nn.*
120     nz.psgml,                                            -- möglicher Filter Personen-GML-ID
121     nz.nachnameoderfirma, nz.vorname, nz.geburtsdatum    -- p.*
122
123  FROM ax_flurstueck    f               -- FlurstÃŒck
124  JOIN doppelverbindung d               -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von BS an BS
125    ON d.fsgml = f.gml_id
126
127  JOIN ax_buchungsstelle s              -- Buchungs-Stelle
128    ON d.bsgml = s.gml_id
129  JOIN ax_buchungsstelle_buchungsart b  -- EnstschlÃŒsselung der Buchungsart
130    ON s.buchungsart = b.wert
131
132--  JOIN alkis_beziehungen v2             -- ax_flurstueck  >weistAuf>  AX_LagebezeichnungMitHausnummer
133--    ON v2.beziehung_von = f.gml_id AND v2.beziehungsart = 'weistAuf'
134--  JOIN ax_lagebezeichnungmithausnummer l
135--    ON v2.beziehung_zu = l.gml_id
136--  JOIN ax_lagebezeichnungkatalogeintrag k
137--   ON l.kreis = k.kreis AND l.gemeinde = k.gemeinde AND l.lage = k.lage
138
139  JOIN alkis_beziehungen v3              -- Grundbuch (zur Buchungs-Stelle)
140    ON s.gml_id = v3.beziehung_von  AND v3.beziehungsart = 'istBestandteilVon'  -- Buchung  --> Blatt
141  JOIN ax_buchungsblatt  gb
142    ON v3.beziehung_zu = gb.gml_id
143
144  LEFT JOIN -- LEFT: fiktives Blatt hat keine Namensnummern, dieser "Zweig" kann leer bleiben.
145  ( SELECT
146    -- Namensnummer
147     v4.beziehung_zu AS v4gml,
148     nn.laufendenummernachdin1421         AS nam_lfd,
149     '=' || nn.zaehler|| '/' || nn.nenner AS nam_ant,         -- als Excel-Formel
150     nn.beschriebderrechtsgemeinschaft    AS nam_bes,
151
152     -- Person (zur Namensnummer)
153     p.gml_id                            AS psgml,            -- möglicher Filter
154     p.nachnameoderfirma,                                     -- Familienname
155     p.vorname,                                               -- Vorname
156     p.geburtsdatum
157
158     -- Adresse des EigentÃŒmers  - Nein
159
160   FROM alkis_beziehungen v4         -- Namensnummer (zum GB-Blatt)
161   JOIN ax_namensnummer nn
162     ON v4.beziehung_von = nn.gml_id
163   JOIN alkis_beziehungen v5         -- Person (zur Namensnummer)
164     ON v5.beziehung_von = nn.gml_id  AND v5.beziehungsart = 'benennt'  -- NamNum   --> Person
165   JOIN ax_person p
166     ON v5.beziehung_zu = p.gml_id
167   WHERE v4.beziehungsart = 'istBestandteilVon'  -- Blatt  --> NamNum
168  ) AS nz   -- Namens-Zweig
169   ON nz.v4gml = gb.gml_id      -- LEFT JOIN: Namenzweig an Grundbuch anbinden
170
171  ORDER BY f.flurstueckskennzeichen, gb.bezirk, gb.buchungsblattnummermitbuchstabenerweiterung, nz.nam_lfd ;
172
173COMMENT ON VIEW exp_csv
174 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.';
175
176-- GRANT SELECT ON TABLE exp_csv TO mb27;       -- User fÃŒr Auskunfts-Programme
177-- GRANT SELECT ON TABLE exp_csv TO alkisbuch;  -- User fÃŒr Auskunfts-Programme RLP-Demo
178
179
180
181-- Welche Karten-Typen ?
182CREATE OR REPLACE VIEW kartentypen_der_texte_fuer_hnr
183AS
184   SELECT DISTINCT advstandardmodell
185   FROM ap_pto p
186   WHERE p.art = 'HNR';
187COMMENT ON VIEW kartentypen_der_texte_fuer_hnr
188  IS 'Datenanalyse: Kartentypen in Tabelle ap_pto fÃŒr Hausnummern.';
189
190-- Dies liefert:
191--  "{DKKM1000}"
192--  "{DKKM1000,DKKM500}"
193--  "{DKKM500}"
194--  NULL
195
196-- Texte, die NICHT dargestellt werden sollen.
197-- -------------------------------------------
198-- Texte und Text-Fragmente aus der Konvertierung ALK+ALB, die noch nicht gelöscht worden sind.
199CREATE OR REPLACE VIEW ap_pto_muell
200AS
201  SELECT p.ogc_fid,
202         p.schriftinhalt,
203         p.art,
204         p.drehwinkel * 57.296 AS winkel, -- * 180 / Pi
205         p.wkb_geometry
206    FROM ap_pto p
207   WHERE not p.schriftinhalt IS NULL
208     AND p.endet IS NULL
209     AND p.art IN ('AOG_AUG','PNR');
210COMMENT ON VIEW ap_pto_muell
211 IS 'Datenanalyse: Beschriftungen aus "ap_pto", die NICHT dargestellt werden sollen.';
212
213
214-- FlurstÃŒcke mit Anzeige der FlurstÃŒcksnummer an der "Standardposition"
215
216-- Nach der Konvertierung aus ALK hat zunÀchst jedes FlurstÌck eine explizit gesetzte Position der FlurstÌcksnummer.
217
218-- Nach einer manuellen Teilung bekommen die neuen FlurstÃŒcke im ALKIS nur dann eine Position,
219-- wenn die Positioin manuell bestimmt (verschoben) wurde.
220-- Wenn die FlurstÃŒcksnummer an ihrer "Standardposition" angezeigt werden soll,
221-- dann wird diese in den Daten (DHK, NAS) nicht gesetzt.
222-- Der Konverter PostNAS konvertiert aber nur die Daten, die er bekommt, er setzt nicht die Standard-Position
223-- fÃŒr die FlurstÃŒcke, die ohne eine manuelle Position kommen.
224
225-- Diese FÀlle identifizieren
226CREATE OR REPLACE VIEW flstnr_ohne_position
227AS
228 SELECT f.gml_id,
229        f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS such -- Suchstring fÃŒr ALKIS-Navigation nach FS-Kennzeichen
230 FROM        ax_flurstueck     f
231   LEFT JOIN alkis_beziehungen v  ON v.beziehung_zu = f.gml_id
232 --LEFT JOIN ap_pto            p  ON p.gml_id       = v.beziehung_von
233  WHERE v.beziehungsart is NULL
234    AND f.endet IS NULL
235--ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler
236  ;
237COMMENT ON VIEW flstnr_ohne_position IS 'Sicht fÌr Datenanalyse: FlurstÌcke ohne manuell gesetzte Position fÌr die PrÀsentation der FS-Nr';
238
239-- Umbruch im Label? z.B. "Schwimm-/nbecken"
240-- Sind 2 Buchstaben in Mapfile bei "WRAP" möglich?
241CREATE OR REPLACE VIEW texte_mit_umbruch
242AS
243 SELECT ogc_fid, schriftinhalt, art
244   FROM ap_pto
245  WHERE not schriftinhalt is null
246    AND schriftinhalt like '%/n%';
247COMMENT ON VIEW texte_mit_umbruch
248 IS 'Sicht fÃŒr Datenanalyse: Vorkommen eines Umbruchs im Label-Text.';
249
250-- EXTENT fÃŒr das Mapfile eines Mandanten ermitteln
251CREATE OR REPLACE VIEW flurstuecks_minmax AS
252 SELECT min(st_xmin(wkb_geometry)) AS r_min,
253        min(st_ymin(wkb_geometry)) AS h_min,
254        max(st_xmax(wkb_geometry)) AS r_max,
255        max(st_ymax(wkb_geometry)) AS h_max
256   FROM ax_flurstueck f
257   WHERE f.endet IS NULL;
258COMMENT ON VIEW flurstuecks_minmax
259 IS 'Sicht fÃŒr Datenanalyse: Maximale Ausdehnung von ax_flurstueck fuer EXTENT-Angabe im Mapfile.';
260
261-- Nach Laden der Keytables:
262CREATE OR REPLACE VIEW baurecht
263AS
264  SELECT r.ogc_fid,
265         r.wkb_geometry,
266         r.gml_id,
267         r.artderfestlegung as adfkey, -- Art der Festlegung - Key
268         r."name",                     -- Eigenname des Gebietes
269         r.stelle,                     -- Stelle Key
270         r.bezeichnung AS rechtbez,    -- Verfahrensnummer
271         a.bezeichner  AS adfbez,      -- Art der Festlegung - Bezeichnung
272         d.bezeichnung AS stellbez     -- Stelle Bezeichnung
273      -- , d.stellenart                -- weiter entschluesseln?
274    FROM ax_bauraumoderbodenordnungsrecht r
275    LEFT JOIN ax_bauraumoderbodenordnungsrecht_artderfestlegung a
276      ON r.artderfestlegung = a.wert
277    LEFT JOIN ax_dienststelle d
278      ON r.land   = d.land
279     AND r.stelle = d.stelle
280  WHERE r.endet IS NULL AND d.endet IS NULL ;
281COMMENT ON VIEW baurecht
282 IS 'Datenanalyse: EnstschlÃŒsselte Felder zu einer FlÀche des Baurechts.';
283
284-- Man glaubt es kaum, aber im ALKIS haben Gemeinde und Gemarkung keinerlei Beziehung miteinander
285-- Nur durch Auswertung der FlurstÃŒcke kann man ermitteln, in welcher Gemeinde eine Gemarkung liegt.
286CREATE OR REPLACE VIEW gemarkung_in_gemeinde
287AS
288  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
289  FROM            ax_flurstueck
290  WHERE           endet IS NULL
291  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer;
292COMMENT ON VIEW gemarkung_in_gemeinde
293 IS 'Welche Gemarkung liegt in welcher Gemeinde? Durch Verweise aus FlurstÃŒck.';
294
295
296-- Untersuchen, welche Geometrie-Typen vorkommen
297CREATE OR REPLACE VIEW arten_von_flurstuecksgeometrie
298AS
299 SELECT   count(gml_id) as anzahl,
300          st_geometrytype(wkb_geometry)
301 FROM     ax_flurstueck
302 WHERE    endet IS NULL
303 GROUP BY st_geometrytype(wkb_geometry);
304COMMENT ON VIEW arten_von_flurstuecksgeometrie IS 'Datenanalyse: vorkommende Geometry-Typen in Tabelle ax_flurstueck.';
305
306
307-- A d r e s s e n
308
309-- Verschluesselte Lagebezeichnung (Strasse und Hausnummer) fuer eine Gemeinde
310-- Schluessel der Gemeinde nach Bedarf anpassen!
311CREATE OR REPLACE VIEW adressen_hausnummern
312AS
313    SELECT
314        s.bezeichnung AS strassenname,
315        g.bezeichnung AS gemeindename,
316        l.land,
317        l.regierungsbezirk,
318        l.kreis,
319        l.gemeinde,
320        l.lage        AS strassenschluessel,
321        l.hausnummer
322    FROM   ax_lagebezeichnungmithausnummer l 
323    JOIN   ax_gemeinde g
324      ON l.kreis=g.kreis
325     AND l.gemeinde=g.gemeinde
326    JOIN   ax_lagebezeichnungkatalogeintrag s
327      ON l.kreis=s.kreis
328     AND l.gemeinde=s.gemeinde
329     AND l.lage = s.lage
330    WHERE l.gemeinde = 40;  -- "40" = Stadt Lage
331COMMENT ON VIEW adressen_hausnummern IS 'Datenanalyse: VerschlÃŒsselte Lagebezeichnung (Straße und Hausnummer) fÃŒr eine Gemeinde. SchlÃŒssel der Gemeinde nach Bedarf anpassen.';
332
333-- Zuordnung dieser Adressen zu Flurstuecken
334-- Schluessel der Gemeinde nach Bedarf anpassen!
335CREATE OR REPLACE VIEW adressen_zum_flurstueck
336AS
337    SELECT
338           f.gemarkungsnummer,
339           f.flurnummer,
340           f.zaehler,
341           f.nenner,
342           g.bezeichnung AS gemeindename,
343           s.bezeichnung AS strassenname,
344           l.lage        AS strassenschluessel,
345           l.hausnummer
346      FROM   ax_flurstueck f
347      JOIN   alkis_beziehungen v
348        ON f.gml_id=v.beziehung_von
349      JOIN   ax_lagebezeichnungmithausnummer l 
350        ON l.gml_id=v.beziehung_zu
351      JOIN   ax_gemeinde g
352        ON l.kreis=g.kreis
353       AND l.gemeinde=g.gemeinde
354      JOIN   ax_lagebezeichnungkatalogeintrag s
355        ON l.kreis=s.kreis
356       AND l.gemeinde=s.gemeinde
357       AND l.lage = s.lage
358     WHERE v.beziehungsart='weistAuf'
359       AND l.gemeinde = 40  -- "40" = Stadt Lage
360     ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler, f.nenner;
361COMMENT ON VIEW adressen_zum_flurstueck IS 'Datenanalyse: Zuordnung von Adressen zu Flurstuecken. SchlÃŒssel der Gemeinde nach Bedarf anpassen.';
362
363-- Punktförmige  P r À s e n t a t i o n s o b j e k t e  (ap_pto)
364-- Ermittlung der vorkommenden Arten
365CREATE OR REPLACE VIEW beschriftung_was_kommt_vor
366AS
367  SELECT DISTINCT art, horizontaleausrichtung, vertikaleausrichtung
368    FROM ap_pto
369   WHERE not schriftinhalt is null
370  ORDER BY art;
371COMMENT ON VIEW beschriftung_was_kommt_vor IS 'Analyse der vorkommenden Kombinationen in ap_pto (Beschriftung)';
372
373-- Ergebnis:
374-- 2013: PostNAS 0.7  (aus 150,260,340)
375-- ------------------
376--      "AOG_AUG"                               "zentrisch";"Basis"  - Schriftinhalkt immer nur "I" ?
377--      "BWF"                                   "zentrisch";"Basis"/"zentrisch";"Mitte"
378--      "BWF_ZUS"                               "zentrisch";"Basis"
379--      "FKT"                                   "zentrisch";"Basis"/"linksbÃŒndig";"Basis"/"zentrisch";"Mitte"
380--      "FKT_TEXT"                              "zentrisch";"Mitte"
381--      "FreierText"                    "zentrisch";"Basis"/"zentrisch";"Mitte"/"linksbÃŒndig";"Basis"
382--      "FreierTextHHO"                 "zentrisch";"Mitte"
383--      "Friedhof"                              "zentrisch";"Basis"
384--      "Gewanne"                               "zentrisch";"Basis"/"zentrisch";"Mitte"
385--      "GFK"                                   "zentrisch";"Basis"/"zentrisch";"Mitte"
386--      "HNR"                                   "zentrisch";"Basis"/"linksbÃŒndig";"Basis"/"zentrisch";"Mitte"  --> Hausnummer, group gebaeude
387--      "HHO"                                   "zentrisch";"Mitte"  -- HHO = objekthoehe zu ax_gebaeude?
388--      "NAM"                                   "zentrisch";"Basis"/"zentrisch";"Mitte"/"linksbÃŒndig";"Basis"
389--      "SPO"                                   "zentrisch";"Basis"/
390--      "Vorratsbehaelter"              "zentrisch";"Basis"
391--      "WeitereHoehe"                  "zentrisch";"Mitte"
392--      "ZAE_NEN"                               "zentrisch";"Basis"
393--      "ZNM"                                   "zentrisch";"Basis"/"linksbÃŒndig";"Basis"
394
395--* Layer "ap_pto_stra"
396--                          hor ; ver / hor ; ver
397--      "BezKlassifizierungStrasse" "zent.";"Basis"     / "linksbÃŒndig";"Basis"
398--      "Platz"                                 "zentrisch";"Basis" / "zentrisch";"Mitte"
399--      "Strasse"                               "zentrisch";"Basis" / "zentrisch";"Mitte" / "linksbÃŒndig";"Basis"
400--      "Weg"                                   "zentrisch";"Basis" / "zentrisch";"Mitte" / "linksbÃŒndig";"Basis"
401
402--* geplanter layer "ap_pto_wasser"
403--      "StehendesGewaesser"    "zentrisch";"Basis"
404--      "Fliessgewaesser"               "zentrisch";"Basis"/"linksbÃŒndig";"Basis"
405
406
407-- FlurstÃŒcke eines EigentÃŒmers
408-- ----------------------------
409
410-- Dieser View liefert nur die (einfache) Buchungsart "GrundstÃŒck"
411-- Solche FÀlle wie "Erbbaurecht an GrundstÌck" oder "Wohnungs-/Teileigentum an aufgeteiltes GrundstÌck"
412-- oder "Miteigentum an aufteteiltes GrundstÃŒck" fehlen in deisere Auswertung.
413-- Dazu siehe: "rechte_eines_eigentuemers".
414
415-- Das Ergbenis ist gedacht fÃŒr den Export als CSV und Weiterverarbeitung mit einer Tabellenkalkulation
416-- oder einer einfachen Datenbank.
417
418-- Auch ein Export als Shape ist moeglich (dafuer: geom hinzugefuegt, Feldnamen gekuerzt)
419-- Kommando:
420--  pgsql2shp -h localhost -p 5432 -f "/data/.../alkis_fs_gemeinde.shp"  [db-name]  public.flurstuecke_eines_eigentuemers
421
422-- Übersicht der Tabellen:
423--
424-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle >istGebucht> Flurstueck
425--                                              *-> Bezirk                *-> Buchungsart     *-> Gemarkung
426
427-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
428
429CREATE OR REPLACE VIEW flurstuecke_eines_eigentuemers
430AS
431   SELECT
432      k.bezeichnung                AS gemarkung,
433      k.gemarkungsnummer           AS gemkg_nr,
434      f.flurnummer                 AS flur,
435      f.zaehler                    AS fs_zaehler,
436      f.nenner                     AS fs_nenner,
437      f.amtlicheflaeche            AS flaeche,
438      f.wkb_geometry               AS geom,  -- fuer Export als Shape
439   -- g.bezirk,
440      b.bezeichnung                AS bezirkname,
441      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
442      g.blattart,
443      s.laufendenummer             AS bvnr,
444      art.bezeichner               AS buchgsart,
445   -- s.zaehler || '/' || s.nenner AS buchg_anteil,
446      n.laufendenummernachdin1421  AS name_num,
447   -- n.zaehler || '/' || n.nenner AS nam_anteil,
448      p.nachnameoderfirma          AS nachname --,
449   -- p.vorname
450   FROM       ax_person              p
451        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
452        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
453        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
454        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
455        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
456        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
457        JOIN  ax_buchungsstelle      s    ON s.gml_id = bgs.beziehung_von
458        JOIN  ax_buchungsstelle_buchungsart art ON s.buchungsart = art.wert
459        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = s.gml_id
460        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
461        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
462   WHERE p.nachnameoderfirma LIKE 'Gemeinde %'   -- ** Bei Bedarf anpassen!
463     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
464     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
465     AND bgs.beziehungsart = 'istBestandteilVon' -- Buchungs-Stelle >> Grundbuch
466     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> Buchungs-Stelle
467     AND p.endet IS NULL AND n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL
468     AND s.endet IS NULL AND f.endet IS NULL AND k.endet IS NULL
469   ORDER BY k.bezeichnung, f.flurnummer, f.zaehler, f.nenner, g.bezirk, g.buchungsblattnummermitbuchstabenerweiterung, s.laufendenummer;
470COMMENT ON VIEW flurstuecke_eines_eigentuemers IS 'Muster fÃŒr Export: Suchkriteriumnach Bedarf anpassen.';
471
472-- Rechte eines EigentÃŒmers
473-- ------------------------
474-- Dieser View sucht speziell die FÀlle wo eine Buchungsstelle ein Recht "an" einer anderen Buchungsstelle hat.
475--  - "Erbbaurecht *an* GrundstÃŒck"
476--  - "Wohnungs-/Teileigentum *an* Aufgeteiltes GrundstÃŒck"
477--  - "Miteigentum *an* Aufteteiltes GrundstÃŒck"
478-- Suchkriterium ist der Name des EigentÃŒmers auf dem "herrschenden" Grundbuch, also dem Besitzer des Rechtes.
479
480-- Diese FÀlle fehlen im View "flurstuecke_eines_eigentuemers".
481
482-- Übersicht der Tabellen:
483-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle-h >an> Stelle-d >istGebucht> Flurstueck
484-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
485
486CREATE OR REPLACE VIEW rechte_eines_eigentuemers
487AS
488   SELECT
489      k.bezeichnung                AS gemarkung,
490      k.gemarkungsnummer           AS gemkg_nr,
491      f.flurnummer                 AS flur,
492      f.zaehler                    AS fs_zaehler,
493      f.nenner                     AS fs_nenner,
494      f.amtlicheflaeche            AS flaeche,
495      f.wkb_geometry               AS geom,  -- fuer Export als Shape
496   -- g.bezirk,
497      b.bezeichnung                AS bezirkname,
498      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
499   -- g.blattart,
500      sh.laufendenummer            AS bvnr_herr,
501      sh.zaehler || '/' || sh.nenner AS buchg_anteil_herr,
502      arth.bezeichner              AS buchgsa_herr,
503      bss.beziehungsart            AS bez_art,
504      artd.bezeichner              AS buchgsa_dien,
505      sd.laufendenummer            AS bvnr_dien,
506   -- sd.zaehler || '/' || sd.nenner AS buchg_anteil_dien,
507      n.laufendenummernachdin1421  AS name_num,
508   -- n.zaehler || '/' || n.nenner AS nam_anteil,
509      p.nachnameoderfirma          AS nachname --, 
510   -- p.vorname
511   FROM       ax_person              p
512        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
513        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
514        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
515        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
516        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
517        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
518        JOIN  ax_buchungsstelle      sh   ON sh.gml_id = bgs.beziehung_von  -- herrschende Buchung
519        JOIN  ax_buchungsstelle_buchungsart arth ON sh.buchungsart = arth.wert
520        JOIN  alkis_beziehungen      bss  ON sh.gml_id = bss.beziehung_von
521        JOIN  ax_buchungsstelle      sd   ON sd.gml_id = bss.beziehung_zu   -- dienende Buchung
522        JOIN  ax_buchungsstelle_buchungsart artd ON sd.buchungsart = artd.wert
523        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = sd.gml_id
524        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
525        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
526   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!
527     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
528     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
529     AND bgs.beziehungsart = 'istBestandteilVon' -- B-Stelle herr   >> Grundbuch
530     AND bss.beziehungsart in ('an','zu')        -- B-Stelle herr.  >> B-Stelle dien.
531     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> B-Stelle dien
532     AND p.endet IS NULL AND n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL
533     AND sh.endet IS NULL AND sd.endet IS NULL AND f.endet IS NULL AND k.endet IS NULL
534   ORDER BY k.bezeichnung, f.flurnummer, f.zaehler, f.nenner, g.bezirk, g.buchungsblattnummermitbuchstabenerweiterung, sh.laufendenummer;
535COMMENT 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.';
536
537-- Die 2 Views nur fuer Entwicklung:
538
539--      CREATE OR REPLACE VIEW beziehungen_redundant
540--      AS
541--       SELECT *
542--         FROM alkis_beziehungen AS bezalt
543--         WHERE EXISTS
544--                 (SELECT ogc_fid
545--                       FROM alkis_beziehungen AS bezneu
546--                      WHERE bezalt.beziehung_von = bezneu.beziehung_von
547--                        AND bezalt.beziehung_zu  = bezneu.beziehung_zu
548--                        AND bezalt.beziehungsart = bezneu.beziehungsart
549--                        AND bezalt.ogc_fid       < bezneu.ogc_fid
550--                      );
551--      COMMENT ON VIEW beziehungen_redundant IS 'Datenanalyse: alkis_beziehungen zu denen es eine identische neue Version gibt. Fehlersuche bei PostNAS-Trigger fÃŒr Replace.';
552--
553--      CREATE OR REPLACE VIEW beziehungen_redundant_in_delete
554--      AS
555--      SELECT *
556--       FROM alkis_beziehungen AS bezalt
557--       WHERE EXISTS
558--                 (SELECT ogc_fid
559--                       FROM alkis_beziehungen AS bezneu
560--                      WHERE bezalt.beziehung_von = bezneu.beziehung_von
561--                        AND bezalt.beziehung_zu  = bezneu.beziehung_zu
562--                        AND bezalt.beziehungsart = bezneu.beziehungsart
563--                        AND bezalt.ogc_fid       < bezneu.ogc_fid
564--                      )
565--               -- mit dem Zusatz nur die Faelle aus dem letzten Durchlauf,
566--               -- die aktuell noch in der Delete-Tabelle stehen
567--               AND EXISTS
568--                      (SELECT ogc_fid
569--                       FROM delete
570--                       WHERE bezalt.beziehung_von = substr(featureid, 1, 16)
571--                              OR bezalt.beziehung_zu  = substr(featureid, 1, 16)
572--                      );
573--      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.';
574
575
576-- Suche nach Fehler durch "Replace"
577-- Wenn ax_flurstueck ÃŒber "replace" ausgetauscht wird und dabei gleichzeitig eine andere
578-- Buchungsstelle bekommt, dann bleibt die alte Buchungsstelle in den alkis_beziehungen.
579-- Mail PostNAS Mailingliste von 2013-02-20
580CREATE OR REPLACE VIEW mehrfache_buchung_zu_fs
581AS
582  SELECT f.gml_id, count(b.ogc_fid) AS anzahl
583    FROM ax_flurstueck f
584    JOIN alkis_beziehungen b
585      ON f.gml_id = b.beziehung_von
586  WHERE b.beziehungsart = 'istGebucht'
587  GROUP BY f.gml_id
588  HAVING count(b.ogc_fid) > 1;
589
590-- Noch einfacher? - Auch ohne JOIN wird das selbe Ergebnis geliefert.
591-- Doppelte Verweise zÀhlen ohne zu prÌfen, ob die gml_id in ax_flurstueck existiert.
592--   SELECT b.beziehung_von, count(b.ogc_fid) AS anzahl
593--     FROM alkis_beziehungen b
594--    WHERE b.beziehungsart = 'istGebucht'
595--   GROUP BY b.beziehung_von
596--   HAVING count(b.ogc_fid) > 1;
597
598COMMENT ON VIEW mehrfache_buchung_zu_fs
599 IS 'Fehler: Nach replace von ax_flurtstueck mit einer neuen ax_buchungsstelle bleibt die alte Verbindung in alkis_beziehungen';
600
601
602-- Suche nach Fehler durch "Replace"
603-- Eine Hausnummer darf nur einem Gebaeude zugeordnet werden.
604-- Das verschieben der Relation
605--   ax_gebaeude   >von>zeigtAuf>zu>  ax_lagebezeichnungmithausnummer
606-- fuehrt möglicherweise dazu, dass die alte Relation nicht gelöscht wird.
607-- Die angezeigten FÀlle sind potentielle Fehler.
608
609CREATE OR REPLACE VIEW fehler_hausnummer_mehrfach_verwendet
610AS
611 SELECT l.gml_id, l.gemeinde, l.lage, l.hausnummer
612   FROM ax_gebaeude g
613   JOIN alkis_beziehungen b ON b.beziehung_von = g.gml_id
614   JOIN ax_lagebezeichnungmithausnummer l ON b.beziehung_zu = l.gml_id
615  WHERE b.beziehungsart = 'zeigtAuf'
616  GROUP BY l.gml_id, l.gemeinde, l.lage, l.hausnummer
617  HAVING count(g.gml_id) > 1;
618
619COMMENT ON VIEW fehler_hausnummer_mehrfach_verwendet
620 IS 'Fehler: Nach replace von ax_lagebezeichnungmithausnummer mit einem neuen ax_gebaeude bleibt die alte Verbindung in alkis_beziehungen';
621
622
623-- Der umgekehrt Fall ist erlaubt.
624-- GebÀude hat mehrere Nummern.
625
626CREATE OR REPLACE VIEW adressen_zu_gebauede_mit_mehreren_hausnummern
627AS
628 SELECT l.gml_id, l.gemeinde, l.lage, l.hausnummer -- Anzeige der Adressfelder
629 FROM ax_gebaeude g1
630   JOIN alkis_beziehungen b ON b.beziehung_von = g1.gml_id
631   JOIN ax_lagebezeichnungmithausnummer l ON b.beziehung_zu = l.gml_id
632  WHERE b.beziehungsart = 'zeigtAuf' AND g1.gml_id IN -- Subquery sucht GebÀude mit meherern Hausnummen
633   (SELECT g2.gml_id
634    FROM ax_gebaeude g2
635    JOIN alkis_beziehungen b ON b.beziehung_von = g2.gml_id
636    JOIN ax_lagebezeichnungmithausnummer l ON b.beziehung_zu = l.gml_id
637   WHERE b.beziehungsart = 'zeigtAuf'
638   GROUP BY g2.gml_id
639   HAVING count(l.gml_id) > 1);
640
641COMMENT ON VIEW adressen_zu_gebauede_mit_mehreren_hausnummern
642 IS 'GebÀude mit mehreren Hausnummern suchen (ist erlaubt) und dazu die Adressen anzeigen.';
643
644
645-- Analyse der Buchungs-Arten im Bestand
646CREATE OR REPLACE VIEW buchungsarten_vorkommend
647AS
648  SELECT a.wert, a.bezeichner,
649         count(b.gml_id) AS anzahl_buchungen
650    FROM ax_buchungsstelle_buchungsart a
651    JOIN ax_buchungsstelle b  ON a.wert = b.buchungsart
652GROUP BY a.wert, a.bezeichner
653ORDER BY a.wert, a.bezeichner;
654
655COMMENT ON VIEW buchungsarten_vorkommend
656 IS 'Welche Arten von Buchungsart kommen in dieser Datenbank tÀtsÀchlich vor?.';
657
658
659-- Analyse: FÀlle mit Erbbaurecht
660-- Benutzt den Baustein-View "doppelverbindung"
661CREATE OR REPLACE VIEW erbbaurechte_suchen
662AS
663  SELECT f.gml_id,
664  --f.flurstueckskennzeichen,
665    f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler AS fssuch, f.nenner
666   FROM ax_flurstueck    f
667   JOIN doppelverbindung d     -- beide FÀlle ÃŒber Union-View: direkt und ÃŒber Recht von BS an BS
668     ON d.fsgml = f.gml_id
669   JOIN ax_buchungsstelle s    -- Buchungs-Stelle
670     ON d.bsgml = s.gml_id
671   WHERE s.buchungsart = 2101;
672
673COMMENT ON VIEW erbbaurechte_suchen
674 IS 'Suche nach FÀllen mit Buchungsrt 2101=Erbbaurecht';
675
676
677-- Probleme mit der Trigger-Function "update_fields_beziehungen()"
678-- Manchmal kann zu einer gml_id in "alkis_beziehungen" die zustÀndige Tabelle nicht gefunden werden.
679-- Nach Änderung der Trigger-Function am 10.12.2013 wird die Beziehung trotzdem eingetragen,
680-- nur die Felder "von_typename" und "beginnt" bleiben leer.
681
682-- Diese FÀlle anzeigen:
683CREATE OR REPLACE VIEW beziehungsproblem_faelle
684AS
685  SELECT *
686    FROM alkis_beziehungen
687   WHERE beginnt IS NULL;
688
689-- Wie viele sind das?
690CREATE OR REPLACE VIEW beziehungsproblem_zaehler
691AS
692  SELECT count(ogc_fid) AS anzahl
693    FROM alkis_beziehungen
694   WHERE beginnt IS NULL;
695
696
697-- END --
Note: See TracBrowser for help on using the repository browser.