source: trunk/data/konvert/postnas_0.5/alkis_sichten.sql @ 163

Revision 77, 17.0 KB checked in by frank.jaeger, 11 years ago (diff)

Neue SQL-Auswertungen Eigentümer --> Flurstücke

Line 
1-- =====
2-- ALKIS
3-- =====
4
5--  PostNAS 0.5,
6--  01.12.2010  F.J. Gemeinde/Gemarkung
7--  26.01.2011  F.J. Liste der Hausnummern in einer Gemeinde
8--  27.01.2011  F.J. Auflistung der FlurstÃŒcke eines (kommunalen) EigentÃŒmers
9
10
11--  -----------------------------------------
12--  Sichten fuer Verwendung im mapfiles (wms)
13--  -----------------------------------------
14
15
16-- Layer "ag_t_flurstueck"
17-- -----------------------
18
19-- Die Geometrie befindet sich in "ap_pto", der Label in "ax_flurstueck"
20-- Die Verbindung erfolgt ÃŒber "alkis_beziehungen"
21
22-- PostNAS 0.5, September 2010:
23--   Musterdaten RLP: zaehler-nenner steht auch in Feld "ap_pto.schriftinhalt"
24--   Lippe NRW:       Feld "ap_pto.schriftinhalt" ist leer. Label aus Tabelle "ax_flurstueck" entnehmen
25
26
27-- In einigen Gebieten enthÀlt das Feld "ap_pto.art"
28-- nicht den Wert 'ZAE_NEN' sondern 'urn:adv:fachdatenverbindung'.
29-- Die FlurstÃŒcksnummer fehlt dann im WMS.
30-- Die Bedingung vorÌbergehend heraus nehmen. Ursache klÀren!
31
32
33-- Bruchnummerierung erzeugen
34
35CREATE OR REPLACE VIEW s_flurstueck_nr
36AS
37 SELECT ap_pto.ogc_fid,
38        ap_pto.wkb_geometry,   -- Position des Textes
39    --  ax_flurstueck.flurstueckskennzeichen,   -- am Stueck, aufgefuellt, unpraktisch
40    --  ax_flurstueck.gemarkungsnummer,  -- integer
41    --  ax_flurstueck.flurnummer,        -- integer
42    --  ax_flurstueck.zaehler,           -- integer
43    --  ax_flurstueck.nenner,            -- integer oder NULL
44        ax_flurstueck.zaehler::text || COALESCE ('/' || ax_flurstueck.nenner::text, '') AS fsnum
45   FROM ap_pto
46   JOIN alkis_beziehungen
47     ON ap_pto.gml_id = alkis_beziehungen.beziehung_von
48   JOIN ax_flurstueck
49     ON alkis_beziehungen.beziehung_zu = ax_flurstueck.gml_id
50  WHERE alkis_beziehungen.beziehungsart = 'dientZurDarstellungVon'
51  --AND ap_pto.art = 'ZAE_NEN'
52  ;
53
54COMMENT ON VIEW s_flurstueck_nr IS 'fuer Kartendarstellung: Bruchnummerierung FlurstÃŒck';
55
56
57-- Layer "ag_t_gebaeude"
58-- ---------------------
59
60-- In einigen Gebieten in Lippe enthÀlt das Feld "ap_pto.art"
61-- nicht den Wert 'HNR'. Die Hausnummer fehlt dann im WMS.
62-- Die Bedingung vorÌbergehend heraus nehmen. Ursache klÀren!
63
64CREATE OR REPLACE VIEW s_hausnummer_gebaeude
65AS
66 SELECT ap_pto.ogc_fid,
67        ap_pto.wkb_geometry,
68        ap_pto.drehwinkel * 57.296 AS drehwinkel,   -- umn: ANGLE [drehwinkel]
69        ax_lagebezeichnungmithausnummer.hausnummer  -- umn: LABELITEM
70   FROM ap_pto
71   JOIN alkis_beziehungen
72     ON ap_pto.gml_id = alkis_beziehungen.beziehung_von
73   JOIN ax_lagebezeichnungmithausnummer
74     ON alkis_beziehungen.beziehung_zu  = ax_lagebezeichnungmithausnummer.gml_id
75  WHERE alkis_beziehungen.beziehungsart = 'dientZurDarstellungVon';
76
77COMMENT ON VIEW s_hausnummer_gebaeude IS 'fuer Kartendarstellung: Hausnummern HauptgebÀude';
78
79
80
81-- Layer "ag_p_flurstueck"
82-- -----------------------
83
84CREATE OR REPLACE VIEW s_zugehoerigkeitshaken_flurstueck
85AS
86 SELECT ap_ppo.ogc_fid,
87        ap_ppo.wkb_geometry,
88        ap_ppo.drehwinkel * 57.296 + 90 AS drehwinkel,
89        ax_flurstueck.flurstueckskennzeichen
90   FROM ap_ppo
91   JOIN alkis_beziehungen
92     ON ap_ppo.gml_id = alkis_beziehungen.beziehung_von
93   JOIN ax_flurstueck
94     ON alkis_beziehungen.beziehung_zu = ax_flurstueck.gml_id
95  WHERE ap_ppo.art = 'Haken'
96    AND alkis_beziehungen.beziehungsart = 'dientZurDarstellungVon';
97
98COMMENT ON VIEW s_zugehoerigkeitshaken_flurstueck IS 'fuer Kartendarstellung';
99
100
101-- Layer "s_zuordungspfeil_flurstueck"
102-- -----------------------------------
103
104CREATE OR REPLACE VIEW s_zuordungspfeil_flurstueck
105AS
106 SELECT ap_lpo.ogc_fid,
107        ap_lpo.wkb_geometry
108   FROM ap_lpo
109   JOIN alkis_beziehungen
110     ON ap_lpo.gml_id = alkis_beziehungen.beziehung_von
111   JOIN ax_flurstueck
112     ON alkis_beziehungen.beziehung_zu = ax_flurstueck.gml_id
113  WHERE ap_lpo.art = 'Pfeil'
114    AND alkis_beziehungen.beziehungsart = 'dientZurDarstellungVon';
115
116COMMENT ON VIEW s_zuordungspfeil_flurstueck IS 'fuer Kartendarstellung';
117
118
119-- Layer NAME "ap_pto" GROUP "praesentation"
120-- ----------------------------------------
121-- Texte, die nicht schon in einem anderen Layer ausgegeben werden
122
123CREATE OR REPLACE VIEW s_beschriftung
124AS
125  SELECT ap_pto.ogc_fid,
126      -- ap_pto.gml_id,
127         ap_pto.schriftinhalt,
128         ap_pto.art,
129         ap_pto.drehwinkel * 57.296 AS winkel, -- * 180 / Pi
130         ap_pto.wkb_geometry
131    FROM ap_pto
132   WHERE not ap_pto.schriftinhalt IS NULL
133     AND art NOT IN ('ZAE_NEN', 'HNR')
134   ;
135--  IN ('FKT', 'Friedhof', 'urn:adv:fachdatenv')
136
137-- Diese IN-Liste fortschreiben bei Erweiterungen des Mapfiles
138
139-- Lippe: Der Wert 'ZAE_NEN' fehlt. Diese FÀlle anders identifizieren?
140
141COMMENT ON VIEW s_beschriftung IS 'ap_pto, die noch nicht in anderen Layern angezeigt werden';
142
143--  ------------------------------------------
144--  Sichten fuer Fehlersuche und Daten-Analyse
145--  ------------------------------------------
146
147-- Zeigt die Texte an, die nicht in einem der Mapfile-Views verarbeitet werden
148CREATE OR REPLACE VIEW s_allgemeine_texte
149AS
150 SELECT ap_pto.ogc_fid,
151      --ap_pto.wkb_geometry,
152      --ap_pto.gml_id,
153        ap_pto.art,
154        ap_pto.drehwinkel * 57.296 AS drehwinkel,   -- * 180 / Pi
155        ap_pto.schriftinhalt
156   FROM ap_pto
157  WHERE NOT ap_pto.art = 'ZAE_NEN'
158    AND NOT ap_pto.art = 'HNR'
159    AND NOT ap_pto.art = 'FKT'
160    AND NOT ap_pto.art = 'Friedhof'
161    AND ap_pto.schriftinhalt IS NOT NULL;
162
163
164-- Analyse zu o.g. Fehler:
165--  Welche Inhalte kommen im Feld ap_pto.art vor?
166CREATE OR REPLACE VIEW ap_pto_arten
167AS
168  SELECT DISTINCT art
169    FROM ap_pto;
170
171
172-- Umbruch im Label?
173-- z.B. "Schwimm-/nbecken"
174-- Sind 2 Buchstaben in Mapfile bei "WRAP" möglich?
175CREATE OR REPLACE VIEW texte_mit_umbruch
176AS
177 SELECT ogc_fid, schriftinhalt, art
178   FROM ap_pto
179  WHERE not schriftinhalt is null
180    AND schriftinhalt like '%/n%';
181
182-- ... schriftinhalt like '%/%';
183-- RLP: FlurstÌcks-Bruchnummer art='ZAE_NEN' als Schriftinhalt (2 FÀlle)
184
185
186
187CREATE OR REPLACE VIEW s_allgemeine_texte_arten
188AS
189 SELECT DISTINCT art
190   FROM s_allgemeine_texte;
191
192-- dies liefert die Werte:
193--  Bahnverkehr, BWF, FKT_LGT, Fliessgewaesser, FreierText, Gewanne, NAM, Platz,
194--  StehendesGewaesser, Strasse, urn:adv:fachdatenv, Weg, ZNM
195
196
197
198-- EXTENT fÃŒr Mapfile eines Mandenten ermitteln
199
200CREATE OR REPLACE VIEW flurstuecks_minmax AS
201 SELECT min(st_xmin(wkb_geometry)) AS r_min,
202        min(st_ymin(wkb_geometry)) AS h_min,
203        max(st_xmax(wkb_geometry)) AS r_max,
204        max(st_ymax(wkb_geometry)) AS h_max
205   FROM public.ax_flurstueck;
206
207COMMENT ON VIEW flurstuecks_minmax IS 'Maximale Ausdehnung von ax_flurstueck fuer EXTENT-Angabe im Mapfile';
208
209
210
211-- Nach Laden der Keytables:
212
213-- MAP ALT:
214-- 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"
215
216CREATE OR REPLACE VIEW baurecht
217AS
218  SELECT r.ogc_fid,
219         r.wkb_geometry,
220         r.gml_id,
221         r.artderfestlegung as adfkey, -- Art der Festlegung - Key
222         r."name",                     -- Eigenname des Gebietes
223         r.stelle,                     -- Stelle Key
224         r.bezeichnung AS rechtbez,    -- Verfahrensnummer
225         a.bezeichner  AS adfbez,      -- Art der Festlegung - Bezeichnung
226         d.bezeichnung AS stellbez     -- Stelle Bezeichnung
227      -- , d.stellenart                -- weiter entschluesseln?
228    FROM ax_bauraumoderbodenordnungsrecht r
229    LEFT JOIN ax_bauraumoderbodenordnungsrecht_artderfestlegung a
230      ON r.artderfestlegung = a.wert
231    LEFT JOIN ax_dienststelle d
232      ON r.land = d.land AND r.stelle = d.stelle
233 ;
234
235-- MAP NEU:
236-- 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
237
238
239-- Man glaubt es kaum, aber im ALKIS haben Gemeinde und Gemarkung keinerlei Beziehung miteinander
240-- Nur durch Auswertung der FlurstÃŒcke kann man ermitteln, in welcher Gemeinde eine Gemarkung liegt.
241
242CREATE OR REPLACE VIEW gemeinde_in_gemarkung
243AS
244  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
245  FROM            ax_flurstueck
246  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
247;
248
249COMMENT ON VIEW gemeinde_in_gemarkung IS 'Welche Gemarkung liegt in welcher Gemeinde? Durch Verweise aus FlurstÃŒck.';
250
251
252-- Untersuchen, welche Geometrie-Typen vorkommen
253
254CREATE OR REPLACE VIEW arten_von_flurstuecksgeometrie
255AS
256 SELECT   count(gml_id) as anzahl,
257          st_geometrytype(wkb_geometry)
258 FROM     ax_flurstueck
259 GROUP BY st_geometrytype(wkb_geometry);
260
261-- Lage
262--   256 ST_MultiPolygon
263-- 23377 ST_Polygon
264
265-- RLP
266--    2 ST_MultiPolygon
267-- 2367 ST_Polygon
268
269
270-- A d r e s s e n
271
272-- Verschluesselte Lagebezeichnung (Strasse und Hausnummer) fuer eine Gemeinde
273-- Schluessel der Gemeinde nach Bedarf anpassen!
274
275CREATE VIEW  adressen_hausnummern
276AS
277    SELECT
278        s.bezeichnung AS strassenname,
279         g.bezeichnung AS gemeindename,
280         l.land,
281         l.regierungsbezirk,
282         l.kreis,
283         l.gemeinde,
284         l.lage        AS strassenschluessel,
285         l.hausnummer
286    FROM   ax_lagebezeichnungmithausnummer l 
287    JOIN   ax_gemeinde g
288      ON l.kreis=g.kreis
289     AND l.gemeinde=g.gemeinde
290    JOIN   ax_lagebezeichnungkatalogeintrag s
291      ON l.kreis=s.kreis
292     AND l.gemeinde=s.gemeinde
293     AND to_char(l.lage, 'FM00000')=s.lage
294    WHERE     l.gemeinde = 40  -- 40 = Lage
295 -- LIMIT 200
296;
297
298
299-- Zuordnung dieser Adressen zu Flurstuecken
300-- Schluessel der Gemeinde nach Bedarf anpassen!
301
302CREATE VIEW adressen_zum_flurstueck
303AS
304    SELECT
305           f.gemarkungsnummer,
306           f.flurnummer,
307           f.zaehler,
308           f.nenner,
309           g.bezeichnung AS gemeindename,
310           s.bezeichnung AS strassenname,
311           l.lage        AS strassenschluessel,
312           l.hausnummer
313      FROM   ax_flurstueck f
314      JOIN   alkis_beziehungen v
315        ON f.gml_id=v.beziehung_von
316      JOIN   ax_lagebezeichnungmithausnummer l 
317        ON l.gml_id=v.beziehung_zu
318      JOIN   ax_gemeinde g
319        ON l.kreis=g.kreis
320       AND l.gemeinde=g.gemeinde
321      JOIN   ax_lagebezeichnungkatalogeintrag s
322        ON l.kreis=s.kreis
323       AND l.gemeinde=s.gemeinde
324       AND to_char(l.lage, 'FM00000')=s.lage
325     WHERE v.beziehungsart='weistAuf'
326       AND l.gemeinde = 40  -- 40 = Lage
327     ORDER BY
328           f.gemarkungsnummer,
329           f.flurnummer,
330           f.zaehler,
331           f.nenner
332   -- LIMIT 200
333;
334
335
336-- FlurstÃŒcke eines EigentÃŒmers
337-- ----------------------------
338
339-- Dieser View liefert nur die (einfache) Buchungsart "GrundstÃŒck"
340-- Solche FÀlle wie "Erbbaurecht an GrundstÌck" oder "Wohnungs-/Teileigentum an aufgeteiltes GrundstÌck"
341-- oder "Miteigentum an aufteteiltes GrundstÃŒck" fehlen in deisere Auswertung.
342-- Dazu siehe: "rechte_eines_eigentuemers".
343
344-- Das Ergbenis ist gedacht fÃŒr den Export als CSV und Weiterverarbeitung mit einer Tabellenkalkulation
345-- oder einer einfachen Datenbank.
346
347-- Übersicht der Tabellen:
348--
349-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle >istGebucht> Flurstueck
350--                                              *-> Bezirk                *-> Buchungsart     *-> Gemarkung
351
352-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
353
354CREATE VIEW flurstuecke_eines_eigentuemers
355AS
356   SELECT
357      k.bezeichnung                AS gemarkung,
358      k.gemarkungsnummer,
359      f.flurnummer                 AS flur,
360      f.zaehler                    AS fs_zaehler,
361      f.nenner                     AS fs_nenner,
362      f.amtlicheflaeche,
363   -- g.bezirk,
364      b.bezeichnung                AS bezirkname,
365      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
366      g.blattart,
367      s.laufendenummer             AS bvnr,
368      art.bezeichner               AS buchungsart,
369   -- s.zaehler || '/' || s.nenner AS buchg_anteil,
370      n.laufendenummernachdin1421  AS lfd_name_num,
371   -- n.zaehler || '/' || n.nenner AS nam_anteil,
372      p.nachnameoderfirma  --,
373   -- p.vorname
374   FROM       ax_person              p
375        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
376        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
377        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
378        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
379        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
380        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
381        JOIN  ax_buchungsstelle      s    ON s.gml_id = bgs.beziehung_von
382        JOIN  ax_buchungsstelle_buchungsart art ON s.buchungsart = art.wert
383        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = s.gml_id
384        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
385        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
386   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!
387     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
388     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
389     AND bgs.beziehungsart = 'istBestandteilVon' -- Buchungs-Stelle >> Grundbuch
390     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> Buchungs-Stelle
391   ORDER BY   
392         k.bezeichnung,
393         f.flurnummer,
394         f.zaehler,
395         f.nenner,
396         g.bezirk,
397         g.buchungsblattnummermitbuchstabenerweiterung,
398         s.laufendenummer
399;
400
401
402-- Rechte eines EigentÃŒmers
403-- ------------------------
404
405-- Dieser View sucht speziell die FÀlle wo eine Buchungsstelle ein Recht "an" einer anderen Buchungsstelle hat.
406--  - "Erbbaurecht *an* GrundstÃŒck"
407--  - "Wohnungs-/Teileigentum *an* Aufgeteiltes GrundstÃŒck"
408--  - "Miteigentum *an* Aufteteiltes GrundstÃŒck"
409-- Suchkriterium ist der Name des EigentÃŒmers auf dem "herrschenden" Grundbuch, also dem Besitzer des Rechtes.
410
411-- Diese FÀlle fehlen im View "flurstuecke_eines_eigentuemers".
412
413-- Übersicht der Tabellen:
414--
415-- Person <benennt< NamNum. >istBestandteilVon> Blatt <istBestandteilVon< Stelle-h >an> Stelle-d >istGebucht> Flurstueck
416--
417
418-- Wobei ">xxx>" = JOIN ÃŒber die Verbindungs-Tabelle "alkis_beziehungen" mit der Beziehungsart "xxx".
419
420
421CREATE VIEW rechte_eines_eigentuemers
422AS
423   SELECT
424      k.bezeichnung                AS gemarkung,
425      k.gemarkungsnummer,
426      f.flurnummer                 AS flur,
427      f.zaehler                    AS fs_zaehler,
428      f.nenner                     AS fs_nenner,
429      f.amtlicheflaeche,
430   -- g.bezirk,
431      b.bezeichnung                AS bezirkname,
432      g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
433   -- g.blattart,
434      sh.laufendenummer            AS bvnr_herr,
435      sh.zaehler || '/' || sh.nenner AS buchg_anteil_herr,
436      arth.bezeichner              AS buchungsart_herrschend,
437      bss.beziehungsart            AS bez_art,
438      artd.bezeichner              AS buchungsart_dienend,
439      sd.laufendenummer            AS bvnr_dien,
440   -- sd.zaehler || '/' || sd.nenner AS buchg_anteil_dien,
441      n.laufendenummernachdin1421  AS lfd_name_num,
442   -- n.zaehler || '/' || n.nenner AS nam_anteil,
443      p.nachnameoderfirma  --,
444   -- p.vorname
445   FROM       ax_person              p
446        JOIN  alkis_beziehungen      bpn  ON bpn.beziehung_zu  = p.gml_id
447        JOIN  ax_namensnummer        n    ON bpn.beziehung_von =n.gml_id
448        JOIN  alkis_beziehungen      bng  ON n.gml_id = bng.beziehung_von
449        JOIN  ax_buchungsblatt       g    ON bng.beziehung_zu = g.gml_id
450        JOIN  ax_buchungsblattbezirk b    ON g.land = b.land AND g.bezirk = b.bezirk
451        JOIN  alkis_beziehungen      bgs  ON bgs.beziehung_zu = g.gml_id
452        JOIN  ax_buchungsstelle      sh   ON sh.gml_id = bgs.beziehung_von  -- herrschende Buchung
453        JOIN  ax_buchungsstelle_buchungsart arth ON sh.buchungsart = arth.wert
454        JOIN  alkis_beziehungen      bss  ON sh.gml_id = bss.beziehung_von
455        JOIN  ax_buchungsstelle      sd   ON sd.gml_id = bss.beziehung_zu   -- dienende Buchung
456        JOIN  ax_buchungsstelle_buchungsart artd ON sd.buchungsart = artd.wert
457        JOIN  alkis_beziehungen      bsf  ON bsf.beziehung_zu = sd.gml_id
458        JOIN  ax_flurstueck          f    ON f.gml_id = bsf.beziehung_von
459        JOIN  ax_gemarkung           k    ON f.land = k.land AND f.gemarkungsnummer = k.gemarkungsnummer
460   WHERE p.nachnameoderfirma LIKE 'Stadt %'   -- ** Bei Bedarf anpassen!
461     AND bpn.beziehungsart = 'benennt'           -- Namennummer     >> Person
462     AND bng.beziehungsart = 'istBestandteilVon' -- Namensnummer    >> Grundbuch
463     AND bgs.beziehungsart = 'istBestandteilVon' -- B-Stelle herr   >> Grundbuch
464     AND bss.beziehungsart in ('an','zu')        -- B-Stelle herr.  >> B-Stelle dien.
465     AND bsf.beziehungsart = 'istGebucht'        -- Flurstueck      >> B-Stelle dien
466   ORDER BY   
467         k.bezeichnung,
468         f.flurnummer,
469         f.zaehler,
470         f.nenner,
471         g.bezirk,
472         g.buchungsblattnummermitbuchstabenerweiterung,
473         sh.laufendenummer
474;
475
476
477-- END --
478
Note: See TracBrowser for help on using the repository browser.