source: trunk/data/konvert/postnas_0.7/pp_laden.sql @ 183

Revision 183, 11.1 KB checked in by frank.jaeger, 8 years ago (diff)

Trigger fuer NAS-Replace-Sätze repariert (Quick 'n Dirty).

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--             Mehr buffer, mehr simplify?
14--  2012-10-29 Redundanzen aus alkis_beziehungen beseitigen, die nach NAS replace auftreten
15
16-- ============================
17-- Tabellen des Post-Processing
18-- ============================
19
20-- Einige Informationen liegen nach der NAS-Konvertierung in der Datenbank "verstreut" vor.
21-- Die dynamische Aufbereitung über Views und Functions würde zu lange dauern und somit lange
22-- Antwortzeiten in WMS, WFS, Buchauskunft oder Navigation (Suche) verursachen.
23
24-- Im Rahmen eines "Post-Processing" werden diese Daten nach jeder Konvertierung (NBA-Aktualisierung)
25-- einmal komplett aufbereitet. Die benötigten Informationen stehen somit den Anwendungen mundgerecht zur Verfügung.
26
27-- Die per PostProcessing gefüllten Tabellen bekommen den Profix "pp_".
28
29-- Die Ausführung dieses Scriptes auf einer Datenbank für eine 80T-Einwohner-Stadt dauert ca.: 500 Sek. !
30
31SET client_encoding = 'UTF-8';
32
33
34-- ============================================================================
35-- Redundanzen aus alkis_beziehungen beseitigen, die nach NAS replace auftreten
36-- ============================================================================
37-- Work arround: alle Redundazen nach einem Lauf entfernen.
38-- Besser wäre: sofort im Trigger bei replace entfernen.
39-- Siehe Schema in FUNCTION delete_feature_kill
40
41DELETE
42  FROM alkis_beziehungen AS bezalt        -- Beziehung Alt
43 WHERE EXISTS
44       (SELECT ogc_fid
45         FROM alkis_beziehungen AS bezneu -- Beziehung Neu
46        WHERE bezalt.beziehung_von = bezneu.beziehung_von
47          AND bezalt.beziehung_zu  = bezneu.beziehung_zu
48          AND bezalt.beziehungsart = bezneu.beziehungsart
49          AND bezalt.ogc_fid       < bezneu.ogc_fid
50        );
51-- Denkbar ist eine Variante für den Trigger, die zusätzlich
52-- auf eine bestimmte gml_id filtert.
53-- Damit wäre die DB schon während der Konvertierung konsistenter.
54
55
56-- SELECT *
57--  FROM alkis_beziehungen AS bezalt
58--  WHERE EXISTS
59--        (SELECT ogc_fid
60--          FROM alkis_beziehungen AS bezneu
61--         WHERE bezalt.beziehung_von = bezneu.beziehung_von
62--           AND bezalt.beziehung_zu  = bezneu.beziehung_zu
63--           AND bezalt.beziehungsart = bezneu.beziehungsart
64--           AND bezalt.ogc_fid       < bezneu.ogc_fid
65--         );
66
67-- SELECT *
68--  FROM alkis_beziehungen AS bezalt
69--  WHERE EXISTS
70--        (SELECT ogc_fid
71--          FROM alkis_beziehungen AS bezneu
72--         WHERE bezalt.beziehung_von = bezneu.beziehung_von
73--           AND bezalt.beziehung_zu  = bezneu.beziehung_zu
74--           AND bezalt.beziehungsart = bezneu.beziehungsart
75--           AND bezalt.ogc_fid       < bezneu.ogc_fid
76--         )
77--      -- mit dem Zusatz nur die Faelle aus dem letzten Durchlauf,
78--      -- die aktuell noch in der Delet-Tabelle stehen
79--      AND EXISTS
80--         (SELECT ogc_fid
81--          FROM delete
82--          WHERE bezalt.beziehung_von = substr(featureid, 1, 16)
83--             OR bezalt.beziehung_zu  = substr(featureid, 1, 16)
84--         );
85
86
87-- =================================
88-- Flurstuecksnummern-Label-Position
89-- =================================
90
91-- ersetzt den View "s_flurstueck_nr" für WMS-Layer "ag_t_flurstueck"
92
93  DELETE FROM pp_flurstueck_nr;
94
95  INSERT INTO pp_flurstueck_nr
96          ( fsgml, fsnum, the_geom )
97    SELECT f.gml_id,
98           f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum,
99           p.wkb_geometry  -- manuelle Position des Textes
100      FROM ap_pto             p
101      JOIN alkis_beziehungen  v  ON p.gml_id       = v.beziehung_von
102      JOIN ax_flurstueck      f  ON v.beziehung_zu = f.gml_id
103     WHERE v.beziehungsart = 'dientZurDarstellungVon'
104       AND f.endet IS NULL
105       AND p.endet IS NULL
106     --AND p."art" = 'ZAE_NEN'
107   UNION
108    SELECT f.gml_id,
109           f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum,
110           ST_PointOnSurface(f.wkb_geometry) AS wkb_geometry  -- Flaechenmitte als Position des Textes
111      FROM      ax_flurstueck     f
112      LEFT JOIN alkis_beziehungen v  ON v.beziehung_zu = f.gml_id
113     WHERE v.beziehungsart is NULL
114       AND f.endet IS NULL
115  ;
116-- Ausführung: mittlere Stadt: ca. 4 - 18 Sec.
117
118
119-- ========================================================
120-- Tabellen fuer die Zuordnung vom Gemarkungen zu Gemeinden
121-- ========================================================
122
123-- Für die Regelung der Zugriffsberechtigung einer Gemeindeverwaltung auf die
124-- Flurstücke in ihrem Gebiet braucht man die Information, in welcher Gemeinde eine Gemarkung liegt.
125-- 'ax_gemeinde' und 'ax_gemarkung' haben aber im ALKIS keinerlei Beziehung zueinander - kaum zu glauben!
126-- Nur über die Auswertung der Flurstücke kann man die Zuordnung ermitteln.
127-- Da nicht ständig mit 'SELECT DISTINCT' sämtliche Flurstücke durchsucht werden können,
128-- muss diese Information als (redundante) Tabelle nach dem Laden zwischengespeichert werden.
129
130
131-- G E M A R K U N G
132
133DELETE FROM pp_gemarkung;
134
135-- Vorkommende Paarungen Gemarkung <-> Gemeinde in ax_Flurstueck
136INSERT INTO pp_gemarkung
137  (               land, regierungsbezirk, kreis, gemeinde, gemarkung       )
138  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
139  FROM            ax_flurstueck
140  WHERE           endet IS NULL
141  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
142;
143
144-- Namen der Gemarkung dazu als Optimierung bei der Auskunft
145UPDATE pp_gemarkung a
146   SET gemarkungsname =
147   ( SELECT b.bezeichnung
148     FROM    ax_gemarkung b
149     WHERE a.land=b.land
150       AND a.gemarkung=b.gemarkungsnummer
151       AND b.endet IS NULL
152   );
153
154
155-- G E M E I N D E
156
157DELETE FROM pp_gemeinde;
158
159-- Vorkommende Gemeinden aus den gemarkungen
160INSERT INTO pp_gemeinde
161  (               land, regierungsbezirk, kreis, gemeinde)
162  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde
163  FROM            pp_gemarkung
164  ORDER BY        land, regierungsbezirk, kreis, gemeinde
165;
166
167
168-- Namen der Gemeinde dazu als Optimierung bei der Auskunft
169UPDATE pp_gemeinde a
170   SET gemeindename =
171   ( SELECT b.bezeichnung
172     FROM    ax_gemeinde b
173     WHERE a.land=b.land
174       AND a.regierungsbezirk=b.regierungsbezirk
175       AND a.kreis=b.kreis
176       AND a.gemeinde=b.gemeinde
177       AND b.endet IS NULL
178   );
179
180
181-- ==============================================================================
182-- Geometrien der Flurstücke schrittweise zu groesseren Einheiten zusammen fassen
183-- ==============================================================================
184
185-- Dies macht nur Sinn, wenn der Inhalt der Datenbank einen ganzen Katasterbezirk enthält.
186-- Wenn ein Gebiet durch geometrische Filter im NBA ausgegeben wurde, dann gibt es Randstreifen,
187-- die zu Pseudo-Fluren zusammen gefasst werden. Fachlich falsch!
188
189-- Ausführungszeit: 1 mittlere Stadt mit ca. 14.000 Flurstücken > 100 Sek
190
191-- ToDo:
192--   Nur "geprüfte Flurstücke" verwenden?  Filter?
193
194--   070: TopologyException: found non-noded intersection between   ...
195
196
197DELETE FROM pp_flur;
198
199INSERT INTO pp_flur (land, regierungsbezirk, kreis, gemarkung, flurnummer, anz_fs, the_geom )
200   SELECT  f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer as gemarkung, f.flurnummer,
201           count(gml_id) as anz_fs,
202           multi(st_union(st_buffer(f.wkb_geometry,0.05))) AS the_geom -- 5 cm Zugabe um Zwischenräume zu vermeiden
203     FROM  ax_flurstueck f
204     WHERE f.endet IS NULL
205  GROUP BY f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer, f.flurnummer;
206
207-- Geometrie vereinfachen, auf 1 Meter glätten
208UPDATE pp_flur SET simple_geom = simplify(the_geom, 1.0);
209
210
211-- Fluren zu Gemarkungen zusammen fassen
212-- -------------------------------------
213
214-- FEHLER: 290 Absturz PG! Bei Verwendung der ungebufferten präzisen Geometrie. 
215-- bufferOriginalPrecision failed (TopologyException: unable to assign hole to a shell), trying with reduced precision
216-- UPDATE: ../../source/headers/geos/noding/SegmentString.h:175: void geos::noding::SegmentString::testInvariant() const: Zusicherung »pts->size() > 1« nicht erfüllt.
217
218
219-- Flächen vereinigen (aus der bereits vereinfachten Geometrie)
220UPDATE pp_gemarkung a
221  SET the_geom =
222   ( SELECT multi(st_union(st_buffer(b.simple_geom,0.1))) AS the_geom -- noch mal 10 cm Zugabe
223     FROM    pp_flur b
224     WHERE a.land      = b.land
225       AND a.gemarkung = b.gemarkung
226   );
227
228-- Fluren zaehlen
229UPDATE pp_gemarkung a
230  SET anz_flur =
231   ( SELECT count(flurnummer) AS anz_flur
232     FROM    pp_flur b
233     WHERE a.land      = b.land
234       AND a.gemarkung = b.gemarkung
235   ); -- Gemarkungsnummer ist je BundesLand eindeutig
236
237-- Geometrie vereinfachen (Wirkung siehe pp_gemarkung_analyse)
238UPDATE pp_gemarkung SET simple_geom = simplify(the_geom, 8.0);
239
240
241-- Gemarkungen zu Gemeinden zusammen fassen
242-- ----------------------------------------
243
244-- Flächen vereinigen (aus der bereits vereinfachten Geometrie)
245UPDATE pp_gemeinde a
246  SET the_geom =
247   ( SELECT multi(st_union(st_buffer(b.simple_geom,0.1))) AS the_geom -- noch mal Zugabe 10 cm
248     FROM    pp_gemarkung b
249     WHERE a.land     = b.land
250       AND a.gemeinde = b.gemeinde
251   );
252
253-- Gemarkungen zählen
254UPDATE pp_gemeinde a
255  SET anz_gemarkg =
256   ( SELECT count(gemarkung) AS anz_gemarkg
257     FROM    pp_gemarkung b
258     WHERE a.land     = b.land
259       AND a.gemeinde = b.gemeinde
260   );
261
262-- Geometrie vereinfachen (Wirkung siehe pp_gemeinde_analyse)
263UPDATE pp_gemeinde SET simple_geom = simplify(the_geom, 20.0);
264
265
266-- =======================================================
267-- Tabelle fuer die Zuordnung vom Eigentümern zu Gemeinden
268-- =======================================================
269
270
271-- erst mal sauber machen
272DELETE FROM gemeinde_person;
273
274-- alle direkten Buchungen mit View ermitteln und in Tabelle speichern
275-- Für eine Stadt: ca. 20 Sekunden
276INSERT INTO  gemeinde_person
277       (land, regierungsbezirk, kreis, gemeinde, person, buchtyp)
278 SELECT land, regierungsbezirk, kreis, gemeinde, person, 1
279   FROM gemeinde_person_typ1;
280
281
282-- noch die komplexeren Buchungen ergänzen (Recht an ..)
283-- Mit View ermitteln und in Tabelle speichern
284-- Für eine Stadt: ca. 10 Sekunden
285INSERT INTO  gemeinde_person
286       (  land,   regierungsbezirk,   kreis,   gemeinde,   person,  buchtyp)
287 SELECT q.land, q.regierungsbezirk, q.kreis, q.gemeinde, q.person,  2
288   FROM gemeinde_person_typ2 q   -- Quelle
289   LEFT JOIN gemeinde_person z   -- Ziel
290     ON q.person   = z.person    -- Aber nur, wenn dieser Fall im Ziel
291    AND q.land     = z.land
292    AND q.regierungsbezirk = z.regierungsbezirk
293    AND q.kreis    = z.kreis
294    AND q.gemeinde = z.gemeinde
295  WHERE z.gemeinde is Null;      -- ..  noch nicht vorhanden ist
296
297
298-- ENDE --
Note: See TracBrowser for help on using the repository browser.