GLE-Postgis-Wege- und Routennetz

-- Schema wegenetz
CREATE SCHEMA wegenetz
  AUTHORIZATION kvwmap;

----------------------------------------------------------------

-- Trigger für Wegabschnitte

CREATE OR REPLACE FUNCTION wegenetz.wegabschnitte()
  RETURNS trigger AS
$BODY$DECLARE
    
BEGIN
 
    IF (TG_OP = 'INSERT') THEN
      IF NEW.kfzverkehr = '2' -- '2' = 'ohne'
       THEN NEW.verbot_kfz = 't';
      END IF;
      IF NEW.kfzverkehr != '2'
       THEN NEW.verbot_kfz = 'f';
      END IF;
RETURN NEW;
    END IF;
    
    IF (TG_OP = 'UPDATE') THEN
      IF NEW.kfzverkehr = '2' -- '2' = 'ohne'
       THEN OLD.verbot_kfz = 't';
      END IF;
      IF NEW.kfzverkehr != '2'
       THEN OLD.verbot_kfz = 'f';
      END IF;
RETURN NEW;
    END IF;

RETURN null;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
----------------------------------------------------------------
 
-- Tabelle Wegabschnitte

CREATE TABLE wegenetz.wegabschnitte
(
  wid serial NOT NULL),
  status character varying(20) NOT NULL DEFAULT 'idee'::character varying,
  baujahr character varying(20),
  wegbreite numeric,
  ausbauart character varying(100) NOT NULL,
  zustbereich boolean NOT NULL DEFAULT true,
  unterh_tra character varying(50),
  kfzverkehr character varying(100),
  wohlbefinden character varying DEFAULT 'keine Beeinträchtigung'::character varying,
  verbot_reiten boolean DEFAULT false,
  verbot_rad boolean DEFAULT false,
  verbot_wandern boolean DEFAULT false,
  dient_sicherheit boolean,
  dient_attraktivitaet boolean,
  dient_ergaenzung boolean,
  bemerkung text,
  letzte_akt date,
  bearbeiter character varying(50),
  verbot_kfz boolean DEFAULT false,
  rechtlicher_status character varying DEFAULT 'öffentlicher Weg'::character varying,
  wegfuehrung character varying
)
WITH (
  OIDS=TRUE
);
SELECT AddGeometryColumn('wegenetz','wegabschnitte','the_geom',25833,'MULTILINESTRING', 2);

ALTER TABLE wegenetz.wegabschnitte
  ADD CONSTRAINT pk_wid PRIMARY KEY(wid);
  
CREATE INDEX gist_wegabschnitte
  ON wegenetz.wegabschnitte
  USING gist
  (the_geom);

CREATE UNIQUE INDEX ixwegab
  ON wegenetz.wegabschnitte
  USING btree
  (wid);

CREATE TRIGGER wegabschnitte
  AFTER INSERT OR UPDATE
  ON wegenetz.wegabschnitte
  FOR EACH ROW
  EXECUTE PROCEDURE wegenetz.wegabschnitte();

-- Tabelle Ausbauarten

CREATE TABLE ausbauarten (
    bezeichnung character varying(50) NOT NULL,
    typ character varying NOT NULL,
    id smallint NOT NULL
);
INSERT INTO ausbauarten VALUES ('unbefestigt', 'unbefestigt', 13);
INSERT INTO ausbauarten VALUES ('befestigt', 'befestigt', 11);
INSERT INTO ausbauarten VALUES ('versiegelt', 'befestigt', 12);
INSERT INTO ausbauarten VALUES ('Betonspurplatten', 'befestigt', 4);
INSERT INTO ausbauarten VALUES ('Betonspurbahn', 'befestigt', 3);
INSERT INTO ausbauarten VALUES ('unbekannt', 'unbekannt', 14);
INSERT INTO ausbauarten VALUES ('Kopfsteinpflaster', 'befestigt', 6);
INSERT INTO ausbauarten VALUES ('Waldweg', 'unbefestigt', 15);
INSERT INTO ausbauarten VALUES ('Asphalt', 'befestigt', 1);
INSERT INTO ausbauarten VALUES ('Betonoberfläche', 'befestigt', 2);
INSERT INTO ausbauarten VALUES ('Kunststeinpflaster', 'befestigt', 5);
INSERT INTO ausbauarten VALUES ('naturbelassener Weg', 'unbefestigt', 7);
INSERT INTO ausbauarten VALUES ('wassergebundene Decke', 'unbefestigt', 10);
INSERT INTO ausbauarten VALUES ('Schotterweg', 'unbefestigt', 9);
INSERT INTO ausbauarten VALUES ('Sand-/Kiesweg', 'unbefestigt', 8);
  
-- Tabelle Zuordnung Abschnitt-Route

CREATE TABLE wegenetz.weg2route
(
  wid integer NOT NULL,
  did integer NOT NULL,
  status character varying(25) NOT NULL DEFAULT 'Teil der Route'::character varying
)
WITH (
  OIDS=TRUE
);

CREATE OR REPLACE RULE delete_weg2route AS
    ON DELETE TO wegenetz.weg2route DO  UPDATE wegenetz.routen SET the_geom = ( SELECT st_union(w.the_geom) AS st_union
           FROM wegenetz.wegabschnitte w, wegenetz.weg2route wr
          WHERE wr.wid = w.wid AND wr.wid <> old.wid AND wr.did = old.did AND wr.status::text = 'Teil der Route'::text AND (w.status::text = 'vorhanden'::text OR w.status::text = 'nicht begangen'::text))
  WHERE routen.did = old.did;

CREATE OR REPLACE RULE insert_weg2route AS
    ON INSERT TO wegenetz.weg2route
   WHERE new.status::text = 'Teil der Route'::text DO ( UPDATE wegenetz.routen r SET the_geom = 
        CASE
            WHEN r.the_geom IS NULL THEN ( SELECT ww.the_geom
               FROM wegenetz.wegabschnitte ww
              WHERE new.wid = ww.wid)
            ELSE st_union(r.the_geom, ( SELECT ww.the_geom
               FROM wegenetz.wegabschnitte ww
              WHERE new.wid = ww.wid))
        END
  WHERE r.did = new.did;
 UPDATE wegenetz.routen r SET letzte_akt = 'now'::text::date
  WHERE r.did = new.did;
);

CREATE OR REPLACE RULE update_weg2route_konzept AS
    ON UPDATE TO wegenetz.weg2route
   WHERE old.status::text = 'Konzeption'::text AND new.status::text = 'Teil der Route'::text DO ( UPDATE wegenetz.routen r SET the_geom = 
        CASE
            WHEN r.the_geom IS NULL THEN ( SELECT ww.the_geom
               FROM wegenetz.wegabschnitte ww
              WHERE new.wid = ww.wid)
            ELSE st_union(r.the_geom, ( SELECT ww.the_geom
               FROM wegenetz.wegabschnitte ww
              WHERE new.wid = ww.wid))
        END
  WHERE r.did = new.did;
 UPDATE wegenetz.routen r SET letzte_akt = 'now'::text::date
  WHERE r.did = new.did;
);

CREATE OR REPLACE RULE update_weg2route_teil AS
    ON UPDATE TO wegenetz.weg2route
   WHERE old.status::text = 'Teil der Route'::text AND new.status::text = 'Konzeption'::text DO ( UPDATE wegenetz.routen r SET the_geom = st_difference(r.the_geom, ( SELECT weg.the_geom
           FROM wegenetz.wegabschnitte weg
          WHERE old.wid = weg.wid))
  WHERE r.did = old.did;
 UPDATE wegenetz.routen r SET letzte_akt = 'now'::text::date
  WHERE r.did = new.did;
);

-- Tabelle Routen
-- Die erstmalige Erstellung der Routen-Datensätze selbst erfolgt zunächst ohne Geometrie

CREATE TABLE wegenetz.routen
(
  did integer NOT NULL,
  kategorie character varying(100) NOT NULL,
  bezeichnung character varying(255) NOT NULL,
  letzte_akt date,
  ersteller character varying,
  ersteller_tel character varying,
  ersteller_email character varying,
  ersteller_web character varying
)
WITH (
  OIDS=TRUE
);
SELECT AddGeometryColumn('wegenetz','routen','the_geom',25833,'MULTILINESTRING', 2);

-- INSERT INTO routen VALUES (1, 'Überregionale Radroute', 'Radweg Hamburg-Rügen', '2016-01-06', 'ADFC', NULL, 'kontakt@adfc.de', NULL, 'www.adfc.de');

CREATE INDEX gist_routen
  ON wegenetz.routen
  USING gist
  (the_geom);

CREATE UNIQUE INDEX ixrouten
  ON wegenetz.routen
  USING btree
  (did);

-- Tabelle Einschränkung der Befahrbarkeit

CREATE TABLE wegenetz.gefahrenstellen
(
  wid integer NOT NULL,
  art character varying(40) NOT NULL
)
WITH (
  OIDS=TRUE
);
SELECT AddGeometryColumn('wegenetz','gefahrenstellen','the_geom',25833,'POINT', 2);

-- Tabelle Arten der Einschränkung

CREATE TABLE gefahrenstellen_arten (
    bezeichnung character varying(40) NOT NULL,
    id smallint NOT NULL
);
INSERT INTO gefahrenstellen_arten VALUES ('Poller', 10);
INSERT INTO gefahrenstellen_arten VALUES ('Querrillen', 20);
INSERT INTO gefahrenstellen_arten VALUES ('saisonal unbefahrbar', 30);
INSERT INTO gefahrenstellen_arten VALUES ('starke Unebenheiten / Löcher', 40);
INSERT INTO gefahrenstellen_arten VALUES ('starkes Gefälle / Steigung', 50);
INSERT INTO gefahrenstellen_arten VALUES ('Stufen / Treppen', 60);
INSERT INTO gefahrenstellen_arten VALUES ('Umlaufschranken', 70);

-- Tabelle Zuordnung Straßenquerung - Abschnitt

CREATE TABLE wegenetz.weg2strassenquerung
(
  wid integer NOT NULL,
  sid integer NOT NULL,
  querungsart character varying NOT NULL
)
WITH (
  OIDS=TRUE
);

-- Tabelle Straßenquerungen

CREATE TABLE wegenetz.strassenquerungen
(
  id serial NOT NULL,
  art character varying NOT NULL
)
WITH (
  OIDS=TRUE
);
SELECT AddGeometryColumn('wegenetz','strassenquerungen','the_geom',25833,'POINT', 2);

CREATE INDEX gist_strassenquerungen
  ON wegenetz.strassenquerungen
  USING gist
  (the_geom);

CREATE OR REPLACE RULE delete_querung AS
    ON DELETE TO wegenetz.strassenquerungen DO  DELETE FROM wegenetz.weg2strassenquerung
  WHERE old.id = weg2strassenquerung.sid;

CREATE OR REPLACE RULE insert_querung AS
    ON INSERT TO wegenetz.strassenquerungen DO  INSERT INTO wegenetz.weg2strassenquerung (wid, sid, querungsart)  SELECT w.wid, new.id - 1, new.art
           FROM wegenetz.wegabschnitte_alt w
          WHERE st_distance(w.the_geom, new.the_geom) < 5::double precision;

CREATE OR REPLACE RULE update_querung AS
    ON UPDATE TO wegenetz.strassenquerungen DO ( DELETE FROM wegenetz.weg2strassenquerung
  WHERE old.id = weg2strassenquerung.sid;
 INSERT INTO wegenetz.weg2strassenquerung (wid, sid, querungsart)  SELECT w.wid, old.id, new.art
           FROM wegenetz.wegabschnitte_alt w
          WHERE st_distance(w.the_geom, new.the_geom) < 5::double precision;
);