source: trunk/import/pp_laden.sql @ 315

Revision 315, 11.4 KB checked in by frank.jaeger, 7 years ago (diff)

Feinschliff und Korrektur an Auskunft und Navigation

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
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
185-- ToDo:
186--   Nur "geprüfte Flurstücke" verwenden?  Filter?
187
188--   070: TopologyException: found non-noded intersection between   ...
189
190
191DELETE FROM pp_flur;
192
193INSERT INTO pp_flur (land, regierungsbezirk, kreis, gemarkung, flurnummer, anz_fs, the_geom )
194   SELECT  f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer as gemarkung, f.flurnummer,
195           count(gml_id) as anz_fs,
196           st_multi(st_union(st_buffer(f.wkb_geometry,0.05))) AS the_geom -- 5 cm Zugabe um Zwischenräume zu vermeiden
197     FROM  ax_flurstueck f
198     WHERE f.endet IS NULL
199  GROUP BY f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer, f.flurnummer;
200
201-- Geometrie vereinfachen, auf 1 Meter glätten
202UPDATE pp_flur SET simple_geom = st_simplify(the_geom, 1.0);
203
204
205-- Fluren zu Gemarkungen zusammen fassen
206-- -------------------------------------
207
208-- FEHLER: 290 Absturz PG! Bei Verwendung der ungebufferten präzisen Geometrie. 
209-- bufferOriginalPrecision failed (TopologyException: unable to assign hole to a shell), trying with reduced precision
210-- UPDATE: ../../source/headers/geos/noding/SegmentString.h:175: void geos::noding::SegmentString::testInvariant() const: Zusicherung »pts->size() > 1« nicht erfüllt.
211
212
213-- Flächen vereinigen (aus der bereits vereinfachten Geometrie)
214UPDATE pp_gemarkung a
215  SET the_geom =
216   ( SELECT st_multi(st_union(st_buffer(b.simple_geom,0.1))) AS the_geom -- noch mal 10 cm Zugabe
217     FROM    pp_flur b
218     WHERE a.land      = b.land
219       AND a.gemarkung = b.gemarkung
220   );
221
222-- Fluren zaehlen
223UPDATE pp_gemarkung a
224  SET anz_flur =
225   ( SELECT count(flurnummer) AS anz_flur
226     FROM    pp_flur b
227     WHERE a.land      = b.land
228       AND a.gemarkung = b.gemarkung
229   ); -- Gemarkungsnummer ist je BundesLand eindeutig
230
231-- Geometrie vereinfachen (Wirkung siehe pp_gemarkung_analyse)
232UPDATE pp_gemarkung SET simple_geom = st_simplify(the_geom, 8.0);
233
234
235-- Gemarkungen zu Gemeinden zusammen fassen
236-- ----------------------------------------
237
238-- Flächen vereinigen (aus der bereits vereinfachten Geometrie)
239UPDATE pp_gemeinde a
240  SET the_geom =
241   ( SELECT st_multi(st_union(st_buffer(b.simple_geom,0.1))) AS the_geom -- noch mal Zugabe 10 cm
242     FROM    pp_gemarkung b
243     WHERE a.land     = b.land
244       AND a.gemeinde = b.gemeinde
245   );
246
247-- Gemarkungen zählen
248UPDATE pp_gemeinde a
249  SET anz_gemarkg =
250   ( SELECT count(gemarkung) AS anz_gemarkg
251     FROM    pp_gemarkung b
252     WHERE a.land     = b.land
253       AND a.gemeinde = b.gemeinde
254   );
255
256-- Geometrie vereinfachen (Wirkung siehe pp_gemeinde_analyse)
257UPDATE pp_gemeinde SET simple_geom = st_simplify(the_geom, 20.0);
258
259
260-- =======================================================
261-- Tabelle fuer die Zuordnung vom Eigentümern zu Gemeinden
262-- =======================================================
263
264
265-- erst mal sauber machen
266DELETE FROM gemeinde_person;
267
268-- alle direkten Buchungen mit View ermitteln und in Tabelle speichern
269-- Für eine Stadt: ca. 20 Sekunden
270INSERT INTO  gemeinde_person
271       (land, regierungsbezirk, kreis, gemeinde, person, buchtyp)
272 SELECT land, regierungsbezirk, kreis, gemeinde, person, 1
273   FROM gemeinde_person_typ1;
274
275
276-- noch die komplexeren Buchungen ergänzen (Recht an ..)
277-- Mit View ermitteln und in Tabelle speichern
278-- Für eine Stadt: ca. 10 Sekunden
279INSERT INTO  gemeinde_person
280       (  land,   regierungsbezirk,   kreis,   gemeinde,   person,  buchtyp)
281 SELECT q.land, q.regierungsbezirk, q.kreis, q.gemeinde, q.person,  2
282   FROM gemeinde_person_typ2 q   -- Quelle
283   LEFT JOIN gemeinde_person z   -- Ziel
284     ON q.person   = z.person    -- Aber nur, wenn dieser Fall im Ziel
285    AND q.land     = z.land
286    AND q.regierungsbezirk = z.regierungsbezirk
287    AND q.kreis    = z.kreis
288    AND q.gemeinde = z.gemeinde
289  WHERE z.gemeinde is Null;      -- ..  noch nicht vorhanden ist
290
291-- ENDE --
Note: See TracBrowser for help on using the repository browser.