source: trunk/data/konvert/postnas_0.6/gemeinden_definition.sql @ 144

Revision 140, 7.3 KB checked in by frank.jaeger, 12 years ago (diff)

Filter auf Gemeinde bei Navigation bei Suche nach Eigentümer. Dazu Hilfstabelle aufbauen.

Line 
1
2-- ALKIS PostNAS 0.6
3
4
5-- Teil 1: Anlegen der Tabellen
6
7
8-- Stand
9
10--  2011-07-25 PostNAS 06, Umbenennung
11--  2011-12-08 Person -> Gemeinde
12
13SET client_encoding = 'UTF-8';
14
15-- Alles auf Anfang!
16
17-- DROP VIEW gemeinde_person_typ1;
18-- DROP VIEW gemeinde_person_typ2;
19
20-- DROP TABLE gemeinde_gemarkung;
21
22
23-- =======================================================
24-- Tabelle fuer die Zuordnung vom Gemarkungen zu Gemeinden
25-- =======================================================
26
27-- FÃŒr die Regelung der Zugriffsberechtigung einer Gemeindeverwaltung auf die
28-- FlurstÃŒcke in ihrem Gebiet braucht man die Information, in welcher Gemeinde eine Gemarkung liegt.
29-- 'ax_gemeinde' und 'ax_gemarkung' haben aber im ALKIS keinerlei Beziehung zueinander - kaum zu glauben!
30-- Nur ÃŒber die Auswertung der FlurstÃŒcke kann man die Zuordnung ermitteln.
31-- Da nicht stÀndig mit 'SELECT DISTINCT' sÀmtliche FlurstÌcke durchsucht werden können,
32-- muss diese Information als (redundante) Tabelle nach dem Laden zwischengespeichert werden.
33
34CREATE TABLE gemeinde_gemarkung (
35  land                  integer,
36  regierungsbezirk      integer,
37  kreis                 integer,
38  gemeinde              integer,
39  gemarkung             integer NOT NULL,
40  gemeindename          character varying(80),
41  gemarkungsname        character varying(80),
42  gkz                   character varying(3), 
43  CONSTRAINT gemeinde_gemarkung_pk PRIMARY KEY (land, gemarkung)
44);
45
46
47COMMENT ON TABLE  gemeinde_gemarkung                IS 'Beziehung: Gemarkung liegt in Gemeinde';
48COMMENT ON COLUMN gemeinde_gemarkung.gemeinde       IS 'Gemeindenummer';
49COMMENT ON COLUMN gemeinde_gemarkung.gemarkung      IS 'Gemarkungsnummer';
50
51COMMENT ON COLUMN gemeinde_gemarkung.gkz            IS 'Gemeindekennziffer fÃŒr Mandant';
52
53
54-- =======================================================
55-- Tabelle fuer die Zuordnung vom EigentÃŒmern zu Gemeinden
56-- =======================================================
57
58-- Die Feststellung, ob eine Person (Mit-) EigentÃŒmer von mindestens einem
59-- FlurstÃŒck in einer Gemeinde ist, geht ÃŒber viele Relationen.
60-- Dabei kann es mehrere Varianten geben.
61-- Dies sollte nach dem Laden ermittelt und gespeichert werden, damit dies in
62-- der Navigation auf einfache Art verwendet werden kann.
63
64
65-- DROP TABLE gemeinde_person;
66
67CREATE TABLE gemeinde_person (
68  land                  integer,
69  regierungsbezirk      integer,
70  kreis                 integer,
71  gemeinde              integer,
72  person                character varying(16),
73  buchtyp               integer,
74  CONSTRAINT gemeinde_person_pk PRIMARY KEY (gemeinde, person)
75);
76
77
78COMMENT ON TABLE  gemeinde_person                IS 'Person ist EigentÃŒmer von mindestens einem FlurstÃŒck in der Gemeinde';
79COMMENT ON COLUMN gemeinde_person.gemeinde       IS 'Gemeindenummer';
80COMMENT ON COLUMN gemeinde_person.buchtyp        IS 'Typ der Buchung 1=direkt, 2=Recht einer Buchungsstele an andere Buchungsstelle';
81COMMENT ON COLUMN gemeinde_person.person         IS 'gml_id von Person';
82
83-- Index zum Filtern in der Buchauskunft
84CREATE INDEX person_gemeinde  ON gemeinde_person (person, gemeinde);
85
86
87-- =======================================================
88-- VIEWs  fuer die Zuordnung vom EigentÃŒmern zu Gemeinden
89-- =======================================================
90
91-- "Normale" Buchungen
92
93CREATE VIEW gemeinde_person_typ1
94AS
95  SELECT DISTINCT
96    p.gml_id          AS person,
97    g.land, g.regierungsbezirk, g.kreis, g.gemeinde
98
99  FROM ax_person               p
100
101-- Person < benennt < Namensnummer
102  JOIN alkis_beziehungen      bpn  ON bpn.beziehung_zu   = p.gml_id  -- Bez. Person - Nummer
103  JOIN ax_namensnummer         n   ON bpn.beziehung_von  = n.gml_id
104
105-- Namensnummer > istBestandteilVon > Blatt
106  JOIN alkis_beziehungen      bnb  ON bnb.beziehung_von  = n.gml_id  -- Bez. Nummer - Blatt
107  JOIN ax_buchungsblatt        b   ON bnb.beziehung_zu   = b.gml_id
108
109-- Blatt < istBestandteilVon < buchungsStelle
110  JOIN alkis_beziehungen      bbg  ON bbg.beziehung_zu   = b.gml_id  -- Bez. Blatt  - Stelle
111  JOIN ax_buchungsstelle       s   ON bbg.beziehung_von  = s.gml_id
112
113-- buchungsStelle < istGebucht < flurstÃŒck
114  JOIN alkis_beziehungen      bsf  ON bsf.beziehung_zu   = s.gml_id  -- Bez. Stelle - FlurstÃŒck
115  JOIN ax_flurstueck           f   ON bsf.beziehung_von  = f.gml_id
116
117  JOIN ax_gemarkung            k   ON f.land             = k.land
118                                  AND f.gemarkungsnummer = k.gemarkungsnummer
119  JOIN gemeinde_gemarkung      g   ON k.gemarkungsnummer = g.gemarkung
120
121  WHERE bpn.beziehungsart = 'benennt'
122    AND bnb.beziehungsart = 'istBestandteilVon'
123    AND bbg.beziehungsart = 'istBestandteilVon'
124    AND bsf.beziehungsart = 'istGebucht'
125;
126
127COMMENT ON VIEW gemeinde_person_typ1 IS 'Personen die EigentÃŒmer vom FlurstÃŒcken in einer Gemeinde sind. Typ1 = nomale Buchungen mit direkter Beziehung.';
128
129
130-- "Komplexe" Buchungen mit Rechten von Buchungen an Buchungen
131
132CREATE VIEW gemeinde_person_typ2
133AS
134  SELECT DISTINCT
135    p.gml_id          AS person,
136  --bpn.beziehungsart AS bpnbez,
137  --bnb.beziehungsart AS bnbbez,
138  --bbg.beziehungsart AS bbgbez,
139  --bsf.beziehungsart AS bsfbez,
140  --k.gemarkungsnummer,
141    g.land, g.regierungsbezirk, g.kreis, g.gemeinde
142
143  FROM ax_person               p
144
145-- Person < benennt < Namensnummer
146  JOIN alkis_beziehungen      bpn  ON bpn.beziehung_zu   = p.gml_id  -- Bez. Person - Nummer
147  JOIN ax_namensnummer         n   ON bpn.beziehung_von  = n.gml_id
148
149-- Namensnummer > istBestandteilVon > Blatt
150  JOIN alkis_beziehungen      bnb  ON bnb.beziehung_von  = n.gml_id  -- Bez. Nummer - Blatt
151  JOIN ax_buchungsblatt        b   ON bnb.beziehung_zu   = b.gml_id
152
153-- Blatt < istBestandteilVon < buchungsStelle1
154  JOIN alkis_beziehungen      bbg  ON bbg.beziehung_zu   = b.gml_id  -- Bez. Blatt  - Stelle
155  JOIN ax_buchungsstelle       s1  ON bbg.beziehung_von  = s1.gml_id
156
157-- buchungsStelle2 < an < buchungsStelle1
158  JOIN alkis_beziehungen      bss  ON bss.beziehung_von  = s1.gml_id  -- Bez. Stelle  - Stelle
159  JOIN ax_buchungsstelle       s2  ON bss.beziehung_zu   = s2.gml_id
160
161-- buchungsStelle2 < istGebucht < flurstÃŒck
162  JOIN alkis_beziehungen      bsf  ON bsf.beziehung_zu   = s2.gml_id  -- Bez. Stelle - FlurstÃŒck
163  JOIN ax_flurstueck           f   ON bsf.beziehung_von  = f.gml_id
164
165  JOIN ax_gemarkung            k   ON f.land             = k.land
166                                  AND f.gemarkungsnummer = k.gemarkungsnummer
167  JOIN gemeinde_gemarkung      g   ON k.gemarkungsnummer = g.gemarkung
168
169  WHERE bpn.beziehungsart = 'benennt'
170    AND bnb.beziehungsart = 'istBestandteilVon'
171    AND bbg.beziehungsart = 'istBestandteilVon'
172    AND bss.beziehungsart = 'an'
173    AND bsf.beziehungsart = 'istGebucht'
174 -- LIMIT 100  -- Test-Option
175;
176
177COMMENT ON VIEW gemeinde_person_typ2 IS 'Personen die EigentÃŒmer vom FlurstÃŒcken in einer Gemeinde sind. Typ2 = Buchungen mit Rechten einer Buchungssstelle an einer anderen.';
178
179
180-- Statistik ÃŒber die Buchungs-Typen je Gemeinde
181CREATE VIEW gemeinde_person_statistik
182AS
183  SELECT p.land, p.regierungsbezirk, p.kreis, p.gemeinde, g.gemeindename, p.buchtyp, count(p.person) as personen
184  FROM   gemeinde_person    p
185  JOIN   gemeinde_gemarkung g
186    ON   p.land     = g.land
187    AND  p.regierungsbezirk = g.regierungsbezirk
188    AND  p.kreis    = g.kreis
189    AND  p.gemeinde = g.gemeinde
190  GROUP BY p.land, p.regierungsbezirk, p.kreis, p.gemeinde, g.gemeindename, p.buchtyp
191  ORDER BY p.land, p.regierungsbezirk, p.kreis, p.gemeinde, p.buchtyp
192;
193
194COMMENT ON VIEW gemeinde_person_statistik IS 'ZÀhlen der Personen je Gemeinde und Buchungstyp';
195
196
197-- ENDE --
Note: See TracBrowser for help on using the repository browser.