GLE-Postgis-Baumkataster-WIRO
Aus kvwmap
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.4.10
-- Dumped by pg_dump version 9.4.10
-- Started on 2019-08-26 10:34:09 UTC
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- TOC entry 12 (class 2615 OID 517158)
-- Name: baumkataster; Type: SCHEMA; Schema: -; Owner: -
--
CREATE OR REPLACE FUNCTION count_true(text)
RETURNS integer AS
$BODY$
select (length($1) - length(regexp_replace($1, 'true', '', 'g'))) / 4;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
CREATE SCHEMA grunddaten;
CREATE TABLE grunddaten.kundencenter
(
id integer NOT NULL,
name character varying NOT NULL,
zustaendig_fuer character varying,
strasse character(1),
plz character(1),
ort character(1),
tel character(1),
"e-mail" character varying,
CONSTRAINT id PRIMARY KEY (id)
)
WITH (
OIDS=true
);
CREATE TABLE grunddaten.abteilungen
(
id serial NOT NULL,
name character varying(32) NOT NULL,
telefon character varying(32),
"e-mail" character varying(32),
name_kurz character varying(8) NOT NULL
)
WITH (
OIDS=TRUE
);
CREATE TABLE grunddaten.stadtteil
(
id integer NOT NULL,
name character varying NOT NULL,
CONSTRAINT stadtteil_id PRIMARY KEY (id)
)
WITH (
OIDS=true
);
CREATE TABLE grunddaten.wirtschaftseinheiten
(
id serial NOT NULL,
nummer character varying(15),
adressen text,
flaeche double precision,
the_geom geometry,
flurbezirke text,
fluren text,
flurstuecke text,
lgbnummern text,
nutzungsarten text,
eigentuemer text,
abschnitte text,
unummern text,
zaehlernenner text,
gemarkungen text,
grundbuecher text,
bemerkungen text,
updated_at timestamp without time zone,
updated_from character varying(100),
checked character varying(255) NOT NULL DEFAULT 1,
mit_teilflaeche smallint NOT NULL DEFAULT 0,
organisationseinheit character varying(10),
stadtteil character varying(24),
wohneigentum smallint NOT NULL DEFAULT 0,
anzahl_baeume integer,
uebergeordnet boolean NOT NULL DEFAULT false,
weg integer,
the_geom_25833_alt geometry,
the_geom_25833 geometry,
stadtteil_id integer,
abteilung_id serial NOT NULL,
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_dims_the_geom_25833 CHECK (st_ndims(the_geom_25833) = 2),
CONSTRAINT enforce_dims_the_geom_25833_alt CHECK (st_ndims(the_geom_25833_alt) = 2),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2398),
CONSTRAINT enforce_srid_the_geom_25833 CHECK (st_srid(the_geom_25833) = 25833),
CONSTRAINT enforce_srid_the_geom_25833_alt CHECK (st_srid(the_geom_25833_alt) = 25833)
)
WITH (
OIDS=TRUE
);
CREATE SCHEMA baumkataster;
SET search_path = baumkataster, pg_catalog;
--
-- TOC entry 1430 (class 1255 OID 517159)
-- Name: create_new_kontrolle(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION create_new_kontrolle() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.kontrollen (baumid, datum, kontrollart, datenerfasser, baumhoehe, kronendurchmesser, stammumfang)
VALUES (NEW.baumid, now(), 0, new.bearbeiter, 0, 0, 0);
RETURN NEW;
END;
$$;
--
-- TOC entry 1434 (class 1255 OID 517160)
-- Name: create_new_regelkontrolle(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION create_new_regelkontrolle() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.regelkontrollen (kontrollid, k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, k12, k13, k14, k15, k16, k17, k18, bemerkungen_krone, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, 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, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, ''::text, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, ''::text, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, ''::text, FALSE, FALSE, FALSE, ''::text, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, ''::text);
RETURN NEW;
END;
$$;
--
-- TOC entry 1426 (class 1255 OID 517161)
-- Name: document_handlungsbedarf(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION document_handlungsbedarf() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.handlungsbedarfe (baumid, baumnummer, status, baumart, baumart_spezial, standort, eigentuemer_fst, sicherheitserwartung, amt, gemeinde, sorte, kontrollintervall, bearbeiter, kontroll_status, punktnummer_vermessung, wirtschaftseinheit_id, kontrollid, datum, kontrollart, datenerfasser, baumhoehe, kronendurchmesser, stammumfang, zustand, entwicklungsphase, handlungsbedarf, weitereinaugenschein, eingehendeuntersuchung, massnahme, massnahmeart, faellung, erledigung, bemerkung, alteramstandort, massnahmebearbeiter, stammanzahl, user_name, the_geom)
SELECT g.baumid, g.baumnummer, g.status, g.baumart, g.baumart_spezial, g.standort, g.eigentuemer_fst, g.sicherheitserwartung, g.amt, g.gemeinde, g.sorte, g.kontrollintervall, g.bearbeiter, 2, g.punktnummer_vermessung, g.wirtschaftseinheit_id, NEW.kontrollid, NEW.datum, NEW.kontrollart, NEW.datenerfasser, NEW.baumhoehe, NEW.kronendurchmesser, NEW.stammumfang, NEW.zustand, NEW.entwicklungsphase, NEW.handlungsbedarf, NEW.weitereinaugenschein, NEW.eingehendeuntersuchung, NEW.massnahme, NEW.massnahmeart, NEW.faellung, NEW.erledigung, NEW.bemerkung, NEW.alteramstandort, NEW.massnahmebearbeiter, NEW.stammanzahl, NEW.user_name, g.the_geom
FROM baumkataster.grunddaten_baum g
WHERE NEW.handlungsbedarf = 'ja' AND OLD.handlungsbedarf = 'nein' AND g.baumid = NEW.baumid;
RETURN NEW;
END;
$$;
--
-- TOC entry 1453 (class 1255 OID 896147)
-- Name: log_grunddaten_baum_delete(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION log_grunddaten_baum_delete() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.grunddaten_baum_logs (baumid,baumnummer_alt,baumnummer,status_alt,status,baumart_alt,baumart,baumart_spezial_alt,baumart_spezial,standort_alt,standort,eigentuemer_fst_alt,eigentuemer_fst,sicherheitserwartung_alt,sicherheitserwartung,amt_alt,amt,gemeinde_alt,gemeinde,sorte_alt,sorte,kontrollintervall_alt,kontrollintervall,the_geom_alt,the_geom_25833,bearbeiter_alt,bearbeiter,kontroll_status_alt,kontroll_status,wirtschaftseinheit_id_alt,wirtschaftseinheit_id,log_grund)
VALUES (OLD.baumid,OLD.baumnummer,NEW.baumnummer,OLD.status,NEW.status,OLD.baumart,NEW.baumart,OLD.baumart_spezial,NEW.baumart_spezial,OLD.standort,NEW.standort,OLD.eigentuemer_fst,NEW.eigentuemer_fst,OLD.sicherheitserwartung,NEW.sicherheitserwartung,OLD.amt,NEW.amt,OLD.gemeinde,NEW.gemeinde,OLD.sorte,NEW.sorte,OLD.kontrollintervall,NEW.kontrollintervall,OLD.the_geom_25833,NEW.the_geom_25833,OLD.bearbeiter,NEW.bearbeiter,OLD.kontroll_status,NEW.kontroll_status,OLD.wirtschaftseinheit_id,NEW.wirtschaftseinheit_id, 'DELETE'::character varying);
RETURN NEW;
END;
$$;
--
-- TOC entry 1449 (class 1255 OID 896146)
-- Name: log_grunddaten_baum_insert(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION log_grunddaten_baum_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.grunddaten_baum_logs (baumid,baumnummer_alt,baumnummer,status_alt,status,baumart_alt,baumart,baumart_spezial_alt,baumart_spezial,standort_alt,standort,eigentuemer_fst_alt,eigentuemer_fst,sicherheitserwartung_alt,sicherheitserwartung,amt_alt,amt,gemeinde_alt,gemeinde,sorte_alt,sorte,kontrollintervall_alt,kontrollintervall,the_geom_25833_alt,the_geom_25833,bearbeiter_alt,bearbeiter,kontroll_status_alt,kontroll_status,wirtschaftseinheit_id_alt,wirtschaftseinheit_id,log_grund)
VALUES (NULL,NULL,NEW.baumnummer,NULL,NEW.status,NULL,NEW.baumart,NULL,NEW.baumart_spezial,NULL,NEW.standort,NULL,NEW.eigentuemer_fst,NULL,NEW.sicherheitserwartung,NULL,NEW.amt,NULL,NEW.gemeinde,NULL,NEW.sorte,NULL,NEW.kontrollintervall,NULL,NEW.the_geom_25833,NULL,NEW.bearbeiter,NULL,NEW.kontroll_status,NULL,NEW.wirtschaftseinheit_id, 'INSERT'::character varying);
RETURN NEW;
END;
$$;
--
-- TOC entry 1424 (class 1255 OID 896144)
-- Name: log_grunddaten_baum_update(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION log_grunddaten_baum_update() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.grunddaten_baum_logs (baumid,baumnummer_alt,baumnummer,status_alt,status,baumart_alt,baumart,baumart_spezial_alt,baumart_spezial,standort_alt,standort,eigentuemer_fst_alt,eigentuemer_fst,sicherheitserwartung_alt,sicherheitserwartung,amt_alt,amt,gemeinde_alt,gemeinde,sorte_alt,sorte,kontrollintervall_alt,kontrollintervall,the_geom_25833_alt,the_geom_25833,bearbeiter_alt,bearbeiter,kontroll_status_alt,kontroll_status,wirtschaftseinheit_id_alt,wirtschaftseinheit_id,log_grund)
VALUES (OLD.baumid,OLD.baumnummer,NEW.baumnummer,OLD.status,NEW.status,OLD.baumart,NEW.baumart,OLD.baumart_spezial,NEW.baumart_spezial,OLD.standort,NEW.standort,OLD.eigentuemer_fst,NEW.eigentuemer_fst,OLD.sicherheitserwartung,NEW.sicherheitserwartung,OLD.amt,NEW.amt,OLD.gemeinde,NEW.gemeinde,OLD.sorte,NEW.sorte,OLD.kontrollintervall,NEW.kontrollintervall,OLD.the_geom_25833,NEW.the_geom_25833,OLD.bearbeiter,NEW.bearbeiter,OLD.kontroll_status,NEW.kontroll_status,OLD.wirtschaftseinheit_id,NEW.wirtschaftseinheit_id, 'UPDATE'::character varying);
RETURN NEW;
END;
$$;
--
-- TOC entry 1427 (class 1255 OID 517162)
-- Name: log_kontrollen_insert(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION log_kontrollen_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO baumkataster.kontrollen_logs (kontrollid, baumid, datum, kontrollart, datenerfasser, baumhoehe, kronendurchmesser, stammumfang, zustand, entwicklungsphase, bemerkung, user_name, log_grund)
VALUES (NEW.kontrollid, NEW.baumid, NEW.datum, NEW.kontrollart, NEW.datenerfasser, NEW.baumhoehe, NEW.kronendurchmesser, NEW.stammumfang, NEW.zustand, NEW.entwicklungsphase, NEW.bemerkung, NEW.user_name, 'INSERT'::character varying);
RETURN NEW;
END;
$$;
--
-- TOC entry 1428 (class 1255 OID 517163)
-- Name: log_kontrollen_update(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION log_kontrollen_update() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.datum != OLD.datum OR NEW.kontrollart != OLD.kontrollart OR NEW.datenerfasser != OLD.datenerfasser OR NEW.baumhoehe != OLD.baumhoehe OR NEW.kronendurchmesser != OLD.kronendurchmesser OR NEW.stammumfang != OLD.stammumfang OR NEW.zustand != OLD.zustand OR NEW.entwicklungsphase != OLD.entwicklungsphase OR NEW.bemerkung != OLD.bemerkung OR NEW.user_name != OLD.user_name
THEN
INSERT INTO baumkataster.kontrollen_logs (kontrollid, baumid, datum, kontrollart, datenerfasser, baumhoehe, kronendurchmesser, stammumfang, zustand, entwicklungsphase, bemerkung, user_name, log_grund)
VALUES (NEW.kontrollid, NEW.baumid, NEW.datum, NEW.kontrollart, NEW.datenerfasser, NEW.baumhoehe, NEW.kronendurchmesser, NEW.stammumfang, NEW.zustand, NEW.entwicklungsphase, NEW.bemerkung, NEW.user_name, 'UPDATE'::character varying);
END IF;
RETURN NEW;
END;
$$;
--
-- TOC entry 1445 (class 1255 OID 1845389)
-- Name: set_last_after_delete(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION set_last_after_delete() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
baumkataster.kontrollen
SET
is_last = (kontrollid = (SELECT kontrollid FROM baumkataster.kontrollen WHERE baumid = OLD.baumid ORDER BY datum DESC LIMIT 1))
WHERE
baumid = OLD.baumid;
RETURN OLD;
END
$$;
--
-- TOC entry 1441 (class 1255 OID 1845424)
-- Name: set_last_after_insert(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION set_last_after_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
baumkataster.kontrollen
SET
is_last = (kontrollid = (SELECT kontrollid FROM baumkataster.kontrollen WHERE baumid = NEW.baumid ORDER BY datum DESC LIMIT 1))
WHERE
baumid = NEW.baumid;
RETURN NEW;
END
$$;
--
-- TOC entry 1433 (class 1255 OID 896019)
-- Name: set_new_datum(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION set_new_datum() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.datum := now();
RETURN NEW;
END
$$;
--
-- TOC entry 1438 (class 1255 OID 930397)
-- Name: update_handlungsbedarf_wegen_regelkontrolle_k10(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_handlungsbedarf_wegen_regelkontrolle_k10() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
baumkataster.kontrollen
SET
handlungsbedarf = 'ja',
massnahmeart = 'Lichtraumprofilschnitt',
erledigung = '(2) innerhalb von 6 Monaten'
WHERE
baumkataster.kontrollen.kontrollid = NEW.kontrollid AND
new.k10 IS TRUE;
RETURN NEW;
END;
$$;
--
-- TOC entry 1439 (class 1255 OID 930401)
-- Name: update_handlungsbedarf_wegen_regelkontrolle_k13(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_handlungsbedarf_wegen_regelkontrolle_k13() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE baumkataster.kontrollen SET handlungsbedarf = 'ja', massnahmeart = 'Totholz entfernen TH 3-10 cm', erledigung = '(2) innerhalb von 6 Monaten'
WHERE baumkataster.kontrollen.kontrollid = NEW.kontrollid AND new.k13 IS TRUE;
RETURN NEW;
END;
$$;
--
-- TOC entry 1431 (class 1255 OID 517164)
-- Name: update_kontroll_status_baum_wegen_aenderung_handlungsbedarf(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_kontroll_status_baum_wegen_aenderung_handlungsbedarf() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE baumkataster.grunddaten_baum SET kontroll_status =
CASE WHEN new.datum < date_trunc('year', now())
THEN 1 -- in diesem Jahr noch nicht kontrolliert
ELSE
CASE WHEN new.handlungsbedarf = 'ja'::character varying(8)
THEN 2 -- es besteht Handlungsbedarf in diesem Jahr
ELSE 0 -- es besteht kein Handlungsbedarf in diesem Jahr
END
END
WHERE baumkataster.grunddaten_baum.baumid = NEW.baumid;
RETURN NEW;
END;
$$;
--
-- TOC entry 1446 (class 1255 OID 1737596)
-- Name: update_kontroll_status_baum_wegen_handlungsbedarf_erledigt(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_kontroll_status_baum_wegen_handlungsbedarf_erledigt() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE
baumkataster.grunddaten_baum
SET
kontroll_status =
case WHEN old.handlungsbedarf = 'ja' AND new.handlungsbedarf = 'erledigt'
THEN 0 -- Handlungsbedarf wurde erledigt, Baum ist grün
ELSE kontroll_status
END
WHERE baumkataster.grunddaten_baum.baumid = NEW.baumid;
RETURN NEW;
END;
$$;
--
-- TOC entry 1429 (class 1255 OID 517165)
-- Name: update_kontroll_status_baum_wegen_neuer_kontrolle(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_kontroll_status_baum_wegen_neuer_kontrolle() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE baumkataster.grunddaten_baum SET kontroll_status = 0 WHERE grunddaten_baum.baumid = NEW.baumid;
RETURN NEW;
END;
$$;
--
-- TOC entry 1455 (class 1255 OID 2551126)
-- Name: update_kontroll_status_gebiet(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_kontroll_status_gebiet() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE baumkataster.negativkontrollgebiete SET kontroll_status = 0 WHERE gebiet_id = NEW.gebiet_id;
RETURN NEW;
END;
$$;
--
-- TOC entry 1456 (class 1255 OID 2551129)
-- Name: update_kontroll_status_gebiet_handlungsbedarf(); Type: FUNCTION; Schema: baumkataster; Owner: -
--
CREATE FUNCTION update_kontroll_status_gebiet_handlungsbedarf() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE baumkataster.negativkontrollgebiete SET kontroll_status = 2
FROM
baumkataster.negativkontrollen nk
WHERE
nk.kontroll_id = new.kontroll_id AND
nk.gebiet_id = negativkontrollgebiete.gebiet_id;
RETURN NEW;
END;
$$;
SET default_tablespace = '';
SET default_with_oids = true;
--
-- TOC entry 239 (class 1259 OID 517166)
-- Name: baumarten; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE baumarten (
bezeichnung character varying(255) NOT NULL,
bezeichnunglatein character varying(255) NOT NULL,
baumart_id smallint NOT NULL
);
INSERT INTO baumarten VALUES ('Amberbaum', 'Liquidambar', 72);
INSERT INTO baumarten VALUES ('Apfel', 'Malus spec.', 40);
INSERT INTO baumarten VALUES ('Baumhasel', 'Corylus colurna', 7);
INSERT INTO baumarten VALUES ('Berg-Ahorn', 'Acer pseudoplatanus', 80);
INSERT INTO baumarten VALUES ('Birke', 'Betula spec.', 5);
INSERT INTO baumarten VALUES ('Birne', 'Pyrus', 41);
INSERT INTO baumarten VALUES ('Birne "Beech Hill"', 'Pyrus communis', 49);
INSERT INTO baumarten VALUES ('Blasenesche', 'Koelreuteria paniculata', 98);
INSERT INTO baumarten VALUES ('0 Art unbekannt', '-', 19);
INSERT INTO baumarten VALUES ('Blumen-Esche', 'Fraxinus ornus', 74);
INSERT INTO baumarten VALUES ('Blut-Buche', 'Fagus sylvatica purpurea', 32);
INSERT INTO baumarten VALUES ('Crataegus prunifolia', 'Pflaumenblättriger Weißdorn', 118);
INSERT INTO baumarten VALUES ('Crataegus prunifolia ', 'Pflaumenblättriger Weißdorn', 119);
INSERT INTO baumarten VALUES ('Douglasie', 'Pseudotsuga menziesii', 44);
INSERT INTO baumarten VALUES ('Eberesche, Vogelbeere', 'Sorbus aucuparia', 17);
INSERT INTO baumarten VALUES ('Echte Mehlbeere', 'Sorbus aria', 47);
INSERT INTO baumarten VALUES ('Edelkastanie', 'Castanea sativa', 54);
INSERT INTO baumarten VALUES ('Eibe', 'Taxus spec.', 51);
INSERT INTO baumarten VALUES ('Eiche', 'Quercus spec.', 14);
INSERT INTO baumarten VALUES ('Erle', 'Alnus', 86);
INSERT INTO baumarten VALUES ('Esche', 'Fraxinus spec.', 10);
INSERT INTO baumarten VALUES ('Eschen-Ahorn', 'Acer negundo', 81);
INSERT INTO baumarten VALUES ('Essigbaum', 'Rhus typhina', 46);
INSERT INTO baumarten VALUES ('Europäische Lärche', 'Larix decidua', 23);
INSERT INTO baumarten VALUES ('Fächer-Ahorn', 'Acer palmatum', 106);
INSERT INTO baumarten VALUES ('Faulbaum', 'Frangula alnus', 56);
INSERT INTO baumarten VALUES ('Feld-Ahorn', 'Acer campestre', 83);
INSERT INTO baumarten VALUES ('Felsmispel', 'Cotoneaster ssp.', 111);
INSERT INTO baumarten VALUES ('Feuer-Ahorn', 'Acer ginnala', 82);
INSERT INTO baumarten VALUES ('Fichte', 'Picea spec.', 22);
INSERT INTO baumarten VALUES ('Flieder', 'Syringa vulgaris', 50);
INSERT INTO baumarten VALUES ('Geweihbaum', 'Gymnocladus dioicus', 70);
INSERT INTO baumarten VALUES ('Ginkgobaum', 'Ginkgo biloba L.', 33);
INSERT INTO baumarten VALUES ('Ginster', 'Genista', 58);
INSERT INTO baumarten VALUES ('Gleditschie', 'Gleditsia', 34);
INSERT INTO baumarten VALUES ('Goldregen', 'Laburnum anagyroides', 37);
INSERT INTO baumarten VALUES ('Götterbaum', 'Ailanthus altissima', 3);
INSERT INTO baumarten VALUES ('Grau-Erle', 'Alnus incana', 93);
INSERT INTO baumarten VALUES ('Hahnensporn-Weißdorn', 'Crataegus crus-galli', 91);
INSERT INTO baumarten VALUES ('Hainbuche', 'Carpinus betulus', 6);
INSERT INTO baumarten VALUES ('Hartriegel', 'Cornus spec.', 29);
INSERT INTO baumarten VALUES ('Hasel', 'Corylus avellana', 30);
INSERT INTO baumarten VALUES ('Hemlockstanne', 'Tsuga spec.', 52);
INSERT INTO baumarten VALUES ('Hibalebensbaum', 'Thujopsis dolabrata', 112);
INSERT INTO baumarten VALUES ('Hibiscus', 'Hibiscus', 101);
INSERT INTO baumarten VALUES ('Holunder', 'Sambucus', 59);
INSERT INTO baumarten VALUES ('Japanische Nelkenkirsche', 'Prunus serrulata ´Kanzan´', 113);
INSERT INTO baumarten VALUES ('Japanischer Schnurbaum', 'Styphnolobium japonicum', 66);
INSERT INTO baumarten VALUES ('Johannisbeere', 'Ribes ssp.', 108);
INSERT INTO baumarten VALUES ('Judasbaum', 'Cercis', 89);
INSERT INTO baumarten VALUES ('Kaukasische Flügelnuss', 'Pterocarya fraxinifolia', 61);
INSERT INTO baumarten VALUES ('Kiefer', 'Pinus spec.', 24);
INSERT INTO baumarten VALUES ('Kirschpflaume', 'Prunus cerasifera', 69);
INSERT INTO baumarten VALUES ('Kreuzdorn', 'Rhamnus', 85);
INSERT INTO baumarten VALUES ('Lärche', 'Larix', 75);
INSERT INTO baumarten VALUES ('Lebensbaum', 'Thuja spec.', 25);
INSERT INTO baumarten VALUES ('Liguster', 'Ligustrum', 110);
INSERT INTO baumarten VALUES ('Linde', 'Tilia spec.', 18);
INSERT INTO baumarten VALUES ('Lorbeerkirsche', 'Prunus laurocerasus', 60);
INSERT INTO baumarten VALUES ('Magnolie', 'Magnolia spec.', 39);
INSERT INTO baumarten VALUES ('Mammutbaum', 'Metasequoia glyptostroboides', 97);
INSERT INTO baumarten VALUES ('Mandelbäumchen', 'Prunus triloba', 43);
INSERT INTO baumarten VALUES ('Maulbeere', 'Broussonetia ssp.', 94);
INSERT INTO baumarten VALUES ('Mehlbeere', 'Sorbus spec.', 122);
INSERT INTO baumarten VALUES ('Mispel', 'Mespilus germanica', 42);
INSERT INTO baumarten VALUES ('Öweide', 'Eleagnus angustifolia', 92);
INSERT INTO baumarten VALUES ('Pappel', 'Populus spec.', 12);
INSERT INTO baumarten VALUES ('Perückenstrauch', 'Cotinus', 102);
INSERT INTO baumarten VALUES ('Pfaffenhütchen', 'Euonymus europaeus', 103);
INSERT INTO baumarten VALUES ('Pfeifenstrauch', 'Philadelphius', 109);
INSERT INTO baumarten VALUES ('Pfirsich', 'Prunus persica', 55);
INSERT INTO baumarten VALUES ('Pflaume', 'Prunus spec.', 63);
INSERT INTO baumarten VALUES ('Pflaumenblättriger Weißdorn', 'Crataegus prunifolia ', 120);
INSERT INTO baumarten VALUES ('Platane', 'Platanus x Hybrida', 11);
INSERT INTO baumarten VALUES ('Rhododendron', 'Rhododendron spec.', 45);
INSERT INTO baumarten VALUES ('Robinie (Scheinakazie)', 'Robinia pseudoacacia', 15);
INSERT INTO baumarten VALUES ('Rose', 'Rosa', 99);
INSERT INTO baumarten VALUES ('Rosskastanie', 'Aesculus spec.', 2);
INSERT INTO baumarten VALUES ('Rot-Buche', 'Fagus sylvatica', 9);
INSERT INTO baumarten VALUES ('Rotdorn', 'Crataegus laevigata ´Paul´s Scarlet´', 31);
INSERT INTO baumarten VALUES ('Rot-Eiche', 'Quercus rubra', 84);
INSERT INTO baumarten VALUES ('Rot-Esche', 'Fraxinus pennsylvanica', 105);
INSERT INTO baumarten VALUES ('Sanddorn', 'Hippophae rhamnoides', 57);
INSERT INTO baumarten VALUES ('Scheinzypresse', 'Chamaecyparis', 64);
INSERT INTO baumarten VALUES ('Schlehe', 'Prunus spinosa', 76);
INSERT INTO baumarten VALUES ('Schmalblättrige Öweide', 'Elaeagnus angustifolia', 117);
INSERT INTO baumarten VALUES ('Schmucktanne', 'Araucaria', 100);
INSERT INTO baumarten VALUES ('Schneeball', 'Viburnum spec.', 104);
INSERT INTO baumarten VALUES ('Schnurbaum', 'Sophoa', 123);
INSERT INTO baumarten VALUES ('Schwarz-Erle', 'Alnus glutinosa', 4);
INSERT INTO baumarten VALUES ('Silber-Ahorn', 'Acer saccharinum', 88);
INSERT INTO baumarten VALUES ('Silber-Linde', 'Tilia tomentosa', 87);
INSERT INTO baumarten VALUES ('Spähts-Erle', 'Alnus x spaethii', 95);
INSERT INTO baumarten VALUES ('Speierling', 'Sorbus domestica', 48);
INSERT INTO baumarten VALUES ('Spindelbaum', 'Euonymus europaeus', 121);
INSERT INTO baumarten VALUES ('Spitz-Ahorn', 'Acer planatoides', 77);
INSERT INTO baumarten VALUES ('Stechpalme', 'Ilex aquifolium', 35);
INSERT INTO baumarten VALUES ('Steppenkirsche', 'Prunus fruticosa Globosa', 115);
INSERT INTO baumarten VALUES ('Sumpfeiche', 'Quercus palustris', 107);
INSERT INTO baumarten VALUES ('Tamarisk', 'Tamarix', 65);
INSERT INTO baumarten VALUES ('Tanne', 'Abies spec.', 21);
INSERT INTO baumarten VALUES ('Traubenkirsche', 'Prunus padus', 68);
INSERT INTO baumarten VALUES ('Trompetenbaum', 'Catalpa bignonioides', 27);
INSERT INTO baumarten VALUES ('Tulpenbaum', 'Liriodendron tulipifera', 38);
INSERT INTO baumarten VALUES ('Ulme', 'Ulmus spec.', 53);
INSERT INTO baumarten VALUES ('Wacholder', 'Juniperus', 36);
INSERT INTO baumarten VALUES ('Walnuss', 'Juglans regia', 26);
INSERT INTO baumarten VALUES ('Weide', 'Salix spec.', 16);
INSERT INTO baumarten VALUES ('Weißdorn', 'Crataegus spec.', 8);
INSERT INTO baumarten VALUES ('Weißdorn Hybride', 'Crataegus crus-galli', 96);
INSERT INTO baumarten VALUES ('Winterlinde', 'Tilia cordata', 116);
INSERT INTO baumarten VALUES ('Zeder', 'Cedrus spec.', 28);
INSERT INTO baumarten VALUES ('Zierkirsche', '-', 114);
INSERT INTO baumarten VALUES ('Zitter-Pappel', 'Populus tremula', 90);
INSERT INTO baumarten VALUES ('Amerikanische Linde', 'Tilia americana', 125);
INSERT INTO baumarten VALUES ('Kirsche', 'Prunus spec.', 62);
INSERT INTO baumarten VALUES ('Japanische Blütenkirsche', 'Prunus serrulata', 126);
INSERT INTO baumarten VALUES ('Ahorn', 'Acer spec.', 1);
INSERT INTO baumarten VALUES ('Akazie', 'Acacia', 20);
INSERT INTO baumarten VALUES ('Blutpflaume', 'Prunus cerasifera ', 124);
--
-- TOC entry 240 (class 1259 OID 517172)
-- Name: grunddaten_baum; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE grunddaten_baum (
baumid integer NOT NULL,
baumnummer integer,
status character varying(10) DEFAULT 'Baum'::character varying NOT NULL,
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),
amt character varying(100),
gemeinde character varying(100),
sorte character varying(50),
kontrollintervall character varying(50) DEFAULT 'jährlich'::character varying,
the_geom public.geometry,
bearbeiter character varying(100) NOT NULL,
kontroll_status integer DEFAULT 1 NOT NULL,
punktnummer_vermessung character varying(64),
wirtschaftseinheit_id integer,
status_bemerkung character varying(255),
the_geom_25833 public.geometry NOT NULL,
CONSTRAINT enforce_geotype_the_geom_25833 CHECK (((public.geometrytype(the_geom_25833) = 'POINT'::text) OR (the_geom_25833 IS NULL))),
CONSTRAINT enforce_srid_the_geom_25833 CHECK ((public.st_srid(the_geom_25833) = 25833))
);
--
-- TOC entry 241 (class 1259 OID 517185)
-- Name: kontrollen; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE kontrollen (
kontrollid integer NOT NULL,
baumid integer NOT NULL,
datum date DEFAULT ('now'::text)::date NOT NULL,
kontrollart character varying(20) DEFAULT 'Regelkontrolle'::character varying NOT NULL,
datenerfasser character varying(100) DEFAULT 'Name wird nach dem Speichern angezeigt'::character varying NOT NULL,
baumhoehe smallint NOT NULL,
kronendurchmesser smallint NOT NULL,
stammumfang_meter numeric,
zustand character varying(50),
entwicklungsphase character varying(20),
handlungsbedarf character varying(8) DEFAULT 'nein'::character varying NOT NULL,
weitereinaugenschein character varying(4) DEFAULT '-'::character varying NOT NULL,
eingehendeuntersuchung character varying(4) DEFAULT '-'::character varying NOT NULL,
massnahme character varying(4) DEFAULT '-'::character varying NOT NULL,
massnahmeart character varying(50) DEFAULT '-'::character varying,
faellung character varying(4) DEFAULT 'nein'::character varying NOT NULL,
erledigung character varying(50) DEFAULT '-'::character varying NOT NULL,
bemerkung text,
alteramstandort smallint,
massnahmebearbeiter character varying(100),
stammanzahl smallint DEFAULT 1 NOT NULL,
user_name character varying(100),
stammumfang integer NOT NULL,
massnahmeart2 character varying(50) DEFAULT '-'::character varying,
faellung_bemerkung character varying(255),
is_last boolean DEFAULT false NOT NULL
);
--
-- TOC entry 242 (class 1259 OID 517201)
-- Name: kontrollen_docs; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE kontrollen_docs (
kontrollid integer NOT NULL,
dokument character varying(255) NOT NULL
);
--
-- TOC entry 263 (class 1259 OID 891308)
-- Name: letzte_kontrollen; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW letzte_kontrollen AS
SELECT k.kontrollid,
k.baumid,
k.datum,
k.kontrollart,
k.datenerfasser AS bearbeiter,
k.baumhoehe,
k.kronendurchmesser,
k.stammumfang,
k.zustand,
k.entwicklungsphase,
k.handlungsbedarf,
k.weitereinaugenschein,
k.eingehendeuntersuchung,
k.massnahme,
k.massnahmeart,
k.faellung,
k.erledigung,
k.bemerkung,
k.alteramstandort,
k.massnahmebearbeiter,
k.stammanzahl,
k.massnahmeart2,
gd.the_geom_25833 AS the_geom,
gd.oid,
gd.baumnummer,
gd.baumart,
gd.wirtschaftseinheit_id,
gd.status,
gd.kontroll_status,
gd.status_bemerkung,
k.faellung_bemerkung
FROM kontrollen k,
grunddaten_baum gd
WHERE ((gd.baumid = k.baumid) AND k.is_last)
ORDER BY k.kontrollid;
--
-- TOC entry 269 (class 1259 OID 936550)
-- Name: daten_baum_mit_letzter_kontrolle; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW daten_baum_mit_letzter_kontrolle AS
SELECT DISTINCT gd.oid,
gd.baumid,
gd.baumnummer,
gd.the_geom_25833 AS the_geom,
w.organisationseinheit,
w.nummer AS wirtschaftseinheit,
w.adressen AS adresse,
w.stadtteil_id,
st.name AS stadtteil,
gd.status,
ba.bezeichnung AS baumart,
ba.bezeichnunglatein AS baumart_latein,
k.kontrollid AS kontroll_id,
k.datum AS kontrolldatum,
k.kontrollart,
k.bearbeiter,
k.baumhoehe,
k.kronendurchmesser,
k.stammanzahl,
k.stammumfang,
k.bemerkung,
kd.dokument,
(((k.handlungsbedarf)::text ||
CASE
WHEN ((k.handlungsbedarf)::text = 'nein'::text) THEN ''::text
WHEN ((k.handlungsbedarf)::text = 'erledigt'::text) THEN ''::text
ELSE (': '::text || (k.erledigung)::text)
END) ||
CASE
WHEN (k.massnahmebearbeiter IS NOT NULL) THEN (' durch '::text || (k.massnahmebearbeiter)::text)
ELSE ''::text
END) AS handlungsbedarfbeschriftung,
k.massnahmeart,
to_char((k.datum)::timestamp with time zone, 'DD.MM.YYYY'::text) AS datumkontrolle,
gd.kontroll_status,
k.massnahmeart2,
k.massnahme,
k.faellung,
k.erledigung,
w.id AS wirtschaftseinheit_id,
k.handlungsbedarf,
(((((((('Nr.'::text || gd.baumnummer) || ' - '::text) || (ba.bezeichnung)::text) || ' (letzte Kontrolle am '::text) || k.datum) || ' durch '::text) || (k.bearbeiter)::text) || ')'::text) AS beschriftung,
gd.status_bemerkung,
CASE
WHEN (gd.kontroll_status = 0) THEN 'durchgeführt'::text
WHEN (gd.kontroll_status = 1) THEN 'nicht durchgeführt'::text
WHEN (gd.kontroll_status = 2) THEN 'Handlungsbedarf'::text
ELSE NULL::text
END AS kontroll_status_beschriftung,
k.faellung_bemerkung,
k.eingehendeuntersuchung,
w.abteilung_id,
ab.name AS abteilung,
ab.name_kurz AS abteilung_kurz
FROM grunddaten_baum gd,
baumarten ba,
grunddaten.stadtteil st,
grunddaten.wirtschaftseinheiten w,
grunddaten.abteilungen ab,
(letzte_kontrollen k
LEFT JOIN kontrollen_docs kd ON ((k.kontrollid = kd.kontrollid)))
WHERE ((((((gd.baumid = k.baumid) AND ((gd.status)::text = 'Baum'::text)) AND ((gd.baumart)::text = (ba.baumart_id)::text)) AND (gd.wirtschaftseinheit_id = w.id)) AND (st.id = w.stadtteil_id)) AND (w.abteilung_id = ab.id));
--
-- TOC entry 279 (class 1259 OID 1793167)
-- Name: mapfile_baum_handlung_notwendig; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW mapfile_baum_handlung_notwendig AS
SELECT DISTINCT db.oid,
db.baumnummer,
db.the_geom,
db.baumart,
db.baumart_latein,
db.kontroll_id,
db.kontrollart,
db.bearbeiter,
db.baumhoehe,
db.kronendurchmesser,
db.stammumfang,
db.bemerkung,
db.handlungsbedarfbeschriftung,
db.datumkontrolle,
db.stammanzahl,
db.massnahmeart,
db.massnahmeart2,
db.faellung,
db.faellung_bemerkung
FROM daten_baum_mit_letzter_kontrolle db
WHERE ((((db.status)::text = 'Baum'::text) AND ((db.kontroll_status)::text = '2'::text)) AND (db.wirtschaftseinheit_id IS NOT NULL));
--
-- TOC entry 283 (class 1259 OID 2154890)
-- Name: auswertung_wiro_baum_handlung_notwendig; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW auswertung_wiro_baum_handlung_notwendig AS
SELECT wi.nummer AS wi,
v.baumnummer,
v.baumart,
v.baumart_latein,
v.kontroll_id,
to_date(v.datumkontrolle, 'DD.MM.YYYY'::text) AS datumkontrolle,
v.kontrollart,
v.bearbeiter,
v.baumhoehe,
v.kronendurchmesser,
v.stammumfang,
(v.bemerkung)::character varying(255) AS bemerkung,
(v.handlungsbedarfbeschriftung)::character varying(255) AS handlungsbedarfbeschriftung,
v.stammanzahl,
v.massnahmeart
FROM (((mapfile_baum_handlung_notwendig v
JOIN kontrollen k ON ((k.kontrollid = v.kontroll_id)))
JOIN grunddaten_baum b ON ((b.baumid = k.baumid)))
LEFT JOIN grunddaten.wirtschaftseinheiten wi ON ((wi.id = b.wirtschaftseinheit_id)));
--
-- TOC entry 270 (class 1259 OID 936555)
-- Name: auswertung_wiro_baum_mit_letzter_kontrolle; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW auswertung_wiro_baum_mit_letzter_kontrolle AS
SELECT v.wirtschaftseinheit AS wi,
v.baumnummer,
v.baumart AS bezeichnung,
v.baumart_latein AS bezeichnunglatein,
v.kontroll_id AS kontrollid,
v.kontrollart,
v.bearbeiter,
v.baumhoehe,
v.kronendurchmesser,
v.stammumfang,
(v.bemerkung)::character varying(255) AS bemerkung,
v.dokument,
(v.handlungsbedarfbeschriftung)::character varying(255) AS handlungsbedarfbeschriftung,
to_date(v.datumkontrolle, 'DD.MM.YYYY'::text) AS datumkontrolle,
v.stammanzahl,
v.massnahmeart
FROM daten_baum_mit_letzter_kontrolle v;
--
-- TOC entry 243 (class 1259 OID 517228)
-- Name: baeume2wirtschaftseinheiten; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW baeume2wirtschaftseinheiten AS
SELECT b.baumid AS wirtschaftseinheitbaumid,
CASE
WHEN (w.nummer IS NULL) THEN 'außerhalb'::character varying
ELSE w.nummer
END AS nummer
FROM (grunddaten_baum b
LEFT JOIN grunddaten.wirtschaftseinheiten w ON (((b.the_geom_25833 OPERATOR(public.&&) w.the_geom_25833) AND public.st_within(b.the_geom_25833, w.the_geom_25833))))
WHERE (w.uebergeordnet = false);
--
-- TOC entry 244 (class 1259 OID 517233)
-- Name: daten_baum; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW daten_baum AS
SELECT b.baumid,
round(((((k.stammumfang)::numeric / (2)::numeric) / (pi())::numeric) / (100)::numeric), 2) AS stammradius,
b.the_geom_25833 AS the_geom,
b.wirtschaftseinheit_id,
b.status
FROM (grunddaten_baum b
LEFT JOIN letzte_kontrollen k ON ((b.baumid = k.baumid)));
--
-- TOC entry 245 (class 1259 OID 517238)
-- Name: baeume_mitte_ausserhalb; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW baeume_mitte_ausserhalb AS
SELECT b.baumid,
b.stammradius,
b.the_geom
FROM (daten_baum b
LEFT JOIN grunddaten.wirtschaftseinheiten w ON (public.st_within(b.the_geom, w.the_geom)))
WHERE (w.id IS NULL);
--
-- TOC entry 246 (class 1259 OID 517243)
-- Name: baeume_schneiden_wirtschaftseinheiten; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW baeume_schneiden_wirtschaftseinheiten AS
SELECT b.baumid AS baum_id,
w.id AS we_id,
public.st_distance(b.the_geom, w.the_geom) AS distance
FROM baeume_mitte_ausserhalb b,
grunddaten.wirtschaftseinheiten w
WHERE public.st_dwithin(b.the_geom, w.the_geom, (b.stammradius)::double precision);
--
-- TOC entry 291 (class 1259 OID 2536365)
-- Name: daten_baum_andere_mit_letzter_kontrolle; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW daten_baum_andere_mit_letzter_kontrolle AS
SELECT DISTINCT gd.oid,
gd.baumid,
gd.baumnummer,
gd.the_geom_25833 AS the_geom,
w.organisationseinheit,
w.nummer AS wirtschaftseinheit,
w.adressen AS adresse,
w.stadtteil_id,
st.name AS stadtteil,
gd.status,
ba.bezeichnung AS baumart,
ba.bezeichnunglatein AS baumart_latein,
k.kontrollid AS kontroll_id,
k.datum AS kontrolldatum,
k.kontrollart,
k.bearbeiter,
k.baumhoehe,
k.kronendurchmesser,
k.stammanzahl,
k.stammumfang,
k.bemerkung,
kd.dokument,
(((k.handlungsbedarf)::text ||
CASE
WHEN ((k.handlungsbedarf)::text = 'nein'::text) THEN ''::text
WHEN ((k.handlungsbedarf)::text = 'erledigt'::text) THEN ''::text
ELSE (': '::text || (k.erledigung)::text)
END) ||
CASE
WHEN (k.massnahmebearbeiter IS NOT NULL) THEN (' durch '::text || (k.massnahmebearbeiter)::text)
ELSE ''::text
END) AS handlungsbedarfbeschriftung,
k.massnahmeart,
to_char((k.datum)::timestamp with time zone, 'DD.MM.YYYY'::text) AS datumkontrolle,
gd.kontroll_status,
k.massnahmeart2,
k.massnahme,
k.faellung,
k.erledigung,
w.id AS wirtschaftseinheit_id,
k.handlungsbedarf,
(((((((((('Status: '::text || (gd.status)::text) || ' - Nr.'::text) || gd.baumnummer) || ' - '::text) || (ba.bezeichnung)::text) || ' (letzte Kontrolle am '::text) || k.datum) || ' durch '::text) || (k.bearbeiter)::text) || ')'::text) AS beschriftung,
gd.status_bemerkung,
CASE
WHEN (gd.kontroll_status = 0) THEN 'durchgeführt'::text
WHEN (gd.kontroll_status = 1) THEN 'nicht durchgeführt'::text
WHEN (gd.kontroll_status = 2) THEN 'Handlungsbedarf'::text
ELSE NULL::text
END AS kontroll_status_beschriftung,
k.faellung_bemerkung,
k.eingehendeuntersuchung,
w.abteilung_id,
ab.name AS abteilung,
ab.name_kurz AS abteilung_kurz
FROM grunddaten_baum gd,
baumarten ba,
grunddaten.stadtteil st,
grunddaten.wirtschaftseinheiten w,
grunddaten.abteilungen ab,
(letzte_kontrollen k
LEFT JOIN kontrollen_docs kd ON ((k.kontrollid = kd.kontrollid)))
WHERE ((((((gd.baumid = k.baumid) AND (NOT ((gd.status)::text = 'Baum'::text))) AND ((gd.baumart)::text = (ba.baumart_id)::text)) AND (gd.wirtschaftseinheit_id = w.id)) AND (st.id = w.stadtteil_id)) AND (w.abteilung_id = ab.id));
--
-- TOC entry 256 (class 1259 OID 517316)
-- Name: regelkontrollen; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE regelkontrollen (
kontrollid integer NOT NULL,
k1 boolean,
k2 boolean,
k3 boolean,
k4 boolean,
k5 boolean,
k6 boolean,
k7 boolean,
k8 boolean,
k9 boolean,
k10 boolean,
k11 boolean,
k12 boolean,
k13 boolean,
k14 boolean,
k15 boolean,
bemerkungen_krone text,
s1 boolean,
s2 boolean,
s3 boolean,
s4 boolean,
s5 boolean,
s6 boolean,
s7 boolean,
s8 boolean,
s9 boolean,
s10 boolean,
s11 boolean,
s12 boolean,
s13 boolean,
s14 boolean,
s15 boolean,
bemerkungen_stamm text,
w1 boolean,
w2 boolean,
w3 boolean,
w4 boolean,
w5 boolean,
w6 boolean,
w7 boolean,
w8 boolean,
bemerkungen_stammfuss text,
wu1 boolean,
wu2 boolean,
wu3 boolean,
bemerkungen_wurzel text,
v1 boolean,
v2 boolean,
v3 boolean,
v4 boolean,
v5 boolean,
v6 boolean,
bemerkungen_umfeld text,
k16 boolean,
k17 boolean,
k18 boolean
);
--
-- TOC entry 3811 (class 0 OID 0)
-- Dependencies: 256
-- Name: COLUMN regelkontrollen.k10; Type: COMMENT; Schema: baumkataster; Owner: -
--
COMMENT ON COLUMN regelkontrollen.k10 IS 'Lichtraumprofilschnitt';
--
-- TOC entry 3812 (class 0 OID 0)
-- Dependencies: 256
-- Name: COLUMN regelkontrollen.k13; Type: COMMENT; Schema: baumkataster; Owner: -
--
COMMENT ON COLUMN regelkontrollen.k13 IS 'Totholzentfernung';
--
-- TOC entry 3813 (class 0 OID 0)
-- Dependencies: 256
-- Name: COLUMN regelkontrollen.k16; Type: COMMENT; Schema: baumkataster; Owner: -
--
COMMENT ON COLUMN regelkontrollen.k16 IS 'Kronenauslichtung leicht (5%)';
--
-- TOC entry 3814 (class 0 OID 0)
-- Dependencies: 256
-- Name: COLUMN regelkontrollen.k17; Type: COMMENT; Schema: baumkataster; Owner: -
--
COMMENT ON COLUMN regelkontrollen.k17 IS 'Kronenauslichtung mittel (10%)';
--
-- TOC entry 3815 (class 0 OID 0)
-- Dependencies: 256
-- Name: COLUMN regelkontrollen.k18; Type: COMMENT; Schema: baumkataster; Owner: -
--
COMMENT ON COLUMN regelkontrollen.k18 IS 'Kronenauslichtung stark (15%)';
--
-- TOC entry 282 (class 1259 OID 1857180)
-- Name: daten_baum_regelkontrolle; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW daten_baum_regelkontrolle AS
SELECT regelkontrollen.kontrollid,
(((((((((((((((((((
CASE
WHEN ((public.count_true(((((((((((((((((((regelkontrollen.k1)::text || (regelkontrollen.k2)::text) || (regelkontrollen.k3)::text) || (regelkontrollen.k4)::text) || (regelkontrollen.k5)::text) || (regelkontrollen.k6)::text) || (regelkontrollen.k7)::text) || (regelkontrollen.k8)::text) || (regelkontrollen.k9)::text) || (regelkontrollen.k10)::text) || (regelkontrollen.k11)::text) || (regelkontrollen.k12)::text) || (regelkontrollen.k13)::text) || (regelkontrollen.k14)::text) || (regelkontrollen.k15)::text) || (regelkontrollen.k16)::text) || (regelkontrollen.k17)::text) || (regelkontrollen.k18)::text)) > 0) OR (regelkontrollen.bemerkungen_krone IS NOT NULL)) THEN 'Krone: '::text
ELSE ''::text
END ||
CASE
WHEN (regelkontrollen.k1 = true) THEN 'Astab-/ausbrüche, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k2 = true) THEN 'Astrisse, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k3 = true) THEN 'Astungswunden/-fäulen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k4 = true) THEN 'Baumfremder_Bew., '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k5 = true) THEN 'Belaubung/Auffälligk., '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k6 = true) THEN 'Fehlentwicklung, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k7 = true) THEN 'Höhlungen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k8 = true) THEN 'Kappungsstellen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k9 = true) THEN 'Kronensicherungen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k10 = true) THEN 'Lichtraumprofil, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k11 = true) THEN 'Pilzbefall, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k12 = true) THEN 'Rindenschäden, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k13 = true) THEN 'Totholzbildung, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k14 = true) THEN 'Vergabelungen, Zwiesel, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k15 = true) THEN 'Wipfeldürre, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k16 = true) THEN 'Kronenauslichtung leicht (5%), '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k17 = true) THEN 'Kronenauslichtung mittel (10%), '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.k18 = true) THEN 'Kronenauslichtung stark (15%), '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.bemerkungen_krone IS NOT NULL) THEN regelkontrollen.bemerkungen_krone
ELSE NULL::text
END) AS krone_text,
((((((((((((((((
CASE
WHEN ((public.count_true((((((((((((((((regelkontrollen.s1)::text || (regelkontrollen.s2)::text) || (regelkontrollen.s3)::text) || (regelkontrollen.s4)::text) || (regelkontrollen.s5)::text) || (regelkontrollen.s6)::text) || (regelkontrollen.s7)::text) || (regelkontrollen.s8)::text) || (regelkontrollen.s9)::text) || (regelkontrollen.s10)::text) || (regelkontrollen.s11)::text) || (regelkontrollen.s12)::text) || (regelkontrollen.s13)::text) || (regelkontrollen.s14)::text) || (regelkontrollen.s15)::text)) > 0) OR (regelkontrollen.bemerkungen_stamm IS NOT NULL)) THEN 'Stamm: '::text
ELSE ''::text
END ||
CASE
WHEN (regelkontrollen.s1 = true) THEN 'Anfahrschäden, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s2 = true) THEN 'Astungswunden, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s3 = true) THEN 'Baumfremder Bew., '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s4 = true) THEN 'Fäulen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s5 = true) THEN 'Gewindestangen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s6 = true) THEN 'Höhlungen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s7 = true) THEN 'Pilzbefall, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s8 = true) THEN 'Rindenschäden, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s9 = true) THEN 'Risse, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s10 = true) THEN 'Schadinsekten, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s11 = true) THEN 'Schrägstand, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s12 = true) THEN 'Stammaustriebe, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s13 = true) THEN 'Verletzungen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s14 = true) THEN 'Wuchsanomalien, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.s15 = true) THEN 'S15, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.bemerkungen_stamm IS NOT NULL) THEN regelkontrollen.bemerkungen_stamm
ELSE NULL::text
END) AS stamm_text,
(((((((((
CASE
WHEN ((public.count_true(((((((((regelkontrollen.w1)::text || (regelkontrollen.w2)::text) || (regelkontrollen.w3)::text) || (regelkontrollen.w4)::text) || (regelkontrollen.w5)::text) || (regelkontrollen.w6)::text) || (regelkontrollen.w7)::text) || (regelkontrollen.w8)::text)) > 0) OR (regelkontrollen.bemerkungen_stammfuss IS NOT NULL)) THEN 'Stammfuß: '::text
ELSE ''::text
END ||
CASE
WHEN (regelkontrollen.w1 = true) THEN 'Adventiv-/Würgewurzeln, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w2 = true) THEN 'Höhlungen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w3 = true) THEN 'Pilzbefall, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w4 = true) THEN 'Rindenschäden, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w5 = true) THEN 'Risse, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w6 = true) THEN 'Verbreiterung, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w7 = true) THEN 'Stockaustriebe, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.w8 = true) THEN 'Wuchsanomalien, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.bemerkungen_stammfuss IS NOT NULL) THEN regelkontrollen.bemerkungen_stammfuss
ELSE NULL::text
END) AS stammfuss_text,
((((
CASE
WHEN ((public.count_true((((regelkontrollen.wu1)::text || (regelkontrollen.wu2)::text) || (regelkontrollen.wu3)::text)) > 0) OR (regelkontrollen.bemerkungen_wurzel IS NOT NULL)) THEN 'Wurzel: '::text
ELSE ''::text
END ||
CASE
WHEN (regelkontrollen.wu1 = true) THEN 'Bodenaufwölbungen, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.wu2 = true) THEN 'Bodenrisse, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.wu3 = true) THEN 'Pilzbefall, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.bemerkungen_wurzel IS NOT NULL) THEN regelkontrollen.bemerkungen_wurzel
ELSE NULL::text
END) AS wurzel_text,
(((((((
CASE
WHEN ((public.count_true(((((((regelkontrollen.v1)::text || (regelkontrollen.v2)::text) || (regelkontrollen.v3)::text) || (regelkontrollen.v4)::text) || (regelkontrollen.v5)::text) || (regelkontrollen.v6)::text)) > 0) OR (regelkontrollen.bemerkungen_umfeld IS NOT NULL)) THEN 'Umfeld: '::text
ELSE ''::text
END ||
CASE
WHEN (regelkontrollen.v1 = true) THEN 'Baugruben/-gräben, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.v2 = true) THEN 'Bodenauf-/-abtrag, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.v3 = true) THEN 'Bodenverdichtung, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.v4 = true) THEN 'Bodenversiegelung, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.v5 = true) THEN 'Freistellung, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.v6 = true) THEN 'Grundwasserabsenkung/-stau, '::text
ELSE ''::text
END) ||
CASE
WHEN (regelkontrollen.bemerkungen_umfeld IS NOT NULL) THEN regelkontrollen.bemerkungen_umfeld
ELSE NULL::text
END) AS umfeld_text
FROM regelkontrollen;
--
-- TOC entry 247 (class 1259 OID 517247)
-- Name: entwicklungsphasen; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE entwicklungsphasen (
id integer NOT NULL,
bezeichnung character varying(100) NOT NULL,
text character varying(100) NOT NULL
);
INSERT INTO entwicklungsphasen VALUES (1, 'Jugendphase', 'Jugendphase ( ca. 1-15 Jahre Standzeit)');
INSERT INTO entwicklungsphasen VALUES (2, 'Reifephase', 'Reifephase (ca. 15-50 Jahre Standzeit)');
INSERT INTO entwicklungsphasen VALUES (3, 'Alterungsphase', 'Alterungsphase ( > ca. 50 Jahre Standzeit)');
--
-- TOC entry 271 (class 1259 OID 936567)
-- Name: export_baum_kml; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW export_baum_kml AS
SELECT DISTINCT gd.oid,
gd.baumnummer,
public.st_astext(public.st_transform(gd.the_geom_25833, 4326)) AS koordinate,
ba.bezeichnung,
ba.bezeichnunglatein,
k.kontrollid,
k.datum,
k.kontrollart,
k.bearbeiter,
k.baumhoehe,
k.kronendurchmesser,
k.stammumfang,
k.bemerkung,
kd.dokument,
(((k.handlungsbedarf)::text ||
CASE
WHEN ((k.handlungsbedarf)::text = 'nein'::text) THEN ''::text
WHEN ((k.handlungsbedarf)::text = 'erledigt'::text) THEN ''::text
ELSE ((((': '::text || (k.erledigung)::text) || ' ( '::text) || (k.massnahmeart)::text) || ')'::text)
END) ||
CASE
WHEN (k.massnahmebearbeiter IS NOT NULL) THEN (' durch '::text || (k.massnahmebearbeiter)::text)
ELSE ''::text
END) AS handlungsbedarfbeschriftung,
to_char((k.datum)::timestamp with time zone, 'DD.MM.YYYY'::text) AS datumkontrolle,
k.stammanzahl,
gd.wirtschaftseinheit_id
FROM grunddaten_baum gd,
baumarten ba,
(letzte_kontrollen k
LEFT JOIN kontrollen_docs kd ON ((k.kontrollid = kd.kontrollid)))
WHERE (((((gd.baumid = k.baumid) AND ((gd.status)::text = 'Baum'::text)) AND ((gd.baumart)::text = (ba.baumart_id)::text)) AND (gd.wirtschaftseinheit_id >= 337)) AND (gd.wirtschaftseinheit_id <= 382))
ORDER BY gd.wirtschaftseinheit_id, gd.baumnummer;
--
-- TOC entry 248 (class 1259 OID 517255)
-- Name: grunddaten_baum_baumid_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE grunddaten_baum_baumid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3816 (class 0 OID 0)
-- Dependencies: 248
-- Name: grunddaten_baum_baumid_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE grunddaten_baum_baumid_seq OWNED BY grunddaten_baum.baumid;
--
-- TOC entry 267 (class 1259 OID 896158)
-- Name: grunddaten_baum_logs; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE grunddaten_baum_logs (
id integer NOT NULL,
baumid integer,
baumnummer_alt integer,
baumnummer integer,
status_alt character varying(10),
status character varying(10),
baumart_alt character varying(255),
baumart character varying(255),
baumart_spezial_alt character varying(255),
baumart_spezial character varying(255),
standort_alt character varying(255),
standort character varying(255),
eigentuemer_fst_alt character varying(25),
eigentuemer_fst character varying(25),
sicherheitserwartung_alt character varying(10),
sicherheitserwartung character varying(10),
amt_alt character varying(100),
amt character varying(100),
gemeinde_alt character varying(100),
gemeinde character varying(100),
sorte_alt character varying(50),
sorte character varying(50),
kontrollintervall_alt character varying(50),
kontrollintervall character varying(50),
the_geom_25833_alt public.geometry,
the_geom_25833 public.geometry NOT NULL,
bearbeiter_alt character varying(100),
bearbeiter character varying(100),
kontroll_status_alt integer,
kontroll_status integer DEFAULT 1 NOT NULL,
wirtschaftseinheit_id_alt integer,
wirtschaftseinheit_id integer,
zeitstempel timestamp without time zone DEFAULT timezone('Europe/Berlin'::text, now()),
bearbeiter_update character varying,
log_grund character varying(6)
);
--
-- TOC entry 266 (class 1259 OID 896156)
-- Name: grunddaten_baum_logs_baumid_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE grunddaten_baum_logs_baumid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3817 (class 0 OID 0)
-- Dependencies: 266
-- Name: grunddaten_baum_logs_baumid_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE grunddaten_baum_logs_baumid_seq OWNED BY grunddaten_baum_logs.baumid;
--
-- TOC entry 265 (class 1259 OID 896154)
-- Name: grunddaten_baum_logs_id_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE grunddaten_baum_logs_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3818 (class 0 OID 0)
-- Dependencies: 265
-- Name: grunddaten_baum_logs_id_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE grunddaten_baum_logs_id_seq OWNED BY grunddaten_baum_logs.id;
--
-- TOC entry 249 (class 1259 OID 517257)
-- Name: handlungsbedarfe_ALT; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE "handlungsbedarfe_ALT" (
id integer NOT NULL,
baumid integer,
baumnummer integer,
status character varying(10) DEFAULT 'Baum'::character varying NOT NULL,
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),
amt character varying(100),
gemeinde character varying(100),
sorte character varying(50),
kontrollintervall character varying(50) DEFAULT 'jährlich'::character varying,
bearbeiter character varying(100) NOT NULL,
kontroll_status integer DEFAULT 1 NOT NULL,
punktnummer_vermessung character varying(64),
wirtschaftseinheit_id integer,
kontrollid integer,
datum date DEFAULT ('now'::text)::date NOT NULL,
kontrollart character varying(20) DEFAULT 'Regelkontrolle'::character varying NOT NULL,
datenerfasser character varying(100) DEFAULT 'Name Baumgutachter'::character varying NOT NULL,
baumhoehe smallint,
kronendurchmesser smallint,
stammumfang numeric,
zustand character varying(50),
entwicklungsphase character varying(20),
handlungsbedarf character varying(8) DEFAULT 'nein'::character varying NOT NULL,
weitereinaugenschein character varying(4) DEFAULT '-'::character varying NOT NULL,
eingehendeuntersuchung character varying(4) DEFAULT '-'::character varying NOT NULL,
massnahme character varying(4) DEFAULT '-'::character varying NOT NULL,
massnahmeart character varying(50) DEFAULT '-'::character varying,
faellung character varying(4) DEFAULT 'nein'::character varying NOT NULL,
erledigung character varying(50) DEFAULT '(3) innerhalb von 6 Monaten'::character varying NOT NULL,
bemerkung text,
alteramstandort smallint,
massnahmebearbeiter character varying(100),
stammanzahl smallint DEFAULT 1 NOT NULL,
user_name character varying(100),
email character varying(100),
email_versendet boolean DEFAULT false,
gedruckt boolean DEFAULT false,
created_at timestamp without time zone DEFAULT now(),
the_geom public.geometry,
faellung_bemerkung character varying(255),
CONSTRAINT enforce_dims_the_geom CHECK ((public.st_ndims(the_geom) = 2)),
CONSTRAINT enforce_geotype_the_geom CHECK (((public.geometrytype(the_geom) = 'POINT'::text) OR (the_geom IS NULL))),
CONSTRAINT enforce_srid_the_geom CHECK ((public.st_srid(the_geom) = 2398))
);
--
-- TOC entry 250 (class 1259 OID 517283)
-- Name: handlungsbedarfe_id_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE handlungsbedarfe_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3819 (class 0 OID 0)
-- Dependencies: 250
-- Name: handlungsbedarfe_id_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE handlungsbedarfe_id_seq OWNED BY "handlungsbedarfe_ALT".id;
--
-- TOC entry 251 (class 1259 OID 517285)
-- Name: kontrollen_kontrollid_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE kontrollen_kontrollid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3820 (class 0 OID 0)
-- Dependencies: 251
-- Name: kontrollen_kontrollid_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE kontrollen_kontrollid_seq OWNED BY kontrollen.kontrollid;
--
-- TOC entry 252 (class 1259 OID 517287)
-- Name: kontrollen_logs; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE kontrollen_logs (
id integer NOT NULL,
kontrollid integer,
baumid integer NOT NULL,
datum date NOT NULL,
kontrollart character varying(20) DEFAULT 'Regelkontrolle'::character varying NOT NULL,
datenerfasser character varying(100) DEFAULT 'Name Baumgutachter'::character varying NOT NULL,
baumhoehe smallint,
kronendurchmesser smallint,
stammumfang numeric,
zustand character varying(50),
entwicklungsphase character varying(20),
bemerkung text,
user_name character varying(100),
logged_at timestamp without time zone DEFAULT timezone('Europe/Berlin'::text, now()),
log_grund character varying(6)
);
--
-- TOC entry 253 (class 1259 OID 517296)
-- Name: kontrollen_logs_id_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE kontrollen_logs_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3821 (class 0 OID 0)
-- Dependencies: 253
-- Name: kontrollen_logs_id_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE kontrollen_logs_id_seq OWNED BY kontrollen_logs.id;
--
-- TOC entry 254 (class 1259 OID 517298)
-- Name: mapfile_baum_gefaellt_alt; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW mapfile_baum_gefaellt_alt AS
SELECT gd.oid,
gd.baumid,
gd.baumnummer,
gd.status,
gd.baumart,
gd.baumart_spezial,
gd.standort,
gd.eigentuemer_fst,
gd.sicherheitserwartung,
gd.amt,
gd.gemeinde,
gd.sorte,
gd.kontrollintervall,
gd.the_geom,
gd.bearbeiter,
gd.kontroll_status,
ba.bezeichnung,
ba.bezeichnunglatein,
gd.status_bemerkung
FROM grunddaten_baum gd,
baumarten ba
WHERE (((gd.status)::text = 'gefällt'::text) AND ((gd.baumart)::text = (ba.baumart_id)::text));
--
-- TOC entry 280 (class 1259 OID 1793172)
-- Name: mapfile_baum_kontrolliert; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW mapfile_baum_kontrolliert AS
SELECT DISTINCT db.oid,
db.baumnummer,
db.the_geom,
db.baumart,
db.baumart_latein,
db.kontroll_id,
db.kontrollart,
db.bearbeiter,
db.baumhoehe,
db.kronendurchmesser,
db.stammumfang,
db.bemerkung,
db.handlungsbedarfbeschriftung,
db.datumkontrolle,
db.stammanzahl,
db.massnahmeart,
db.massnahmeart2,
db.faellung,
db.faellung_bemerkung
FROM daten_baum_mit_letzter_kontrolle db
WHERE ((((db.status)::text = 'Baum'::text) AND ((db.kontroll_status)::text = '0'::text)) AND (db.wirtschaftseinheit_id IS NOT NULL));
--
-- TOC entry 281 (class 1259 OID 1793179)
-- Name: mapfile_baum_nicht_kontrolliert; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW mapfile_baum_nicht_kontrolliert AS
SELECT DISTINCT db.oid,
db.baumnummer,
db.the_geom,
db.baumart,
db.baumart_latein,
db.kontroll_id,
db.kontrollart,
db.bearbeiter,
db.baumhoehe,
db.kronendurchmesser,
db.stammumfang,
db.bemerkung,
db.handlungsbedarfbeschriftung,
db.datumkontrolle,
db.stammanzahl,
db.massnahmeart,
db.massnahmeart2,
db.faellung,
db.faellung_bemerkung
FROM daten_baum_mit_letzter_kontrolle db
WHERE ((((db.status)::text = 'Baum'::text) AND ((db.kontroll_status)::text = '1'::text)) AND (db.wirtschaftseinheit_id IS NOT NULL));
--
-- TOC entry 255 (class 1259 OID 517313)
-- Name: massnahmearten; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE massnahmearten (
bezeichnung character varying(50) NOT NULL,
id integer
);
INSERT INTO massnahmearten VALUES ('Jungbaumpflege', 1);
INSERT INTO massnahmearten VALUES ('Kronenpflege', 2);
INSERT INTO massnahmearten VALUES ('Lichtraumprofil herstellen', 3);
INSERT INTO massnahmearten VALUES ('Totholz entfernen TH 3-10 cm', 4);
INSERT INTO massnahmearten VALUES ('Stamm-/ Stockaustriebe', 7);
INSERT INTO massnahmearten VALUES ('Kopfbaumschnitt', 8);
INSERT INTO massnahmearten VALUES ('Einkürzung KE 1 m', 9);
INSERT INTO massnahmearten VALUES ('Einkürzung KE 2 m', 10);
INSERT INTO massnahmearten VALUES ('Einkürzung KE 3 m', 11);
INSERT INTO massnahmearten VALUES ('Einkürzung KTE', 12);
INSERT INTO massnahmearten VALUES ('Kronensicherung', 15);
INSERT INTO massnahmearten VALUES ('Sondermaßnahmen (s.Bemerkung)', 16);
INSERT INTO massnahmearten VALUES ('Baumfremden Bewuchs entfernen/kappen', 17);
INSERT INTO massnahmearten VALUES ('Totholz entfernen TH Ø > 10 cm', 5);
INSERT INTO massnahmearten VALUES ('Totholz entfernen besonders hoher TH-Besatz', 6);
INSERT INTO massnahmearten VALUES ('Nachbehandlung geschäd. Bäume mit Ständerbildung', 14);
INSERT INTO massnahmearten VALUES ('Sofortmaßnahmen nach z.B. Sturmschäden->Bemerkung', 13);
--
-- TOC entry 296 (class 1259 OID 2551078)
-- Name: negativkontrolle_baum; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE negativkontrolle_baum (
kontroll_id integer NOT NULL,
baum_id integer NOT NULL,
bemerkung text
);
--
-- TOC entry 295 (class 1259 OID 2551063)
-- Name: negativkontrollen; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE negativkontrollen (
kontroll_id integer NOT NULL,
gebiet_id integer NOT NULL,
datum date DEFAULT ('now'::text)::date NOT NULL,
kontrollart character varying(20) DEFAULT 'Regelkontrolle'::character varying NOT NULL,
datenerfasser character varying(100) DEFAULT 'Name wird nach dem Speichern angezeigt'::character varying NOT NULL
);
--
-- TOC entry 294 (class 1259 OID 2551061)
-- Name: negativkontrollen_kontroll_id_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE negativkontrollen_kontroll_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3822 (class 0 OID 0)
-- Dependencies: 294
-- Name: negativkontrollen_kontroll_id_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE negativkontrollen_kontroll_id_seq OWNED BY negativkontrollen.kontroll_id;
--
-- TOC entry 293 (class 1259 OID 2551051)
-- Name: negativkontrollgebiete; Type: TABLE; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE TABLE negativkontrollgebiete (
gebiet_id integer NOT NULL,
kontroll_status integer DEFAULT 1 NOT NULL,
the_geom public.geometry(MultiPolygon,25833) NOT NULL
);
--
-- TOC entry 297 (class 1259 OID 2551091)
-- Name: negativkontrollgebiete2baeume; Type: VIEW; Schema: baumkataster; Owner: -
--
CREATE VIEW negativkontrollgebiete2baeume AS
SELECT g.gebiet_id,
b.baumid AS baum_id
FROM grunddaten_baum b,
negativkontrollgebiete g
WHERE (public.st_within(b.the_geom_25833, g.the_geom) AND ((b.status)::text = 'Baum'::text));
--
-- TOC entry 292 (class 1259 OID 2551049)
-- Name: negativkontrollgebiete_gebiet_id_seq; Type: SEQUENCE; Schema: baumkataster; Owner: -
--
CREATE SEQUENCE negativkontrollgebiete_gebiet_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- TOC entry 3823 (class 0 OID 0)
-- Dependencies: 292
-- Name: negativkontrollgebiete_gebiet_id_seq; Type: SEQUENCE OWNED BY; Schema: baumkataster; Owner: -
--
ALTER SEQUENCE negativkontrollgebiete_gebiet_id_seq OWNED BY negativkontrollgebiete.gebiet_id;
--
-- TOC entry 3558 (class 2604 OID 517369)
-- Name: baumid; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY grunddaten_baum ALTER COLUMN baumid SET DEFAULT nextval('grunddaten_baum_baumid_seq'::regclass);
--
-- TOC entry 3602 (class 2604 OID 896161)
-- Name: id; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY grunddaten_baum_logs ALTER COLUMN id SET DEFAULT nextval('grunddaten_baum_logs_id_seq'::regclass);
--
-- TOC entry 3603 (class 2604 OID 896162)
-- Name: baumid; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY grunddaten_baum_logs ALTER COLUMN baumid SET DEFAULT nextval('grunddaten_baum_logs_baumid_seq'::regclass);
--
-- TOC entry 3592 (class 2604 OID 517370)
-- Name: id; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY "handlungsbedarfe_ALT" ALTER COLUMN id SET DEFAULT nextval('handlungsbedarfe_id_seq'::regclass);
--
-- TOC entry 3574 (class 2604 OID 517371)
-- Name: kontrollid; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY kontrollen ALTER COLUMN kontrollid SET DEFAULT nextval('kontrollen_kontrollid_seq'::regclass);
--
-- TOC entry 3599 (class 2604 OID 517372)
-- Name: id; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY kontrollen_logs ALTER COLUMN id SET DEFAULT nextval('kontrollen_logs_id_seq'::regclass);
--
-- TOC entry 3607 (class 2604 OID 2551066)
-- Name: kontroll_id; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY negativkontrollen ALTER COLUMN kontroll_id SET DEFAULT nextval('negativkontrollen_kontroll_id_seq'::regclass);
--
-- TOC entry 3605 (class 2604 OID 2551054)
-- Name: gebiet_id; Type: DEFAULT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY negativkontrollgebiete ALTER COLUMN gebiet_id SET DEFAULT nextval('negativkontrollgebiete_gebiet_id_seq'::regclass);
--
-- TOC entry 3612 (class 2606 OID 672808)
-- Name: baumarten_baumart_id_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY baumarten
ADD CONSTRAINT baumarten_baumart_id_pkey PRIMARY KEY (baumart_id);
--
-- TOC entry 3614 (class 2606 OID 672810)
-- Name: baumid; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY grunddaten_baum
ADD CONSTRAINT baumid PRIMARY KEY (baumid);
--
-- TOC entry 3623 (class 2606 OID 672812)
-- Name: entwicklungsphasen_id_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY entwicklungsphasen
ADD CONSTRAINT entwicklungsphasen_id_pkey PRIMARY KEY (id);
--
-- TOC entry 3634 (class 2606 OID 2551060)
-- Name: gebiet_id; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY negativkontrollgebiete
ADD CONSTRAINT gebiet_id PRIMARY KEY (gebiet_id);
--
-- TOC entry 3632 (class 2606 OID 896168)
-- Name: grunddaten_baum_logs_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY grunddaten_baum_logs
ADD CONSTRAINT grunddaten_baum_logs_pkey PRIMARY KEY (id);
--
-- TOC entry 3626 (class 2606 OID 672814)
-- Name: handlungsbedarfe_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY "handlungsbedarfe_ALT"
ADD CONSTRAINT handlungsbedarfe_pkey PRIMARY KEY (id);
--
-- TOC entry 3636 (class 2606 OID 2551071)
-- Name: kontroll_id; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY negativkontrollen
ADD CONSTRAINT kontroll_id PRIMARY KEY (kontroll_id);
--
-- TOC entry 3621 (class 2606 OID 672816)
-- Name: kontrollen_docs_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY kontrollen_docs
ADD CONSTRAINT kontrollen_docs_pkey PRIMARY KEY (kontrollid, dokument);
--
-- TOC entry 3628 (class 2606 OID 672818)
-- Name: kontrollen_logs_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY kontrollen_logs
ADD CONSTRAINT kontrollen_logs_pkey PRIMARY KEY (id);
--
-- TOC entry 3619 (class 2606 OID 672820)
-- Name: kontrollid; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY kontrollen
ADD CONSTRAINT kontrollid PRIMARY KEY (kontrollid);
--
-- TOC entry 3630 (class 2606 OID 2444129)
-- Name: massnahmeart_bezeichnung_pkey; Type: CONSTRAINT; Schema: baumkataster; Owner: -; Tablespace:
--
ALTER TABLE ONLY massnahmearten
ADD CONSTRAINT massnahmeart_bezeichnung_pkey PRIMARY KEY (bezeichnung);
--
-- TOC entry 3615 (class 1259 OID 672823)
-- Name: grunddaten_baum_gist; Type: INDEX; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE INDEX grunddaten_baum_gist ON grunddaten_baum USING gist (the_geom);
--
-- TOC entry 3624 (class 1259 OID 672824)
-- Name: handlungsbedarfe_gist; Type: INDEX; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE INDEX handlungsbedarfe_gist ON "handlungsbedarfe_ALT" USING gist (the_geom);
--
-- TOC entry 3616 (class 1259 OID 1708471)
-- Name: is_last_idx; Type: INDEX; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE INDEX is_last_idx ON kontrollen USING btree (is_last);
--
-- TOC entry 3617 (class 1259 OID 672825)
-- Name: kontrollen_baumid_idx; Type: INDEX; Schema: baumkataster; Owner: -; Tablespace:
--
CREATE INDEX kontrollen_baumid_idx ON kontrollen USING btree (baumid);
--
-- TOC entry 3795 (class 2618 OID 932433)
-- Name: update_only_own_datasets; Type: RULE; Schema: baumkataster; Owner: -
--
CREATE RULE update_only_own_datasets AS
ON UPDATE TO kontrollen
WHERE ( SELECT ((kontrollen.datenerfasser)::text <> (new.datenerfasser)::text) AS bool
FROM kontrollen
WHERE (kontrollen.kontrollid = new.kontrollid)
LIMIT 1) DO INSTEAD SELECT (('Der Datensatz darf nur durch den Nutzer '::text || (old.datenerfasser)::text) || ' aktualisiert werden.'::text);
ALTER TABLE kontrollen DISABLE RULE update_only_own_datasets;
--
-- TOC entry 3643 (class 2620 OID 672826)
-- Name: baumkataster_create_new_kontrolle; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER baumkataster_create_new_kontrolle AFTER INSERT ON grunddaten_baum FOR EACH ROW EXECUTE PROCEDURE create_new_kontrolle();
--
-- TOC entry 3648 (class 2620 OID 672827)
-- Name: create_new_regelkontrolle; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER create_new_regelkontrolle AFTER INSERT ON kontrollen FOR EACH ROW EXECUTE PROCEDURE create_new_regelkontrolle();
--
-- TOC entry 3656 (class 2620 OID 672828)
-- Name: document_handlungsbedarf; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER document_handlungsbedarf AFTER UPDATE ON kontrollen FOR EACH ROW EXECUTE PROCEDURE document_handlungsbedarf();
ALTER TABLE kontrollen DISABLE TRIGGER document_handlungsbedarf;
--
-- TOC entry 3644 (class 2620 OID 1343825)
-- Name: log_grunddaten_baum_delete; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER log_grunddaten_baum_delete BEFORE DELETE ON grunddaten_baum FOR EACH ROW EXECUTE PROCEDURE log_grunddaten_baum_delete();
ALTER TABLE grunddaten_baum DISABLE TRIGGER log_grunddaten_baum_delete;
--
-- TOC entry 3642 (class 2620 OID 896148)
-- Name: log_grunddaten_baum_insert; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER log_grunddaten_baum_insert AFTER INSERT ON grunddaten_baum FOR EACH ROW EXECUTE PROCEDURE log_grunddaten_baum_insert();
--
-- TOC entry 3646 (class 2620 OID 896145)
-- Name: log_grunddaten_baum_update; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER log_grunddaten_baum_update AFTER UPDATE ON grunddaten_baum FOR EACH ROW EXECUTE PROCEDURE log_grunddaten_baum_update();
ALTER TABLE grunddaten_baum DISABLE TRIGGER log_grunddaten_baum_update;
--
-- TOC entry 3645 (class 2620 OID 2365442)
-- Name: log_grunddaten_baum_update_neu; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER log_grunddaten_baum_update_neu AFTER UPDATE OF baumnummer, status, baumart, baumart_spezial, standort, sorte, kontrollintervall, the_geom, bearbeiter, kontroll_status, status_bemerkung ON grunddaten_baum FOR EACH ROW EXECUTE PROCEDURE log_grunddaten_baum_update();
--
-- TOC entry 3649 (class 2620 OID 672829)
-- Name: log_kontrollen_insert; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER log_kontrollen_insert AFTER INSERT ON kontrollen FOR EACH ROW EXECUTE PROCEDURE log_kontrollen_insert();
--
-- TOC entry 3653 (class 2620 OID 672830)
-- Name: log_kontrollen_update; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER log_kontrollen_update AFTER UPDATE ON kontrollen FOR EACH ROW EXECUTE PROCEDURE log_kontrollen_update();
ALTER TABLE kontrollen DISABLE TRIGGER log_kontrollen_update;
--
-- TOC entry 3647 (class 2620 OID 1845391)
-- Name: set_last_after_delete; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER set_last_after_delete AFTER DELETE ON kontrollen FOR EACH ROW EXECUTE PROCEDURE set_last_after_delete();
--
-- TOC entry 3652 (class 2620 OID 1845425)
-- Name: set_last_after_insert; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER set_last_after_insert AFTER INSERT ON kontrollen FOR EACH ROW EXECUTE PROCEDURE set_last_after_insert();
--
-- TOC entry 3651 (class 2620 OID 896020)
-- Name: set_new_datum; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER set_new_datum BEFORE INSERT ON kontrollen FOR EACH ROW EXECUTE PROCEDURE set_new_datum();
--
-- TOC entry 3657 (class 2620 OID 930398)
-- Name: update_handlungsbedarf_wegen_regelkontrolle_k10; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_handlungsbedarf_wegen_regelkontrolle_k10 AFTER UPDATE ON regelkontrollen FOR EACH ROW EXECUTE PROCEDURE update_handlungsbedarf_wegen_regelkontrolle_k10();
--
-- TOC entry 3658 (class 2620 OID 930402)
-- Name: update_handlungsbedarf_wegen_regelkontrolle_k13; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_handlungsbedarf_wegen_regelkontrolle_k13 AFTER UPDATE ON regelkontrollen FOR EACH ROW EXECUTE PROCEDURE update_handlungsbedarf_wegen_regelkontrolle_k13();
--
-- TOC entry 3659 (class 2620 OID 2551127)
-- Name: update_kontroll_status_baum; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_kontroll_status_baum AFTER INSERT ON negativkontrollen FOR EACH ROW EXECUTE PROCEDURE update_kontroll_status_gebiet();
--
-- TOC entry 3660 (class 2620 OID 2551130)
-- Name: update_kontroll_status_baum; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_kontroll_status_baum AFTER INSERT ON negativkontrolle_baum FOR EACH ROW EXECUTE PROCEDURE update_kontroll_status_gebiet_handlungsbedarf();
--
-- TOC entry 3650 (class 2620 OID 672831)
-- Name: update_kontroll_status_baum_wegen_aenderung_handlungsbedarf; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_kontroll_status_baum_wegen_aenderung_handlungsbedarf AFTER UPDATE ON kontrollen FOR EACH ROW EXECUTE PROCEDURE update_kontroll_status_baum_wegen_aenderung_handlungsbedarf();
--
-- TOC entry 3655 (class 2620 OID 1845376)
-- Name: update_kontroll_status_baum_wegen_handlungsbedarf_erledigt; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_kontroll_status_baum_wegen_handlungsbedarf_erledigt AFTER UPDATE OF handlungsbedarf ON kontrollen FOR EACH ROW EXECUTE PROCEDURE update_kontroll_status_baum_wegen_handlungsbedarf_erledigt();
--
-- TOC entry 3654 (class 2620 OID 672832)
-- Name: update_kontroll_status_baum_wegen_neuer_kontrolle; Type: TRIGGER; Schema: baumkataster; Owner: -
--
CREATE TRIGGER update_kontroll_status_baum_wegen_neuer_kontrolle AFTER INSERT ON kontrollen FOR EACH ROW EXECUTE PROCEDURE update_kontroll_status_baum_wegen_neuer_kontrolle();
--
-- TOC entry 3639 (class 2606 OID 672833)
-- Name: fk_rk; Type: FK CONSTRAINT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY regelkontrollen
ADD CONSTRAINT fk_rk FOREIGN KEY (kontrollid) REFERENCES kontrollen(kontrollid) ON DELETE CASCADE;
--
-- TOC entry 3638 (class 2606 OID 672838)
-- Name: fkb_kdoc; Type: FK CONSTRAINT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY kontrollen_docs
ADD CONSTRAINT fkb_kdoc FOREIGN KEY (kontrollid) REFERENCES kontrollen(kontrollid) ON DELETE CASCADE;
--
-- TOC entry 3637 (class 2606 OID 672843)
-- Name: fkb_ko; Type: FK CONSTRAINT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY kontrollen
ADD CONSTRAINT fkb_ko FOREIGN KEY (baumid) REFERENCES grunddaten_baum(baumid) ON DELETE CASCADE;
--
-- TOC entry 3640 (class 2606 OID 2551072)
-- Name: fkg_ko; Type: FK CONSTRAINT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY negativkontrollen
ADD CONSTRAINT fkg_ko FOREIGN KEY (gebiet_id) REFERENCES negativkontrollgebiete(gebiet_id) ON DELETE CASCADE;
--
-- TOC entry 3641 (class 2606 OID 2551084)
-- Name: fkk_b; Type: FK CONSTRAINT; Schema: baumkataster; Owner: -
--
ALTER TABLE ONLY negativkontrolle_baum
ADD CONSTRAINT fkk_b FOREIGN KEY (kontroll_id) REFERENCES negativkontrollen(kontroll_id) ON DELETE CASCADE;
-- Completed on 2019-08-26 10:34:46 UTC
--
-- PostgreSQL database dump complete
--
