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

Revision 178, 13.3 KB checked in by frank.jaeger, 8 years ago (diff)

delete/replace aus GDAL Patch #5444

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