source: trunk/data/konvert/postnas_0.7/sichten.sql @ 183

Revision 183, 27.9 KB checked in by frank.jaeger, 11 years ago (diff)

Trigger fuer NAS-Replace-Sätze repariert (Quick 'n Dirty).

Line 
1-- =====
2-- ALKIS
3-- =====
4
5--  PostNAS 0.7
6
7--  2012-02-25 PostNAS 07, Umbenennung
8--  2012-04-17 flstnr_ohne_position
9--  2012-04-24 pauschal Filter 'endet IS NULL' um historische Objekte auszublenden
10--  2012-10-29 Redundanzen in Beziehungen suchen (entstehen durch replace)
11
12--  -----------------------------------------
13--  Sichten fuer Verwendung im mapfiles (wms)
14--  -----------------------------------------
15
16
17-- WMS-Layer "ag_t_flurstueck"
18-- ---------------------------
19
20-- Die Geometrie befindet sich in "ap_pto", der Label in "ax_flurstueck"
21-- Die Verbindung erfolgt ÃŒber "alkis_beziehungen"
22
23
24-- Bruchnummerierung erzeugen
25-- ALT 2012-04-17: Diese Version zeigt nur die manuell gesetzten Positionen
26CREATE OR REPLACE VIEW s_flurstueck_nr
27AS
28 SELECT f.ogc_fid,
29        p.wkb_geometry,  -- Position des Textes
30        f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum
31   FROM ap_pto             p
32   JOIN alkis_beziehungen  v  ON p.gml_id       = v.beziehung_von
33   JOIN ax_flurstueck      f  ON v.beziehung_zu = f.gml_id
34  WHERE v.beziehungsart = 'dientZurDarstellungVon'
35    AND p.endet IS NULL
36    AND f.endet IS NULL
37  --AND p."art" = 'ZAE_NEN'
38  ;
39
40COMMENT ON VIEW s_flurstueck_nr IS 'fuer Kartendarstellung: Bruchnummerierung FlurstÃŒck';
41
42-- NEU 2012-04-17
43-- Wenn keine manuelle Position gesetzt ist, wird die Flaechenmitte verwendet
44
45-- ACHTUNG: Dieser View kann nicht direkt im Mapserver-WMS verwendet werden.
46-- Die Anzeige ist zu langsam. Filterung Ìber BBOX kann nicht funktionieren, da zunÀchst ALLE Standardpositionen
47-- berechnet werden mÃŒssen, bevor darÃŒber gefiltert werden kann.
48
49-- FAZIT: In einer Hilfstabelle mit geometrischem Index zwischenspeichern.
50--        Siehe PostProcessing Tabelle "pp_flurstueck_nr"
51
52CREATE OR REPLACE VIEW s_flurstueck_nr2
53AS
54  SELECT f.ogc_fid,
55         p.wkb_geometry,  -- manuelle Position des Textes
56         f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum
57    FROM ap_pto             p
58    JOIN alkis_beziehungen  v  ON p.gml_id       = v.beziehung_von
59    JOIN ax_flurstueck      f  ON v.beziehung_zu = f.gml_id
60   WHERE v.beziehungsart = 'dientZurDarstellungVon'
61     AND p.endet IS NULL
62     AND f.endet IS NULL
63   --AND p."art" = 'ZAE_NEN'
64 UNION
65  SELECT f.ogc_fid,
66         ST_PointOnSurface(f.wkb_geometry) AS wkb_geometry,  -- Flaechenmitte als Position des Textes
67         f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum
68    FROM      ax_flurstueck     f
69    LEFT JOIN alkis_beziehungen v  ON v.beziehung_zu = f.gml_id
70   WHERE v.beziehungsart is NULL
71     AND f.endet IS NULL
72  ;
73
74COMMENT ON VIEW s_flurstueck_nr2 IS 'Bruchnummerierung FlurstÃŒck, auch Standard-Position. Nicht direkt fuer WMS verwenden';
75
76
77-- Layer "ag_t_gebaeude"
78-- ---------------------
79
80-- Problem: Zu einigen GebÀuden gibt es mehrere Hausnummern.
81-- Diese unterscheiden sich im Feld ap-pto.advstandardmodell
82-- z.B. 3 verschiedene EintrÀge mit <NULL>, {DKKM500}, {DKKM1000}, (Beispiel; Lage, Lange Straße 15 c)
83
84
85CREATE OR REPLACE VIEW s_hausnummer_gebaeude
86AS
87 SELECT p.ogc_fid,
88        p.wkb_geometry,                         -- Point
89        p.drehwinkel * 57.296 AS drehwinkel,    -- umn: ANGLE [drehwinkel]
90        l.hausnummer                            -- umn: LABELITEM
91   FROM ap_pto p
92   JOIN alkis_beziehungen v
93     ON p.gml_id = v.beziehung_von
94   JOIN ax_lagebezeichnungmithausnummer l
95     ON v.beziehung_zu  = l.gml_id
96  WHERE v.beziehungsart = 'dientZurDarstellungVon'
97    AND p.endet IS NULL
98    AND l.endet IS NULL;
99
100COMMENT ON VIEW s_hausnummer_gebaeude IS 'fuer Kartendarstellung: Hausnummern HauptgebÀude';
101
102
103-- Layer "ag_t_nebengeb"
104-- ---------------------
105
106CREATE OR REPLACE VIEW s_nummer_nebengebaeude
107AS
108 SELECT p.ogc_fid,
109        p.wkb_geometry,
110        p.drehwinkel * 57.296 AS drehwinkel,    -- umn: ANGLE [drehwinkel]
111     -- v.beziehungsart,                -- TEST
112     -- l.pseudonummer,                 -- die HsNr des zugehoerigen Hauptgebaeudes
113        l.laufendenummer                -- umn: LABELITEM - die laufende Nummer des Nebengebaeudes
114   FROM ap_pto p
115   JOIN alkis_beziehungen v
116     ON p.gml_id = v.beziehung_von
117   JOIN ax_lagebezeichnungmitpseudonummer l
118     ON v.beziehung_zu  = l.gml_id
119  WHERE v.beziehungsart = 'dientZurDarstellungVon'
120    AND p.endet IS NULL
121    AND l.endet IS NULL
122;
123
124COMMENT ON VIEW s_nummer_nebengebaeude IS 'fuer Kartendarstellung: Hausnummern NebengebÀude';
125
126
127-- Layer "ag_p_flurstueck"
128-- -----------------------
129
130CREATE OR REPLACE VIEW s_zugehoerigkeitshaken_flurstueck
131AS
132 SELECT p.ogc_fid,
133        p.wkb_geometry,
134        p.drehwinkel * 57.296 AS drehwinkel,
135        f.flurstueckskennzeichen
136   FROM ap_ppo p
137   JOIN alkis_beziehungen v
138     ON p.gml_id = v.beziehung_von
139   JOIN ax_flurstueck f
140     ON v.beziehung_zu = f.gml_id
141  WHERE p.art = 'Haken'
142    AND v.beziehungsart = 'dientZurDarstellungVon'
143    AND f.endet IS NULL
144    AND p.endet IS NULL;
145
146COMMENT ON VIEW s_zugehoerigkeitshaken_flurstueck IS 'fuer Kartendarstellung';
147
148
149-- Layer "s_zuordungspfeil_flurstueck"
150-- -----------------------------------
151
152CREATE OR REPLACE VIEW s_zuordungspfeil_flurstueck
153AS
154 SELECT l.ogc_fid,
155        l.wkb_geometry
156   FROM ap_lpo l
157   JOIN alkis_beziehungen v
158     ON l.gml_id = v.beziehung_von
159   JOIN ax_flurstueck f
160     ON v.beziehung_zu = f.gml_id
161  WHERE l.art = 'Pfeil'
162    AND v.beziehungsart = 'dientZurDarstellungVon'
163    AND ('DKKM1000' ~~ ANY (l.advstandardmodell))
164    AND f.endet IS NULL
165    AND l.endet IS NULL;
166
167COMMENT ON VIEW s_zuordungspfeil_flurstueck IS 'fuer Kartendarstellung: Zuordnungspfeil FlurstÃŒcksnummer';
168
169
170CREATE OR REPLACE VIEW s_zuordungspfeilspitze_flurstueck
171AS
172 SELECT l.ogc_fid,
173        (((st_azimuth(st_pointn(l.wkb_geometry, 1),
174        st_pointn(l.wkb_geometry, 2)) * (- (180)::double precision)) / pi()) + (90)::double precision) AS winkel,
175        st_startpoint(l.wkb_geometry) AS wkb_geometry
176   FROM ap_lpo l
177   JOIN alkis_beziehungen v
178     ON l.gml_id = v.beziehung_von
179   JOIN ax_flurstueck f
180     ON v.beziehung_zu = f.gml_id
181  WHERE l.art = 'Pfeil'
182    AND v.beziehungsart = 'dientZurDarstellungVon'
183    AND ('DKKM1000' ~~ ANY (l.advstandardmodell))
184    AND f.endet IS NULL
185    AND l.endet IS NULL;
186
187COMMENT ON VIEW s_zuordungspfeilspitze_flurstueck IS 'fuer Kartendarstellung: Zuordnungspfeil FlurstÃŒcksnummer, Spitze';
188
189
190
191-- Layer NAME "ap_pto" GROUP "praesentation"
192-- ----------------------------------------
193-- Texte, die nicht schon in einem anderen Layer ausgegeben werden
194
195CREATE OR REPLACE VIEW s_beschriftung
196AS
197  SELECT p.ogc_fid,
198         p.schriftinhalt,
199         p.art,
200         p.drehwinkel * 57.296 AS winkel, -- * 180 / Pi
201         p.wkb_geometry
202    FROM ap_pto p
203   WHERE not p.schriftinhalt IS NULL
204     AND p.endet IS NULL
205     AND p.art NOT IN ('HNR', 'PNR');
206
207-- Feb. 2012 PostNAS 0.6: 'ZAE_NEN' kommt nicht mehr vor!
208
209-- Diese 'IN'-Liste fortschreiben bei Erweiterungen des Mapfiles
210-- Wenn ein Text zum fachlich passenden Layer angezeigt wird, dann hier ausblenden,
211-- d.h. die Kennung in die Klammer eintragen.
212
213-- Werte in ap_pto.art:
214-- 'HNR'  = Hausnummer
215-- 'PNR'  = Pseudo-Nummer = laufende Nummer NebengebÀude
216
217-- Ermittlung der vorkommenden Arten mit:
218--   SELECT DISTINCT art FROM ap_pto ORDER BY art;
219
220-- Noch nicht berÃŒcksichtigt:
221   
222--"AGT""ART""ATP""BBD""BezKlassifizierungStrasse""BSA""BWF""BWF_ZUS""FKT""Fliessgewaesser""FreierText"
223--"Friedhof""Gewanne""GFK""Halde_LGT""HHO""NAM""PKN""Platz""PRO""SPG""SPO""StehendesGewaesser"
224--"Strasse""VEG""Vorratsbehaelter""Weg""Weitere Höhe""ZNM""<NULL>"
225
226COMMENT ON VIEW s_beschriftung IS 'ap_pto, die noch nicht in anderen Layern angezeigt werden';
227
228
229-- Layer "s_zuordungspfeil_gebaeude"
230-- -----------------------------------
231
232CREATE OR REPLACE VIEW s_zuordungspfeil_gebaeude
233AS
234 SELECT l.ogc_fid,
235     -- alkis_beziehungen.beziehungsart, -- TEST
236     -- ap_lpo.art, -- TEST
237        l.wkb_geometry
238   FROM ap_lpo l
239   JOIN alkis_beziehungen v
240     ON l.gml_id = v.beziehung_von
241   JOIN ax_gebaeude g
242     ON v.beziehung_zu = g.gml_id
243  WHERE l.art = 'Pfeil'
244    AND v.beziehungsart = 'dientZurDarstellungVon'
245    AND g.endet IS NULL
246    AND l.endet IS NULL;
247
248COMMENT ON VIEW s_zuordungspfeil_gebaeude IS 'fuer Kartendarstellung: Zuordnungspfeil fÌr GebÀude-Nummer';
249
250
251-- Sichten vom OBK (Oberbergischer Kreis)
252-- --------------------------------------
253
254-- Dazu notwendig: Feld "ax_besondereflurstuecksgrenze.artderflurstuecksgrenze" als Array "integer[]" !
255-- Anpassung DB-Schema erfolgte am 18.09.2011
256
257
258CREATE OR REPLACE VIEW sk2004_zuordnungspfeil
259AS
260 SELECT ap.ogc_fid, ap.wkb_geometry
261 FROM ap_lpo ap
262 WHERE ((ap.signaturnummer = 2004)
263   AND ('DKKM1000'::text ~~ ANY ((ap.advstandardmodell)::text[])));
264
265COMMENT ON VIEW sk2004_zuordnungspfeil IS 'fuer Kartendarstellung: Zuordnungspfeil FlurstÃŒcksnummer"';
266-- krz: ap.signaturnummer is NULL in allen SÀtzen
267--      Siehe s_zuordungspfeil_flurstueck
268
269CREATE OR REPLACE VIEW sk2004_zuordnungspfeil_spitze
270AS
271 SELECT ap.ogc_fid, (((st_azimuth(st_pointn(ap.wkb_geometry, 1),
272        st_pointn(ap.wkb_geometry, 2)) * (- (180)::double precision)) / pi()) + (90)::double precision) AS winkel,
273        st_startpoint(ap.wkb_geometry) AS wkb_geometry
274 FROM ap_lpo ap
275 WHERE ((ap.signaturnummer = 2004)
276   AND ('DKKM1000'::text ~~ ANY ((ap.advstandardmodell)::text[])));
277-- krz: ap.signaturnummer is NULL in allen SÀtzen
278
279
280CREATE OR REPLACE VIEW sk2012_flurgrenze
281AS
282 SELECT fg.ogc_fid, fg.wkb_geometry
283   FROM ax_besondereflurstuecksgrenze fg
284  WHERE (3000 = ANY (fg.artderflurstuecksgrenze))
285    AND fg.advstandardmodell ~~ 'DLKM'::text;
286
287COMMENT ON VIEW sk2012_flurgrenze IS 'fuer Kartendarstellung: besondere FlurstÃŒcksgrenze "Flurgrenze"';
288
289
290CREATE OR REPLACE VIEW sk2014_gemarkungsgrenze
291AS
292 SELECT gemag.ogc_fid, gemag.wkb_geometry
293   FROM ax_besondereflurstuecksgrenze gemag
294  WHERE (7003 = ANY (gemag.artderflurstuecksgrenze))
295    AND gemag.advstandardmodell ~~ 'DLKM'::text;
296
297COMMENT ON VIEW sk2014_gemarkungsgrenze IS 'fuer Kartendarstellung: besondere FlurstÃŒcksgrenze "Gemarkungsgrenze"';
298
299
300CREATE OR REPLACE VIEW sk2018_bundeslandgrenze
301AS
302 SELECT blg.ogc_fid, blg.wkb_geometry
303   FROM ax_besondereflurstuecksgrenze blg
304  WHERE (7102 = ANY (blg.artderflurstuecksgrenze))
305    AND blg.advstandardmodell ~~ 'DLKM'::text;
306
307COMMENT ON VIEW sk2018_bundeslandgrenze IS 'fuer Kartendarstellung: besondere FlurstÃŒcksgrenze "Bundeslandgrenze"';
308
309
310CREATE OR REPLACE VIEW sk2020_regierungsbezirksgrenze
311AS
312 SELECT rbg.ogc_fid, rbg.wkb_geometry
313   FROM ax_besondereflurstuecksgrenze rbg
314  WHERE (7103 = ANY (rbg.artderflurstuecksgrenze))
315    AND rbg.advstandardmodell ~~ 'DLKM'::text;
316
317COMMENT ON VIEW sk2020_regierungsbezirksgrenze IS 'fuer Kartendarstellung: besondere FlurstÃŒcksgrenze "Regierungsbezirksgrenze"';
318
319
320CREATE OR REPLACE VIEW sk2022_gemeindegrenze
321AS
322 SELECT gemg.ogc_fid, gemg.wkb_geometry
323   FROM ax_besondereflurstuecksgrenze gemg
324  WHERE (7106 = ANY (gemg.artderflurstuecksgrenze))
325    AND gemg.advstandardmodell ~~ 'DLKM'::text;
326
327COMMENT ON VIEW sk2022_gemeindegrenze IS 'fuer Kartendarstellung: besondere FlurstÃŒcksgrenze "Gemeindegrenze"';
328
329
330-- Zusammenfassung "Politische Grenzen"  Art= 7102, 7103, 7104, 7106
331
332-- Grenze der Bundesrepublik Deutschland 7101 (G)
333-- Grenze des Bundeslandes 7102 (G)
334-- Grenze des Regierungsbezirks 7103 (G)
335-- Grenze des Landkreises 7104 (G)
336-- Grenze der Gemeinde 7106
337-- Grenze des Gemeindeteils 7107
338-- Grenze der Verwaltungsgemeinschaft 7108
339
340CREATE OR REPLACE VIEW sk201x_politische_grenze
341AS
342 SELECT ogc_fid, artderflurstuecksgrenze as art, wkb_geometry
343   FROM ax_besondereflurstuecksgrenze
344
345-- WHERE ( ANY (artderflurstuecksgrenze) IN (7102,7103,7104,7106) )
346
347  WHERE (7102 = ANY (artderflurstuecksgrenze)
348     OR  7102 = ANY (artderflurstuecksgrenze)
349     OR  7103 = ANY (artderflurstuecksgrenze)
350     OR  7104 = ANY (artderflurstuecksgrenze)
351     OR  7106 = ANY (artderflurstuecksgrenze)
352    )
353    AND advstandardmodell ~~ 'DLKM'::text;
354
355COMMENT ON VIEW sk201x_politische_grenze IS 'fuer Kartendarstellung: besondere FlurstÃŒcksgrenze Politische Grenzen (Bund, Land, Kreis, Gemeinde)';
356-- GefÀllt mir nicht!
357-- Array-Felder eignen sich nicht als Filter. Optimierung: in Tabelle speichern
358
359
360--  ------------------------------------------
361--  Sichten fuer Fehlersuche und Daten-Analyse
362--  ------------------------------------------
363
364
365-- FlurstÃŒcke mit Anzeige der FlurstÃŒcksnummer an der "Standardposition"
366
367-- Nach der Konvertierung aus ALK hat zunÀchst jedes FlurstÌck eine explizit gesetzte Position der FlurstÌcksnummer.
368
369-- Nach einer manuellen Teilung bekommen die neuen FlurstÃŒcke im ALKIS nur dann eine Position,
370-- wenn die Positioin manuell bestimmt (verschoben) wurde.
371-- Wenn die FlurstÃŒcksnummer an ihrer "Standardposition" angezeigt werden soll,
372-- dann wird diese in den Daten (DHK, NAS) nicht gesetzt.
373-- Der Konverter PostNAS konvertiert aber nur die Daten, die er bekommt, er setzt nicht die Standard-Position
374-- fÃŒr die FlurstÃŒcke, die ohne eine manuelle Position kommen.
375
376-- Diese FÀlle identifizieren
377CREATE OR REPLACE VIEW flstnr_ohne_position
378AS
379 SELECT f.gml_id,
380        f.gemarkungsnummer || '-' || f.flurnummer || '-' || f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS such -- Suchstring fÃŒr ALKIS-Navigation nach FS-Kennzeichen
381 FROM        ax_flurstueck     f
382   LEFT JOIN alkis_beziehungen v  ON v.beziehung_zu = f.gml_id
383 --LEFT JOIN ap_pto            p  ON p.gml_id       = v.beziehung_von
384  WHERE v.beziehungsart is NULL
385    AND f.endet IS NULL
386--ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler
387  ;
388
389COMMENT ON VIEW flstnr_ohne_position IS 'FlurstÌcke ohne manuell gesetzte Position fÌr die PrÀsentation der FS-Nr';
390
391
392-- Zeigt die Texte an, die nicht in einem der Mapfile-Views verarbeitet werden
393CREATE OR REPLACE VIEW s_allgemeine_texte
394AS
395 SELECT p.ogc_fid,
396      --p.wkb_geometry,
397      --p.gml_id,
398        p.art,
399        p.drehwinkel * 57.296 AS drehwinkel,   -- * 180 / Pi
400        p.schriftinhalt
401   FROM ap_pto p
402  WHERE NOT p.art = 'ZAE_NEN'
403    AND NOT p.art = 'HNR'
404    AND NOT p.art = 'FKT'
405    AND NOT p.art = 'Friedhof'
406    AND p.schriftinhalt IS NOT NULL
407    AND p.endet IS NULL;
408
409
410-- Analyse zu o.g. Fehler:
411--  Welche Inhalte kommen im Feld ap_pto.art vor?
412CREATE OR REPLACE VIEW ap_pto_arten
413AS
414  SELECT DISTINCT art
415    FROM ap_pto;
416
417
418-- Umbruch im Label?
419-- z.B. "Schwimm-/nbecken"
420-- Sind 2 Buchstaben in Mapfile bei "WRAP" möglich?
421CREATE OR REPLACE VIEW texte_mit_umbruch
422AS
423 SELECT ogc_fid, schriftinhalt, art
424   FROM ap_pto
425  WHERE not schriftinhalt is null
426    AND schriftinhalt like '%/n%';
427
428-- ... schriftinhalt like '%/%';
429-- RLP: FlurstÌcks-Bruchnummer art='ZAE_NEN' als Schriftinhalt (2 FÀlle)
430
431
432
433CREATE OR REPLACE VIEW s_allgemeine_texte_arten
434AS
435 SELECT DISTINCT art
436   FROM s_allgemeine_texte;
437
438-- dies liefert die Werte:
439--  Bahnverkehr, BWF, FKT_LGT, Fliessgewaesser, FreierText, Gewanne, NAM, Platz,
440--  StehendesGewaesser, Strasse, urn:adv:fachdatenv, Weg, ZNM
441
442
443
444-- EXTENT fÃŒr das Mapfile eines Mandanten ermitteln
445
446CREATE OR REPLACE VIEW flurstuecks_minmax AS
447 SELECT min(st_xmin(wkb_geometry)) AS r_min,
448        min(st_ymin(wkb_geometry)) AS h_min,
449        max(st_xmax(wkb_geometry)) AS r_max,
450        max(st_ymax(wkb_geometry)) AS h_max
451   FROM ax_flurstueck f
452   WHERE f.endet IS NULL;
453
454COMMENT ON VIEW flurstuecks_minmax IS 'Maximale Ausdehnung von ax_flurstueck fuer EXTENT-Angabe im Mapfile';
455
456
457
458-- Nach Laden der Keytables:
459
460-- MAP ALT:
461-- DATA "wkb_geometry from (SELECT ogc_fid, gml_id, artderfestlegung, name, bezeichnung, stelle, wkb_geometry FROM ax_bauraumoderbodenordnungsrecht) as foo using unique ogc_fid using SRID=25832"
462
463CREATE OR REPLACE VIEW baurecht
464AS
465  SELECT r.ogc_fid,
466         r.wkb_geometry,
467         r.gml_id,
468         r.artderfestlegung as adfkey, -- Art der Festlegung - Key
469         r."name",                     -- Eigenname des Gebietes
470         r.stelle,                     -- Stelle Key
471         r.bezeichnung AS rechtbez,    -- Verfahrensnummer
472         a.bezeichner  AS adfbez,      -- Art der Festlegung - Bezeichnung
473         d.bezeichnung AS stellbez     -- Stelle Bezeichnung
474      -- , d.stellenart                -- weiter entschluesseln?
475    FROM ax_bauraumoderbodenordnungsrecht r
476    LEFT JOIN ax_bauraumoderbodenordnungsrecht_artderfestlegung a
477      ON r.artderfestlegung = a.wert
478    LEFT JOIN ax_dienststelle d
479      ON r.land   = d.land
480     AND r.stelle = d.stelle
481  WHERE r.endet IS NULL
482    AND d.endet IS NULL
483 ;
484
485-- MAP NEU:
486-- DATA "wkb_geometry from (SELECT ogc_fid, gml_id, adfkey, name, stelle, rechtbez, adfbez, stellbez, wkb_geometry FROM baurecht) as foo using unique ogc_fid using SRID=25832" # gespeicherter View
487
488
489-- Man glaubt es kaum, aber im ALKIS haben Gemeinde und Gemarkung keinerlei Beziehung miteinander
490-- Nur durch Auswertung der FlurstÃŒcke kann man ermitteln, in welcher Gemeinde eine Gemarkung liegt.
491
492-- 2011-12-08 umbenannt
493
494CREATE OR REPLACE VIEW gemarkung_in_gemeinde
495AS
496  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
497  FROM            ax_flurstueck
498  WHERE           endet IS NULL
499  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
500;
501
502COMMENT ON VIEW gemarkung_in_gemeinde IS 'Welche Gemarkung liegt in welcher Gemeinde? Durch Verweise aus FlurstÃŒck.';
503
504
505-- Untersuchen, welche Geometrie-Typen vorkommen
506
507CREATE OR REPLACE VIEW arten_von_flurstuecksgeometrie
508AS
509 SELECT   count(gml_id) as anzahl,
510          st_geometrytype(wkb_geometry)
511 FROM     ax_flurstueck
512 WHERE    endet IS NULL
513 GROUP BY st_geometrytype(wkb_geometry);
514
515
516-- A d r e s s e n
517
518-- Verschluesselte Lagebezeichnung (Strasse und Hausnummer) fuer eine Gemeinde
519-- Schluessel der Gemeinde nach Bedarf anpassen!
520
521--  FEHLER: Funktion to_char(character varying, unknown) existiert nicht
522
523
524CREATE OR REPLACE VIEW adressen_hausnummern
525AS
526    SELECT
527        s.bezeichnung AS strassenname,
528         g.bezeichnung AS gemeindename,
529         l.land,
530         l.regierungsbezirk,
531         l.kreis,
532         l.gemeinde,
533         l.lage        AS strassenschluessel,
534         l.hausnummer
535    FROM   ax_lagebezeichnungmithausnummer l 
536    JOIN   ax_gemeinde g
537      ON l.kreis=g.kreis
538     AND l.gemeinde=g.gemeinde
539    JOIN   ax_lagebezeichnungkatalogeintrag s
540      ON l.kreis=s.kreis
541     AND l.gemeinde=s.gemeinde
542     AND l.lage = s.lage        -- ab PostNAS 0.6
543    WHERE     l.gemeinde = 40;  -- "40" = Stadt Lage
544
545
546-- Zuordnung dieser Adressen zu Flurstuecken
547-- Schluessel der Gemeinde nach Bedarf anpassen!
548
549CREATE OR REPLACE VIEW adressen_zum_flurstueck
550AS
551    SELECT
552           f.gemarkungsnummer,
553           f.flurnummer,
554           f.zaehler,
555           f.nenner,
556           g.bezeichnung AS gemeindename,
557           s.bezeichnung AS strassenname,
558           l.lage        AS strassenschluessel,
559           l.hausnummer
560      FROM   ax_flurstueck f
561      JOIN   alkis_beziehungen v
562        ON f.gml_id=v.beziehung_von
563      JOIN   ax_lagebezeichnungmithausnummer l 
564        ON l.gml_id=v.beziehung_zu
565      JOIN   ax_gemeinde g
566        ON l.kreis=g.kreis
567       AND l.gemeinde=g.gemeinde
568      JOIN   ax_lagebezeichnungkatalogeintrag s
569        ON l.kreis=s.kreis
570       AND l.gemeinde=s.gemeinde
571       AND l.lage = s.lage   -- ab PostNAS 0.6
572     WHERE v.beziehungsart='weistAuf'
573       AND l.gemeinde = 40  -- "40" = Stadt Lage
574     ORDER BY
575           f.gemarkungsnummer,
576           f.flurnummer,
577           f.zaehler,
578           f.nenner;
579
580
581-- FlurstÃŒcke eines EigentÃŒmers
582-- ----------------------------
583
584-- Dieser View liefert nur die (einfache) Buchungsart "GrundstÃŒck"
585-- Solche FÀlle wie "Erbbaurecht an GrundstÌck" oder "Wohnungs-/Teileigentum an aufgeteiltes GrundstÌck"
586-- oder "Miteigentum an aufteteiltes GrundstÃŒck" fehlen in deisere Auswertung.
587-- Dazu siehe: "rechte_eines_eigentuemers".
588
589-- Das Ergbenis ist gedacht fÃŒr den Export als CSV und Weiterverarbeitung mit einer Tabellenkalkulation
590-- oder einer einfachen Datenbank.
591
592-- Auch ein Export als Shape ist moeglich (dafuer: geom hinzugefuegt, Feldnamen gekuerzt)
593-- Kommando:
594--  pgsql2shp -h localhost -p 5432 -f "/data/.../alkis_fs_gemeinde.shp"  [db-name]  public.flurstuecke_eines_eigentuemers
595
596-- Übersicht der Tabellen:
597--
598-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle >istGebucht> Flurstueck
599--                                              *-> Bezirk                *-> Buchungsart     *-> Gemarkung
600
601-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
602
603CREATE OR REPLACE VIEW flurstuecke_eines_eigentuemers
604AS
605   SELECT
606      k.bezeichnung                AS gemarkung,
607      k.gemarkungsnummer           AS gemkg_nr,
608      f.flurnummer                 AS flur,
609      f.zaehler                    AS fs_zaehler,
610      f.nenner                     AS fs_nenner,
611      f.amtlicheflaeche            AS flaeche,
612      f.wkb_geometry               AS geom,  -- fuer Export als Shape
613   -- g.bezirk,
614      b.bezeichnung                AS bezirkname,
615      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
616      g.blattart,
617      s.laufendenummer             AS bvnr,
618      art.bezeichner               AS buchgsart,
619   -- s.zaehler || '/' || s.nenner AS buchg_anteil,
620      n.laufendenummernachdin1421  AS name_num,
621   -- n.zaehler || '/' || n.nenner AS nam_anteil,
622      p.nachnameoderfirma          AS nachname --,
623   -- p.vorname
624   FROM       ax_person              p
625        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
626        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
627        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
628        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
629        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
630        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
631        JOIN  ax_buchungsstelle      s    ON s.gml_id = bgs.beziehung_von
632        JOIN  ax_buchungsstelle_buchungsart art ON s.buchungsart = art.wert
633        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = s.gml_id
634        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
635        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
636   WHERE p.nachnameoderfirma LIKE 'Gemeinde %'   -- ** Bei Bedarf anpassen!
637     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
638     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
639     AND bgs.beziehungsart = 'istBestandteilVon' -- Buchungs-Stelle >> Grundbuch
640     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> Buchungs-Stelle
641     AND p.endet IS NULL
642     AND n.endet IS NULL
643     AND g.endet IS NULL
644     AND b.endet IS NULL
645     AND s.endet IS NULL
646     AND f.endet IS NULL
647     AND k.endet IS NULL
648   ORDER BY   
649         k.bezeichnung,
650         f.flurnummer,
651         f.zaehler,
652         f.nenner,
653         g.bezirk,
654         g.buchungsblattnummermitbuchstabenerweiterung,
655         s.laufendenummer
656;
657
658
659-- Rechte eines EigentÃŒmers
660-- ------------------------
661
662-- Dieser View sucht speziell die FÀlle wo eine Buchungsstelle ein Recht "an" einer anderen Buchungsstelle hat.
663--  - "Erbbaurecht *an* GrundstÃŒck"
664--  - "Wohnungs-/Teileigentum *an* Aufgeteiltes GrundstÃŒck"
665--  - "Miteigentum *an* Aufteteiltes GrundstÃŒck"
666-- Suchkriterium ist der Name des EigentÃŒmers auf dem "herrschenden" Grundbuch, also dem Besitzer des Rechtes.
667
668-- Diese FÀlle fehlen im View "flurstuecke_eines_eigentuemers".
669
670-- Übersicht der Tabellen:
671--
672-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle-h >an> Stelle-d >istGebucht> Flurstueck
673--
674
675-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
676
677
678CREATE OR REPLACE VIEW rechte_eines_eigentuemers
679AS
680   SELECT
681      k.bezeichnung                AS gemarkung,
682      k.gemarkungsnummer           AS gemkg_nr,
683      f.flurnummer                 AS flur,
684      f.zaehler                    AS fs_zaehler,
685      f.nenner                     AS fs_nenner,
686      f.amtlicheflaeche            AS flaeche,
687      f.wkb_geometry               AS geom,  -- fuer Export als Shape
688   -- g.bezirk,
689      b.bezeichnung                AS bezirkname,
690      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
691   -- g.blattart,
692      sh.laufendenummer            AS bvnr_herr,
693      sh.zaehler || '/' || sh.nenner AS buchg_anteil_herr,
694      arth.bezeichner              AS buchgsa_herr,
695      bss.beziehungsart            AS bez_art,
696      artd.bezeichner              AS buchgsa_dien,
697      sd.laufendenummer            AS bvnr_dien,
698   -- sd.zaehler || '/' || sd.nenner AS buchg_anteil_dien,
699      n.laufendenummernachdin1421  AS name_num,
700   -- n.zaehler || '/' || n.nenner AS nam_anteil,
701      p.nachnameoderfirma          AS nachname --, 
702   -- p.vorname
703   FROM       ax_person              p
704        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
705        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
706        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
707        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
708        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
709        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
710        JOIN  ax_buchungsstelle      sh   ON sh.gml_id = bgs.beziehung_von  -- herrschende Buchung
711        JOIN  ax_buchungsstelle_buchungsart arth ON sh.buchungsart = arth.wert
712        JOIN  alkis_beziehungen      bss  ON sh.gml_id = bss.beziehung_von
713        JOIN  ax_buchungsstelle      sd   ON sd.gml_id = bss.beziehung_zu   -- dienende Buchung
714        JOIN  ax_buchungsstelle_buchungsart artd ON sd.buchungsart = artd.wert
715        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = sd.gml_id
716        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
717        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
718   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!
719     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
720     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
721     AND bgs.beziehungsart = 'istBestandteilVon' -- B-Stelle herr   >> Grundbuch
722     AND bss.beziehungsart in ('an','zu')        -- B-Stelle herr.  >> B-Stelle dien.
723     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> B-Stelle dien
724     AND p.endet IS NULL
725     AND n.endet IS NULL
726     AND g.endet IS NULL
727     AND b.endet IS NULL
728     AND sh.endet IS NULL
729     AND sd.endet IS NULL
730     AND f.endet IS NULL
731     AND k.endet IS NULL
732   ORDER BY   
733         k.bezeichnung,
734         f.flurnummer,
735         f.zaehler,
736         f.nenner,
737         g.bezirk,
738         g.buchungsblattnummermitbuchstabenerweiterung,
739         sh.laufendenummer
740;
741
742CREATE OR REPLACE VIEW beziehungen_redundant
743AS
744SELECT *
745 FROM alkis_beziehungen AS bezalt
746 WHERE EXISTS
747       (SELECT ogc_fid
748         FROM alkis_beziehungen AS bezneu
749        WHERE bezalt.beziehung_von = bezneu.beziehung_von
750          AND bezalt.beziehung_zu  = bezneu.beziehung_zu
751          AND bezalt.beziehungsart = bezneu.beziehungsart
752          AND bezalt.ogc_fid       < bezneu.ogc_fid
753        );
754
755COMMENT ON VIEW beziehungen_redundant IS 'alkis_beziehungen zu denen es eine identische neue Version gibt.';
756
757
758CREATE OR REPLACE VIEW beziehungen_redundant_in_delete
759AS
760SELECT *
761 FROM alkis_beziehungen AS bezalt
762 WHERE EXISTS
763       (SELECT ogc_fid
764         FROM alkis_beziehungen AS bezneu
765        WHERE bezalt.beziehung_von = bezneu.beziehung_von
766          AND bezalt.beziehung_zu  = bezneu.beziehung_zu
767          AND bezalt.beziehungsart = bezneu.beziehungsart
768          AND bezalt.ogc_fid       < bezneu.ogc_fid
769        )
770     -- mit dem Zusatz nur die Faelle aus dem letzten Durchlauf,
771     -- die aktuell noch in der Delet-Tabelle stehen
772     AND EXISTS
773        (SELECT ogc_fid
774         FROM delete
775         WHERE bezalt.beziehung_von = substr(featureid, 1, 16)
776            OR bezalt.beziehung_zu  = substr(featureid, 1, 16)
777        );
778
779COMMENT ON VIEW beziehungen_redundant_in_delete IS 'alkis_beziehungen zu denen es eine identische neue Version gibt und wo das Objekt noch in der delete-Tabelle vorkommt.';
780
781-- END --
Note: See TracBrowser for help on using the repository browser.