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

Revision 176, 13.0 KB checked in by frank.jaeger, 12 years ago (diff)

Flurstücksnummern an Standard-Position aus Fläche generieren

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