source: trunk/import/pp_definition.sql @ 316

Revision 316, 15.9 KB checked in by astrid.emde, 7 years ago (diff)

festen Code 25832 durch Variable erstetzt alkis_epsg

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