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

Revision 163, 11.8 KB checked in by frank.jaeger, 12 years ago (diff)

Vers. 0.7: Flächen von Fluren, Gemarkungen, Gemeinden zusammen fassen und darstellen

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