GLE-Postgis-Kompensationsflächen

CREATE SCHEMA lung;

SET search_path = lung, public;

SET default_with_oids = true;

CREATE TABLE kompensationsflaechen
(
  id serial NOT NULL,
  massnahme text NOT NULL,
  bil_methode text NOT NULL,
  zb_haupt_mnt text NOT NULL,
  haupt_mnt integer NOT NULL,
  zb_neben_mnt text,
  neben_mnt integer,
  flaeche_gis bigint NOT NULL,
  flaeche_planung bigint NOT NULL,
  kreis text NOT NULL,
  ortsname text NOT NULL,
  planverfasser text NOT NULL,
  status text NOT NULL,
  realisierung_y text NOT NULL,
  unterhaltung_y text NOT NULL,
  oeff_sicherung text NOT NULL,
  privat_sicherung text NOT NULL,
  komp_fuer text NOT NULL,
  komp_pflichtiger text NOT NULL,
  gen_verfahren text NOT NULL,
  rechtsgrundlage text NOT NULL,
  gen_behoerde text NOT NULL,
  gen_dat date NOT NULL,
  gen_az text NOT NULL,
  web_url text,
  dokument text,
  bemerkung text,
  bearbeiter text NOT NULL,
  letzte_akt character varying(20) NOT NULL,
);
SELECT AddGeometryColumn('lung', 'kompensationsflaechen','the_geom',2398,'GEOMETRY', 2);


CREATE TABLE oekokontoflaechen
(
  id serial NOT NULL,
  massnahme text NOT NULL,
  bil_methode text NOT NULL,
  zb_haupt_mnt text NOT NULL,
  haupt_mnt integer NOT NULL,
  zb_neben_mnt text,
  neben_mnt integer,
  flaeche_gis bigint NOT NULL,
  flaeche_planung bigint NOT NULL,
  kreis text NOT NULL,
  ortsname text NOT NULL,
  planverfasser text NOT NULL,
  status text NOT NULL DEFAULT 'Ökokonto'::text,
  realisierung_y text NOT NULL,
  unterhaltung_y text NOT NULL,
  oeff_sicherung text NOT NULL,
  privat_sicherung text NOT NULL,
  web_url text,
  dokument text,
  bemerkung text,
  oek_reg_nr text NOT NULL,
  oek_kf_behoerde text NOT NULL,
  oek_einb_dat date NOT NULL,
  oek_handelbar text NOT NULL,
  oek_gen_verfahren text NOT NULL,
  oek_gen_behoerde text NOT NULL,
  oek_gen_dat date NOT NULL,
  oek_rechtsgrundlage text NOT NULL,
  oek_az text NOT NULL,
  oek_inhaber text NOT NULL,
  oek_ansprechpartner text NOT NULL,
  oek_adresse text NOT NULL,
  oek_telefon text NOT NULL,
  oek_email text NOT NULL,
  oek_flaechen_aequi bigint NOT NULL,
  bearbeiter text NOT NULL,
  letzte_akt character varying(20) NOT NULL,
);
SELECT AddGeometryColumn('lung', 'oekokontoflaechen','the_geom',2398,'GEOMETRY', 2);

CREATE TABLE abbuchungen
(
  id integer NOT NULL,
  abbuchungsnr integer NOT NULL,
  abbuchung_dat date NOT NULL,
  ausgleichspflichtiger text NOT NULL,
  abbuchung_vorhaben text NOT NULL,
  abbuchung_punkte integer NOT NULL,
  bemerkung text,
  bearbeiter text,
  letzte_akt character varying(20) NOT NULL,
  CONSTRAINT fkabbuchungenoekokf FOREIGN KEY (id)
      REFERENCES oekokontoflaechen (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE kontrollen_kompf
(
  id integer NOT NULL,
  kontroll_nr integer NOT NULL,
  kontroll_dat date NOT NULL,
  kontrolle_durch text NOT NULL,
  erstbearbeiter text NOT NULL,
  ergebnis text NOT NULL,
  bemerkung text,
  kontrolle_neu text,
  bearbeiter text,
  letzte_akt character varying(20) NOT NULL,
  CONSTRAINT fkkontrollenkompf FOREIGN KEY (id)
      REFERENCES kompensationsflaechen (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE lung.kontrollen_oekokf
(
  id integer NOT NULL,
  kontroll_nr integer NOT NULL,
  kontroll_dat date NOT NULL,
  kontrolle_durch text NOT NULL,
  erstbearbeiter text NOT NULL,
  ergebnis text NOT NULL,
  bemerkung text,
  kontrolle_neu text,
  bearbeiter text,
  letzte_akt character varying(20) NOT NULL,
  CONSTRAINT fkkontrollenoekokf FOREIGN KEY (id)
      REFERENCES oekokontoflaechen (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);