source: trunk/import/pp_laden.sql @ 339

Revision 339, 13.7 KB checked in by frank.jaeger, 6 years ago (diff)

Rückbau von "substring(gml_id)" in Views und Programmen.
Namensänderung von Schlüsseltabellen von ax_* auf v_*.

Line 
1
2-- ALKIS PostNAS 0.8
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--  2014-09-08 PostNAS 0.8, Umbenennung. Ohne Tabelle "alkis_beziehungen".
21--             Beseitigung eines Fehlers beim Laden der Straßennamen-Label.
22--             Dabei Trennung in pp_strassenname_p und -_l (Punkt- und Liniengeometrie).
23--  2014-09-19 Substring auf gml_id, Korrektur "endet IS NULL"
24--  2014-09-30 RÃŒckbau substring(gml_id)
25
26
27-- ============================
28-- Tabellen des Post-Processing
29-- ============================
30
31-- Einige Informationen liegen nach der NAS-Konvertierung in der Datenbank "verstreut" vor.
32-- Die dynamische Aufbereitung ÃŒber Views und Functions wÃŒrde zu lange dauern und somit lange
33-- Antwortzeiten in WMS, WFS, Buchauskunft oder Navigation (Suche) verursachen.
34
35-- Im Rahmen eines "Post-Processing" werden diese Daten nach jeder Konvertierung (NBA-Aktualisierung)
36-- einmal komplett aufbereitet. Die benötigten Informationen stehen somit den Anwendungen mundgerecht zur VerfÌgung.
37
38-- Die per PostProcessing gefÃŒllten Tabellen bekommen den Profix "pp_".
39
40-- Die AusfÃŒhrung dieses Scriptes auf einer Datenbank fÃŒr eine 80T-Einwohner-Stadt dauert ca.: 500 Sek. !
41
42SET client_encoding = 'UTF-8';
43
44
45-- Flurstuecksnummern-Label-Position
46-- =================================
47
48TRUNCATE pp_flurstueck_nr;  -- effektiver als DELETE
49
50/*
51-- Alte Version ÃŒber "alkis_beziehungen"
52-- AusfÃŒhrung: mittlere Stadt: ca. 4 - 18 Sec.
53
54-- 1:1 umgestellt (UNION) unter Verwendeung der neuen Relationenspalten
55
56INSERT 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 ax_flurstueck  f
62      JOIN ap_pto         p
63        ON f.gml_id = ANY(p.dientzurdarstellungvon)
64     WHERE f.endet IS NULL AND p.endet IS NULL -- AND p."art" = 'ZAE_NEN'
65   UNION
66    SELECT f.gml_id,
67           f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum,
68           ST_PointOnSurface(f.wkb_geometry) AS wkb_geometry  -- Flaechenmitte als Position des Textes
69      FROM ax_flurstueck  f
70      LEFT JOIN ap_pto    p
71        ON f.gml_id = ANY(p.dientzurdarstellungvon)  -- kein PrÀsentationsobjekt vorhanden
72     WHERE p.gml_id is NULL AND f.endet IS NULL ;
73*/
74
75-- Ersatz: FÃŒllen in 3 Schritten
76
77-- 1. zu allen FlurstÌcken zunÀchst nur die Nummer, aber noch keine Geometrie
78INSERT INTO pp_flurstueck_nr
79          ( fsgml, fsnum )
80    SELECT f.gml_id, f.zaehler::text || COALESCE ('/' || f.nenner::text, '') AS fsnum
81      FROM ax_flurstueck f
82     WHERE f.endet IS NULL;
83
84-- 2. Geometrie aus PrÀsentationsobjekt (manuelle Position), wenn vorhanden. Aus Subquery.
85UPDATE pp_flurstueck_nr n
86  SET the_geom = (
87      SELECT p.wkb_geometry
88        FROM ap_pto p
89       WHERE n.fsgml=ANY(p.dientzurdarstellungvon)
90         AND p.endet IS NULL
91       LIMIT 1 -- wegen vereinzelt FEHLER: als Ausdruck verwendete Unteranfrage ergab mehr als eine Zeile
92    );
93-- Dies lÀuft sehr lange. Optimierbar?
94
95
96-- 3. Geometrie auf FlÀchenmitte (Standard), wenn jetzt noch leer
97UPDATE pp_flurstueck_nr n
98  SET the_geom = (
99      SELECT ST_PointOnSurface(f.wkb_geometry) AS wkb_geometry
100       FROM ax_flurstueck  f
101       WHERE f.gml_id = n.fsgml
102         AND f.endet IS NULL
103      )
104   WHERE n.the_geom IS NULL; --- nur die Fehlenden, nichts ÃŒberschreiben
105
106
107-- Straßen - N a m e n  und  - K l a s s i f i k a t i o n
108-- Tabellen fÃŒr die PrÀsentation von Straßen-Namen und -Klassifikationen
109-- Daten aus dem View "ap_pto_stra" werden im PostProcessing gespeichert in der Tabelle "pp_strassenname".
110-- Der View ÃŒbernimmt die Auswahl des passenden "advstandardmodell" und rechnet den Winkel passend um.
111-- In der Tabelle werden dann die leer gebliebenen Label aus dem Katalog noch ergÀnzt.
112
113-- Tabelle aus View befÃŒllen
114-- 2014-08-22
115--  Variante "_p" = Punktgeometrie, Spalte gml_id ergÀnzt.
116--  Es werden nun auch SÀtze mit leerem "schriftinhalt" angelegt. Das wird dann nachtrÀglich gefÃŒllt.
117
118-- Alles auf Anfang
119TRUNCATE pp_strassenname_p;
120
121-- ZunÀchst die Sonderschreibweisen (AbkÌrzungen) und die Standardschreibweisen,
122-- die von der Migration redundant abgelegt wurden.
123INSERT INTO pp_strassenname_p (gml_id, schriftinhalt, hor, ver, art, winkel, the_geom)
124       SELECT gml_id, schriftinhalt, hor, ver, art, winkel, wkb_geometry
125       FROM ap_pto_stra; -- Der View sucht das passende advstandardmodell
126
127-- Schriftinhalt ergÀnzen
128-- Das sind die Standardschreibweisen aus dem Katalog, die nicht mehr redundant in ap_pto sind.
129UPDATE pp_strassenname_p  p
130   SET schriftinhalt =     -- Hier ist der Label noch leer
131   -- Subquery "Gib mir den Straßennamen":
132   ( SELECT k.bezeichnung                         -- Straßenname ..
133       FROM ax_lagebezeichnungkatalogeintrag k    --  .. aus Katalog
134       JOIN ax_lagebezeichnungohnehausnummer l    -- verwendet als Lage o.H.
135         ON (k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis AND k.gemeinde=l.gemeinde AND k.lage=l.lage )
136      WHERE p.gml_id = l.gml_id                   -- die gml_id wurde aus View importiert
137    )
138 WHERE     p.schriftinhalt IS NULL
139   AND NOT p.the_geom      IS NULL;
140
141-- Die immer noch leeren Texte sind nun sinnlos.
142-- Die finden sich ggf. in der Variante "_l" mit Liniengeometrie.
143DELETE FROM pp_strassenname_p WHERE schriftinhalt IS NULL;
144
145-- Nun das Gleiche noch einmal fÃŒr Linien-Geometrie
146
147-- Auf Anfang
148TRUNCATE pp_strassenname_l;
149
150-- ZunÀchst die Sonderschreibweisen (AbkÌrzungen) und die Standardschreibweisen,
151-- die von der Migration redundant abgelegt wurden.
152INSERT INTO pp_strassenname_l (gml_id, schriftinhalt, hor, ver, art, the_geom)
153       SELECT gml_id, schriftinhalt, hor, ver, art, wkb_geometry
154       FROM ap_lto_stra; -- Der View sucht das passende advstandardmodell
155
156-- Schriftinhalt ergÀnzen (korrigiert 2014-08-25)
157-- Das sind die Standardschreibweisen aus dem Katalog, die nicht mehr redundant in ap_pto sind.
158-- Der Satz mit der passenen gml_id (Lage o.H.) ist aus dem View bereits importiert.
159-- Jetzt noch den dazu passenen Schriftinhalt aus dem Katalog holen.
160UPDATE pp_strassenname_l  p
161   SET schriftinhalt =     -- Hier ist der Label noch leer
162   -- Subquery "Gib mir den Straßennamen":
163   ( SELECT k.bezeichnung                         -- Straßenname ..
164       FROM ax_lagebezeichnungkatalogeintrag k    --  .. aus Katalog
165       JOIN ax_lagebezeichnungohnehausnummer l    -- verwendet als Lage o.H.
166         ON (k.land=l.land AND k.regierungsbezirk=l.regierungsbezirk AND k.kreis=l.kreis AND k.gemeinde=l.gemeinde AND k.lage=l.lage )
167      WHERE p.gml_id = l.gml_id                   -- die gml_id wurde aus View importiert
168    )
169 WHERE     p.schriftinhalt IS NULL
170   AND NOT p.the_geom      IS NULL;
171
172-- Die immer noch leeren Texte sind sinnlos.
173DELETE FROM pp_strassenname_l WHERE schriftinhalt IS NULL;
174
175
176-- Tabellen fuer die Zuordnung vom Gemarkungen zu Gemeinden
177-- ========================================================
178
179-- FÃŒr die Regelung der Zugriffsberechtigung einer Gemeindeverwaltung auf die
180-- FlurstÃŒcke in ihrem Gebiet braucht man die Information, in welcher Gemeinde eine Gemarkung liegt.
181-- 'ax_gemeinde' und 'ax_gemarkung' haben aber im ALKIS keinerlei Beziehung zueinander - kaum zu glauben!
182-- Nur ÃŒber die Auswertung der FlurstÃŒcke kann man die Zuordnung ermitteln.
183-- Da nicht stÀndig mit 'SELECT DISTINCT' sÀmtliche FlurstÌcke durchsucht werden können,
184-- muss diese Information als (redundante) Tabelle nach dem Laden zwischengespeichert werden.
185
186
187-- G E M A R K U N G
188
189TRUNCATE pp_gemarkung;
190-- Vorkommende Paarungen Gemarkung <-> Gemeinde in ax_Flurstueck
191INSERT INTO pp_gemarkung
192  (               land, regierungsbezirk, kreis, gemeinde, gemarkung       )
193  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
194  FROM            ax_flurstueck
195  WHERE           endet IS NULL
196  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer ;
197
198-- Namen der Gemarkung dazu als Optimierung bei der Auskunft
199UPDATE pp_gemarkung a
200   SET gemarkungsname =
201   ( SELECT b.bezeichnung
202     FROM    ax_gemarkung b
203     WHERE a.land=b.land
204       AND a.gemarkung=b.gemarkungsnummer
205       AND b.endet IS NULL
206   );
207
208
209-- G E M E I N D E
210
211TRUNCATE pp_gemeinde;
212-- Vorkommende Gemeinden aus den Gemarkungen
213INSERT INTO pp_gemeinde
214  (               land, regierungsbezirk, kreis, gemeinde)
215  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde
216  FROM            pp_gemarkung
217  ORDER BY        land, regierungsbezirk, kreis, gemeinde;
218
219-- Namen der Gemeinde dazu als Optimierung bei der Auskunft
220UPDATE pp_gemeinde a
221   SET gemeindename =
222   ( SELECT b.bezeichnung
223     FROM    ax_gemeinde b
224     WHERE a.land=b.land
225       AND a.regierungsbezirk=b.regierungsbezirk
226       AND a.kreis=b.kreis
227       AND a.gemeinde=b.gemeinde
228       AND b.endet IS NULL
229   );
230
231
232-- Geometrien der FlurstÃŒcke schrittweise zu groesseren Einheiten zusammen fassen
233-- ==============================================================================
234
235-- Dies macht nur Sinn, wenn der Inhalt der Datenbank einen ganzen Katasterbezirk enthÀlt.
236-- Wenn ein Gebiet durch geometrische Filter im NBA ausgegeben wurde, dann gibt es Randstreifen,
237-- die zu Pseudo-Fluren zusammen gefasst werden. Fachlich falsch!
238
239-- AusfÃŒhrungszeit: 1 mittlere Stadt mit ca. 14.000 FlurstÃŒcken > 100 Sek
240
241TRUNCATE pp_flur;
242INSERT INTO pp_flur (land, regierungsbezirk, kreis, gemarkung, flurnummer, anz_fs, the_geom )
243   SELECT  f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer as gemarkung, f.flurnummer,
244           count(gml_id) as anz_fs,
245           st_multi(st_union(st_buffer(f.wkb_geometry,0.06))) AS the_geom -- Zugabe um ZwischenrÀume zu vermeiden
246     FROM  ax_flurstueck f
247     WHERE f.endet IS NULL AND NOT f.wkb_geometry IS NULL
248  GROUP BY f.land, f.regierungsbezirk, f.kreis, f.gemarkungsnummer, f.flurnummer;
249
250
251-- Fluren zu Gemarkungen zusammen fassen
252-- -------------------------------------
253
254-- FlÀchen vereinigen
255UPDATE pp_gemarkung a
256  SET the_geom =
257   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.12))) AS the_geom -- Puffer/Zugabe um Löcher zu vermeiden
258       FROM pp_flur b
259      WHERE a.land      = b.land
260        AND a.gemarkung = b.gemarkung
261   );
262
263-- Fluren zaehlen
264UPDATE pp_gemarkung a
265  SET anz_flur =
266   ( SELECT count(flurnummer) AS anz_flur
267     FROM    pp_flur b
268     WHERE a.land      = b.land
269       AND a.gemarkung = b.gemarkung
270   ); -- Gemarkungsnummer ist je BundesLand eindeutig
271
272
273-- Gemarkungen zu Gemeinden zusammen fassen
274-- ----------------------------------------
275
276-- FlÀchen vereinigen (aus der bereits vereinfachten Geometrie)
277UPDATE pp_gemeinde a
278  SET the_geom =
279   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.1))) AS the_geom -- noch mal Zugabe
280     FROM    pp_gemarkung b
281     WHERE a.land     = b.land
282       AND a.gemeinde = b.gemeinde
283   );
284
285-- Gemarkungen zÀhlen
286UPDATE pp_gemeinde a
287  SET anz_gemarkg =
288   ( SELECT count(gemarkung) AS anz_gemarkg
289     FROM    pp_gemarkung b
290     WHERE a.land     = b.land
291       AND a.gemeinde = b.gemeinde
292   );
293
294
295-- Geometrie glÀtten und vereinfachen.
296-- Diese "simplen" Geometrien sollen nur fÃŒr die Darstellung einer Übersicht verwendet werden.
297-- Ablage der simplen Geometrie in einem alternativen Geometriefeld im gleichen Datensatz.
298
299UPDATE pp_flur      SET simple_geom = st_simplify(the_geom, 0.5); -- Flur
300
301UPDATE pp_gemarkung SET simple_geom = st_simplify(the_geom, 2.2); -- Gemarkung (Wirkung siehe pp_gemarkung_analyse)
302
303UPDATE pp_gemeinde  SET simple_geom = st_simplify(the_geom, 5.0); -- Gemeinde (Wirkung siehe pp_gemeinde_analyse)
304
305
306-- Tabelle fuer die Zuordnung vom EigentÃŒmern zu Gemeinden
307-- =======================================================
308
309-- erst mal sauber machen
310DELETE FROM gemeinde_person;
311
312-- alle direkten Buchungen mit View ermitteln und in Tabelle speichern
313-- FÃŒr eine Stadt: ca. 20 Sekunden
314INSERT INTO  gemeinde_person
315       (land, regierungsbezirk, kreis, gemeinde, person, buchtyp)
316 SELECT land, regierungsbezirk, kreis, gemeinde, person, 1
317   FROM gemeinde_person_typ1;
318
319
320-- noch die komplexeren Buchungen ergÀnzen (Recht an ..)
321-- Mit View ermitteln und in Tabelle speichern
322-- FÃŒr eine Stadt: ca. 10 Sekunden
323INSERT INTO  gemeinde_person
324       (  land,   regierungsbezirk,   kreis,   gemeinde,   person,  buchtyp)
325 SELECT q.land, q.regierungsbezirk, q.kreis, q.gemeinde, q.person,  2
326   FROM gemeinde_person_typ2 q   -- Quelle
327   LEFT JOIN gemeinde_person z   -- Ziel
328     ON q.person   = z.person    -- Aber nur, wenn dieser Fall im Ziel
329    AND q.land     = z.land
330    AND q.regierungsbezirk = z.regierungsbezirk
331    AND q.kreis    = z.kreis
332    AND q.gemeinde = z.gemeinde
333  WHERE z.gemeinde is Null;      -- ..  noch nicht vorhanden ist
334
335-- ENDE --
Note: See TracBrowser for help on using the repository browser.