GLE-Postgis-Wege- und Routennetz
Aus kvwmap
-- 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_b.weg2route DO UPDATE wegenetz_b.routen SET the_geom = ( SELECT st_union(w.the_geom) AS st_union
FROM wegenetz_b.wegabschnitte w, wegenetz_b.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_b.weg2route
WHERE new.status::text = 'Teil der Route'::text DO ( UPDATE wegenetz_b.routen r SET the_geom =
CASE
WHEN r.the_geom IS NULL THEN ( SELECT ww.the_geom
FROM wegenetz_b.wegabschnitte ww
WHERE new.wid = ww.wid AND (ww.status::text = 'vorhanden'::text OR ww.status::text = 'nicht begangen'::text))
ELSE st_union(r.the_geom, ( SELECT ww.the_geom
FROM wegenetz_b.wegabschnitte ww
WHERE new.wid = ww.wid AND (ww.status::text = 'vorhanden'::text OR ww.status::text = 'nicht begangen'::text)))
END
WHERE r.did = new.did;
UPDATE wegenetz_b.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_b.weg2route
WHERE old.status::text = 'Konzeption'::text AND new.status::text = 'Teil der Route'::text DO ( UPDATE wegenetz_b.routen r SET the_geom =
CASE
WHEN r.the_geom IS NULL THEN ( SELECT ww.the_geom
FROM wegenetz_b.wegabschnitte ww
WHERE new.wid = ww.wid AND (ww.status::text = 'vorhanden'::text OR ww.status::text = 'nicht begangen'::text))
ELSE st_union(r.the_geom, ( SELECT ww.the_geom
FROM wegenetz_b.wegabschnitte ww
WHERE new.wid = ww.wid AND (ww.status::text = 'vorhanden'::text OR ww.status::text = 'nicht begangen'::text)))
END
WHERE r.did = new.did;
UPDATE wegenetz_b.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;
);
