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 wegenetz.ausbauarten (
     bezeichnung character varying(50) NOT NULL,
     typ character varying NOT NULL,
     id smallint NOT NULL
 );
 INSERT INTO wegenetz.ausbauarten VALUES ('unbefestigt', 'unbefestigt', 13);
 INSERT INTO wegenetz.ausbauarten VALUES ('befestigt', 'befestigt', 11);
 INSERT INTO wegenetz.ausbauarten VALUES ('versiegelt', 'befestigt', 12);
 INSERT INTO wegenetz.ausbauarten VALUES ('Betonspurplatten', 'befestigt', 4);
 INSERT INTO wegenetz.ausbauarten VALUES ('Betonspurbahn', 'befestigt', 3);
 INSERT INTO wegenetz.ausbauarten VALUES ('unbekannt', 'unbekannt', 14);
 INSERT INTO wegenetz.ausbauarten VALUES ('Kopfsteinpflaster', 'befestigt', 6);
 INSERT INTO wegenetz.ausbauarten VALUES ('Waldweg', 'unbefestigt', 15);
 INSERT INTO wegenetz.ausbauarten VALUES ('Asphalt', 'befestigt', 1);
 INSERT INTO wegenetz.ausbauarten VALUES ('Betonoberfläche', 'befestigt', 2);
 INSERT INTO wegenetz.ausbauarten VALUES ('Kunststeinpflaster', 'befestigt', 5);
 INSERT INTO wegenetz.ausbauarten VALUES ('naturbelassener Weg', 'unbefestigt', 7);
 INSERT INTO wegenetz.ausbauarten VALUES ('wassergebundene Decke', 'unbefestigt', 10);
 INSERT INTO wegenetz.ausbauarten VALUES ('Schotterweg', 'unbefestigt', 9);
 INSERT INTO wegenetz.ausbauarten VALUES ('Sand-/Kiesweg', 'unbefestigt', 8);
 
 -- Tabelle KFZ-Verkehr
 
 CREATE TABLE wegenetz.wegabschnitt_kfzverkehr (
    id integer NOT NULL,
    bezeichnung character varying
 );
 INSERT INTO wegenetz.wegabschnitt_kfzverkehr VALUES (5, 'Land- und forstwirtschaftlicher Verkehr');
 INSERT INTO wegenetz.wegabschnitt_kfzverkehr VALUES (2, 'ohne');
 INSERT INTO wegenetz.wegabschnitt_kfzverkehr VALUES (1, 'unbekannt');
 INSERT INTO wegenetz.wegabschnitt_kfzverkehr VALUES (3, 'Allgemein befahrbar');
 INSERT INTO wegenetz.wegabschnitt_kfzverkehr VALUES (4, 'Anliegerverkehr');
 
 -- 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 wegenetz.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 wegenetz.gefahrenstellen_arten (
     bezeichnung character varying(40) NOT NULL,
     id smallint NOT NULL
 );
 INSERT INTO wegenetz.gefahrenstellen_arten VALUES ('Poller', 10);
 INSERT INTO wegenetz.gefahrenstellen_arten VALUES ('Querrillen', 20);
 INSERT INTO wegenetz.gefahrenstellen_arten VALUES ('saisonal unbefahrbar', 30);
 INSERT INTO wegenetz.gefahrenstellen_arten VALUES ('starke Unebenheiten / Löcher', 40);
 INSERT INTO wegenetz.gefahrenstellen_arten VALUES ('starkes Gefälle / Steigung', 50);
 INSERT INTO wegenetz.gefahrenstellen_arten VALUES ('Stufen / Treppen', 60);
 INSERT INTO wegenetz.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 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 w
           WHERE st_distance(w.the_geom, new.the_geom) < 5::double precision;
 );