source: trunk/import/pp_laden.sql @ 353

Revision 351, 16.0 KB checked in by frank.jaeger, 9 years ago (diff)

Verbesserte Darstellung von Flur, Gemarkung und Gemeinde im WMS.

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