source: trunk/data/konvert/postnas_0.7/pp_definition.sql @ 162

Revision 162, 10.2 KB checked in by frank.jaeger, 8 years ago (diff)

Entwurf: Post-Processing-Scripte Flur/Gemarkung/Gemeinde?

Line 
1
2-- ALKIS PostNAS 0.7
3
4-- Post Processing Teil 1: Anlegen der Tabellen
5
6-- Stand
7
8--  2012-02-13 PostNAS 07, Umbenennung
9
10-- ============================
11-- Tabellen des Post-Processing
12-- ============================
13
14-- Einige Informationen liegen nach der NAS-Konvertierung in der Datenbank "verstreut" vor.
15-- Die dynamische Aufbereitung ÃŒber Views und Functions wÃŒrde zu lange dauern und somit lange
16-- Antwortzeiten in WMS, WFS, Buchauskunft oder Navigation (Suche) verursachen.
17
18-- Im Rahmen eines "Post-Processing" werden diese Daten nach jeder Konvertierung (NBA-Aktialisierung)
19-- einmal komplett aufbereitet. Die benötigten Informationen stehen somit den Anwendungen mundgerecht zur VerfÌgung.
20
21-- Die per PostProcessing gefÃŒllten Tabellen bekommen den Prefix "pp_".
22
23-- ToDo:
24
25-- Muss *multi*-Polygon sein? Gibt es "zerrissene" Fluren/Gemarkungen?
26
27
28SET client_encoding = 'UTF-8';
29
30-- Alles auf Anfang!
31
32-- DROP VIEW gemeinde_person_typ1;
33-- DROP VIEW gemeinde_person_typ2;
34-- DROP VIEW gemeinde_gemarkung;
35-- DROP TABLE pp_gemeinde;
36-- DROP TABLE pp_gemarkung;
37-- DROP TABLE pp_flur;
38
39
40-- Tabelle fuer Gemeinden
41-- ========================
42
43  CREATE TABLE pp_gemeinde (
44    land                integer,
45    regierungsbezirk    integer,
46    kreis               integer,
47    gemeinde            integer,
48    gemeindename        character varying(80),
49    gkz                 character varying(03),  -- wird (noch) nicht benutzt
50    anz_gemarkg         integer,                -- Anzahl Gemarkungen
51    CONSTRAINT pp_gemeinde_pk PRIMARY KEY (land, gemeinde)
52  );
53
54
55-- GesamtflÀche
56SELECT AddGeometryColumn('pp_gemeinde','the_geom','25832','MULTIPOLYGON',2);
57
58-- Index
59CREATE INDEX pp_gemeinde_gidx ON pp_gemeinde USING gist(the_geom);
60
61-- vereinfachte GesamtflÀche
62SELECT AddGeometryColumn('pp_gemeinde','simple_geom','25832','MULTIPOLYGON',2);
63
64CREATE INDEX pp_gemeinde_sgidx ON pp_gemeinde USING gist(simple_geom);
65
66
67COMMENT ON TABLE  pp_gemeinde                IS 'Post-Processing: Gemeinde';
68COMMENT ON COLUMN pp_gemeinde.gemeinde       IS 'Gemeindenummer';
69COMMENT ON COLUMN pp_gemeinde.gkz            IS 'Gemeindekennziffer fÃŒr Mandant';
70
71
72-- Tabelle fuer Gemarkungen
73-- ========================
74
75-- FÃŒr die Regelung der Zugriffsberechtigung einer Gemeindeverwaltung auf die
76-- FlurstÃŒcke in ihrem Gebiet braucht man die Information, in welcher Gemeinde eine Gemarkung liegt.
77-- 'ax_gemeinde' und 'ax_gemarkung' haben aber im ALKIS keinerlei Beziehung zueinander - kaum zu glauben!
78-- Nur ÃŒber die Auswertung der FlurstÃŒcke kann man die Zuordnung ermitteln.
79-- Da nicht stÀndig mit 'SELECT DISTINCT' sÀmtliche FlurstÌcke durchsucht werden können,
80-- muss diese Information als (redundante) Tabelle nach dem Laden zwischengespeichert werden.
81
82--CREATE TABLE gemeinde_gemarkung (  -- PostNAS 0.6
83  CREATE TABLE pp_gemarkung (        -- PostNAS 0.7
84    land                integer,
85    regierungsbezirk    integer,
86    kreis               integer,
87    gemeinde            integer,        -- fast ein Foreign-Key Constraint
88    gemarkung           integer NOT NULL,
89    gemarkungsname      character varying(80),
90    anz_flur            integer,        -- Anzahl Fluren
91    CONSTRAINT pp_gemarkung_pk PRIMARY KEY (land, gemarkung)
92  );
93
94
95-- GesamtflÀche
96SELECT AddGeometryColumn('pp_gemarkung','the_geom','25832','MULTIPOLYGON',2);
97
98-- Index
99CREATE INDEX pp_gemarkung_gidx ON pp_gemarkung USING gist(the_geom);
100
101
102-- vereinfachte GesamtflÀche
103SELECT AddGeometryColumn('pp_gemarkung','simple_geom','25832','MULTIPOLYGON',2);
104
105CREATE INDEX pp_gemarkung_sgidx ON pp_gemarkung USING gist(simple_geom);
106
107
108COMMENT ON TABLE  pp_gemarkung                IS 'Post-Processing: Gemarkung. u.a. liegt in welcher Gemeinde';
109COMMENT ON COLUMN pp_gemarkung.gemeinde       IS 'Gemeindenummer';
110COMMENT ON COLUMN pp_gemarkung.gemarkung      IS 'Gemarkungsnummer';
111
112
113-- Tabelle fuer Fluren
114-- ===================
115
116  CREATE TABLE pp_flur (
117    land                integer,
118    regierungsbezirk    integer,
119    kreis               integer,
120    gemarkung           integer NOT NULL,
121    flurnummer          integer NOT NULL,
122    anz_fs              integer,        -- Anzahl FlurstÃŒcke
123    CONSTRAINT pp_flur_pk PRIMARY KEY (land, gemarkung, flurnummer)
124  );
125
126-- GesamtflÀche
127SELECT AddGeometryColumn('pp_flur','the_geom','25832','MULTIPOLYGON',2);
128
129-- Index
130CREATE INDEX pp_flur_gidx ON pp_flur USING gist(the_geom);
131
132COMMENT ON TABLE  pp_flur                IS 'Post-Processing: Flur';
133COMMENT ON COLUMN pp_flur.gemarkung      IS 'Gemarkungsnummer';
134
135
136-- =======================================================
137-- Tabelle fuer die Zuordnung vom EigentÃŒmern zu Gemeinden
138-- =======================================================
139
140-- Die Feststellung, ob eine Person (Mit-) EigentÃŒmer von mindestens einem
141-- FlurstÃŒck in einer Gemeinde ist, geht ÃŒber viele Relationen.
142-- Dabei kann es mehrere Varianten geben.
143-- Dies sollte nach dem Laden ermittelt und gespeichert werden, damit dies in
144-- der Navigation auf einfache Art verwendet werden kann.
145
146
147-- pp_  ?
148
149--DROP TABLE gemeinde_person;
150
151  CREATE TABLE gemeinde_person (
152    land                        integer,
153    regierungsbezirk    integer,
154    kreis                       integer,
155    gemeinde            integer,
156    person              character varying(16),
157    buchtyp             integer,
158    CONSTRAINT gemeinde_person_pk PRIMARY KEY (gemeinde, person)
159  );
160
161COMMENT ON TABLE  gemeinde_person            IS 'Person ist EigentÃŒmer von mindestens einem FlurstÃŒck in der Gemeinde';
162COMMENT ON COLUMN gemeinde_person.gemeinde   IS 'Gemeindenummer';
163COMMENT ON COLUMN gemeinde_person.buchtyp    IS 'Typ der Buchung 1=direkt, 2=Recht einer Buchungsstele an andere Buchungsstelle';
164COMMENT ON COLUMN gemeinde_person.person     IS 'gml_id von Person';
165
166-- Index zum Filtern in der Buchauskunft
167CREATE INDEX person_gemeinde  ON gemeinde_person (person, gemeinde);
168
169
170-- =====
171-- VIEWs
172-- =====
173
174-- Ein View, der ÃŒbergangsweise die ehemalige Tabelle mit diesem Namen ersetzt.
175-- Wird in der Navigatioin verwendet, bis alle Datenbanken auf die Struktur 0.7 umgestellt
176-- sind *UND* die Navigation an die neuen Tabellen angepasst ist.
177
178CREATE VIEW gemeinde_gemarkung
179AS
180  SELECT g.land, g.regierungsbezirk, g.kreis, g.gemeinde, k.gemarkung, g.gemeindename, k.gemarkungsname, g.gkz
181  FROM pp_gemarkung k
182  JOIN pp_gemeinde  g
183    ON k.land = g.land
184   AND k.gemeinde = g.gemeinde
185;
186
187-- VIEWs  fuer die Zuordnung vom EigentÃŒmern zu Gemeinden
188-- ------------------------------------------------------
189
190-- "Normale" Buchungen
191
192CREATE VIEW gemeinde_person_typ1
193AS
194  SELECT DISTINCT
195    p.gml_id          AS person,
196    g.land, g.regierungsbezirk, g.kreis, g.gemeinde
197
198  FROM ax_person               p
199
200-- Person < benennt < Namensnummer
201  JOIN alkis_beziehungen      bpn  ON bpn.beziehung_zu   = p.gml_id  -- Bez. Person - Nummer
202  JOIN ax_namensnummer         n   ON bpn.beziehung_von  = n.gml_id
203
204-- Namensnummer > istBestandteilVon > Blatt
205  JOIN alkis_beziehungen      bnb  ON bnb.beziehung_von  = n.gml_id  -- Bez. Nummer - Blatt
206  JOIN ax_buchungsblatt        b   ON bnb.beziehung_zu   = b.gml_id
207
208-- Blatt < istBestandteilVon < buchungsStelle
209  JOIN alkis_beziehungen      bbg  ON bbg.beziehung_zu   = b.gml_id  -- Bez. Blatt  - Stelle
210  JOIN ax_buchungsstelle       s   ON bbg.beziehung_von  = s.gml_id
211
212-- buchungsStelle < istGebucht < flurstÃŒck
213  JOIN alkis_beziehungen      bsf  ON bsf.beziehung_zu   = s.gml_id  -- Bez. Stelle - FlurstÃŒck
214  JOIN ax_flurstueck           f   ON bsf.beziehung_von  = f.gml_id
215
216  JOIN ax_gemarkung            k   ON f.land             = k.land
217                                  AND f.gemarkungsnummer = k.gemarkungsnummer
218  JOIN gemeinde_gemarkung      g   ON k.gemarkungsnummer = g.gemarkung
219
220  WHERE bpn.beziehungsart = 'benennt'
221    AND bnb.beziehungsart = 'istBestandteilVon'
222    AND bbg.beziehungsart = 'istBestandteilVon'
223    AND bsf.beziehungsart = 'istGebucht'
224;
225
226COMMENT ON VIEW gemeinde_person_typ1 IS 'Personen die EigentÃŒmer vom FlurstÃŒcken in einer Gemeinde sind. Typ1 = nomale Buchungen mit direkter Beziehung.';
227
228
229-- "Komplexe" Buchungen mit Rechten von Buchungen an Buchungen
230
231CREATE VIEW gemeinde_person_typ2
232AS
233  SELECT DISTINCT
234    p.gml_id          AS person,
235    g.land, g.regierungsbezirk, g.kreis, g.gemeinde
236  FROM ax_person               p
237
238-- Person < benennt < Namensnummer
239  JOIN alkis_beziehungen      bpn  ON bpn.beziehung_zu   = p.gml_id  -- Bez. Person - Nummer
240  JOIN ax_namensnummer         n   ON bpn.beziehung_von  = n.gml_id
241
242-- Namensnummer > istBestandteilVon > Blatt
243  JOIN alkis_beziehungen      bnb  ON bnb.beziehung_von  = n.gml_id  -- Bez. Nummer - Blatt
244  JOIN ax_buchungsblatt        b   ON bnb.beziehung_zu   = b.gml_id
245
246-- Blatt < istBestandteilVon < buchungsStelle1
247  JOIN alkis_beziehungen      bbg  ON bbg.beziehung_zu   = b.gml_id  -- Bez. Blatt  - Stelle
248  JOIN ax_buchungsstelle       s1  ON bbg.beziehung_von  = s1.gml_id
249
250-- buchungsStelle2 < an < buchungsStelle1
251  JOIN alkis_beziehungen      bss  ON bss.beziehung_von  = s1.gml_id  -- Bez. Stelle  - Stelle
252  JOIN ax_buchungsstelle       s2  ON bss.beziehung_zu   = s2.gml_id
253
254-- buchungsStelle2 < istGebucht < flurstÃŒck
255  JOIN alkis_beziehungen      bsf  ON bsf.beziehung_zu   = s2.gml_id  -- Bez. Stelle - FlurstÃŒck
256  JOIN ax_flurstueck           f   ON bsf.beziehung_von  = f.gml_id
257
258  JOIN ax_gemarkung            k   ON f.land             = k.land
259                                  AND f.gemarkungsnummer = k.gemarkungsnummer
260  JOIN gemeinde_gemarkung      g   ON k.gemarkungsnummer = g.gemarkung
261
262  WHERE bpn.beziehungsart = 'benennt'
263    AND bnb.beziehungsart = 'istBestandteilVon'
264    AND bbg.beziehungsart = 'istBestandteilVon'
265    AND bss.beziehungsart = 'an'
266    AND bsf.beziehungsart = 'istGebucht'
267 -- LIMIT 100  -- Test-Option
268;
269
270COMMENT 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.';
271
272
273-- Statistik ÃŒber die Buchungs-Typen je Gemeinde
274CREATE VIEW gemeinde_person_statistik
275AS
276  SELECT p.land, p.regierungsbezirk, p.kreis, p.gemeinde, g.gemeindename, p.buchtyp, count(p.person) as personen
277  FROM   gemeinde_person    p
278  JOIN   gemeinde_gemarkung g
279    ON   p.land     = g.land
280    AND  p.regierungsbezirk = g.regierungsbezirk
281    AND  p.kreis    = g.kreis
282    AND  p.gemeinde = g.gemeinde
283  GROUP BY p.land, p.regierungsbezirk, p.kreis, p.gemeinde, g.gemeindename, p.buchtyp
284  ORDER BY p.land, p.regierungsbezirk, p.kreis, p.gemeinde, p.buchtyp
285;
286
287COMMENT ON VIEW gemeinde_person_statistik IS 'ZÀhlen der Personen je Gemeinde und Buchungstyp';
288
289
290-- ENDE --
Note: See TracBrowser for help on using the repository browser.