source: trunk/import/pp_definition.sql @ 336

Revision 333, 17.9 KB checked in by frank.jaeger, 10 years ago (diff)

Anpassung der Programme und Views an verschieden lange gm_id und Datenbanken mit historischen Objekten.

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