source: trunk/import/sichten.sql @ 305

Revision 305, 33.8 KB checked in by frank.jaeger, 7 years ago (diff)

Verbesserung CSV-Export: Rechtsgemeinschaft in Namenszeile.

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