source: trunk/import/pp_definition.sql @ 256

Revision 256, 13.7 KB checked in by frank.jaeger, 11 years ago (diff)

pp_flur.simple_geom

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