source: trunk/import/pp_laden.sql @ 319

Revision 319, 11.5 KB checked in by frank.jaeger, 10 years ago (diff)

nur kleine Korrekturen

Line 
1
2-- ALKIS PostNAS 0.7
3
4-- Post Processing (pp_) Teil 2: Laden der Tabellen
5
6-- Stand
7
8--  2012-02-13 PostNAS 07, Umbenennung
9--  2012-02-17 Optimierung
10--  2012-04-17 Flurstuecksnummern auf Standardposition
11--  2012-04-24 Generell Filter 'endet IS NULL' um historische Objekte auszublenden
12--  2012-04-25 Abstürze und Fehler (durch kaputte Geometrie?) beim Zusammenfassen der Flächen
13--  2012-10-29 F.J. Redundanzen aus alkis_beziehungen beseitigen, die nach NAS replace auftreten
14--  2013-02-06 A.E. Function-Name an PostGIS 2 angepasst: multi() -> st_multi(), simplify() -> st_simplify()
15--  2013-02-21 F.J. doppelte Buchungen zum Flurstück aus alkis_beziehungen beseitigen, die nach NAS replace auftreten
16--  2013-07-10 F.J. Bereinigen der alkis_beziehungen auskommentiert, wird jetzt im Trigger gelöst.
17--  2012-10-24 Neue Tabelle für die Präsentation von Straßennamen und -Klassifikationen
18--  2014-02-05 Bereits auskommentierte Aktionen gelöscht für die Beseitigung von Rdundanzen aus fehlerhaften Triggern
19--  2014-02-12 Zusammen fassen Flur->Gemarkung->Gemeinde nicht aus simple_geom weil dadurch Löscher entstehen können.
20
21-- ============================
22-- Tabellen des Post-Processing
23-- ============================
24
25-- Einige Informationen liegen nach der NAS-Konvertierung in der Datenbank "verstreut" vor.
26-- Die dynamische Aufbereitung über Views und Functions würde zu lange dauern und somit lange
27-- Antwortzeiten in WMS, WFS, Buchauskunft oder Navigation (Suche) verursachen.
28
29-- Im Rahmen eines "Post-Processing" werden diese Daten nach jeder Konvertierung (NBA-Aktualisierung)
30-- einmal komplett aufbereitet. Die benötigten Informationen stehen somit den Anwendungen mundgerecht zur Verfügung.
31
32-- Die per PostProcessing gefüllten Tabellen bekommen den Profix "pp_".
33
34-- Die Ausführung dieses Scriptes auf einer Datenbank für eine 80T-Einwohner-Stadt dauert ca.: 500 Sek. !
35
36SET client_encoding = 'UTF-8';
37
38
39-- ============================================================================
40-- Redundanzen aus alkis_beziehungen beseitigen, die nach NAS replace auftreten
41-- ============================================================================
42-- Workaround: alle Redundazen nach einem Lauf entfernen.
43-- Besser wäre: sofort im Trigger bei replace entfernen.
44-- Siehe Schema in FUNCTION delete_feature_kill
45
46
47-- =================================
48-- Flurstuecksnummern-Label-Position
49-- =================================
50
51-- ersetzt den View "s_flurstueck_nr" für WMS-Layer "ag_t_flurstueck"
52
53--DELETE FROM pp_flurstueck_nr;
54  TRUNCATE pp_flurstueck_nr;  -- effektiver als DELETE
55
56  INSERT INTO pp_flurstueck_nr
57          ( fsgml, fsnum, the_geom )
58    SELECT f.gml_id,
59           f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum,
60           p.wkb_geometry  -- manuelle Position des Textes
61      FROM ap_pto             p
62      JOIN alkis_beziehungen  v  ON p.gml_id       = v.beziehung_von
63      JOIN ax_flurstueck      f  ON v.beziehung_zu = f.gml_id
64     WHERE v.beziehungsart = 'dientZurDarstellungVon'
65       AND f.endet IS NULL
66       AND p.endet IS NULL
67     --AND p."art" = 'ZAE_NEN'
68   UNION
69    SELECT f.gml_id,
70           f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum,
71           ST_PointOnSurface(f.wkb_geometry) AS wkb_geometry  -- Flaechenmitte als Position des Textes
72      FROM      ax_flurstueck     f
73      LEFT JOIN alkis_beziehungen v  ON v.beziehung_zu = f.gml_id
74     WHERE v.beziehungsart is NULL
75       AND f.endet IS NULL
76  ;
77-- Ausführung: mittlere Stadt: ca. 4 - 18 Sec.
78
79
80-- Straßen - N a m e n  und  - K l a s s i f i k a t i o n
81-- Tabellen für die Präsentation von Straßen-Namen und -Klassifikationen
82-- Daten aus dem View "ap_pto_stra" werden im PostProcessing gespeichert in der Tabelle "pp_strassenname".
83-- Der View übernimmt die Auswahl des passenden "advstandardmodell" und rechnet den Winkel passend um.
84-- In der Tabelle werden dann die leer gebliebenen Label aus dem Katalog noch ergänzt.
85
86-- Tabelle aus View befüllen
87TRUNCATE pp_strassenname;
88INSERT INTO pp_strassenname (schriftinhalt, hor, ver, art, winkel, the_geom)
89       SELECT schriftinhalt, hor, ver, art, winkel, wkb_geometry
90       FROM ap_pto_stra; -- View sucht das passende advstandardmodell
91
92-- Schriftinhalt ergänzen
93--DATE ap_pto           p  -- Präsentationsobjekte Punktförmig
94UPDATE pp_strassenname  p
95   SET schriftinhalt =     -- Hier fehlt der Label
96   -- Subquery "Gib mir den Straßennamen":
97   ( SELECT k.bezeichnung                       -- Straßenname ..
98       FROM ax_lagebezeichnungkatalogeintrag k  --  .. aus Katalog
99       JOIN ax_lagebezeichnungohnehausnummer l  -- verwendet als Lage o.H.
100         ON (k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk
101             AND k.kreis=l.kreis AND k.gemeinde=l.gemeinde AND k.lage=l.lage )
102       JOIN alkis_beziehungen x ON l.gml_id = x.beziehung_zu  -- Relation zum Präsentationsobjekt
103      WHERE p.gml_id = x.beziehung_von
104        AND x.beziehungsart = 'dientZurDarstellungVon'
105      -- LIMIT 1 -- war in einem Fall notwendig, wo 2mal der gleiche Text zugeordnet war, Ursache?
106   )
107 WHERE     p.schriftinhalt IS NULL
108   AND NOT p.the_geom      IS NULL;
109
110
111-- ========================================================
112-- Tabellen fuer die Zuordnung vom Gemarkungen zu Gemeinden
113-- ========================================================
114
115-- Für die Regelung der Zugriffsberechtigung einer Gemeindeverwaltung auf die
116-- Flurstücke in ihrem Gebiet braucht man die Information, in welcher Gemeinde eine Gemarkung liegt.
117-- 'ax_gemeinde' und 'ax_gemarkung' haben aber im ALKIS keinerlei Beziehung zueinander - kaum zu glauben!
118-- Nur über die Auswertung der Flurstücke kann man die Zuordnung ermitteln.
119-- Da nicht ständig mit 'SELECT DISTINCT' sämtliche Flurstücke durchsucht werden können,
120-- muss diese Information als (redundante) Tabelle nach dem Laden zwischengespeichert werden.
121
122
123-- G E M A R K U N G
124
125--DELETE FROM pp_gemarkung;
126  TRUNCATE pp_gemarkung;
127
128-- Vorkommende Paarungen Gemarkung <-> Gemeinde in ax_Flurstueck
129INSERT INTO pp_gemarkung
130  (               land, regierungsbezirk, kreis, gemeinde, gemarkung       )
131  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
132  FROM            ax_flurstueck
133  WHERE           endet IS NULL
134  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
135;
136
137-- Namen der Gemarkung dazu als Optimierung bei der Auskunft
138UPDATE pp_gemarkung a
139   SET gemarkungsname =
140   ( SELECT b.bezeichnung
141     FROM    ax_gemarkung b
142     WHERE a.land=b.land
143       AND a.gemarkung=b.gemarkungsnummer
144       AND b.endet IS NULL
145   );
146
147
148-- G E M E I N D E
149
150--DELETE FROM pp_gemeinde;
151  TRUNCATE pp_gemeinde;
152
153-- Vorkommende Gemeinden aus den gemarkungen
154INSERT INTO pp_gemeinde
155  (               land, regierungsbezirk, kreis, gemeinde)
156  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde
157  FROM            pp_gemarkung
158  ORDER BY        land, regierungsbezirk, kreis, gemeinde
159;
160
161
162-- Namen der Gemeinde dazu als Optimierung bei der Auskunft
163UPDATE pp_gemeinde a
164   SET gemeindename =
165   ( SELECT b.bezeichnung
166     FROM    ax_gemeinde b
167     WHERE a.land=b.land
168       AND a.regierungsbezirk=b.regierungsbezirk
169       AND a.kreis=b.kreis
170       AND a.gemeinde=b.gemeinde
171       AND b.endet IS NULL
172   );
173
174
175-- ==============================================================================
176-- Geometrien der Flurstücke schrittweise zu groesseren Einheiten zusammen fassen
177-- ==============================================================================
178
179-- Dies macht nur Sinn, wenn der Inhalt der Datenbank einen ganzen Katasterbezirk enthält.
180-- Wenn ein Gebiet durch geometrische Filter im NBA ausgegeben wurde, dann gibt es Randstreifen,
181-- die zu Pseudo-Fluren zusammen gefasst werden. Fachlich falsch!
182
183-- Ausführungszeit: 1 mittlere Stadt mit ca. 14.000 Flurstücken > 100 Sek
184
185DELETE FROM pp_flur;
186
187INSERT INTO pp_flur (land, regierungsbezirk, kreis, gemarkung, flurnummer, anz_fs, the_geom )
188   SELECT  f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer as gemarkung, f.flurnummer,
189           count(gml_id) as anz_fs,
190           st_multi(st_union(st_buffer(f.wkb_geometry,0.05))) AS the_geom -- Zugabe um Zwischenräume zu vermeiden
191     FROM  ax_flurstueck f
192     WHERE f.endet IS NULL
193  GROUP BY f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer, f.flurnummer;
194
195-- Fluren zu Gemarkungen zusammen fassen
196-- -------------------------------------
197
198-- FEHLER: 290 Absturz PG! Bei Verwendung der ungebufferten präzisen Geometrie. 
199-- bufferOriginalPrecision failed (TopologyException: unable to assign hole to a shell), trying with reduced precision
200-- UPDATE: ../../source/headers/geos/noding/SegmentString.h:175: void geos::noding::SegmentString::testInvariant() const: Zusicherung »pts->size() > 1« nicht erfüllt.
201
202-- Flächen vereinigen
203UPDATE pp_gemarkung a
204  SET the_geom =
205   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.1))) AS the_geom -- Puffer/Zugabe um Löcher zu vermeiden
206       FROM pp_flur b
207      WHERE a.land      = b.land
208        AND a.gemarkung = b.gemarkung
209   );
210
211-- Fluren zaehlen
212UPDATE pp_gemarkung a
213  SET anz_flur =
214   ( SELECT count(flurnummer) AS anz_flur
215     FROM    pp_flur b
216     WHERE a.land      = b.land
217       AND a.gemarkung = b.gemarkung
218   ); -- Gemarkungsnummer ist je BundesLand eindeutig
219
220
221-- Gemarkungen zu Gemeinden zusammen fassen
222-- ----------------------------------------
223
224-- Flächen vereinigen (aus der bereits vereinfachten Geometrie)
225UPDATE pp_gemeinde a
226  SET the_geom =
227   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.1))) AS the_geom -- noch mal Zugabe
228     FROM    pp_gemarkung b
229     WHERE a.land     = b.land
230       AND a.gemeinde = b.gemeinde
231   );
232
233-- Gemarkungen zählen
234UPDATE pp_gemeinde a
235  SET anz_gemarkg =
236   ( SELECT count(gemarkung) AS anz_gemarkg
237     FROM    pp_gemarkung b
238     WHERE a.land     = b.land
239       AND a.gemeinde = b.gemeinde
240   );
241
242
243-- Geometrie glätten / vereinfachen
244-- Diese "simplen" Geometrien sollen nur für die Darstellung einer Übersicht verwendet werden.
245-- Ablage der simplen Geometrie in einem alternativen Geometriefeld im gleichen Datensatz.
246
247UPDATE pp_flur      SET simple_geom = st_simplify(the_geom, 0.4); -- Flur
248
249UPDATE pp_gemarkung SET simple_geom = st_simplify(the_geom, 2.0); -- Gemarkung  (Wirkung siehe pp_gemarkung_analyse)
250
251UPDATE pp_gemeinde  SET simple_geom = st_simplify(the_geom, 5.0); -- Gemeinde (Wirkung siehe pp_gemeinde_analyse)
252
253
254-- =======================================================
255-- Tabelle fuer die Zuordnung vom Eigentümern zu Gemeinden
256-- =======================================================
257
258
259-- erst mal sauber machen
260DELETE FROM gemeinde_person;
261
262-- alle direkten Buchungen mit View ermitteln und in Tabelle speichern
263-- Für eine Stadt: ca. 20 Sekunden
264INSERT INTO  gemeinde_person
265       (land, regierungsbezirk, kreis, gemeinde, person, buchtyp)
266 SELECT land, regierungsbezirk, kreis, gemeinde, person, 1
267   FROM gemeinde_person_typ1;
268
269
270-- noch die komplexeren Buchungen ergänzen (Recht an ..)
271-- Mit View ermitteln und in Tabelle speichern
272-- Für eine Stadt: ca. 10 Sekunden
273INSERT INTO  gemeinde_person
274       (  land,   regierungsbezirk,   kreis,   gemeinde,   person,  buchtyp)
275 SELECT q.land, q.regierungsbezirk, q.kreis, q.gemeinde, q.person,  2
276   FROM gemeinde_person_typ2 q   -- Quelle
277   LEFT JOIN gemeinde_person z   -- Ziel
278     ON q.person   = z.person    -- Aber nur, wenn dieser Fall im Ziel
279    AND q.land     = z.land
280    AND q.regierungsbezirk = z.regierungsbezirk
281    AND q.kreis    = z.kreis
282    AND q.gemeinde = z.gemeinde
283  WHERE z.gemeinde is Null;      -- ..  noch nicht vorhanden ist
284
285-- ENDE --
Note: See TracBrowser for help on using the repository browser.