source: trunk/import/pp_definition.sql @ 278

Revision 278, 13.9 KB checked in by frank.jaeger, 7 years ago (diff)

Mapbender-Navigation mit ALKIS-Daten komplett überarbeitet.

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