source: trunk/import/sichten.sql @ 303

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