GLE-Postgis-Baumkataster

CREATE SCHEMA baumkataster AUTHORIZATION kvwmap;

CREATE TABLE baumkataster.grunddaten_baum
(
  baumid serial NOT NULL,
  baumnummer integer NOT NULL,
  status character varying(10) NOT NULL DEFAULT 'vorhanden'::character varying,
  baumart character varying(255),
  baumart_spezial character varying(255),
  standort character varying(255),
  eigentuemer_fst character varying(25) DEFAULT 'öffentlich'::character varying,
  sicherheitserwartung character varying(10) NOT NULL,
  the_geom geometry,
  amt character varying(100),
  gemeinde character varying(100),
  sorte character varying(50),
  kontrollintervall character varying(50) NOT NULL)
WITH (
  OIDS=TRUE
);
SELECT AddGeometryColumn('baumkataster', 'grunddaten_baum','the_geom',25833,'POINT', 2);
ALTER TABLE baumkataster.grunddaten_baum ADD CONSTRAINT baumid PRIMARY KEY(baumid);

CREATE TABLE baumkataster.kontrollen
(
  kontrollid serial NOT NULL,
  baumid integer NOT NULL,
  datum date NOT NULL,
  kontrollart character varying(20) NOT NULL DEFAULT 'Ersterfassung'::character varying,
  bearbeiter character varying(100) NOT NULL,
  baumhoehe smallint,
  kronendurchmesser smallint,
  stammumfang numeric,
  zustand character varying(50),
  entwicklungsphase character varying(20) NOT NULL,
  handlungsbedarf character varying(4) NOT NULL DEFAULT 'nein'::character varying,
  weitereinaugenschein character varying(4) NOT NULL DEFAULT '-'::character varying,
  eingehendeuntersuchung character varying(4) NOT NULL DEFAULT '-'::character varying,
  massnahme character varying(4) NOT NULL DEFAULT '-'::character varying,
  massnahmeart character varying(50) DEFAULT '-'::character varying,
  faellung character varying(4) NOT NULL DEFAULT 'nein'::character varying,
  erledigung character varying(50) NOT NULL DEFAULT '-'::character varying,
  bemerkung text,
  alteramstandort smallint)
WITH (
  OIDS=TRUE
);
CREATE OR REPLACE RULE insert_kontrolle AS
    ON INSERT TO baumkataster.kontrollen DO ( INSERT INTO baumkataster.regelkontrollen (kontrollid, k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, k12, k13, k14, k15, bemerkungen_krone, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, bemerkungen_stamm, w1, w2, w3, w4, w5, w6, w7, w8, bemerkungen_stammfuss, wu1, wu2, wu3, bemerkungen_wurzel, v1, v2, v3, v4, v5, v6, bemerkungen_umfeld) 
  VALUES (new.kontrollid - 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ''::text, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ''::text, 0, 0, 0, 0, 0, 0, 0, 0, ''::text, 0, 0, 0, ''::text, 0, 0, 0, 0, 0, 0, ''::text);
 SELECT setval('baumkataster.kontrollen_kontrollid_seq'::regclass, (new.kontrollid - 2)::bigint) AS setval;
);
ALTER TABLE baumkataster.kontrollen ADD CONSTRAINT kontrollid PRIMARY KEY(kontrollid);
ALTER TABLE baumkataster.kontrollen
  ADD CONSTRAINT fkb_ko FOREIGN KEY (baumid)
      REFERENCES baumkataster.grunddaten_baum (baumid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE;

CREATE TABLE baumkataster.kontrollen_docs
(
  kontrollid integer NOT NULL,
  dokument character varying(255) NOT NULL
)
WITH (
  OIDS=TRUE
);
ALTER TABLE baumkataster.kontrollen_docs
  ADD CONSTRAINT fkb_kdoc FOREIGN KEY (kontrollid)
      REFERENCES baumkataster.kontrollen (kontrollid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE;

CREATE TABLE baumkataster.regelkontrollen
(
  kontrollid integer NOT NULL,
  k1 smallint DEFAULT 0,
  k2 smallint DEFAULT 0,
  k3 smallint DEFAULT 0,
  k4 smallint DEFAULT 0,
  k5 smallint DEFAULT 0,
  k6 smallint DEFAULT 0,
  k7 smallint DEFAULT 0,
  k8 smallint DEFAULT 0,
  k9 smallint DEFAULT 0,
  k10 smallint DEFAULT 0,
  k11 smallint DEFAULT 0,
  k12 smallint DEFAULT 0,
  k13 smallint DEFAULT 0,
  k14 smallint DEFAULT 0,
  k15 smallint DEFAULT 0,
  bemerkungen_krone text,
  s1 smallint DEFAULT 0,
  s2 smallint DEFAULT 0,
  s3 smallint DEFAULT 0,
  s4 smallint DEFAULT 0,
  s5 smallint DEFAULT 0,
  s6 smallint DEFAULT 0,
  s7 smallint DEFAULT 0,
  s8 smallint DEFAULT 0,
  s9 smallint DEFAULT 0,
  s10 smallint DEFAULT 0,
  s11 smallint DEFAULT 0,
  s12 smallint DEFAULT 0,
  s13 smallint DEFAULT 0,
  s14 smallint DEFAULT 0,
  s15 smallint DEFAULT 0,
  bemerkungen_stamm text,
  w1 smallint DEFAULT 0,
  w2 smallint DEFAULT 0,
  w3 smallint DEFAULT 0,
  w4 smallint DEFAULT 0,
  w5 smallint DEFAULT 0,
  w6 smallint DEFAULT 0,
  w7 smallint DEFAULT 0,
  w8 smallint DEFAULT 0,
  bemerkungen_stammfuss text,
  wu1 smallint DEFAULT 0,
  wu2 smallint DEFAULT 0,
  wu3 smallint DEFAULT 0,
  bemerkungen_wurzel text,
  v1 smallint DEFAULT 0,
  v2 smallint DEFAULT 0,
  v3 smallint DEFAULT 0,
  v4 smallint DEFAULT 0,
  v5 smallint DEFAULT 0,
  v6 smallint DEFAULT 0,
  bemerkungen_umfeld text)
WITH (
  OIDS=TRUE
);
ALTER TABLE baumkataster.regelkontrollen
  ADD CONSTRAINT fk_rk FOREIGN KEY (kontrollid)
      REFERENCES baumkataster.kontrollen (kontrollid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE;