source: trunk/import/pp_laden.sql @ 330

Revision 330, 14.8 KB checked in by frank.jaeger, 10 years ago (diff)

Umstellung von PostNAS 0.7 auf PostNAS 0.8, ohne Tabelle "alkis_beziehungen".

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