--- a/flys-backend/doc/schema/postgresql-spatial.sql	Mon Sep 24 07:58:34 2012 +0000
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Mon Sep 24 09:13:29 2012 +0000
@@ -5,7 +5,9 @@
 CREATE TABLE river_axes (
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
-    kind     int             NOT NULL DEFAULT 0
+    kind     int             NOT NULL DEFAULT 0,
+    name     VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
@@ -17,7 +19,9 @@
 CREATE TABLE river_axes_km (
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
-    km       NUMERIC NOT NULL
+    km       NUMERIC NOT NULL,
+    name     VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2);
 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
@@ -29,7 +33,9 @@
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
     km       NUMERIC NOT NULL,
-    z        NUMERIC NOT NULL DEFAULT 0
+    z        NUMERIC NOT NULL DEFAULT 0,
+    name     VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
@@ -41,7 +47,9 @@
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
     kind     VARCHAR(16) NOT NULL,
-    z        NUMERIC DEFAULT 0
+    z        NUMERIC DEFAULT 0,
+    name     VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
@@ -55,7 +63,8 @@
 CREATE TABLE buildings (
     id       int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
-    name     VARCHAR(256)
+    name     VARCHAR(256),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
@@ -69,7 +78,9 @@
     x        int,
     y        int,
     km       NUMERIC NOT NULL,
-    HPGP     VARCHAR(2)
+    HPGP     VARCHAR(2),
+    name     VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
@@ -79,7 +90,9 @@
 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
 CREATE TABLE floodplain (
     id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id)
+    river_id int REFERENCES rivers(id),
+    name     VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2);
 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
@@ -105,7 +118,8 @@
     id int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
     area NUMERIC,
-    name VARCHAR(256)
+    name VARCHAR(256),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
@@ -117,7 +131,9 @@
     id int PRIMARY KEY NOT NULL,
     river_id int REFERENCES rivers(id),
     hws_facility VARCHAR(256),
-    type VARCHAR(256)
+    type VARCHAR(256),
+    name VARCHAR(64),
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
@@ -133,14 +149,7 @@
 -- 121 = Berechnung->Potenziell->BfG
 -- 122 = Berechnung->Potenziell->Land
 --
-CREATE SEQUENCE FLOODMAPS_SEQ;
-CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$
-    BEGIN
-        NEW.id := nextval('floodmaps_seq');
-        RETURN NEW;
-    END;
-$floodmaps_id_func$ LANGUAGE plpgsql;
-
+CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
     id         int PRIMARY KEY NOT NULL,
     river_id   int REFERENCES rivers(id),
@@ -149,13 +158,47 @@
     diff       real,
     count      int,
     area       real,
-    perimeter  real
+    perimeter  real,
+    path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2);
 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
-ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ');
+ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
 
-CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps
-    FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func();
-END;
+
+CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
+CREATE TABLE hydr_boundaries (
+    id         int PRIMARY KEY NOT NULL,
+    river_id   int REFERENCES rivers(id),
+    name       VARCHAR(255),
+    kind       int,
+    path       VARCHAR(256)
+);
+SELECT AddGeometryColumn('hydr_boundaries','geom',31466,'LINESTRING',4);
+ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
+
+
+CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
+CREATE TABLE hydr_boundaries_poly (
+    id         int PRIMARY KEY NOT NULL,
+    river_id   int REFERENCES rivers(id),
+    name       VARCHAR(255),
+    kind       int,
+    path       VARCHAR(256)
+);
+SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31466,'POLYGON',4);
+ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
+
+
+CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
+CREATE TABLE gauge_location (
+    id         int PRIMARY KEY NOT NULL,
+    river_id   int REFERENCES rivers(id),
+    name       VARCHAR(255),
+    path       VARCHAR(256)
+);
+SELECT AddGeometryColumn('gauge_location','geom',31466,'POINT',2);
+ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
+
+COMMIT;