source: trunk/import/pp_gebiete.sql @ 354

-- ALKIS PostNAS 0.8

-- Post Processing (pp_) Teil 2 B: Laden der Tabellen fÃŒr Gebiete

-- Stand 
--  2015-10-27 Die Erzeugung der vereinfachten Geometrie von Gemeinde, Gemarkung und Flur
--             wird von "pp_laden.sql" ausgegliedert in dies Script "pp_gebiete.sql".
--             Dieses Script braucht NICHT nach jeder Aktualisierung der Datenbank laufen.

-- Geometrie glÀtten und vereinfachen fÌr Flur, Gemarkung und Gemeinde:
-- Die "simplen" Geometrien sollen nur fÃŒr die Darstellung einer Übersicht verwendet werden.
-- Ablage der simplen Geometrie in einem alternativen Geometriefeld im gleichen Datensatz.

SET client_encoding = 'UTF-8';

-- Tabellen fuer die Zuordnung vom Gemarkungen zu Gemeinden
-- ========================================================

-- FÃŒr die Regelung der Zugriffsberechtigung einer Gemeindeverwaltung auf die 
-- FlurstÃŒcke in ihrem Gebiet braucht man die Information, in welcher Gemeinde eine Gemarkung liegt.
-- 'ax_gemeinde' und 'ax_gemarkung' haben aber im ALKIS keinerlei Beziehung zueinander - kaum zu glauben!
-- Nur ÃŒber die Auswertung der FlurstÃŒcke kann man die Zuordnung ermitteln.
-- Da nicht stÀndig mit 'SELECT DISTINCT' sÀmtliche FlurstÌcke durchsucht werden können, 
-- muss diese Information als (redundante) Tabelle nach dem Laden zwischengespeichert werden. 


-- G E M A R K U N G

TRUNCATE pp_gemarkung;
-- Vorkommende Paarungen Gemarkung <-> Gemeinde in ax_Flurstueck
INSERT INTO pp_gemarkung
  (               land, regierungsbezirk, kreis, gemeinde, gemarkung       )
  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer
  FROM            ax_flurstueck
  WHERE           endet IS NULL
  ORDER BY        land, regierungsbezirk, kreis, gemeinde, gemarkungsnummer ;

-- Namen der Gemarkung dazu als Optimierung bei der Auskunft 
UPDATE pp_gemarkung a
   SET gemarkungsname =
   ( SELECT b.bezeichnung 
     FROM    ax_gemarkung b
     WHERE a.land=b.land 
       AND a.gemarkung=b.gemarkungsnummer
       AND b.endet IS NULL
   );


-- G E M E I N D E

TRUNCATE pp_gemeinde;
-- Vorkommende Gemeinden aus den Gemarkungen
INSERT INTO pp_gemeinde
  (               land, regierungsbezirk, kreis, gemeinde)
  SELECT DISTINCT land, regierungsbezirk, kreis, gemeinde
  FROM            pp_gemarkung
  ORDER BY        land, regierungsbezirk, kreis, gemeinde;

-- Namen der Gemeinde dazu als Optimierung bei der Auskunft 
UPDATE pp_gemeinde a
   SET gemeindename =
   ( SELECT b.bezeichnung 
     FROM    ax_gemeinde b
     WHERE a.land=b.land 
       AND a.regierungsbezirk=b.regierungsbezirk 
       AND a.kreis=b.kreis
       AND a.gemeinde=b.gemeinde
       AND b.endet IS NULL
   );


-- Geometrien der FlurstÃŒcke schrittweise zu groesseren Einheiten zusammen fassen
-- ==============================================================================
-- http://postgis.net/docs/manual-1.5/ST_Union.html (Aggregate version)
-- http://postgis.net/docs/manual-1.5/ST_Collect.html

-- Wenn ein Gebiet durch geometrische Filter im NBA-Verfahren ausgegeben wurde, dann gibt es 
-- Randstreifen, die zu Pseudo-Fluren zusammen gefasst werden. 
-- Diese werden fÃŒr den WMS durch einen View ausgefiltert, der nur eine Gemeinde darstellt.

-- AusfÃŒhrungszeit: 1 mittlere Stadt mit ca. 14.000 FlurstÃŒcken > 100 Sek

TRUNCATE pp_flur;

INSERT INTO pp_flur (land, regierungsbezirk, kreis, gemeinde, gemarkung, flurnummer, anz_fs, the_geom )
   SELECT  f.land, f.regierungsbezirk, f.kreis, f.gemeinde, f.gemarkungsnummer as gemarkung, f.flurnummer, 
           count(gml_id) as anz_fs,
           ST_Multi(st_union(st_buffer(f.wkb_geometry,0.06))) AS the_geom -- Zugabe um ZwischenrÀume zu vermeiden
     FROM  ax_flurstueck f
     WHERE f.endet IS NULL AND NOT f.wkb_geometry IS NULL
  GROUP BY f.land, f.regierungsbezirk, f.kreis, f.gemeinde, f.gemarkungsnummer, f.flurnummer;

-- Vereinfachte Geometrie der Flur fÃŒr die Darstelung im WMS in einem kleinen Maßstab
UPDATE pp_flur      SET simple_geom = st_simplify(the_geom, 0.5);


-- Fluren zu Gemarkungen zusammen fassen
-- -------------------------------------

-- FlÀchen vereinigen
UPDATE pp_gemarkung a
  SET the_geom = 
   ( SELECT ST_Multi(ST_Union(ST_Buffer(b.the_geom,0.15))) AS the_geom -- Puffer/Zugabe um Löcher zu vermeiden
       FROM pp_flur b
      WHERE a.land      = b.land 
        AND a.gemarkung = b.gemarkung
   );
-- Hierbei können Exceptions auftreten wobei das Geometriefeld einzelner Gemarkungen leer bleibt.
-- Die Ursache ist unklar.
--  TopologyException: found non-noded intersection between LINESTRING

-- Praktische Lösung:
-- Bei den Gemarkungen, die nichts abbekommen haben, mit schrittweise groesserem Buffer noch mal versuchen.
UPDATE pp_gemarkung a
  SET the_geom = 
   ( SELECT ST_Multi(ST_Union(ST_Buffer(b.the_geom,0.20))) AS the_geom -- Puffer vergroessert
       FROM pp_flur b
      WHERE a.land      = b.land 
        AND a.gemarkung = b.gemarkung
   ) WHERE the_geom IS NULL;

UPDATE pp_gemarkung a
  SET the_geom = 
   ( SELECT ST_Multi(ST_Union(ST_Buffer(b.the_geom,0.30))) AS the_geom -- Puffer vergroessert
       FROM pp_flur b
      WHERE a.land      = b.land 
        AND a.gemarkung = b.gemarkung
   ) WHERE the_geom IS NULL;

/* Guggst du (auf leeren Geometry-Typ achten:

   SELECT gemarkungsname, geometrytype(the_geom) AS typ, 
     st_isvalid(the_geom) AS vali, st_asewkt(the_geom) AS ewkt
   FROM pp_gemarkung;

*/

-- Die Fluren in der Gemarkung zaehlen
UPDATE pp_gemarkung a
  SET anz_flur = 
   ( SELECT count(flurnummer) AS anz_flur 
     FROM    pp_flur b
     WHERE a.land      = b.land 
       AND a.gemarkung = b.gemarkung
   ); -- Gemarkungsnummer ist je BundesLand eindeutig


-- Vereinfachte Geometrie der Gemarkung fÃŒr die Darstellung im WMS in einem kleinen Maßstab
-- Gemarkung (Wirkung siehe pp_gemarkung_analyse)
UPDATE pp_gemarkung SET simple_geom = st_simplify(the_geom, 2.2); 


-- Gemarkungen zu Gemeinden zusammen fassen
-- ----------------------------------------

-- FlÀchen vereinigen
UPDATE pp_gemeinde a
  SET the_geom = 
   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.2))) AS the_geom -- noch mal Zugabe
     FROM    pp_gemarkung b
     WHERE a.land     = b.land 
       AND a.gemeinde = b.gemeinde
   );
-- Hierbei können Exceptions auftreten wobei das Geometriefeld einzelner Gemarkungen leer bleibt.
-- Die Ursache ist unklar.

-- Praktische Lösung:
-- weitere Versuche mit mehr Puffer, wenn Exception auftrat
UPDATE pp_gemeinde a
  SET the_geom = 
   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.3))) AS the_geom -- MEHR Zugabe
     FROM    pp_gemarkung b
     WHERE a.land     = b.land 
       AND a.gemeinde = b.gemeinde
   ) WHERE the_geom IS NULL;

UPDATE pp_gemeinde a
  SET the_geom = 
   ( SELECT st_multi(st_union(st_buffer(b.the_geom,0.4))) AS the_geom -- noch MEHR Zugabe
     FROM    pp_gemarkung b
     WHERE a.land     = b.land 
       AND a.gemeinde = b.gemeinde
   ) WHERE the_geom IS NULL;


-- Vereinfachte Geometrie der Gemeinde fÃŒr die Darstellung im WMS in einem kleinen Maßstab
-- Gemeinde (Wirkung siehe pp_gemeinde_analyse)
UPDATE pp_gemeinde  SET simple_geom = st_simplify(the_geom, 5.0); 

-- Gemarkungen in der Gemeinde zÀhlen
UPDATE pp_gemeinde a
  SET anz_gemarkg = 
   ( SELECT count(gemarkung) AS anz_gemarkg 
     FROM    pp_gemarkung b
     WHERE a.land     = b.land 
       AND a.gemeinde = b.gemeinde
   );

-- ENDE --