GLE-Postgis-Jagdkataster

EJB Verdachtsflächen

 CREATE TABLE alkis.lkvr_ejb_verdachtsflaechen
 (
   eigentuemer text,
   flaeche double precision
 )
 WITH (
   OIDS=TRUE
 );
 SELECT AddGeometryColumn('alkis', 'lkvr_ejb_verdachtsflaechen','the_geom',25833,'MULTIPOLYGON', 2);
 CREATE INDEX ixlkvrejbverd_the_geom_gist
  ON alkis.lkvr_ejb_verdachtsflaechen
  USING gist
  (the_geom);
 

Abgerundete EJB

 CREATE TABLE jagdkataster.jagdbezirke_abgerundet
 (
   id character varying(10) NOT NULL,
   flaeche numeric,
   name character varying(50),
   concode character varying(5),
   conname character varying(40),
   verzicht boolean,
   datum_beschluss date,
   datum_bestandskraft date,
   datum_erfassung date
 )
 WITH (
   OIDS=TRUE
 );
 SELECT AddGeometryColumn('jagdkataster', 'jagdbezirke_abgerundet','the_geom',25833,'MULTIPOLYGON', 2);
 CREATE INDEX jagdbezirke_abgerundet_the_geom_gist
  ON jagdkataster.jagdbezirke_abgerundet
  USING gist
  (the_geom);
 
 CREATE OR REPLACE FUNCTION jagdkataster.jagdbezirke_abrunden()
   RETURNS trigger AS
 $BODY$DECLARE
 
 -- Abzug = 'atf' (Abtrennflächen)
 -- Zuordnung = 'jbe', 'agf', 'jbf', 'jex' (Enklaven, Angliederungsflächen, jagdbezirksfreie Flächen, Exklaven)
 -- Sonderfall: Abtrennflächen durch Verzicht ('atv') ergeben immer zusätzliche separate Objekte
 count_abtrennung INTEGER;
 count_verzicht INTEGER;
 count_zuordnung INTEGER;
     
 BEGIN
 
     IF (TG_OP = 'UPDATE') THEN
      IF NEW.art = 'ajb' THEN
 
       NEW.art = 'ejb';  -- Der EJB in Abrundung bleibt EJB in Abrundung, 
       NEW.status='t';   -- wird aber historisch
 
         SELECT count(b.art) INTO count_abtrennung FROM jagdkataster.jagdbezirke b WHERE b.art = 'atf' AND b.jb_zuordnung=NEW.id;
         SELECT count(b.art) INTO count_verzicht FROM jagdkataster.jagdbezirke b WHERE b.art = 'atv' AND b.jb_zuordnung=NEW.id;
         SELECT count(b.art) INTO count_zuordnung FROM jagdkataster.jagdbezirke b WHERE b.art in ('jbe','agf','jbf','jex') AND b.jb_zuordnung=NEW.id;
 
         INSERT INTO jagdkataster.jagdbezirke_abgerundet
         SELECT id, NULL, name, concode, conname, 'vor Bestandskraft', 'f', NULL, NULL, cast(now() as date),
          case
          when count_abtrennung = 0 
          then 
            case
            when count_zuordnung = 0 
            then                     -- Wenn es keine Abzugsflächen gibt und keine anderen Zuordnungsflächen: AJB = EJB
              st_multi(a.the_geom)
            else                     -- Wenn es keine Abzugsflächen gibt, aber Zuordnungsflächen: AJB = EJB + Zuordnung
             st_multi((st_union(a.the_geom,(select st_union(b.the_geom) FROM jagdkataster.jagdbezirke b WHERE b.art in ('jbe','agf','jbf','jex') AND b.jb_zuordnung=a.id))))
            end
          else
            case
            when count_zuordnung = 0
            then                     -- Wenn es Abzugsflächen gibt aber keine anderen Zuordnungsflächen: AJB = EJB - Abzug
              st_multi(st_difference(
                a.the_geom,
                (select st_union(b.the_geom) FROM jagdkataster.jagdbezirke b WHERE b.art = 'atf' AND b.jb_zuordnung=a.id))
              )
 
            else                     -- Wenn es Abzugsflächen gibt und Zuordnungsflächen: AJB = EJB + Zuordnung - Abzug
             st_multi((st_difference(
               (select st_union(a.the_geom,(select st_union(b.the_geom) FROM jagdkataster.jagdbezirke b WHERE b.art in ('jbe','agf','jbf','jex') AND b.jb_zuordnung=a.id))),
               (select st_union(b.the_geom) FROM jagdkataster.jagdbezirke b WHERE b.art = 'atf' AND b.jb_zuordnung=a.id))))
            end
          end
         FROM jagdkataster.jagdbezirke a WHERE a.id = NEW.id;
 
    -- Wenn der EJB in Abrundung auf "Verzicht" gesetzt wurde, wird ein zweiter - deckungsgleicher - abgerundeter EJB mit Verzicht angelegt
       IF NEW.verzicht = 't' THEN
 
         INSERT INTO jagdkataster.jagdbezirke_abgerundet
         SELECT id, NULL, name, NULL, NULL, 'vor Bestandskraft', 't', NULL, NULL, cast(now() as date), the_geom
         FROM jagdkataster.jagdbezirke_abgerundet WHERE id = NEW.id;
 
       END IF;
 
    -- Wenn Verzicht-Flächen vorhanden sind, aber nicht den gesamten EJB überdecken, werden diese in die abgerundeten EJB übernommen
       IF count_verzicht > 0 THEN
 
         INSERT INTO jagdkataster.jagdbezirke_abgerundet
         (SELECT jb_zuordnung, NULL, (SELECT name FROM jagdkataster.jagdbezirke b where b.id = NEW.id), NULL, NULL, 'vor Bestandskraft', 't', NULL, NULL, cast(now() as date), st_multi(a.the_geom)
         FROM jagdkataster.jagdbezirke a WHERE a.jb_zuordnung = NEW.id AND art = 'atv');
 
       END IF;
 
       UPDATE jagdkataster.jagdbezirke_abgerundet set flaeche = round(cast(st_area(the_geom)/10000 as numeric),1) where id = NEW.id;
 
     END IF;
     RETURN NEW;
    END IF;
 
 RETURN NULL;
 END;$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 
 CREATE TRIGGER ejb_bereinigen
  AFTER INSERT
  ON jagdkataster.jagdbezirke_abgerundet
  FOR EACH ROW
  EXECUTE PROCEDURE jagdkataster.ejb_bereinigen();
 

Befriedete Bezirke

 CREATE TABLE jagdkataster.befriedete_bezirke
 (
  id serial NOT NULL,
  name character varying(50),
  zu_gjb character varying(50)
 )
 WITH (
  OIDS=TRUE
 );
 SELECT AddGeometryColumn('jagdkataster', 'befriedete_bezirke','the_geom',25833,'MULTIPOLYGON', 2);
 CREATE INDEX ixbefbezirke_the_geom_gist
  ON jagdkataster.befriedete_bezirke
  USING gist
  (the_geom);
 

Befriedete Bezirke Flurstücke/Nutzungen

 CREATE OR REPLACE VIEW jagdkataster.befriedete_bezirke_flurstuecke AS 
 SELECT b.id, a.flurstueckskennzeichen, a.amtlicheflaeche, round((st_area_utm(st_intersection(b.the_geom, a.wkb_geometry), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision))::numeric, 0) AS fst_teilflaeche_abs, round((st_area_utm(st_intersection(b.the_geom, a.wkb_geometry), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision) / a.amtlicheflaeche * 100::double precision)::numeric, 1) AS fst_teilflaeche_proz, ((nas.nutzungsartengruppe::text || nas.nutzungsart::text) || nas.untergliederung1::text) || nas.untergliederung2::text AS naschluessel, COALESCE(bn.bezeichnung, nag.gruppe) AS nutzung, round((st_area_utm(st_intersection(b.the_geom, st_intersection(n.wkb_geometry, a.wkb_geometry)), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision))::numeric, 0) AS na_teilflaeche_abs, round((st_area_utm(st_intersection(b.the_geom, st_intersection(n.wkb_geometry, a.wkb_geometry)), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision) / (st_area_utm(st_intersection(b.the_geom, a.wkb_geometry), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision)) * 100::double precision)::numeric, 1) AS na_teilflaeche_proz
   FROM jagdkataster.befriedete_bezirke b
   LEFT JOIN alkis.ax_flurstueck a ON st_intersects(b.the_geom, a.wkb_geometry) AND a.endet IS NULL AND (st_area_utm(st_intersection(b.the_geom, a.wkb_geometry), 25833, 6384000::numeric, 38) / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38) * 100::numeric) > 0.1
   LEFT JOIN alkis.n_nutzung n ON st_intersects(n.wkb_geometry, a.wkb_geometry) AND st_area_utm(st_intersection(n.wkb_geometry, a.wkb_geometry), 25833, 6384000::numeric, 38) > 0.001 AND st_area_utm(st_intersection(b.the_geom, st_intersection(n.wkb_geometry, a.wkb_geometry)), 25833, 6384000::numeric, 38) > 0.1
   LEFT JOIN alkis.n_nutzungsartenschluessel nas ON n.nutzungsartengruppe = nas.nutzungsartengruppe AND n.werteart1 = nas.werteart1 AND n.werteart2 = nas.werteart2
   LEFT JOIN alkis.n_nutzungsartengruppe nag ON nas.nutzungsartengruppe = nag.schluessel
   LEFT JOIN jagdkataster.befriedete_bezirke_nutzungen bn ON bn.schluessel::text = (((nas.nutzungsartengruppe::text || nas.nutzungsart::text) || nas.untergliederung1::text) || nas.untergliederung2::text)
  ORDER BY a.flurstueckskennzeichen, round((st_area_utm(st_intersection(b.the_geom, st_intersection(n.wkb_geometry, a.wkb_geometry)), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision) / (st_area_utm(st_intersection(b.the_geom, a.wkb_geometry), 25833, 6384000::numeric, 38)::double precision * (a.amtlicheflaeche / st_area_utm(a.wkb_geometry, 25833, 6384000::numeric, 38)::double precision)) * 100::double precision)::numeric, 1) DESC;