source: trunk/import/sichten.sql @ 298

Revision 298, 25.2 KB checked in by frank.jaeger, 10 years ago (diff)

Metadatentabelle zu Nutzungsarten bekommt 0-Inhalte. Neue Views. Neuer WMS "Flurstücke kommunal".

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