source: trunk/import/pp_laden.sql @ 336

Revision 336, 14.5 KB checked in by frank.jaeger, 6 years ago (diff)

Schema zurück auf 16stellige gml_id.
Überarbeiten Trigger-Funktion "delete_feature_hist()" - noch nicht ausgetestet.

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